Sort of a reverse crosstab concept

[fredalina]

i am attempting to help a coworker to update some old queries which were written very much in spaghetti code. The queries were used to lump backorders into groups from 0-30 days old, 30-60 days old, 60-90, and >90. One of the queries' recipients would like to see this in 0-10 business days old, 11-20 business days, etc.

The original queries used about 10 update queries to create a table (each query creating a column of data). One of the queries created the "buckets" of 0-30, 60-90, etc, and it later uses crosstabs to create a table that lists each of the buckets as a column and sums the parts that are backordered in those buckets.

Our new query uses VBA to determine the new "buckets". We have a query that shows the part number and each bucket as column headings already.

We have found, in updating the macro that sends these reports out, that one of the reports that is sent is built upon the query preceding the crosstab in the original format (i hope this is making sense), so that there are 4 rows for every part number (one row with the backorder qty 0-30 days back, one line for 30-60 days back, etc, four total categories).

We need to update that other query to be identical to the old query that is used for the crosstab except with the new categories, as it feeds another query the macro sends out. How would we take a query that is a select query but already lists the "buckets" as column headings (similar to the results of a crosstab) and create a new query from it that lists the "buckets" on separate lines?

Thanks!
Your friendly neighborhood vampire

Edited to say that i have committed to helping her truly clean this database up and replace all the Update queries with actual data, but meanwhile this change is being requested for Monday.

 

[pbaldy]

Since the bucket changes over time, do you have a date field that it's all based on? You shouldn't really store the bucket anyway. The short answer to your question is that you could use a UNION query to get the data back into rows instead of columns.

 

[fredalina]

i'm not sure i follow you. The bucket labels themselves shouldn't really change over time (they will always be "0-10 business days", "11-20 business days", etc), unless of course someone decides to change the time frames from business days to months or something the way they changed it from total days to business days. The data within the buckets will be rolling data, though. An example of the query results:

CustomerID 0-10 11-20 21-30
0001 3 5 0
0002 13 0 11
0003 2 7 1

In this case, the numbers 3, 5, and 0 are the parts that have been backordered from 0-10 days, from 11-20 days, and from 21-30 days for customer 0001. Next week some of the ones from 0-10 days will shift to the 11-20 bucket, but there will always be a 0-10, an 11-20, and a 21-30 day buckets.

i will definitely look into Union queries. i've successfully avoided them until now.

Thanks!

 

[pbaldy]

My point is that an item that's 10 days old today will be 11 days old tomorrow, and thus change buckets. Generally you would store the date of the item and calculate the bucket it belongs in on the fly. If you store how many are in the 10 day bucket today, it will be wrong tomorrow.

 

[fredalina]

By storing the bucket data in queries and using a Union between the queries (instead of tables), should it not update whenever the union query is run?

 

[pbaldy]

Sure; I guess I'm just not clear on your situation. The queries must be based on raw data that includes date. In any case, the UNION query will return the current results of the query, so see if it works for you.

 

[fredalina]

Thanks again!