Duane,
I solved this using a DLookup back to the table. Thanks for the push in the right direction!
Doyce
---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :
Duane,
That's pretty cool, however I don't think it will work in my situation because I only want the report to show workdays M-F. I have a table where for the current month, workday 1 = May 4, 2= May 5, 3 = May 6 and etc. Is there a way I can use the idea of dynamic labels and link the text boxes back to the workday table?
Doyce
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Doyce,
I use the solution for dynamic monthly column headings in report at http://www.tek-tips.com/faqs.cfm?fid=5466. This should be easily converted to days rather than months.
Let me know if you have any questions.
Duane Hookom, MVP
MS Access
I use the solution for dynamic monthly column headings in report at http://www.tek-tips.com/faqs.cfm?fid=5466. This should be easily converted to days rather than months.
Let me know if you have any questions.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 1 Jun 2015 13:20:45 -0700
Subject: [MS_AccessPros] Crosstab Query Labels
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
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 1 Jun 2015 13:20:45 -0700
Subject: [MS_AccessPros] Crosstab Query Labels
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 (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar