Crosstab query issue with 52 columns and no value aggregation required.

[marcnz]

Hi,

I have spent all day trying to figure out how to get a crosstab query in MS Access 2010 that would allow a dump of resulted record in Excel sheet.

I only need help with the crosstab issue:

1. I have the following tables:
tblForecast
tblAccount

2. I have to display the following rows from the tblForecastData:
Brand
ItemID
Account

3. I need to "make" the columns: 52 weeks of the year, starting 1/1/13 and ending 31/12/13 (sorry, I am not US for the format )

4. I also "make" the value as a concatenated string joining various other fields from the tblForecast table, WITHOUT any aggregation required. No max, min, count, sum, etc... just a plain string to be display when the following criteria is met:

5. Criteria:
Forecast Promotion Start date < current week + 7
Forecast Promotion End date > current week

So if there is a product promoted in the week starting 8/1/13, I must retrieve all products that have a promotion start date < 8/1/13 + 7 days and a promotion end date that is > than 8/1/13.

I tried a lot of options:
create one query per starting week, then union per trimester, then union per year (I can't union 52 queries without a too complex select error...)

The result would almost be OK except that there are way too many rows per Brand as there is one row for each value instead of having the value on the same row for the product with corresponding dates.

I hope someone will have an idea where I can put my mind into. I feel bad wasting my entire day at the office for one issue like this one.

Thank you.

 

[JHB]

Hi,

The result would almost be OK except that there are way too many rows per Brand as there is one row for each value instead of having the value on the same row for the product with corresponding dates.

Show the query you have, (and a screenshot of the result would also be good, zip it because you haven't post 10 post yet).

 

[marcnz]

I think I found my solution while walking back home after work.

No need to have any union queries at all! It is actually so simple that I was surprised I spent so much time making things so complicated.

The idea is that:
1/1/13: iif(startdate < #8/1/13# and enddate > 1/1/13, "my value in there","")

This will be repeated for all the weeks in the year, changing the < #date# and > #date# to match whatever week starting date we are on.

However I will have to do it tomorrow and see the result. I will confirm the solution if true.

I appreciate such a quick response. It is amazing to have a community of members ready to help so quickly.

 

[marcnz]

Hi,

Well, my idea still doesn't give me the result I need.

I have attached a spreadsheet showing the query results. As you can see, I should have only one row for DDS account with the "promo" value in each of the week date column, all in one row. Instead, it is not grouping as I hoped and I have several rows.

I have qryPAG_0 first query that only set some fields variables that will help build the "thePromo" final variable.

The qryPAG_1 takes the qryPAG_0 and adds the "thePromo" variable.

Finally the resulting query takes the qryPAG_1 and set the weeks field (53) putting the "thePromo" variable if the dates match a specific range. The output is correct dates wise. Just that it isn't putting all in a single row.

Probably I could have only one query for qryPAG_0 and qryPAG_1, however I prefer dealing with prepared variable as I feel it makes my life easier in the long run. The performance impact for this specific result is of no importance.

Here is my final query:

SELECT qryPAG_1.Brand, qryPAG_1.[sanofi code] AS Prod, qryPAG_1.Account, IIf([Start Date (DP)]<#1/8/2013# And [End Date (DP)]>#1/1/2013#,[thePromo],"") AS [01/01/13], IIf([Start Date (DP)]<#1/15/2013# And [End Date (DP)]>#1/8/2013#,[thePromo],"") AS [08/01/13], IIf([Start Date (DP)]<#1/22/2013# And [End Date (DP)]>#1/15/2013#,[thePromo],"") AS [15/01/13], IIf([Start Date (DP)]<#1/29/2013# And [End Date (DP)]>#1/22/2013#,[thePromo],"") AS [22/01/13], IIf([Start Date (DP)]<#2/5/2013# And [End Date (DP)]>#1/29/2013#,[thePromo],"") AS [29/01/13], IIf([Start Date (DP)]<#2/12/2013# And [End Date (DP)]>#2/5/2013#,[thePromo],"") AS [05/02/13], IIf([Start Date (DP)]<#2/19/2013# And [End Date (DP)]>#2/12/2013#,[thePromo],"") AS [12/02/13], IIf([Start Date (DP)]<#2/26/2013# And [End Date (DP)]>#2/19/2013#,[thePromo],"") AS [19/02/13], IIf([Start Date (DP)]<#3/5/2013# And [End Date (DP)]>#2/26/2013#,[thePromo],"") AS [26/02/13], IIf([Start Date (DP)]<#3/12/2013# And [End Date (DP)]>#3/5/2013#,[thePromo],"") AS [05/03/13], IIf([Start Date (DP)]<#3/19/2013# And [End Date (DP)]>#3/12/2013#,[thePromo],"") AS [12/03/13], IIf([Start Date (DP)]<#3/26/2013# And [End Date (DP)]>#3/19/2013#,[thePromo],"") AS [19/03/13], IIf([Start Date (DP)]<#4/2/2013# And [End Date (DP)]>#3/26/2013#,[thePromo],"") AS [26/03/13], IIf([Start Date (DP)]<#4/9/2013# And [End Date (DP)]>#4/2/2013#,[thePromo],"") AS [02/04/13], IIf([Start Date (DP)]<#4/16/2013# And [End Date (DP)]>#4/9/2013#,[thePromo],"") AS [09/04/13], IIf([Start Date (DP)]<#4/23/2013# And [End Date (DP)]>#4/16/2013#,[thePromo],"") AS [16/04/13], IIf([Start Date (DP)]<#4/30/2013# And [End Date (DP)]>#4/23/2013#,[thePromo],"") AS [23/04/13], IIf([Start Date (DP)] Between #5/7/2013# And [End Date (DP)]>#4/30/2013#,[thePromo],"") AS [30/04/13], IIf([Start Date (DP)]<#5/14/2013# And [End Date (DP)]>#5/7/2013#,[thePromo],"") AS [07/05/13], IIf([Start Date (DP)]<#5/21/2013# And [End Date (DP)]>#5/14/2013#,[thePromo],"") AS [14/05/13], IIf([Start Date (DP)]<#5/28/2013# And [End Date (DP)]>#5/21/2013#,[thePromo],"") AS [21/05/13], IIf([Start Date (DP)]<#6/4/2013# And [End Date (DP)]>#5/28/2013#,[thePromo],"") AS [28/05/13], IIf([Start Date (DP)]<#6/11/2013# And [End Date (DP)]>#6/4/2013#,[thePromo],"") AS [04/06/13], IIf([Start Date (DP)]<#6/18/2013# And [End Date (DP)]>#6/11/2013#,[thePromo],"") AS [11/06/13], IIf([Start Date (DP)]<#6/25/2013# And [End Date (DP)]>#6/18/2013#,[thePromo],"") AS [18/06/13], IIf([Start Date (DP)]<#7/2/2013# And [End Date (DP)]>#6/25/2013#,[thePromo],"") AS [25/06/13], IIf([Start Date (DP)]<#7/9/2013# And [End Date (DP)]>#7/2/2013#,[thePromo],"") AS [02/07/13], IIf([Start Date (DP)]<#7/16/2013# And [End Date (DP)]>#7/9/2013#,[thePromo],"") AS [09/07/13], IIf([Start Date (DP)]<#7/23/2013# And [End Date (DP)]>#7/16/2013#,[thePromo],"") AS [16/07/13], IIf([Start Date (DP)]<#7/30/2013# And [End Date (DP)]>#7/23/2013#,[thePromo],"") AS [23/07/13], IIf([Start Date (DP)]<#8/6/2013# And [End Date (DP)]>#7/30/2013#,[thePromo],"") AS [30/07/13], IIf([Start Date (DP)]<#8/13/2013# And [End Date (DP)]>#8/6/2013#,[thePromo],"") AS [06/08/13], IIf([Start Date (DP)]<#8/20/2013# And [End Date (DP)]>#8/13/2013#,[thePromo],"") AS [13/08/13], IIf([Start Date (DP)]<#8/27/2013# And [End Date (DP)]>#8/20/2013#,[thePromo],"") AS [20/08/13], IIf([Start Date (DP)]<#9/3/2013# And [End Date (DP)]>#8/27/2013#,[thePromo],"") AS [27/08/13], IIf([Start Date (DP)]<#9/10/2013# And [End Date (DP)]>#9/3/2013#,[thePromo],"") AS [03/09/13], IIf([Start Date (DP)]<#9/17/2013# And [End Date (DP)]>#9/10/2013#,[thePromo],"") AS [10/09/13], IIf([Start Date (DP)]<#9/24/2013# And [End Date (DP)]>#9/17/2013#,[thePromo],"") AS [17/09/13], IIf([Start Date (DP)]<#10/1/2013# And [End Date (DP)]>#9/24/2013#,[thePromo],"") AS [24/09/13], IIf([Start Date (DP)]<#10/8/2013# And [End Date (DP)]>#10/1/2013#,[thePromo],"") AS [01/10/13], IIf([Start Date (DP)]<#10/15/2013# And [End Date (DP)]>#10/8/2013#,[thePromo],"") AS [08/10/13], IIf([Start Date (DP)]<#10/22/2013# And [End Date (DP)]>#10/15/2013#,[thePromo],"") AS [15/10/13], IIf([Start Date (DP)]<#10/29/2013# And [End Date (DP)]>#10/22/2013#,[thePromo],"") AS [22/10/13], IIf([Start Date (DP)]<#11/5/2013# And [End Date (DP)]>#10/29/2013#,[thePromo],"") AS [29/10/13], IIf([Start Date (DP)]<#11/12/2013# And [End Date (DP)]>#11/5/2013#,[thePromo],"") AS [05/11/13], IIf([Start Date (DP)]<#11/19/2013# And [End Date (DP)]>#11/12/2013#,[thePromo],"") AS [12/11/13], IIf([Start Date (DP)]<#11/26/2013# And [End Date (DP)]>#11/19/2013#,[thePromo],"") AS [19/11/13], IIf([Start Date (DP)]<#12/3/2013# And [End Date (DP)]>#11/26/2013#,[thePromo],"") AS [26/11/13], IIf([Start Date (DP)]<#12/10/2013# And [End Date (DP)]>#12/3/2013#,[thePromo],"") AS [03/12/13], IIf([Start Date (DP)]<#12/17/2013# And [End Date (DP)]>#12/10/2013#,[thePromo],"") AS [10/12/13], IIf([Start Date (DP)]<#12/24/2013# And [End Date (DP)]>#12/17/2013#,[thePromo],"") AS [17/12/13], IIf([Start Date (DP)]<#12/31/2013# And [End Date (DP)]>#12/24/2013#,[thePromo],"") AS [24/12/13], IIf([Start Date (DP)]<#1/7/2014# And [End Date (DP)]>#12/31/2013#,[thePromo],"") AS [31/12/13]
FROM qryPAG_1
GROUP BY qryPAG_1.Brand, qryPAG_1.[sanofi code], qryPAG_1.Account, IIf([Start Date (DP)]<#1/8/2013# And [End Date (DP)]>#1/1/2013#,[thePromo],""), IIf([Start Date (DP)]<#1/15/2013# And [End Date (DP)]>#1/8/2013#,[thePromo],""), IIf([Start Date (DP)]<#1/22/2013# And [End Date (DP)]>#1/15/2013#,[thePromo],""), IIf([Start Date (DP)]<#1/29/2013# And [End Date (DP)]>#1/22/2013#,[thePromo],""), IIf([Start Date (DP)]<#2/5/2013# And [End Date (DP)]>#1/29/2013#,[thePromo],""), IIf([Start Date (DP)]<#2/12/2013# And [End Date (DP)]>#2/5/2013#,[thePromo],""), IIf([Start Date (DP)]<#2/19/2013# And [End Date (DP)]>#2/12/2013#,[thePromo],""), IIf([Start Date (DP)]<#2/26/2013# And [End Date (DP)]>#2/19/2013#,[thePromo],""), IIf([Start Date (DP)]<#3/5/2013# And [End Date (DP)]>#2/26/2013#,[thePromo],""), IIf([Start Date (DP)]<#3/12/2013# And [End Date (DP)]>#3/5/2013#,[thePromo],""), IIf([Start Date (DP)]<#3/19/2013# And [End Date (DP)]>#3/12/2013#,[thePromo],""), IIf([Start Date (DP)]<#3/26/2013# And [End Date (DP)]>#3/19/2013#,[thePromo],""), IIf([Start Date (DP)]<#4/2/2013# And [End Date (DP)]>#3/26/2013#,[thePromo],""), IIf([Start Date (DP)]<#4/9/2013# And [End Date (DP)]>#4/2/2013#,[thePromo],""), IIf([Start Date (DP)]<#4/16/2013# And [End Date (DP)]>#4/9/2013#,[thePromo],""), IIf([Start Date (DP)]<#4/23/2013# And [End Date (DP)]>#4/16/2013#,[thePromo],""), IIf([Start Date (DP)]<#4/30/2013# And [End Date (DP)]>#4/23/2013#,[thePromo],""), IIf([Start Date (DP)] Between #5/7/2013# And [End Date (DP)]>#4/30/2013#,[thePromo],""), IIf([Start Date (DP)]<#5/14/2013# And [End Date (DP)]>#5/7/2013#,[thePromo],""), IIf([Start Date (DP)]<#5/21/2013# And [End Date (DP)]>#5/14/2013#,[thePromo],""), IIf([Start Date (DP)]<#5/28/2013# And [End Date (DP)]>#5/21/2013#,[thePromo],""), IIf([Start Date (DP)]<#6/4/2013# And [End Date (DP)]>#5/28/2013#,[thePromo],""), IIf([Start Date (DP)]<#6/11/2013# And [End Date (DP)]>#6/4/2013#,[thePromo],""), IIf([Start Date (DP)]<#6/18/2013# And [End Date (DP)]>#6/11/2013#,[thePromo],""), IIf([Start Date (DP)]<#6/25/2013# And [End Date (DP)]>#6/18/2013#,[thePromo],""), IIf([Start Date (DP)]<#7/2/2013# And [End Date (DP)]>#6/25/2013#,[thePromo],""), IIf([Start Date (DP)]<#7/9/2013# And [End Date (DP)]>#7/2/2013#,[thePromo],""), IIf([Start Date (DP)]<#7/16/2013# And [End Date (DP)]>#7/9/2013#,[thePromo],""), IIf([Start Date (DP)]<#7/23/2013# And [End Date (DP)]>#7/16/2013#,[thePromo],""), IIf([Start Date (DP)]<#7/30/2013# And [End Date (DP)]>#7/23/2013#,[thePromo],""), IIf([Start Date (DP)]<#8/6/2013# And [End Date (DP)]>#7/30/2013#,[thePromo],""), IIf([Start Date (DP)]<#8/13/2013# And [End Date (DP)]>#8/6/2013#,[thePromo],""), IIf([Start Date (DP)]<#8/20/2013# And [End Date (DP)]>#8/13/2013#,[thePromo],""), IIf([Start Date (DP)]<#8/27/2013# And [End Date (DP)]>#8/20/2013#,[thePromo],""), IIf([Start Date (DP)]<#9/3/2013# And [End Date (DP)]>#8/27/2013#,[thePromo],""), IIf([Start Date (DP)]<#9/10/2013# And [End Date (DP)]>#9/3/2013#,[thePromo],""), IIf([Start Date (DP)]<#9/17/2013# And [End Date (DP)]>#9/10/2013#,[thePromo],""), IIf([Start Date (DP)]<#9/24/2013# And [End Date (DP)]>#9/17/2013#,[thePromo],""), IIf([Start Date (DP)]<#10/1/2013# And [End Date (DP)]>#9/24/2013#,[thePromo],""), IIf([Start Date (DP)]<#10/8/2013# And [End Date (DP)]>#10/1/2013#,[thePromo],""), IIf([Start Date (DP)]<#10/15/2013# And [End Date (DP)]>#10/8/2013#,[thePromo],""), IIf([Start Date (DP)]<#10/22/2013# And [End Date (DP)]>#10/15/2013#,[thePromo],""), IIf([Start Date (DP)]<#10/29/2013# And [End Date (DP)]>#10/22/2013#,[thePromo],""), IIf([Start Date (DP)]<#11/5/2013# And [End Date (DP)]>#10/29/2013#,[thePromo],""), IIf([Start Date (DP)]<#11/12/2013# And [End Date (DP)]>#11/5/2013#,[thePromo],""), IIf([Start Date (DP)]<#11/19/2013# And [End Date (DP)]>#11/12/2013#,[thePromo],""), IIf([Start Date (DP)]<#11/26/2013# And [End Date (DP)]>#11/19/2013#,[thePromo],""), IIf([Start Date (DP)]<#12/3/2013# And [End Date (DP)]>#11/26/2013#,[thePromo],""), IIf([Start Date (DP)]<#12/10/2013# And [End Date (DP)]>#12/3/2013#,[thePromo],""), IIf([Start Date (DP)]<#12/17/2013# And [End Date (DP)]>#12/10/2013#,[thePromo],""), IIf([Start Date (DP)]<#12/24/2013# And [End Date (DP)]>#12/17/2013#,[thePromo],""), IIf([Start Date (DP)]<#12/31/2013# And [End Date (DP)]>#12/24/2013#,[thePromo],""), IIf([Start Date (DP)]<#1/7/2014# And [End Date (DP)]>#12/31/2013#,[thePromo],"");


Thanks for helping me with this issue. I am sure it is something simple but I can't put my fingers on it.

 

[JHB]

Could we get the result from each query you run, in an Excel sheet, or get the database you've, only to "play" with?
Have you tried with at crosstable - "Transform/pivot"?

 

[marcnz]

Hi,

Thanks for looking into this.

I have attached an excel file with the original table, the intermediate query and the final query. Each on its own worksheet including the SQL statement for each.

I apologize I can't provide more information due to the company's restrictions. I hope you will understand. What is provided here is all what matters.

Much appreciated.

 

[JHB]

Hi,

I apologize I can't provide more information due to the company's restrictions. I hope you will understand. What is provided here is all what matters.

Oh yes I understand, the data you gave was very ok.

I've made a solution for you which is attaced.
Open the only formular and type in the start date and the end date and hit the button "Create calender".

I would like if you'll check the dates if the result is ok. In the table "CheckTable" you can see, which date pull which information. Check the dates in the "Dato" field against the date, (Startdate and Enddate), in the field "Udtryk"

 

[marcnz]

Hi JHB,

That was very quick! Amazing job.

I also thought about having a table with all the week dates as you have done. I didn't find a way to use it without a join as you did.

I like the final result, all on one row as requested.

However, checking the dates, I spotted one missing entry in the "9/07/2013" week.

If you look at the screenshot attached in the TEST_qryPAG_0 data, there is one entry with "Start Date (DP)" of "15-07-2013". This entry should therefore be showing in the week slot "9/07/2013" as the start date is the 15th, before the next week start of 16th.

I tried to play with you final query's date condition. You have a "Between [Start Date (DP)] and [End Date (DP)]. The condition I had in my example are:
[Start Date (DP)] < [Next Week Start Date] (in this case it would be < 16/7/2013) AND [End Date (DP) > [Current Week Start Date] (in this case it would be > 9/7/2013).

I am not sure how to get this programmed into just one field without making it crazy long (52 weeks!). I did it for each week as you could see.

I really thank you for trying out.

 

[JHB]

Now it should be correct, (please check up).

Did you read my signature?

 

[marcnz]

Hi,

Yes I read your signature.

I still don't see the record that should be showing in the 9/7/13 slot with a start date of 15/7/13.

I put 1/1/13 to 31/12/13 in the calendar.

Do you agree that the record with a start date of 15/7/13 should be visible in the 9/7/13 week?

Thanks a lot for trying. I pulled my hair so many days with this issue. You definitely are on something close here, but just not quite yet.

 

[JHB]

Hi,
I still don't see the record that should be showing in the 9/7/13 slot with a start date of 15/7/13.

By me, it is there, se attached picture. (I downloaded the datebase and run it from that database).

You definitely are on something close here, but just not quite yet.

I'm there.

 

[marcnz]

Hi JHB,

You are correct. I don't know why it wasn't showing. I might have still used the previous version.

Case closed. Full rewards to you

Thank you very much for such a quick and great help.

ps: I am amending this post to attach the result showing on the page after clicking the "Thank You" button to JHB's post. It might help fixing access-programmers.co.uk "thank you" link event.

 

[JHB]

You're welcome - luck with you projekt.
Only one thing I have thought about later, what if there are different data in the overlapping dates (eg 09-07-2013), which one must be maintained/showed?