crosstab query values

[klp via AccessMonster.com]

I have a crosstab query that I've created for a Rolling 12 month report. It
works great but now I need to add another value to the query. I know that you
can only have 1 value field, 1 column field and multiple row fields. My
current value field is ShipQty. I need another value field of Price. Is there
a way around to get this output?

Thanks in Advance

Kim P

 

[Duane Hookom]

You can possibly use the solution for multivalue crosstabs at
http://www.tek-tips.com/faqs.cfm?fid=4524.

 

[klp via AccessMonster.com]

That works, but now my months are not in order. How can I get them to be in
chronological order so that they show up correctly on my report.

You can possibly use the solution for multivalue crosstabs at
http://www.tek-tips.com/faqs.cfm?fid=4524.

I have a crosstab query that I've created for a Rolling 12 month report. It
works great but now I need to add another value to the query. I know that you

[quoted text clipped - 5 lines]

Kim P

 

[Duane Hookom]

Are months used as row or column headings? If column headings, try the
solution found at http://www.tek-tips.com/faqs.cfm?fid=5466
--
Duane Hookom
Microsoft Access MVP

That works, but now my months are not in order. How can I get them to be in
chronological order so that they show up correctly on my report.

You can possibly use the solution for multivalue crosstabs at
http://www.tek-tips.com/faqs.cfm?fid=4524.

I have a crosstab query that I've created for a Rolling 12 month report. It
works great but now I need to add another value to the query. I know that you

[quoted text clipped - 5 lines]

Kim P

 

[klp via AccessMonster.com]

So I do I keep my crosstab query as is, with the previous post you sent me
and also apply this solution?

I did it without the previous solution and I am not getting any data in my
query. I'm only getting my customer information, not their sales amounts and
qty's.

Are months used as row or column headings? If column headings, try the
solution found at http://www.tek-tips.com/faqs.cfm?fid=5466

That works, but now my months are not in order. How can I get them to be in
chronological order so that they show up correctly on my report.

[quoted text clipped - 7 lines]

 

[Duane Hookom]

I don't know how to apply what you have to what you want since you haven't
shared any SQL or much else.
--
Duane Hookom
Microsoft Access MVP

So I do I keep my crosstab query as is, with the previous post you sent me
and also apply this solution?

I did it without the previous solution and I am not getting any data in my
query. I'm only getting my customer information, not their sales amounts and
qty's.

Are months used as row or column headings? If column headings, try the
solution found at http://www.tek-tips.com/faqs.cfm?fid=5466

That works, but now my months are not in order. How can I get them to be in
chronological order so that they show up correctly on my report.

[quoted text clipped - 7 lines]

Kim P

 

[klp via AccessMonster.com]

Well here is my SQL. This is with the 2nd solution. I only get CustNo &
CustomerName to show up. Nothing else. Which this doesn't simplify my main
problem of putting together more than one value.

PARAMETERS [Forms]![frmReportRFMRolling12]![StartDate] DateTime, [Forms]!
[frmReportRFMRolling12]![EndDate] DateTime;
TRANSFORM Sum(qryReportRFMRolliingTotals.ShipQty) AS SumOfShipQty
SELECT qryReportRFMRolliingTotals.CustNo, qryReportRFMRolliingTotals.
CustomerName, qryReportRFMRolliingTotals.DOB, Max(qryReportRFMRolliingTotals.
InvDate) AS MaxOfInvDate
FROM qryReportRFMRolliingTotals, tblXTabColumns
WHERE (((qryReportRFMRolliingTotals.ShipDate) Between [Forms]!
[frmReportRFMRolling12]![StartDate] And [Forms]![frmReportRFMRolling12]!
[EndDate]))
GROUP BY qryReportRFMRolliingTotals.CustNo, qryReportRFMRolliingTotals.
CustomerName, qryReportRFMRolliingTotals.DOB
PIVOT "Mth" & DateDiff("m",[ShipDate],[Forms]![frmReportRFMRolling12]!
[StartDate]) In ("M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11",
"M12");

I don't know how to apply what you have to what you want since you haven't
shared any SQL or much else.

So I do I keep my crosstab query as is, with the previous post you sent me
and also apply this solution?

[quoted text clipped - 10 lines]

 

[klp via AccessMonster.com]

Just some more information on my report.

It's an unbound report with quite a bit of logic behind it. I could post it
but it's quite lengthy. It's a 12 month RFM rolling report. As for the
layout I have 17 unbound fields. It displays the CustNo, CustomerName, DOB,
MaxOfInvDate, and the qty's for each month, & total. In my previous posts, I
noted that I need to have more than one value so that I could incorporate the
QtyXPrice. Below is my current query that has the current solution sent to me.


Any questions please ask.

Well here is my SQL. This is with the 2nd solution. I only get CustNo &
CustomerName to show up. Nothing else. Which this doesn't simplify my main
problem of putting together more than one value.

PARAMETERS [Forms]![frmReportRFMRolling12]![StartDate] DateTime, [Forms]!
[frmReportRFMRolling12]![EndDate] DateTime;
TRANSFORM Sum(qryReportRFMRolliingTotals.ShipQty) AS SumOfShipQty
SELECT qryReportRFMRolliingTotals.CustNo, qryReportRFMRolliingTotals.
CustomerName, qryReportRFMRolliingTotals.DOB, Max(qryReportRFMRolliingTotals.
InvDate) AS MaxOfInvDate
FROM qryReportRFMRolliingTotals, tblXTabColumns
WHERE (((qryReportRFMRolliingTotals.ShipDate) Between [Forms]!
[frmReportRFMRolling12]![StartDate] And [Forms]![frmReportRFMRolling12]!
[EndDate]))
GROUP BY qryReportRFMRolliingTotals.CustNo, qryReportRFMRolliingTotals.
CustomerName, qryReportRFMRolliingTotals.DOB
PIVOT "Mth" & DateDiff("m",[ShipDate],[Forms]![frmReportRFMRolling12]!
[StartDate]) In ("M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11",
"M12");

I don't know how to apply what you have to what you want since you haven't
shared any SQL or much else.

[quoted text clipped - 3 lines]

 

[Duane Hookom]

Can you explain why the report is unbound and why the "quite a bit of logic
behind it"?
--
Duane Hookom
Microsoft Access MVP

Just some more information on my report.

It's an unbound report with quite a bit of logic behind it. I could post it
but it's quite lengthy. It's a 12 month RFM rolling report. As for the
layout I have 17 unbound fields. It displays the CustNo, CustomerName, DOB,
MaxOfInvDate, and the qty's for each month, & total. In my previous posts, I
noted that I need to have more than one value so that I could incorporate the
QtyXPrice. Below is my current query that has the current solution sent to me.


Any questions please ask.

Well here is my SQL. This is with the 2nd solution. I only get CustNo &
CustomerName to show up. Nothing else. Which this doesn't simplify my main
problem of putting together more than one value.

PARAMETERS [Forms]![frmReportRFMRolling12]![StartDate] DateTime, [Forms]!
[frmReportRFMRolling12]![EndDate] DateTime;
TRANSFORM Sum(qryReportRFMRolliingTotals.ShipQty) AS SumOfShipQty
SELECT qryReportRFMRolliingTotals.CustNo, qryReportRFMRolliingTotals.
CustomerName, qryReportRFMRolliingTotals.DOB, Max(qryReportRFMRolliingTotals.
InvDate) AS MaxOfInvDate
FROM qryReportRFMRolliingTotals, tblXTabColumns
WHERE (((qryReportRFMRolliingTotals.ShipDate) Between [Forms]!
[frmReportRFMRolling12]![StartDate] And [Forms]![frmReportRFMRolling12]!
[EndDate]))
GROUP BY qryReportRFMRolliingTotals.CustNo, qryReportRFMRolliingTotals.
CustomerName, qryReportRFMRolliingTotals.DOB
PIVOT "Mth" & DateDiff("m",[ShipDate],[Forms]![frmReportRFMRolling12]!
[StartDate]) In ("M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11",
"M12");

I don't know how to apply what you have to what you want since you haven't
shared any SQL or much else.

[quoted text clipped - 3 lines]

Kim P

 

[klp via AccessMonster.com]

I can try. Being new at creating a crosstab query and making a rolling report,
I had no idea what I was getting into. I found an example of the same report
that I was trying to create, with my own modifications. So I kinda went with
that concept. It had the unbound report, and logic behind the report that
tells where the data goes in what fields. It will roll the report depending
on the date I put in. If I put in a month of June, it will start at June and
end with December. It's just what I found and I have no idea if I'm going way
above & beyond what I am supposed to do. There may be a way more simpler way
of doing this as you have sent me with that one solution. This is all a
learning process for me, so please bare with me. If I need to start over,
using the solutions you sent me I will do so. I just need to make sure that
my report will roll with what ever date I put in first.

Can you explain why the report is unbound and why the "quite a bit of logic
behind it"?

Just some more information on my report.

[quoted text clipped - 32 lines]

 

[Duane Hookom]

The solution you chose was a poor choice (not your fault). That's why I
suggested you try the monthly crosstab report solution from one of my
previous links. This creates a much more efficient report and no code.
--
Duane Hookom
Microsoft Access MVP

I can try. Being new at creating a crosstab query and making a rolling report,
I had no idea what I was getting into. I found an example of the same report
that I was trying to create, with my own modifications. So I kinda went with
that concept. It had the unbound report, and logic behind the report that
tells where the data goes in what fields. It will roll the report depending
on the date I put in. If I put in a month of June, it will start at June and
end with December. It's just what I found and I have no idea if I'm going way
above & beyond what I am supposed to do. There may be a way more simpler way
of doing this as you have sent me with that one solution. This is all a
learning process for me, so please bare with me. If I need to start over,
using the solutions you sent me I will do so. I just need to make sure that
my report will roll with what ever date I put in first.

Can you explain why the report is unbound and why the "quite a bit of logic
behind it"?

Just some more information on my report.

[quoted text clipped - 32 lines]

Kim P

 

[klp via AccessMonster.com]

Ah, ha! Okay, no wonder I was going crazy. I will start from scratch and try
the solution you provided. I may be back! Thank you so much for all your help.

The solution you chose was a poor choice (not your fault). That's why I
suggested you try the monthly crosstab report solution from one of my
previous links. This creates a much more efficient report and no code.

I can try. Being new at creating a crosstab query and making a rolling report,
I had no idea what I was getting into. I found an example of the same report

[quoted text clipped - 16 lines]

 

[klp via AccessMonster.com]

One more question before I start. I am going to try the 2nd solution you sent
me - I get the crosstab query. As for the report, I know I will create the
column labels as stated in the solution, but what about the actual data? What
are the record sources?

Ah, ha! Okay, no wonder I was going crazy. I will start from scratch and try
the solution you provided. I may be back! Thank you so much for all your help.

The solution you chose was a poor choice (not your fault). That's why I
suggested you try the monthly crosstab report solution from one of my

[quoted text clipped - 4 lines]

 

[Duane Hookom]

There is one record source for the report which is the crosstab query.
--
Duane Hookom
Microsoft Access MVP

One more question before I start. I am going to try the 2nd solution you sent
me - I get the crosstab query. As for the report, I know I will create the
column labels as stated in the solution, but what about the actual data? What
are the record sources?

Ah, ha! Okay, no wonder I was going crazy. I will start from scratch and try
the solution you provided. I may be back! Thank you so much for all your help.

The solution you chose was a poor choice (not your fault). That's why I
suggested you try the monthly crosstab report solution from one of my

[quoted text clipped - 4 lines]

Kim P

 

[klp via AccessMonster.com]

Sorry I mis-spoke. What I meant was, for the each of the fields on the report
what would their control source be? Do I just use them as the Mth0, Mth1 etc?
Then depending on the date I put in, it will roll?

There is one record source for the report which is the crosstab query.

One more question before I start. I am going to try the 2nd solution you sent
me - I get the crosstab query. As for the report, I know I will create the

[quoted text clipped - 9 lines]

 

[Duane Hookom]

Exactly The Mth0 are created fields in the crosstab and will display
different month's values based on the date entered into the control on the
form.
--
Duane Hookom
Microsoft Access MVP

Sorry I mis-spoke. What I meant was, for the each of the fields on the report
what would their control source be? Do I just use them as the Mth0, Mth1 etc?
Then depending on the date I put in, it will roll?

There is one record source for the report which is the crosstab query.

One more question before I start. I am going to try the 2nd solution you sent
me - I get the crosstab query. As for the report, I know I will create the

[quoted text clipped - 9 lines]

Kim P

 

[klp via AccessMonster.com]

You guys are great help! Okay, I have done as you said. However I have a few
more questions. First, can I either put in just a month & year for the date
Or, enter in the end date and have my column names be the actual month name?
Because right now I'm getting mm/dd/yyy format. 2nd. I put in an end date of
say 9-1-06. So I would like data from 9/06-8/07 to show. So in the formula
=DateAdd("m",-1,forms!frmName!EndDate), would I replace the -1 w/ a +1? I did
do that in fact, but I only got data for 1 month(Date of 01/01/2007). I
didn't get all the other months data. Boy, does any of this make sense?

Exactly The Mth0 are created fields in the crosstab and will display
different month's values based on the date entered into the control on the
form.

Sorry I mis-spoke. What I meant was, for the each of the fields on the report
what would their control source be? Do I just use them as the Mth0, Mth1 etc?

[quoted text clipped - 6 lines]

 

[Duane Hookom]

Just do what the sample suggests doing. You only need one date value. Use a
date value. Use the controls and the expressions to create your crosstab and
your report.

If your report is monthly columns, don't mess with the expressions.
--
Duane Hookom
Microsoft Access MVP

You guys are great help! Okay, I have done as you said. However I have a few
more questions. First, can I either put in just a month & year for the date
Or, enter in the end date and have my column names be the actual month name?
Because right now I'm getting mm/dd/yyy format. 2nd. I put in an end date of
say 9-1-06. So I would like data from 9/06-8/07 to show. So in the formula
=DateAdd("m",-1,forms!frmName!EndDate), would I replace the -1 w/ a +1? I did
do that in fact, but I only got data for 1 month(Date of 01/01/2007). I
didn't get all the other months data. Boy, does any of this make sense?

Exactly The Mth0 are created fields in the crosstab and will display
different month's values based on the date entered into the control on the
form.

Sorry I mis-spoke. What I meant was, for the each of the fields on the report
what would their control source be? Do I just use them as the Mth0, Mth1 etc?

[quoted text clipped - 6 lines]

Kim P

 

[klp via AccessMonster.com]

Great, now that I have that part understood. Can I now incorporate adding the
other value to my crosstab query? A while back you had sent me a solution to
adding another value. Is that still possible to add?

Just do what the sample suggests doing. You only need one date value. Use a
date value. Use the controls and the expressions to create your crosstab and
your report.

If your report is monthly columns, don't mess with the expressions.

You guys are great help! Okay, I have done as you said. However I have a few
more questions. First, can I either put in just a month & year for the date

[quoted text clipped - 13 lines]

 

[Duane Hookom]

I believe you can add in the other solution. You would need to "double" your
column headings to accommodate two values.

--
Duane Hookom
Microsoft Access MVP

Great, now that I have that part understood. Can I now incorporate adding the
other value to my crosstab query? A while back you had sent me a solution to
adding another value. Is that still possible to add?

Just do what the sample suggests doing. You only need one date value. Use a
date value. Use the controls and the expressions to create your crosstab and
your report.

If your report is monthly columns, don't mess with the expressions.

You guys are great help! Okay, I have done as you said. However I have a few
more questions. First, can I either put in just a month & year for the date

[quoted text clipped - 13 lines]

Kim P