How to link Crosstab Query to other query problem...

[Phil Smith]

I have a fairly complex query. Based around an ITEM_ID, I link various
tables and queries, to get various counts totals, status, etc.

One of the queries, 4monthtrailingqry is a crosstab for trailing sales.
It returns the Item_ID, then 4 months of history. If I were to run it
today, I would get:

Item_id 2009_10 2009_11 2009_12 2010_01

As each month passes, when I run it , I will get different headers, so I
can't drop each header into a query grid.

I tried dropping 4monthtrailingqry.* into the grid, and while that
worked, not only did it drop my four months of data, but it also dropped
the Item_Id. This is redundant for my purposes. I could wrap all of
this in yet another query, and strip this out, but is there an easier
way to include the Coloum and Value portions without the Row portion of
this Crosstab?

Thanx

Phil

 

[Duane Hookom]

Typically if you have a query question you might want to share the SQL view.
However, if you want to create a crosstab with dynamic date interval columns,
consider the solution found at http://www.tek-tips.com/faqs.cfm?fid=5466.

Let us know if you have any questions.

 

[Phil Smith]

I could post the SQL. but it is large and ugly, and mostly not relevant
to the question.

In terms of the suggested solution:

I know how to build columns like that. Initially, my columns were built
that way, and the person who wants this report wants to see discrete
months as column headers. So while this would be a great solution for
most people, it won't work for extremely picky types, like the one I am
building this report for.

Any other ideas?

 

[Duane Hookom]

The solution I suggested does display "discrete months as column headers."

 

[Phil Smith]

I think there is some confusion here somewhere. Probably mine...

"This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from
the same month as the ending date on your form. Mth1 is the previous
month etc."

I need to see 2010-01, 2009-12, 2009-11, etc.

I see further down where it suggests creating labels for purposed of
report headers, but I don't think this helps me. My final result must
be an Excel spreadsheet.

 

[Duane Hookom]

If you want this to go to Excel then just export a standard crosstab. Or,
just pull the data into Excel and build a pivot table.

 

[Phil Smith]

I was hoping to build this as process which I could completely automate.
On a weekly basis, this report would be emailed using SendObject in an
Excel format.

This crosstab is only one piece of the report, which currently looks
like the following: The problem I am running into is that with the
constantly changing headers of CurrentMonthandPreviousThree, I can only
add it to the query using CurrentMonthandPreviousThree.*. The field I
use to link this query and all of the others appears redundantly when I
do this. My goal is to suppress this one field somehow.

SELECT item_types.name AS ItemType, brand.name AS Brand, item.item_id,
item.old_sku, Color.value AS Color, Size.value AS [Size],
item.short_desc, First_PO_Date.MinOfest_delivery_date AS FirstPO,
IIf([item]![purchase_report]="N","NO","YES") AS Purchase,
item_warehouse_link.onhand_qty,
[item_warehouse_link]![po_qty]+[item_warehouse_link]![wo_qty] AS
[NHSWO-PO],
[item_warehouse_link_1]![po_qty]+[item_warehouse_link_1]![wo_qty] AS
[YL-WO-PO], item_warehouse_link.onorder_qty AS [Open Order],
item.active, CurrentMonthandPreviousThree.*
FROM CurrentMonthandPreviousThree INNER JOIN (item_types INNER JOIN
(item_warehouse_link AS item_warehouse_link_1 INNER JOIN
(item_warehouse_link INNER JOIN ([Size] RIGHT JOIN (First_PO_Date RIGHT
JOIN (Color RIGHT JOIN (brand INNER JOIN item ON brand.brand_id =
item.brand_id) ON Color.item_id = item.item_id) ON First_PO_Date.item_id
= item.item_id) ON Size.item_id = item.item_id) ON
item_warehouse_link.item_id = item.item_id) ON
item_warehouse_link_1.item_id = item.item_id) ON item_types.type_id =
item.item_type) ON CurrentMonthandPreviousThree.item_id = item.item_id
WHERE (((brand.name)="Flip") AND ((item.item_id)<>0) AND
((item.active)<>"N") AND ((item_warehouse_link.company_id)=1) AND
((item_warehouse_link.warehouse_id)=1) AND
((item_warehouse_link_1.company_id)=1) AND
((item_warehouse_link_1.warehouse_id)=73))
ORDER BY item_types.name, item.old_sku, item.item_id, Color.value,
Size.value;

 

[John Spencer]

Since you only need ItemID to appear one time, drop it from the SELECT clause
and change the ORDER BY clause to use the ItemId from
CurrentMonthandPreviousThree

SELECT item_types.name AS ItemType, brand.name AS Brand

====== REMOVE This from the SELECT clause ======
, item.item_id
====== REMOVE This from the SELECT clause ======

, item.old_sku, Color.value AS Color, Size.value AS [Size], item.short_desc,
First_PO_Date.MinOfest_delivery_date AS FirstPO,
IIf([item]![purchase_report]="N","NO","YES") AS Purchase,
item_warehouse_link.onhand_qty,
[item_warehouse_link]![po_qty]+[item_warehouse_link]![wo_qty] AS [NHSWO-PO],
[item_warehouse_link_1]![po_qty]+[item_warehouse_link_1]![wo_qty] AS
[YL-WO-PO], item_warehouse_link.onorder_qty AS [Open Order], item.active,
CurrentMonthandPreviousThree.*
FROM CurrentMonthandPreviousThree INNER JOIN (item_types INNER JOIN
(item_warehouse_link AS item_warehouse_link_1 INNER JOIN (item_warehouse_link
INNER JOIN ([Size] RIGHT JOIN (First_PO_Date RIGHT JOIN (Color RIGHT JOIN
(brand INNER JOIN item ON brand.brand_id = item.brand_id) ON Color.item_id =
item.item_id) ON First_PO_Date.item_id = item.item_id) ON Size.item_id =
item.item_id) ON item_warehouse_link.item_id = item.item_id) ON
item_warehouse_link_1.item_id = item.item_id) ON item_types.type_id =
item.item_type) ON CurrentMonthandPreviousThree.item_id = item.item_id
WHERE (((brand.name)="Flip") AND ((item.item_id)<>0) AND ((item.active)<>"N")
AND ((item_warehouse_link.company_id)=1) AND
((item_warehouse_link.warehouse_id)=1) AND
((item_warehouse_link_1.company_id)=1) AND
((item_warehouse_link_1.warehouse_id)=73))
ORDER BY item_types.name, item.old_sku

====== Change to this ======
, CurrentMonthandPreviousThree item_id
====== Change to this ======

, Color.value, Size.value;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I was hoping to build this as process which I could completely automate.
On a weekly basis, this report would be emailed using SendObject in an
Excel format.

This crosstab is only one piece of the report, which currently looks
like the following: The problem I am running into is that with the
constantly changing headers of CurrentMonthandPreviousThree, I can only
add it to the query using CurrentMonthandPreviousThree.*. The field I
use to link this query and all of the others appears redundantly when I
do this. My goal is to suppress this one field somehow.

SELECT item_types.name AS ItemType, brand.name AS Brand, item.item_id,
item.old_sku, Color.value AS Color, Size.value AS [Size],
item.short_desc, First_PO_Date.MinOfest_delivery_date AS FirstPO,
IIf([item]![purchase_report]="N","NO","YES") AS Purchase,
item_warehouse_link.onhand_qty,
[item_warehouse_link]![po_qty]+[item_warehouse_link]![wo_qty] AS
[NHSWO-PO],
[item_warehouse_link_1]![po_qty]+[item_warehouse_link_1]![wo_qty] AS
[YL-WO-PO], item_warehouse_link.onorder_qty AS [Open Order],
item.active, CurrentMonthandPreviousThree.*
FROM CurrentMonthandPreviousThree INNER JOIN (item_types INNER JOIN
(item_warehouse_link AS item_warehouse_link_1 INNER JOIN
(item_warehouse_link INNER JOIN ([Size] RIGHT JOIN (First_PO_Date RIGHT
JOIN (Color RIGHT JOIN (brand INNER JOIN item ON brand.brand_id =
item.brand_id) ON Color.item_id = item.item_id) ON First_PO_Date.item_id
= item.item_id) ON Size.item_id = item.item_id) ON
item_warehouse_link.item_id = item.item_id) ON
item_warehouse_link_1.item_id = item.item_id) ON item_types.type_id =
item.item_type) ON CurrentMonthandPreviousThree.item_id = item.item_id
WHERE (((brand.name)="Flip") AND ((item.item_id)<>0) AND
((item.active)<>"N") AND ((item_warehouse_link.company_id)=1) AND
((item_warehouse_link.warehouse_id)=1) AND
((item_warehouse_link_1.company_id)=1) AND
((item_warehouse_link_1.warehouse_id)=73))
ORDER BY item_types.name, item.old_sku, item.item_id, Color.value,
Size.value;

If you want this to go to Excel then just export a standard crosstab. Or,
just pull the data into Excel and build a pivot table.