By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,324 Members | 1,967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,324 IT Pros & Developers. It's quick & easy.

newbie trying to query multible spreadsheets into one table in Access 2003

P: 1
I am using Access 2003 on Windows XP

I'm a school teacher and need help achieving the following:

I have three spreadsheets with various information in them. But, they have a common cell named "Student ID".

To be specific this is what my three spreadsheets consist of is this:

Spreadsheet 1
Student ID
Last Name
First Name
Teacher
Grade

Spreadsheet 2
Student ID
FCAT Username
FCAT Password

Spreadsheet 3
Student ID
Pearson Username
Pearson Password



THis is what I have done -- and perhaps someone can let me know where I went wrong or a better way to do it.

I created a blank table in Microsoft Access named "Student Password". That table had all of the above cell names as fields.


I then imported the files (using file/import/external data) and created relationships with the tables so that they all related to the Student ID.

So far so good I think.

NExt I created a query using design view and selected the first table above and the newly created blank table.

When I run the query nothing happens. my blank table stays blank.



I then created queries using the "Student ID" as the relation.
Jan 7 '07 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,661
Maybe an easier solution would be to link to the three spreadsheets which enables you to use them like (basic) Access tables.
Next create an APPEND query based on a linked query of all three tables
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [Student Password]
  2. ([Student ID],[Last Name],[First Name],[Teacher],[Grade],
  3.  [FCAT Username],[FCAT Password],
  4.  [Pearson Username],[Pearson Password])
  5. SELECT S1.[Student ID],
  6.        S1.[Last Name],
  7.        S1.[First Name],
  8.        S1.[Teacher],
  9.        S1.[Grade],
  10.        S2.[FCAT Username],
  11.        S2.[FCAT Password],
  12.        S3.[Pearson Username],
  13.        S3.[Pearson Password]
  14. FROM ([Spreadsheet 1] AS S1
  15.   INNER JOIN [Spreadsheet 2] AS S2
  16.     ON S1.[Student ID]=S2.[Student ID])
  17.   INNER JOIN [Spreadsheet 3] AS S3
  18.     ON S1.[Student ID]=S3.[Student ID]
Your names may be different but I've assumed exactly as you've posted.
Jan 8 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.