Query Problems

  • Thread starter Thread starter gr
  • Start date Start date
G

gr

Hi, I have a table named tblName which contains three
fields: NameID, Names and Dt

I also have 4 tables showing a Time Recording entry for
each Name. There are 4 tables because there are 4
categories.

I want to make a query to display the Name, Dt and the
total of hours for each category.
e.g.

Name Dt Admin Project Sales Service
John 01/01/2004 2 3 1
John 02/01/2004 1 1
Sophie 01/01/2004 3

The SQL Code of the query is the following, but IS NOT
WORKING!!

SELECT tblName.NameID, tblName.Names, tblName.Dt,
tblTRAdmin.Hours AS tblTRAdmin_Hours, tblTRPrj.PrjHours,
tblTRSales.Hours AS tblTRSales_Hours, tblTRService.Hours
AS tblTRService_Hours
FROM (((tblName LEFT JOIN tblTRAdmin ON tblName.NameID =
tblTRAdmin.NameID) LEFT JOIN tblTRPrj ON tblName.NameID =
tblTRPrj.NameID) LEFT JOIN tblTRSales ON tblName.NameID =
tblTRSales.NameID) LEFT JOIN tblTRService ON
tblName.NameID = tblTRService.NameID;

The problem is that I think some entries are been
duplicated, since I have not all the NameID for every
category (I guess) the proof is that if make a query for
Admin (only) and I get 82 records which is ok. if I filter
the 4 areas query I get 117 records for Admin, by
reviewing the filter results I realize that some admin
entries hours have been duplicated in the query result.

How can I make the 4 categories in one query without
outputting wrong info?
thx!
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If the info is the same for all time tables, but just different
categories, don't put the time info in separate tables, just add the
column "Category" to one time table and put all the info into that
table w/ the correct Category. Then the query is simpler:

SELECT N.NameID, N.Names, N.Dt, T.Category, T.Hours
FROM tblName As N LEFT JOIN tblTime As T ON N.NameID = T.NameID

If you wanted the category hours spread out in separate columns:

SELECT N.NameID, N.Names, N.Dt,
IIf(T.Category="A",T.Hours,0) AS AdminHours,
IIf(T.Category="P",T.Hours,0) AS ProjectHours,
IIf(T.Category="S",T.Hours,0) AS SalesHours,
IIf(T.Category="V",T.Hours,0) AS ServiceHours
FROM tblName As N LEFT JOIN tblTime As T ON N.NameID = T.NameID

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQEZFnoechKqOuFEgEQKSzACg0yGrkYjGe6Rf/P++K5W2VKbcNZQAnA9V
baAyuRRVtPmZFcAJX+d9LLzT
=zqpy
-----END PGP SIGNATURE-----

Hi, I have a table named tblName which contains three
fields: NameID, Names and Dt

I also have 4 tables showing a Time Recording entry for
each Name. There are 4 tables because there are 4
categories.

I want to make a query to display the Name, Dt and the
total of hours for each category.
e.g.

Name Dt Admin Project Sales Service
John 01/01/2004 2 3 1
John 02/01/2004 1 1
Sophie 01/01/2004 3
Click to expand...

< SNIP >
 
Back
Top