Cross tab heading update issue

[mattc66 via AccessMonster.com]

I have built a cross tab query and I used the wizard. The Column Heading is a
Due Date.

When I built it looked great, putting all the due dates across the top.
However these dates have now changed, but the cross tab reflects the old due
dates and has not updated to reflect the new due dates.

How do I automate the update of the cross tab header?

Thanks
Matt

 

[Guest]

Post your SQL by opening the query in design view and then right clicking in
the open space where the table(s) show. Select SQL view, highlight, copy,
and paste in a post.

 

[mattc66 via AccessMonster.com]

Here is a copy of the SQL Corsstab

TRANSFORM Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS [SumOfQTY DUE]
SELECT [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND], Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS
[Total QTY DUE]
FROM ([qryIR_CHINA_RMS_ON ORDER] INNER JOIN ICSTOCK_C2 ON [qryIR_CHINA_RMS_ON
ORDER].ITEM = ICSTOCK_C2.ITEM) INNER JOIN qryICLOC_C2_OnHand ON ICSTOCK_C2.
ITEM = qryICLOC_C2_OnHand.ITEM
GROUP BY [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND]
PIVOT Format([DUE DATE],"Short Date");

Post your SQL by opening the query in design view and then right clicking in
the open space where the table(s) show. Select SQL view, highlight, copy,
and paste in a post.

I have built a cross tab query and I used the wizard. The Column Heading is a
Due Date.

[quoted text clipped - 7 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

Hi Karl,

I found out that the cross tab is updated. Its the form that I have based on
the query that is not reflecting the change.

I am now looking into how to update the form.

Matt

Post your SQL by opening the query in design view and then right clicking in
the open space where the table(s) show. Select SQL view, highlight, copy,
and paste in a post.

I have built a cross tab query and I used the wizard. The Column Heading is a
Due Date.

[quoted text clipped - 7 lines]

Thanks
Matt

 

[Duane Hookom]

I would use relative date headings. Assuming you have a form and want to see
14 columns that end on a date entered into a text box on the form. You could
use SQL like:

PARAMETERS [Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS [SumOfQTY DUE]
SELECT [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND], Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS
[Total QTY DUE]
FROM ([qryIR_CHINA_RMS_ON ORDER] INNER JOIN ICSTOCK_C2 ON
[qryIR_CHINA_RMS_ON
ORDER].ITEM = ICSTOCK_C2.ITEM) INNER JOIN qryICLOC_C2_OnHand ON ICSTOCK_C2.
ITEM = qryICLOC_C2_OnHand.ITEM
GROUP BY [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND]
PIVOT "D" & DateDiff("d", Forms!frmDates!txtEndDate, [DUE DATE]) IN
("D0,"D1","D2",..."D13");

The D0 column would be for qty due on the date entered into the text box.
D13 would be 13 days earlier.

--
Duane Hookom
MS Access MVP

Here is a copy of the SQL Corsstab

TRANSFORM Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS [SumOfQTY DUE]
SELECT [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND], Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS
[Total QTY DUE]
FROM ([qryIR_CHINA_RMS_ON ORDER] INNER JOIN ICSTOCK_C2 ON
[qryIR_CHINA_RMS_ON
ORDER].ITEM = ICSTOCK_C2.ITEM) INNER JOIN qryICLOC_C2_OnHand ON
ICSTOCK_C2.
ITEM = qryICLOC_C2_OnHand.ITEM
GROUP BY [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND]
PIVOT Format([DUE DATE],"Short Date");

Post your SQL by opening the query in design view and then right clicking
in
the open space where the table(s) show. Select SQL view, highlight, copy,
and paste in a post.

I have built a cross tab query and I used the wizard. The Column Heading
is a
Due Date.

[quoted text clipped - 7 lines]

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com

 

[mattc66 via AccessMonster.com]

Hi Duane,

Forgive my ignorance, but where would I place the below SQL statement. The
data sent was from the query.

Matt

I would use relative date headings. Assuming you have a form and want to see
14 columns that end on a date entered into a text box on the form. You could
use SQL like:

PARAMETERS [Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS [SumOfQTY DUE]
SELECT [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND], Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS
[Total QTY DUE]
FROM ([qryIR_CHINA_RMS_ON ORDER] INNER JOIN ICSTOCK_C2 ON
[qryIR_CHINA_RMS_ON
ORDER].ITEM = ICSTOCK_C2.ITEM) INNER JOIN qryICLOC_C2_OnHand ON ICSTOCK_C2.
ITEM = qryICLOC_C2_OnHand.ITEM
GROUP BY [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND]
PIVOT "D" & DateDiff("d", Forms!frmDates!txtEndDate, [DUE DATE]) IN
("D0,"D1","D2",..."D13");

The D0 column would be for qty due on the date entered into the text box.
D13 would be 13 days earlier.

Here is a copy of the SQL Corsstab

[quoted text clipped - 24 lines]

 

[mattc66 via AccessMonster.com]

Hi Duane,

I just want to display the data from an exsisting table. No user input will
be performed.

If I uderstand your suggest below, I would create 13 text boxes on my form
and call them "D0" and "D1" and "D2" and etc.. "D13".

When I do that and add the SQL suggestion the DX flds are blank.

I would use relative date headings. Assuming you have a form and want to see
14 columns that end on a date entered into a text box on the form. You could
use SQL like:

PARAMETERS [Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS [SumOfQTY DUE]
SELECT [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND], Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS
[Total QTY DUE]
FROM ([qryIR_CHINA_RMS_ON ORDER] INNER JOIN ICSTOCK_C2 ON
[qryIR_CHINA_RMS_ON
ORDER].ITEM = ICSTOCK_C2.ITEM) INNER JOIN qryICLOC_C2_OnHand ON ICSTOCK_C2.
ITEM = qryICLOC_C2_OnHand.ITEM
GROUP BY [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND]
PIVOT "D" & DateDiff("d", Forms!frmDates!txtEndDate, [DUE DATE]) IN
("D0,"D1","D2",..."D13");

The D0 column would be for qty due on the date entered into the text box.
D13 would be 13 days earlier.

Here is a copy of the SQL Corsstab

[quoted text clipped - 24 lines]

 

[Duane Hookom]

What is your latest SQL view?

--
Duane Hookom
MS Access MVP

Hi Duane,

I just want to display the data from an exsisting table. No user input
will
be performed.

If I uderstand your suggest below, I would create 13 text boxes on my form
and call them "D0" and "D1" and "D2" and etc.. "D13".

When I do that and add the SQL suggestion the DX flds are blank.

I would use relative date headings. Assuming you have a form and want to
see
14 columns that end on a date entered into a text box on the form. You
could
use SQL like:

PARAMETERS [Forms]!frmDates![txtEndDate] DateTime;
TRANSFORM Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS [SumOfQTY DUE]
SELECT [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND], Sum([qryIR_CHINA_RMS_ON ORDER].[QTY DUE]) AS
[Total QTY DUE]
FROM ([qryIR_CHINA_RMS_ON ORDER] INNER JOIN ICSTOCK_C2 ON
[qryIR_CHINA_RMS_ON
ORDER].ITEM = ICSTOCK_C2.ITEM) INNER JOIN qryICLOC_C2_OnHand ON
ICSTOCK_C2.
ITEM = qryICLOC_C2_OnHand.ITEM
GROUP BY [qryIR_CHINA_RMS_ON ORDER].ITEM, ICSTOCK_C2.DESCRIPTION,
qryICLOC_C2_OnHand.AVAILABLE, qryICLOC_C2_OnHand.ALLOCATED,
qryICLOC_C2_OnHand.[ON HAND]
PIVOT "D" & DateDiff("d", Forms!frmDates!txtEndDate, [DUE DATE]) IN
("D0,"D1","D2",..."D13");

The D0 column would be for qty due on the date entered into the text box.
D13 would be 13 days earlier.

Here is a copy of the SQL Corsstab

[quoted text clipped - 24 lines]

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com