Select Query with changing Cross tab data

[mbtedrick]

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.

 

[xenou]

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]

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]

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]

I have so much to learn. Just found that you can specify the column headings in a crosstab query.

Life saver.