Crosstab Query

[palnud]

I have tried several ways to build this crosstab query, but can't get results.
Result Desired: A crosstab query with row heading-partID and column heading orderDate, the value is quantity. A query for each customer. This isn't a problem so far, here's the twist: I want to sum the past due quantities (quanties due before the start date)in a colummn for each partID. The past due date range goes back 30 days before the schedule start date.

Table: sales
Fields: customerID, partID, orderDate, quantity

I have created a query that gives me the data I want, and I can create the correct crosstab for a date range. The user selects a date and the crosstab shows five days of schedule beginning on date selected. The problem is the past due quantities.

Select Query used to create the crosstab:

PARAMETERS [forms]![frmselect]![txtchoice] Text ( 255 ), [forms]![frmselect]![txtbegdate] DateTime, [forms]![frmselect]![txtenddate] DateTime;
SELECT DISTINCTROW SALES.ORDER_ID, SALES.PART_ID, SALES.TARGET_COMPLETION AS [ORDER DATE], SALES.PROJECTED_COMPLETION AS [SHIPPING DATE], [SALES]![ORDER_QTY]-[SALES]![QTY_SHIPPED] AS QUANTITY, SALES.QTY_SHIPPED, SALES.TARGET_COMPLETION
FROM SALES
WHERE (((SALES.ORDER_ID)=[forms]![frmselect]![txtchoice]) AND ((SALES.TARGET_COMPLETION) Between [forms]![frmselect]![txtbegdate] And [forms]![frmselect]![txtenddate]) AND (([SALES]![ORDER_QTY]-[SALES]![QTY_SHIPPED])>0))
ORDER BY SALES.ORDER_ID, SALES.PART_ID;

Thanks for any ideas.

 

[dhookom]

You can add another Row Heading column with an expression like:
Past30: Abs(Sum((OrderDate >= DateAdd(&quot;d&quot;,-30, [forms]![frmselect]![txtbegdate]) AND OrderDate < [forms]![frmselect]![txtbegdate]) * Quantity))

Set the Totals to &quot;Expression&quot; and Crosstab to &quot;Row Heading&quot;.

I think I got all the ()s correct.

I wouldn't limit the date of the records until the crosstab.
You should also consider changing your Column Headings from the date to
ColHead:&quot;Day&quot; & DateDiff(&quot;D&quot;,[forms]![frmselect]![txtbegdate],[OrderDate])

Then set the column headings property to:
&quot;Day0&quot;,&quot;Day1&quot;,...&quot;Day4&quot;
Day0 will return the same date as txtBegDate and Day4 will be 4 days later. This produces a consistent set of columns that can easily be reported.



Duane
MS Access MVP

 

[palnud]

Duane

Sorry it took so long for me to respond. I've been trying to get this to work, but can't. The basic problem seems to be when I try to accumulate the past dues they get filtered out because I'm only showing the last five days of the date range on the crosstab. I've tried using a make-table query, but to no avail. Let's start new.

I want to create a cross-tab query/report that looks at 35 days, shows the last five days in the crosstab with a column that sums the quantities before the last five days.

I have one table calles Sales. The fields I will be using are Customer_ID, Part_ID, Target_Date, and Quantity. Grouped by Customer_ID, and Part_id for Rows; Target_Date for columns.

I would appreciate any direction you could give me. Thanks alot for your time.

David

 

[dhookom]

With my solution above, you will not need to set a criteria since the Column Headings property does this for you. The extra Row Heading value will get you 5-35 days previous.

You have only provided what you want for column heading and value. What did you want in for Row Headings?

Duane
MS Access MVP

 

[palnud]

Row headings are Customer_ID and Part_ID. And the other would be the Past_Due sum. I didn't understand the first part of your original post. It looks like a date is being multiplied by a numeric quantitiy. I couldn't get it to work for whatever reason. Should I continue working with your first reply?
Thanks

 

[dhookom]

The first part of the reply multiplies Quantity times an expression that returns True/-1 or False/0. It checks a date field and determines if it falls between specific dates. If it does, it returns true/-1 which is multiplied by the Quantity. When these are summed, the Abs() function is applied to make the sum or the quantities into a positive number.

Duane
MS Access MVP

 

[palnud]

No wonder I didn't understand it! Thanks. I'll work on your original suggestions again. Now that I understand the formula; that will be of help.