Cross tab heading update issue

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

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
 
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.
 
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");


KARL said:
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.
Click to expand...
[quoted text clipped - 7 lines]
Thanks
Matt
Click to expand...
Click to expand...
 
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

KARL said:
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.
Click to expand...
[quoted text clipped - 7 lines]
Thanks
Matt
Click to expand...
Click to expand...
 
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



mattc66 via AccessMonster.com said:
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");


KARL said:
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.
Click to expand...
[quoted text clipped - 7 lines]
Thanks
Matt
Click to expand...
Click to expand...

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

Message posted via AccessMonster.com
Click to expand...
 
Hi Duane,

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

Matt

Duane said:
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
Click to expand...
[quoted text clipped - 24 lines]
Click to expand...
 
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.

Duane said:
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
Click to expand...
[quoted text clipped - 24 lines]
Click to expand...
 
What is your latest SQL view?

--
Duane Hookom
MS Access MVP

mattc66 via AccessMonster.com said:
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.

Duane said:
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
Click to expand...
[quoted text clipped - 24 lines]
Thanks
Matt
Click to expand...
Click to expand...

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

Message posted via AccessMonster.com
Click to expand...
 
Back
Top