Can I do this in a query?

  • Thread starter Thread starter Karin
  • Start date Start date
K

Karin

tblNames: id (auto), name
tblSvcCode: id, name (there are only 2, 0=nonbill; 1=bill)
tblWIP: id(auto), EmpID (linkto tblNames), SVCcode(link to tblsvccode),WIP
WIP looks like:
id Emp ID SVC Code WIP
1 1 1 55
1 1 1 20
2 1 0 60
3 2 1 75
4 2 0 89
5 3 1 40
6 3 0 12

Can I get a single query to show me:
tblNames.Name Billable WIP NonBillable WIP
alpha (1) 75 60
etc
(all names will have multiple entires for billable and nonbillable, just
trying to keep this simple)
Thanks!
 
First create a query called something like qryWipBill that looks like this:

SELECT tblNames.[Name],
tblSvcCode.[name],
TblWIP.WIP
FROM tblSvcCode
INNER JOIN (TblWIP INNER JOIN tblNames
ON TblWIP.[Emp ID] = tblNames.ID)
ON tblSvcCode.id = TblWIP.[SVC Code];

Then create a crosstab like so:

TRANSFORM Sum(qryWipBill.[WIP]) AS SumOfWIP
SELECT qryWipBill.[Name], Sum(qryWipBill.[WIP]) AS [Total Of WIP]
FROM qryWipBill
GROUP BY qryWipBill.[Name]
PIVOT qryWipBill.[name];
 
Back
Top