Hello friends,
I have designed a crosstab query to track our production by workday of the month. In order to include all days and create reports, I linked the transaction dates to tblWorkDays which has 2 fields. The PK is the date and the other field is the number of the workday for the month. My crosstab query has column headings, 1,2,3 etc. up to 23 which pair with the work day of the month. Is there a way to use both the workday and date as column heading in the crosstab query? Here is the SQL of the query:
PARAMETERS Forms![StartDateEndDate]![Start Date] DateTime, Forms![StartDateEndDate]![End Date] DateTime;
TRANSFORM Sum([Transaction History].TNXQTY_15) AS SumOfTNXQTY_15
SELECT [Transaction History].PRTNUM_15, Sum(Format(([TNXQTY_15]*[COST_15]),"Currency")) AS [Extended Cost], Sum([Transaction History].TNXQTY_15) AS SumOfTNXQTY_151
FROM ([Transaction History] LEFT JOIN [Part Master] ON [Transaction History].PRTNUM_15 = [Part Master].PRTNUM_01) INNER JOIN tblWorkDays ON [Transaction History].TNXDTE_15 = tblWorkDays.WorkDate
WHERE ((([Transaction History].TNXDTE_15) Between [Forms]![StartDateEndDate]![Start Date] And [Forms]![StartDateEndDate]![End Date]) AND (([Transaction History].TNXCDE_15)="I") AND (([Transaction History].STKID_15)="12"))
GROUP BY [Transaction History].PRTNUM_15
PIVOT tblWorkDays.WorkDay In ("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23");
Doyce
Posted by: winberry.doyce@roadsysinc.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar