I can't keep data from duplicating itself.

[UniqueTII]

I have a query that pulls data from 8 different tables, each containing data about a student's earnings during a 2 week pay period. The tables are connected by the students' SSN's, but since each student may have more than one job, their SSN can't be the primary key. My problem is that when I start adding fields for their earnings for each pay period, I get duplicate listings for many of the students. I'm so confused I don't really know how to explain the problem, so if there's anything at all you can think of that may help, let me know.

 

[jfgambit]

Can you explain your table structure to us. You said there are 8 different tables each containing information on a students work activities. It soulds like you may not have a Normalized database, this would be the first step in determining your problem.

Can you please describe the tables?

 

[UniqueTII]

Each table has the student's SSN, work program (work study, community service, etc...), departmental earnings for that period, and work study earnings for that period.

Since not all students that currently have earnings are listed in every report, the tables in the query are left joined to the newest table so the fields where the students don't have any earnings listed are blank.

Here is the code from the query:

SELECT [010604].SSN, [010604].NAME, [010604].[DEPT EARNINGS], [010604].[CWS EARNINGS], [091903].[DEPT EARNINGS], [091903].[CWS EARNINGS], [102103].[DEPT EARNINGS], [102103].[CWS EARNINGS], [110603].[DEPT EARNINGS], [110603].[CWS EARNINGS], [111903].[DEPT EARNINGS], [111903].[CWS EARNINGS], [120303].[DEPT EARNINGS], [120303].[CWS EARNINGS], [121313].[DEPT EARNINGS], [121313].[CWS EARNINGS]
FROM ((((((010604 LEFT JOIN 091903 ON [010604].SSN = [091903].SSN) LEFT JOIN 100603 ON [010604].SSN = [100603].SSN) LEFT JOIN 102103 ON [010604].SSN = [102103].SSN) LEFT JOIN 110603 ON [010604].SSN = [110603].SSN) LEFT JOIN 111903 ON [010604].SSN = [111903].SSN) LEFT JOIN 120303 ON [010604].SSN = [120303].SSN) LEFT JOIN 121313 ON [010604].SSN = [121313].SSN;

There are currently 1379 students in the program, but that query gives me 10779 results.

 

[jfgambit]

It seems to me you have a design problem. Is there a sample db you can post so that we can look at combining information from tables to Normalize the database?