1. ## Query Problem (2000)

I have a query and in that query I have to find how many clients per County. I also have to have in this query how many contracts per client. Then I have to break down the contracts per client according to how many of those clients have 1-10 contracts, how many of the total clients have contracts 11-19 and so on.
For example: Dade County has 52 Clients. How many of those 52 clients have between 1-10 contracts and how many have 11-19. So it would be Dade county has 52 clients. Of the 52 Clients 25 has between 1-10 contracts and 27 Clients have contracts between 11-19. Would I setup a separate query to qroup

I know I have to make some grouping and totals queries but how would I break down the contracts for the clients per county into those categories (1-10, 11-19 etc)
I think I would start by making a column in the query called 1-10 and then an IIF statement but I am stumped from there.
Anyone know how to set this up? Any help would be appreciated. I don't know where to begin......

2. ## Re: Query Problem (2000)

You need two queries for this:
- A totals query to count the number of contracts per client.
- A totals query based on the first one that counts by country, and uses the Partition function to create the groups.
By the way, a partition of 1-10, 11-19 etc, is not consistent. Either use 0-9, 10-19 etc., or 1-10, 11-20 etc.

I have attached a simple example database. Because of the small number of records, I used a partitioning into groups 1-3, 4-6 etc. This can easily be adapted. I added a third query to show how a crosstab query can display the same data in a different way.

3. ## Re: Query Problem (2000)

For some reason I can't open the attachment. It says file cannot be found.

4. ## Re: Query Problem (2000)

Anyway, I have attached a screenshot of the table relationships in the database. Here is the SQL for the first query, named qryContractsPerClient:

SELECT tblClients.ClientID, tblClients.Client, tblClients.CountyID, Count(tblContracts.ContractID) AS NumberOfContracts
FROM tblClients LEFT JOIN tblContracts ON tblClients.ClientID = tblContracts.ClientID
GROUP BY tblClients.ClientID, tblClients.Client, tblClients.CountyID;

And this is the SQL for the second query:

SELECT tblCounties.CountyID, tblCounties.County, Partition([NumberOfContracts],1,100,3) AS Contracts, Count(qryContractsPerClient.NumberOfContracts) AS CountOfClients
FROM qryContractsPerClient INNER JOIN tblCounties ON qryContractsPerClient.CountyID = tblCounties.CountyID
GROUP BY tblCounties.CountyID, tblCounties.County, Partition([NumberOfContracts],1,100,3);

5. ## Re: Query Problem (2000)

Did you save the attachment to your local drive and unzip it? I had no problem with it.

6. ## Re: Query Problem (2000)

Thanks Hans. I probably can open the attachment at work. I tried to open another post that had a zip attachment and I couldn't open that either. I could open the second attachment you sent I don't have Win Zip. I downloaded Win Zip and thought maybe that was the problem. I still get the error message. Thank you for your help with this query.

7. ## Re: Query Problem (2000)

Can you explain the numbers in the partition function: Contracts: Partition([NumberOfContracts],1,100,3).
If I have a definite range like 2-19, 1000+, Associations and at least 2 more ranges that I can't remember at the moment. I am home and I am trying to solve this problem before I go back to work on Monday. Would a range like "Associations" be possible?

8. ## Re: Query Problem (2000)

The Partition function works only with regular intervals of a constant width. In my example, 1 is the start value, 100 is the stop value and 3 is the interval. The values for NumberOfContracts will be divided into groups
1 - 3
4 - 6
7 - 9
etc.
If you want to use the same ranges as <!profile=LindaR>LindaR<!/profile> in <post#=432543>post 432543</post#>, Partition won't work. What does Associations mean?

9. ## Re: Query Problem (2000)

Another person in my department created these two data files and my job is to import them into Access and create reports from them. The reports are predefined. There are 56 reports that I have to recreate using these two files. Somehow he has added association numbers to the client number field and does some kind of calculations so I can do these reports that are predefined. It took me one whole day to figure out how to do report 1. My manager wants the reports done in Access so that once the reports are finished the database can be given to this department to run their own reports and all we have to do is refresh the data once a month. They think dong the reports in Access is easy. I am not that familiar with the data so it is taking me forever to figure out what is going on. The first report has a predefined field called "Segment" that includes those ranges (2-19 etc). The second report has those fields going across the top so I have to figure out how to get them going across instead of down the side. Sorry for the long explanation.

10. ## Re: Query Problem (2000)

Thanks for the explanation about the reports. It is also important for us to know what the data look like. Is Segment a field in a table, or is it calculated in a query?

11. ## Re: Query Problem (2000)

Walt is creating the data from a report generating program that is called Impromptu which is connected to an Oracle database. Once he gets the data (sort of like in Access, he is creating calculations that create new columns etc. He created the "Segment" field by doing a calculaltion), he saves the reports as Excel files. I take those files and import them into Access. The reason we aren't using Impromptu to create these 56 reports is because each report would take hours to run and doing this on a monthly basis is unrealistic. Once all the reports are created and the database is completed, refreshing the data once a month and allowing them to run their own reports from the database, seems to be a better option.

12. ## Re: Query Problem (2000)

That means my guess of what your data looked like is wrong. They are already aggregated. I have attached a screenshot that might be closer to what you have.

A query to count the number of clients within a segment, per country:

SELECT tblCounties.CountyID, tblCounties.County, tblSegments.SortID, tblSegments.Segment, Count(*) AS HowMany
FROM tblSegments INNER JOIN (tblCounties INNER JOIN tblClients ON tblCounties.CountyID = tblClients.CountyID) ON tblSegments.Segment = tblClients.Segment
GROUP BY tblCounties.CountyID, tblCounties.County, tblSegments.SortID, tblSegments.Segment;

And a crosstab query with the segments as column headings:

TRANSFORM Count(*) AS HowMany
SELECT tblCounties.CountyID, tblCounties.County
FROM tblCounties INNER JOIN tblClients ON tblCounties.CountyID = tblClients.CountyID
GROUP BY tblCounties.CountyID, tblCounties.County
ORDER BY tblCounties.CountyID, tblCounties.County
PIVOT tblClients.Segment In (2-19,20-50,51-999,"1000+","Association");

I will attach a zipped database in the next post; perhaps you can open it at work.

13. ## Re: Query Problem (2000)

Here is the zipped database.

14. ## Re: Query Problem (2000)

I think this is something I can use. Can you explain how the two queries relate. I opened ( I can access the attachment now) the crosstab query and I don't see the qrycount query. Is it somewhere in the SQL?

15. ## Re: Query Problem (2000)

The two queries are independent of each other. Both are based directly on tables, not on other queries.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•