Crosstab Situation

[Guest]

Question: Is there a workaround to "emulate" a 2-level crosstab in Access?

Issue:
I'm getting in data every data, which is used to make up a kind of
comparison report on commercial delinquencies, the data comes in raw table
form something like this...

DATE - CUSTOMERID - COLLECTIONTYPE - 1-30 $ - 30-59 $ ... (up to 120+ $)
12/1/05 - CUSTOMER1 - ACTIVE - $500 - $200 - ...
12/1/05 - CUSTOMER1 - MAJORACCT - $100 - $0 - ...
12/1/05 - CUSTOMER2 - ACTIVE - $200 - $100 - ...
12/1/05 - CUSTOMER2 - BANKRUPTCY - $600 - $40 - ...
....
and so on

What I would like to be able to do with this data, is consolidate it into a
single row that I can write out to Excel, something like so:

DATE - CUSTOMERID - 1-30_Active - 1-30_MajorAcct - 1-30_Bankruptcy - 30-59...
12/1/05 - CUSTOMER1 - $500 - $100 - $0 - $200 - ...
12/1/05 - CUSTOMER2 - $200 - $0 - $600 - $100 - ...

Basically, what I'm wanting to do is break it down for each date/customer
into its "bucket" (1-30, 30-59, etc) and then on collection type (Active,
MajorAcct, etc) and have the output all in one nice little row that I can
query on, write out to Excel, etc.

Crosstabs make this job a lot easier, but from my understanding you can only
have one column/value row. So doing this in one query wouldn't be possible I
don't believe. Any other ideas?

 

[Duane Hookom]

I am having trouble understanding your table structure. Do you have fields
named like "1-30 $"? If so, the first step would be to use a normalizing
union query to place the values of "1-30" into a field/column. You could
then create a crosstab from this union query that would combine two
columns/field into a column heading.

 

[Guest]

Well, the column names would be something like 1-30_Dollars, but you get the
idea. Am I understanding you right in that I should transpose this data into
a form like so (following the previous example)?

DATE - CUSTOMERID - COLLECTIONTYPE_BREAKOUT - DOLLARS
12/1/2005 - CUSTOMER1 - ACTIVE_1-30 - $500
12/1/2005 - CUSTOMER1 - ACTIVE_30-59 - $200
....
etc?

That looks like it would give me what I want, but how might I transpose this
data into this form with a "normalizing" union query like you suggest?

 

[Duane Hookom]

You stated "the column names would be something like 1-30_Dollars". I don't
know if these are derived from a crosstab or what. Can you provide your
exact table structure and a few records?

 

[Guest]

I'd like to give you a full example but I can't because of the company's
Confidentiality Agreement...

I can tell you this though, that the data that I am receiving now comes in
that form, and it is fed to the Backend database that I use on a daily basis
from the IT department (they pull it off of Oracle I believe, which gets fed
into an Access database for our reporting purposes).

To answer your question though, no those field names are not derived from
Crosstab queries (at least to the best of my knowledge), and yes the field
names are:

DATE - CUSTOMERID - COLLECTIONTYPE - 1-30_DOLLARS - 30-60_DOLLARS -
60-90_DOLLARS - 90-120_DOLLARS - 120PLUS_DOLLARS - TOTALOUTSTANDING_DOLLARS

(Total Outstanding is different from the sum of 1-30+...+120plus (the sum of
those fields would be considered "1+ Dollars"), whereas Total O/S includes
everything "0+" (items that are "current" and not delinquent)).


My only question at this point would be on how to use a Union query for the
purpose of normalizing out this data like you suggest. I've only used Union
queries in the past for concatenating tables that have identical fields into
one query (which is unfortunately very slow usually since it caches the first
table I think, so I don't use them much when I can avoid them).

 

[Guest]

I figured out how to do the normalization union query this morning as per
your suggestion. It worked like a charm and I was able to do my crosstab
easily. Thank you!

 

[Duane Hookom]

Glad to hear it worked for you...