Select Query with changing Cross tab data

mbtedrick

New Member
Joined
Nov 27, 2018
Messages
13
I am going to try and explain this the best way I can and I hope I don't confuse you. I may have gone about this the wrong way, but here is where I am. I am trying to create a Select query using 2 Tables, and a Cross Tab Query. The Cross tab Query's information changes based on the year being pulled. If pulling for 2019 the Cross tab will have 01 2019, 02 2019, 03 2019 and 04 2019. And all 4 of these are being pulled into the Select Query in the bottom section. Now if I run this same query for 2020 the Cross tab shows 01 2020 and that combination is not in the lower section. Is there a way for the query to pull whatever fields show up rather than having to revise it when running for different years. I have included a screen shot of the query for 2019 through third quarter.

So basically I want the query to pull whatever shows up in the cross tab as month year without having to update it manually.
1580324222508.png
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
Generally not so easily. For any query that includes changing you need a long term strategy. Almost no one does that.
So for instance, you might write your query to report CYPeriod1, CYPeriod2, etc.
Then get your results for the current year (CY), with year as the parameter.

Or even just, Period1, Period2, etc.
and again, year is a parameter - now all years can be run.
 

mbtedrick

New Member
Joined
Nov 27, 2018
Messages
13
Generally not so easily. For any query that includes changing you need a long term strategy. Almost no one does that.
So for instance, you might write your query to report CYPeriod1, CYPeriod2, etc.
Then get your results for the current year (CY), with year as the parameter.

Or even just, Period1, Period2, etc.
and again, year is a parameter - now all years can be run.
This makes sense. I never thought of it that way. I am self taught and know I am probably doing things incorrectly.

I do have another question based on this. If I use your suggestion and run for CYPeriod1 how would I set up so that the query does not need to updated each month/quarter. For example I am running CYPeriod1 so there is data in the Ct-TB-Direct Time-TIAA Hours Job Dept YEAR... for Period 1 only. I can't put the following Periods in the query columns. Not sure how to word it to pick up each period regardless of how many there are. Does that make sense? What you say makes perfect sense just not sure how to get it to pull everything even if its not yet in the Crosstab table.

Thank you,
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
Back in the day when I was doing a similar set of reports I set up a simple table with the key datapoints in it: CurrentYear, CurrentPeriodBegin, CurrentPeriodEnd, CurrentQuarterBegin, CurrentQuarterEnd.

Many of my reports joined on this table, basically. So if was a monthly report, I would a criteria TransDate >=CurrentPeriodBegin and TransDate <= CurrentPeriodEnd. If it was a quarterly report I would add a criteria where TransDate >= CurrentPeriodBegin and TransDate <= CurrentPeriodEnd. And so on .. we are assuming dates without times are normal here (if not you have to be careful about the boundaries with datetime ranges).

The nice thing here is that I flip the switch in my table with the key dates, and all the reports dynamically update for a new period. It also keeps the report definitions clean and easy to understand - no complicated date parsing formulas are required.
 

mbtedrick

New Member
Joined
Nov 27, 2018
Messages
13
I have so much to learn. Just found that you can specify the column headings in a crosstab query.

Life saver.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,281
Messages
5,641,293
Members
417,202
Latest member
AndyVBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top