Condensing Information

User Name

Registered User.
Local time
Today, 14:38
Joined
Jun 10, 2003
Messages
26
I want to combine a large amount of data into 7 groups in a query. I want to get the average and sum of all 7 groups seperatly. I want to create a report that divides the 30000 records of numbers ranging from 0 to 2.5Mm into seperate number catagories. 1 - 100000, 100001 - 500000......etc.
 
I would require an example of what you are referring to, but my blind gusse would be to base the report off of queries, and seperate that data into the catagories within sub reports. By basing each subreport on individual criteria, you could easily catagorize them by the 0-2.5 field.

Were you wanting to create a query that would sort all records then create a report based on thier rankings?
 
subreports or union query

I need to create a report that condenses an takes the average and sum of all of the records divided up into there respected amounts. 0 - 100000, 100001 - 250000, 250001 - 500000......
I have already created 7 queries that have the information I need. Would it be easier to create the report with subreports or to create a union query to combine all of the information. If I use the union query what would the code be. I need FACE_AMT(face amount which is dividing up the info into the 7 groups), Average and sum of face amount, and a count of each which shows how many records are in each catagory.
 
Look up the Partition function. It might help with this task. If you can't use it for your purpose, write your own. That way you can section the recordset with a single query.
 
partition

The increment between the data is not equal. Some increment is a different size. Is it possible to more than one range expression in a query for each increment or is there a different function that can allow me to specify the specific increments.
 
You can use a lookup table to hold the start and end boundaries of the ranges and then link it to the main table in a Totals Query to get the sum and average of each range.

I have attached a demo DB in Access 97. You can open it and run the query.

(To open the DB in Access 2000 or 2002, just choose Convert and save as a new name when the DB is opened for the first time.)
 

Attachments

Users who are viewing this thread

Back
Top Bottom