Cross-tab issue "absolute" to "relative" change?? 1

[valkyry]

Hello,
I did a search and found a post that dhookum pointed to a Dynamic Crosstab ... FAQ703-5466

I was hoping I can get some help with that.
I don't understand if you need the form for the date.

I have a report that calls out a query which is based on two cross-tab queries. So the columns are always changing.
And I have to manually change the query whenever the "ww" changes in the cross-tab query. It changes weekly.

Here's the SQL view of the query:
SELECT [8WeekCombined-BookingsCounts].Rep AS RepGroup, [8WeekCombined-BookingsCounts].OrderFrom, [8WeekCombined-BookingsCounts].[34] AS [8 Wks Ago Count], [8WeekCombined-BookingsAmount].[34] AS [8 Wks Ago Amount], [8WeekCombined-BookingsCounts].[33] AS [7 Wks Ago Count], [8WeekCombined-BookingsCounts].[33] AS [7 Wks Ago Amount], [8WeekCombined-BookingsCounts].[32] AS [6 Wks Ago Count], [8WeekCombined-BookingsAmount].[32] AS [6 Wks Ago Amount], [8WeekCombined-BookingsCounts].[31] AS [5 Wks Ago Count], [8WeekCombined-BookingsAmount].[31] AS [5 Wks Ago Amount], [8WeekCombined-BookingsCounts].[30] AS [4 Wks Ago Count], [8WeekCombined-BookingsAmount].[30] AS [4 Wks Ago Amount], [8WeekCombined-BookingsCounts].[29] AS [3 Wks Ago Count], [8WeekCombined-BookingsAmount].[29] AS [3 Wks Ago Amount], [8WeekCombined-BookingsCounts].[28] AS [2 Wks Ago Count], [8WeekCombined-BookingsAmount].[28] AS [2 Wks Ago Amount], [8WeekCombined-BookingsCounts].[27] AS [Last Wk Count], [8WeekCombined-BookingsAmount].[27] AS [Last Wk Amount], [8WeekCombined-BookingsCounts].[8 Wk Ttl No Of Orders], [8WeekCombined-BookingsAmount].[8 Wk Ttl Amt]

Can anyone help me please??

 

[dhookom]

It would really help to know your table structure and what you want to display in your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[valkyry]

Well, the tables are extractions of the data set that is required for the report.

One is to extract the dollar amounts and another is for the number of orders.

Here's the Extract query for the Number of Orders:
SELECT IIf([RepGroup] Is Null,Trim([AddressLine1]),[RepGroup]) AS Rep, IIf([OrderFromDesc] Is Null," NA",[OrderFromDesc]) AS OrderFrom, Format([ML_UDF_SOH_PO_DATE],"ww") AS Weeks, Count(SO_03SOHistoryHeader.SalesOrderNumber) AS NoOfOrders, Count(SO_03SOHistoryHeader.SalesOrderNumber) AS TtlNoOfOrders INTO BookingsOrderCountsSummary
FROM OrderFrom, ((SO_03SOHistoryHeader LEFT JOIN SO_90_UDF_Sales_Order ON SO_03SOHistoryHeader.SalesOrderNumber = SO_90_UDF_Sales_Order.SalesOrderNumber) LEFT JOIN ARD_SalespersonMasterfile ON (SO_03SOHistoryHeader.DivisionNumber = ARD_SalespersonMasterfile.Division) AND (SO_03SOHistoryHeader.SalespersonCode = ARD_SalespersonMasterfile.SalespersonNumber)) LEFT JOIN RepGroups ON ARD_SalespersonMasterfile.SalesManager = RepGroups.SalesManager
WHERE (((IIf([ML_UDF_SOH_PO_DATE] Is Null,[OrderDate],[ML_UDF_SOH_PO_DATE])) Between WeekStart()-57 And WeekStart()-2))
GROUP BY IIf([RepGroup] Is Null,Trim([AddressLine1]),[RepGroup]), IIf([OrderFromDesc] Is Null," NA",[OrderFromDesc]), Format([ML_UDF_SOH_PO_DATE],"ww");

From the table the above extract query I have the cross-tab for the counts and amounts:
TRANSFORM Sum(BookingsOrderCountsSummary.NoOfOrders) AS SumOfNoOfOrders
SELECT BookingsOrderCountsSummary.Rep, BookingsOrderCountsSummary.OrderFrom, Sum(BookingsOrderCountsSummary.TtlNoOfOrders) AS [8 Wk Ttl No Of Orders]
FROM BookingsOrderCountsSummary
GROUP BY BookingsOrderCountsSummary.Rep, BookingsOrderCountsSummary.OrderFrom
PIVOT BookingsOrderCountsSummary.Weeks;


Then I link the two cross-tab queries for the originally listed query results which is used in a report.

The report is Grouped By the RepGroup from query

RepGroup1
OrderFrom 8WksAgo_NoOfOrders 8WksAgrderAmount ... LastWk_NoOfOrders LastWk_OrderAmount TtlNoOfOrders TtlOrderAmount

 

[dhookom]

Change your maketable query to

SELECT Nz([RepGroup],Trim([AddressLine1]) AS Rep, 
   Nz([OrderFromDesc]," NA") AS OrderFrom, [ML_UDF_SOH_PO_DATE], 
   Count(SO_03SOHistoryHeader.SalesOrderNumber) AS NoOfOrders, 
   Count(SO_03SOHistoryHeader.SalesOrderNumber) AS TtlNoOfOrders 
INTO BookingsOrderCountsSummary
FROM OrderFrom, ((SO_03SOHistoryHeader LEFT JOIN 
   SO_90_UDF_Sales_Order ON SO_03SOHistoryHeader.SalesOrderNumber = SO_90_UDF_Sales_Order.SalesOrderNumber) 
   LEFT JOIN ARD_SalespersonMasterfile ON (SO_03SOHistoryHeader.DivisionNumber = ARD_SalespersonMasterfile.Division) 
   AND (SO_03SOHistoryHeader.SalespersonCode = ARD_SalespersonMasterfile.SalespersonNumber)) 
   LEFT JOIN RepGroups ON ARD_SalespersonMasterfile.SalesManager = RepGroups.SalesManager
WHERE Nz([ML_UDF_SOH_PO_DATE],[OrderDate]) Between WeekStart()-57 And WeekStart()-2
GROUP BY Nz([RepGroup],Trim([AddressLine1]), Nz([OrderFromDesc]," NA"), [ML_UDF_SOH_PO_DATE];

Then use the FAQ to create dynamic relative column headings based on an ending date entered into a form control. Your column heading expression would be something like:
ColHead: "W" & DateDiff("ww",[ML_UDF_SOH_PO_DATE], Forms!frmDates!txtEndDate)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[valkyry]

ok, will try that.

is the form control needed?
I have the startdate and an enddate of Between WeekStart()-57 And WeekStart()-2 as you saw.

So I'm not getting what the form control is doing.

anyway, i'll try it and let you know what happens.

thank you!

 

[dhookom]

My solution would not have used the functions. It may work without the control on the form. If it doesn't work, you may have to tell us something about the function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[valkyry]

Ok, the part I'm having difficulties with is the fact that there is no form for the date ranges.
The date ranges are preset.

"create dynamic relative column headings based on an ending date entered into a form control. Your column heading expression would be something like:
ColHead: "W" & DateDiff("ww",[ML_UDF_SOH_PO_DATE], Forms!frmDates!txtEndDate)"

I'm not getting where this expression would be used. Again, I don't have a form.
I have queries that create the tables based on the preset date range for 8 weeks back. Then other queries that are the cross-tabs for the week to week columns.
Then finally the report that shows the data for the columns. The column headings do not change. Just the data for each column "buckets"


Here are the date functions I am using:
Function WeekStart()
WeekStart = Date - (Weekday(Date) - 2)
If Date - (Weekday(Date) - 2) = 0 Then WeekStart = WeekStart - 7
End Function

Function Yesterday()
Yesterday = Date - 1
Select Case Weekday(Yesterday)
Case 1
Yesterday = Yesterday - 2
End Select
End Function

Function WorkDay(tmpDate As Date) As Boolean
If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then WorkDay = False Else WorkDay = True
End Function

 

[dhookom]

See what you get if you just replace the form/control to Date()"
ColHead: "W" & DateDiff("ww",[ML_UDF_SOH_PO_DATE], Date())

This should get your weeks based on the current date.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[valkyry]

Let me just thank you for trying to help ...

It looks like it's working.

I got W1, W2, W3, W4, W5, W6, W7, W8 so it's looking good.

I have to finalize.

 

[dhookom]

Glad to hear you are making progress.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[valkyry]

1st - let me say SORRY, i was being an idiot! I couldn't see what you were talking about and got hung-up with the "form" thing!

NOW I see what you were telling me and it WORKS GREAT!


THANK YOU THANK YOU THANK YOU VERY VERY VERY MUCH!!!!

YOU ARE TOTALLY AWESOME!

CAN'T THANK YOU ENOUGH!

This was much better then all the functions by far!!

 

[dhookom]

Glad to hear. I like to keep solutions as simple as possible. I enjoy coding but if there are more efficient and maintainable methods, I try to use them.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]