I have a crosstab query that compiles a list of expenses for the current
month and the previous two months. After the first of next month, the 3
column headings will change. When I tested that by changing the formula i
used to create the crosstab query, the report couldn't find one of the
columns. How do i setup the report to use the three columns, regardless of
what the column headings are?
--
Allen Whitelock
Systems Administrator
World Class Automotive
I’ve seen several attempts in blogs to deal with crosstab queries with dynamic column headings, most of which being VBA based solutions. Not being an expert in VBA, I had a hard time understanding how to execute the solutions provided. After much deliberation on the subject, I have come up with a solution. I wanted to provide this to the public for their use.
My problem statement:
Every day I capture the inventory for parts at specific inventory locations in our ERP DBA tables. The information is appended to a table with a date stamp. In this table (“Logged_Data_Table”)there are the following fields:
· Item, text
· BIN, text
· Logdate, date/time (xx/xx/xxx)
My goal was to track the ERP transactions to watch material flow and to ensure transactions were being done correctly and timely.
I created a crosstab query and created a report based on the crosstab query. The report had a grouping on the Item and in the detail section the BIN locations were sorted by proper material flow. This report was to run every day showing the last 14 days’ worth of transactions showing the quantity per day. Below is an example of the report.
Item -14 -13 -12 -11 -10 -9 -8 -7 ….. yesterday today
BIN1 12
BIN2 12
BIN3 12 12 12
BIN4 12
Etc.
The problem is that every day when this report runs the column headings generated by the crosstab query change. On top of that sometimes my append query logging the inventory does not run because of normal IT glitches so not only do the column headings (logdates) change but I may have omitted dates, making the list of logdates not consecutive of the 14 day window, for example I might get this:
7/1/2015 7/2/2015 7/4/2015 7/5/2015 7/7/2015 … etc.
Here is the solution:
1. First create a table (“your_table”) with 2 fields. One is the date field and one is an autonumber field with a primary key assigned.
2. Create a delete query (“qClear_Table”)for the table (“your_table”) you just created
3. Create an append query (“qAppend_data”), appending to (“your_table”) and this query is based on the table where you are storing the logged data (“Logged_Data_Table”). Only append the date field. This will populate (“your_table”) with the list of dates that would be normally created in the cross tab data.
4. Next create a query (“qCross_Tab_Headers”) based on (“your_table”). In this query there are two fields. The first one is the logged date field from (“your_table”). The second one is this: idnew: DCount("[id]","your_table","[id]<" & [id]). This will force the autonumber to start with 0 every time, the other rows will be consecutive, e.g. 0, 1,2,3,4,...n
5. In your cross tab query add the (“qCross_Tab_Headers”) query to the design view and give it a one-to-one relationship with the log date from the table that your crosstab query is based on ("Logged_Data_Table").
6. The crosstab query wizard would have assigned the logged date field as a column header. Replace that field with the (“idnew”) field from the (“qCross_Tab_Header”) query.
7. Sort ascending on this column
8. Use the function CLng([idnew]) on this field to convert the string to a long integer. This will ensure that the columns are sorted properly.
9. Create a macro and the first statement is OpenQery. You want to select (“qClear_Table”)
10. Next statement is OpenQuery. You want to select (“qAppend_Data”)
11. Now in your report you want to use the fields that will start with 0, 1,2,3,4,…,13 in my case. These numbers come from the autonumber and will always be consecutive.
So, in summary you are clearing the contents of the table, appending the new dates, forcing the the autonumber to always start with 0. You are associating the new query dates to the one the original crosstab query created and using the primary key autonumber field in the crosstab to become the column headers, converting it to a long integer so the crosstab sorts the dates correctly.
Now your report always get static fields for the column headers and will run properly.
Hope this helps!
David Chippi
(e-mail address removed) for questions.
