VBA for crosstab query 
Author Message
 VBA for crosstab query

I have a db that takes in information on healthcare
claims. The information includes the date the claim was
received as well as which product (FCO) and type it is
and which processor is working on the claim.  We are now
wanting to print out/view a report on the age of these
claims. That is how many claims are 1-2 days, 3-4 days,5-
6 ,etc. up to 21....everything over 21 is lumped
together.  I have a make table query that takes all the
claims on a specific day, ages them and makes a table
with the claim aging by business days.  Next I use a
query to partition that table into the aging categories.
Then I have a crosstab query that runs a report to show
the product and type down the left side, the aging
categories across the top, and at the intersections the
total amount of claims that fall in that aging group.

I have sample data that covers all the possible aging
categories.  When I run the report (asks for a date
parameter--all claims held June 6 or May 15, etc.) using
the date for the data that covers all the aging
categories, it runs great (except the report needs to be
in landscape and not matter how I change it comes out in
portrait).  However when I input a date where there are
fewer claims that do not cover all of the aging
categories, I get an error that says "db jetengine does
not recognize 5: 6 as a valid field".  [where 5;6 is an
aging category]  

Since the exact aging categories could be random (that is
sometimes there will be no claims in days 7:8. etc.),
I've been told that I need to do some fancy VBA to make
only the categories that are to be populated appear on
the report.

Here is the SQL for my crosstab query:

TRANSFORM Sum([Partition Query on Age].[SumOfAmount]) AS
SumOfSumOfAmount
SELECT [Partition Query on Age].[FCO], [Partition Query
on Age].[Type], Sum([Partition Query on Age].
[SumOfAmount]) AS [Total Of SumOfAmount]
FROM [Partition Query on Age]
GROUP BY [Partition Query on Age].[FCO], [Partition Query
on Age].[Type]
PIVOT [Partition Query on Age].[Range];

PS. I tried changing the PIVOT line to read Range IN
(list of aging groups), but all that did was to have the
system prompt me for each category.

The query that does the partition is:

SELECT Count([Aged Inventory by Date].[Age]) AS
CountOfAge, Partition([age],1,20,2) AS Range, Sum([Aged
Inventory by Date].[Amount]) AS SumOfAmount, [Aged
Inventory by Date].[FCO], [Aged Inventory by Date].[Type]
FROM [Aged Inventory by Date]
GROUP BY Partition([age],1,20,2), [Aged Inventory by
Date].[FCO], [Aged Inventory by Date].[Type];

Is there any hope for a non VBA guy?  My gut tells me
that the crosstab query is creating the first report and
when the new data arrives and there are no totals for one
of the aging categories, the db gives me the error.

Any assistance will be greatly appreciated.  

Thanks,  sorry for being so wordy...just trying to cover
the bases.
Ray



Sun, 16 Oct 2005 09:42:19 GMT  
 VBA for crosstab query

Quote:
> categories, it runs great (except the report needs to be
> in landscape and not matter how I change it comes out in
> portrait).

Preview report and check Page Setup

Quote:
> portrait).  However when I input a date where there are
> fewer claims that do not cover all of the aging
> categories, I get an error that says "db jetengine does
> not recognize 5: 6 as a valid field".  [where 5;6 is an
> aging category]

The IN clause of the PIVOT statement in the solution to this.  YOu may just
have typed the options in brackets if it promted for them like parameters.
Try this:

PIVOT [Partition Query on Age].[Range]
IN("1","2","3","4","5","6","7","8","9","10");
or
PIVOT [Partition Query on Age].[Range] IN(1,2,3,4,5,6,7,8,9,10);

I think the top one is probably the right answer.  I have found XTabs to be
a little troublesome if the Column Heading comes from  a calculated value in
a query, as in your case,  versus data saved in a table.  You may have to
turn your 'Partition Query on Age' query into a make-table query and use the
new table in your XTab.

Quote:
> I've been told that I need to do some fancy VBA to make
> only the categories that are to be populated appear on
> the report.

If the Pivot clause and Make-table don't work then buy Access Developer's
Handbook (Sybex).  It contains a report and sample code for a dynamic report
with columns.

HTH,
Josh


Quote:
> I have a db that takes in information on healthcare
> claims. The information includes the date the claim was
> received as well as which product (FCO) and type it is
> and which processor is working on the claim.  We are now
> wanting to print out/view a report on the age of these
> claims. That is how many claims are 1-2 days, 3-4 days,5-
> 6 ,etc. up to 21....everything over 21 is lumped
> together.  I have a make table query that takes all the
> claims on a specific day, ages them and makes a table
> with the claim aging by business days.  Next I use a
> query to partition that table into the aging categories.
> Then I have a crosstab query that runs a report to show
> the product and type down the left side, the aging
> categories across the top, and at the intersections the
> total amount of claims that fall in that aging group.

> I have sample data that covers all the possible aging
> categories.  When I run the report (asks for a date
> parameter--all claims held June 6 or May 15, etc.) using
> the date for the data that covers all the aging
> categories, it runs great (except the report needs to be
> in landscape and not matter how I change it comes out in
> portrait).  However when I input a date where there are
> fewer claims that do not cover all of the aging
> categories, I get an error that says "db jetengine does
> not recognize 5: 6 as a valid field".  [where 5;6 is an
> aging category]

> Since the exact aging categories could be random (that is
> sometimes there will be no claims in days 7:8. etc.),
> I've been told that I need to do some fancy VBA to make
> only the categories that are to be populated appear on
> the report.

> Here is the SQL for my crosstab query:

> TRANSFORM Sum([Partition Query on Age].[SumOfAmount]) AS
> SumOfSumOfAmount
> SELECT [Partition Query on Age].[FCO], [Partition Query
> on Age].[Type], Sum([Partition Query on Age].
> [SumOfAmount]) AS [Total Of SumOfAmount]
> FROM [Partition Query on Age]
> GROUP BY [Partition Query on Age].[FCO], [Partition Query
> on Age].[Type]
> PIVOT [Partition Query on Age].[Range];

> PS. I tried changing the PIVOT line to read Range IN
> (list of aging groups), but all that did was to have the
> system prompt me for each category.

> The query that does the partition is:

> SELECT Count([Aged Inventory by Date].[Age]) AS
> CountOfAge, Partition([age],1,20,2) AS Range, Sum([Aged
> Inventory by Date].[Amount]) AS SumOfAmount, [Aged
> Inventory by Date].[FCO], [Aged Inventory by Date].[Type]
> FROM [Aged Inventory by Date]
> GROUP BY Partition([age],1,20,2), [Aged Inventory by
> Date].[FCO], [Aged Inventory by Date].[Type];

> Is there any hope for a non VBA guy?  My gut tells me
> that the crosstab query is creating the first report and
> when the new data arrives and there are no totals for one
> of the aging categories, the db gives me the error.

> Any assistance will be greatly appreciated.

> Thanks,  sorry for being so wordy...just trying to cover
> the bases.
> Ray



Sun, 16 Oct 2005 20:30:56 GMT  
 VBA for crosstab query
How sure are you that your day ranges are not going to change? I would
create a table of day ranges like
MinDays    MaxDays     Title
  0                 1               Who cares
  1                 2                A little late
  2                 4               A little later
  4                 6                Moderately Late
...

You can then add this to your query and set a datediff() criteria of

Quote:
>=MinDays AND < MaxDays

This will find one and only one "Title" to go with the DateDiff(). Use this
Title field as your column heading and use Joshua's syntax for the In
("...")

--
Duane Hookom
MS Access MVP


Quote:
> I have a db that takes in information on healthcare
> claims. The information includes the date the claim was
> received as well as which product (FCO) and type it is
> and which processor is working on the claim.  We are now
> wanting to print out/view a report on the age of these
> claims. That is how many claims are 1-2 days, 3-4 days,5-
> 6 ,etc. up to 21....everything over 21 is lumped
> together.  I have a make table query that takes all the
> claims on a specific day, ages them and makes a table
> with the claim aging by business days.  Next I use a
> query to partition that table into the aging categories.
> Then I have a crosstab query that runs a report to show
> the product and type down the left side, the aging
> categories across the top, and at the intersections the
> total amount of claims that fall in that aging group.

> I have sample data that covers all the possible aging
> categories.  When I run the report (asks for a date
> parameter--all claims held June 6 or May 15, etc.) using
> the date for the data that covers all the aging
> categories, it runs great (except the report needs to be
> in landscape and not matter how I change it comes out in
> portrait).  However when I input a date where there are
> fewer claims that do not cover all of the aging
> categories, I get an error that says "db jetengine does
> not recognize 5: 6 as a valid field".  [where 5;6 is an
> aging category]

> Since the exact aging categories could be random (that is
> sometimes there will be no claims in days 7:8. etc.),
> I've been told that I need to do some fancy VBA to make
> only the categories that are to be populated appear on
> the report.

> Here is the SQL for my crosstab query:

> TRANSFORM Sum([Partition Query on Age].[SumOfAmount]) AS
> SumOfSumOfAmount
> SELECT [Partition Query on Age].[FCO], [Partition Query
> on Age].[Type], Sum([Partition Query on Age].
> [SumOfAmount]) AS [Total Of SumOfAmount]
> FROM [Partition Query on Age]
> GROUP BY [Partition Query on Age].[FCO], [Partition Query
> on Age].[Type]
> PIVOT [Partition Query on Age].[Range];

> PS. I tried changing the PIVOT line to read Range IN
> (list of aging groups), but all that did was to have the
> system prompt me for each category.

> The query that does the partition is:

> SELECT Count([Aged Inventory by Date].[Age]) AS
> CountOfAge, Partition([age],1,20,2) AS Range, Sum([Aged
> Inventory by Date].[Amount]) AS SumOfAmount, [Aged
> Inventory by Date].[FCO], [Aged Inventory by Date].[Type]
> FROM [Aged Inventory by Date]
> GROUP BY Partition([age],1,20,2), [Aged Inventory by
> Date].[FCO], [Aged Inventory by Date].[Type];

> Is there any hope for a non VBA guy?  My gut tells me
> that the crosstab query is creating the first report and
> when the new data arrives and there are no totals for one
> of the aging categories, the db gives me the error.

> Any assistance will be greatly appreciated.

> Thanks,  sorry for being so wordy...just trying to cover
> the bases.
> Ray



Mon, 17 Oct 2005 11:58:16 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. create a table from a crosstab parameter query in VBA

2. multiple queries into a crosstab query in VB

3. multiple queries into a crosstab query in VB

4. Opening a CrossTab query via QueryDef.OpenRecordset

5. Crosstab Query

6. Sort or limit row displayed in a crosstab query

7. QueryDefs Fields Collection on a CrossTab Query

8. report based on a crosstab query

9. a crosstab + parameter query question..

10. report based on a crosstab query

11. Crosstab Query for Report

12. CrossTab Query At Runtime...

 

 
Powered by phpBB® Forum Software