If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can I do this in a query?
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! |
#2
|
|||
|
|||
Can I do this in a query?
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! |
Thread Tools | |
Display Modes | |
|
|