Selecting unique range of records and adding them up

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Hello,

I have a complex question.

I have started the process of this solution by creating the basic query:

SELECT DISTINCT tblCustomer.area, ([predir] & " " & [street] & " " &
[strsfx]) AS [Unique Street Name], tblCustomer.city,
Count(tblCustomer.customerkey) AS CountOfCustomers
FROM tblCustomer
GROUP BY tblCustomer.area, ([predir] & " " & [street] & " " & [strsfx]),
tblCustomer.city
HAVING (((tblCustomer.area)=[select Customer Area]));

Now the complex part of this is question is creating a range of addresses
from the Unique Street Name part of it with the basis is if there is 325
customers on S 100th Street with the starting address of 300 and ending
address of 21356 and with this it needs to be grouped odd/even and portions
of 80 per grouping.

TIA

Aaron
 
Dear Aaron:

I've reproduced you query below with some simplifications and
formatting that make it easier for me to read:

SELECT DISTINCT area,
predir & " " & street & " " & [strsfx] AS [Unique Street Name],
city, Count(customerkey) AS CountOfCustomers
FROM tblCustomer
GROUP BY area, predir & " " & street & " " & strsfx, city
HAVING area = [select Customer Area];

From the information you have shown, it is not clear to me exactly
where the address number portion is stored. It seems to me the first
task is to find a way to separate that number from the rest of the
address, and to be sure to have a way to account for any address where
we cannot separate that number, perhaps by assigning 0 to this.

The odd/even separation could be a column added to the query based on
the modulo 2 value of the address number after we have generated that.

A ranking technique could then be used to divide them into sets of 80.
However, I'll tell you in advance that if you have two customers with
the same address, you may not get exactly 80 in each set. This will
depend on other factors. Perhaps you are not reporting Customers
here, but customer's addresses, and DISTINCTly so. That may eliminate
this potential problem, since addresses would be unique this way.

If you'll provide details about what is in each column (predir,
street, city, customerkey) perhaps with sample data, I'll try to take
this farther.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top