Basic Access Question

[Bliss]

I'll admit I'm somewhat new to Access, and I have trouble getting my head
around the application.
I created a database that has 7 related sheetsd. One master sheet has
student names and student numbers, and the other 6 sheets contain test score
data (there is a student number column on each). However, when I design a
query, the table only gives me the students whose number appears on all seven
sheets, and some studentsnets are on each sheet (except the master sheet).
Of the 7000 students on the mater sheet, only 126 appear on the query table.

Can anyone point me in the right direction so that I get all the student
numbers from the master sheet and data (if it's there) from the other sheets
in a query.

Thanks in advance . . .

 

[Jeff Boyce]

A couple of observations...

First, Access doesn't have "sheets" -- that's what spreadsheets have, and
Access is a relational database.

Next, why are there "6 [other] sheets" with test score data? What is
different about the 6? What is the same?

It may be that your data structure is committing spreadsheet on Access (this
is NOT a good idea!).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bliss]

Thank you very much for your response . . .

I'm using Access to compile a master table of student data for export to an
Excel applictaion I have written (conditional formats, charts, etc.). Once
the master query is complete, I export it to the Excel file (it becomes the
master lookup file for the Excel sheet). However, I need a clean master
table in Access, and I update student test score data throughout the year,
hence the 6 linked Excel workbooks. One table contains all student data from
the state test, another 4 tables from local tests in different subject areas.
The Excel tables are linked in Access. Problem is 10% of our students take
the local tests but moved in after the state tests, etc. The only thing in
common on all tables is a student number, but not every student on the master
list is on every table.

The Excel application lets teachers import their class lists and then see
all sorts of charts on test score data (which comes from different sources).

Basically, I want Access to take my master list of student numbers and
populate all data fields in a master query. All the numbers on the master
list are not necessarily represented on the other data sheets. It does this
for me, but only the student numbers that are represented on all tables.

hope this helps--as you can probably tell, as is typical for school
districts, we have to rely on some home-grown solutions, and I know with some
programming savvy that this cna be done much more efficiently. However, the
Excel sheet I created works well enough, but is a bit too processor heavy for
some district computers. Cleaning the data in Access before putting it in
Excel reduces the Excel lookup by about 80% and speeds up the workbook.

A couple of observations...

First, Access doesn't have "sheets" -- that's what spreadsheets have, and
Access is a relational database.

Next, why are there "6 [other] sheets" with test score data? What is
different about the 6? What is the same?

It may be that your data structure is committing spreadsheet on Access (this
is NOT a good idea!).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'll admit I'm somewhat new to Access, and I have trouble getting my head
around the application.
I created a database that has 7 related sheetsd. One master sheet has
student names and student numbers, and the other 6 sheets contain test
score
data (there is a student number column on each). However, when I design a
query, the table only gives me the students whose number appears on all
seven
sheets, and some studentsnets are on each sheet (except the master sheet).
Of the 7000 students on the mater sheet, only 126 appear on the query
table.

Can anyone point me in the right direction so that I get all the student
numbers from the master sheet and data (if it's there) from the other
sheets
in a query.

Thanks in advance . . .

 

[John W. Vinson]

I'll admit I'm somewhat new to Access, and I have trouble getting my head
around the application.
I created a database that has 7 related sheetsd. One master sheet has
student names and student numbers, and the other 6 sheets contain test score
data (there is a student number column on each). However, when I design a
query, the table only gives me the students whose number appears on all seven
sheets, and some studentsnets are on each sheet (except the master sheet).
Of the 7000 students on the mater sheet, only 126 appear on the query table.

Can anyone point me in the right direction so that I get all the student
numbers from the master sheet and data (if it's there) from the other sheets
in a query.

Thanks in advance . . .

I agree with all Jeff's points - it looks to me like you have a different test
score table for each test (you now have six, next month you might have eight,
etc.). If each Student can take one or more Tests, and each Test can be taken
by one or more Students, then a proper relational design would have three
tables:

Students
StudentID <Primary Key>
LastName
FirstName
<other bio data>

Tests
TestNo <Primary Key>
TestName
<other info about the test>

Scores
StudentID <link to Students>
TestNo <link to Tests>
Score
<other info about this student's performance on this test>

You can create a Tests table and then run six append queries to populate
Scores.

With your current spreadsheetish design you'll need a rather complex query -
I'd suggest a UNION query to string the six test queries together, and then a
Left Outer Join query to connect your student table to it. Alternatively, you
could use a Form with six Subforms (one for each test), or a Report with six
Subreports. It'll be a LOT simpler if you normalize the data though!

 

[Brett Stone]

Bliss,
I believe I understand where you are trying to get with this and I agree
with Jeff that you might want to look a bit further into how you are trying
to accomplish it.

What he is asking is are the 6 non-master spreadsheets basically the same
fields (excel columns) with different data for the specific tests. In other
words are you gathering the same information about each test like student,
date, location, score. Or do you have different fields in each specific test
table?

Keep in mind that once you build the data structure properly, a simple query
will be used to provide the appropriate data to your excel export so it does
not need the data separated at the table level unless the data is not similar
for each test.

-Brett