Prev: summarize data in access 2007
Next: marvendas@gmail.com Kit completo deSolenóides ( solenoid ) + chicote Para Câmbio automatico hidramatico Audi A3 Vw Golf gti turbo 95314
From: Karin on 19 Feb 2010 16:17
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!
From: Jerry Whittle on 19 Feb 2010 17:15
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];


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Karin" wrote:

> 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!
 | 
Pages: 1
Prev: summarize data in access 2007
Next: marvendas@gmail.com Kit completo deSolenóides ( solenoid ) + chicote Para Câmbio automatico hidramatico Audi A3 Vw Golf gti turbo 95314