Need help with query / relationships

[BukHix]

I am trying to consolidate data from a proprietary database that was built for a DOS based point of sale application. The database is basically a series of flat files. I have managed to get the data into SQL Server but I am having trouble getting the Joins correct so that the data shows the way I need.

For instance here are three tables

The results I would like to get is to list all the employees along with a total of Worked Hours and the amount of Cars Worked on.

The Query looks like this

SELECT     
	dbo.EENAME.EECODE, 
	dbo.EENAME.NAME, 
	dbo.vHoursWorked.TOTAL_HRS, 
	dbo.vCars.CarCount

FROM      dbo.vHoursWorked INNER JOIN
                dbo.EENAME ON dbo.vHoursWorked.EECODE = dbo.EENAME.EECODE INNER JOIN
                dbo.vCars ON dbo.EENAME.EECODE = dbo.vCars.EECODE

The results I get only show one employee over and over (EECODE is the Employee number).

What I am doing wrong?

 

[ScottGem]

You need Interim queries. You create a query on vCars that groups on EECODE and SUMS CarCount. You then get another query to group by EECODE and sums total Hours. Join those two together on EECODE to get one record per employee.

 

[BukHix]

I think you are right and that is how I had it, well almost. I had some additional relationships added to the interim queries that was goofing it up. After I read your post I went back and took another look, which prompted me to remove the relationships I didn't need.

Anyway I think I am getting closer but I am still getting way more records then I should be...... Uh Oh its 5:00 and time to go so I guess I will look at it again tomorrow with fresh eyes. Thanks for the help

 

[AndyVM]

One note, the field with the name DATE needs to change. DATE is a reserved word.

It's all about the grouping.


Try:

SELECT 
EENAME.EECODE, 
EENAME.NAME, 
Sum(vHoursWorked.TOTAL_HRS) AS SumOfTOTAL_HRS, 
Sum(vCars.CarCount) AS SumOfCarCount
FROM 
(
EENAME 
INNER JOIN vHoursWorked ON EENAME.EECODE = vHoursWorked.EECODE
) INNER JOIN vCars ON EENAME.EECODE = vCars.EECODE
GROUP BY EENAME.EECODE, EENAME.NAME;