Group on first n characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to design a query that will group records where the first n
characters match on multiple records?

I'm trying to summarize sales data by customer and when there are 10 sales
for "XYZ" and 10 sales for "XYZ Inc", both customers are the same company, I
need all 20 sales to be totaled on one line. If I could match on the first 3
characters, that would do the trick, but I don't know how to do it.

Thanks,
Frank
 
If your sales table contains the name of the customer, then you have a
fundamental problem of your tables not being designed correctly and you
ought to remedy that. You should have a Customers table with a PK of
CustomerID and then the customer is referenced in the Sales table by
CustomerID. This avoids the problem you have. If your problem exists in the
Customer table, then it was caused by poor data entry; someone didn't check
multiple times to see that XYZ Inc was in the table before they entered XYZ.
If the problem is in the Customer table, you can quickly remedy the problem
by using an Update query and updating XYZ to XYZ Inc.
 
FrankTimJr said:
Is it possible to design a query that will group records where the first n
characters match on multiple records?

I'm trying to summarize sales data by customer and when there are 10 sales
for "XYZ" and 10 sales for "XYZ Inc", both customers are the same company, I
need all 20 sales to be totaled on one line. If I could match on the first 3
characters, that would do the trick, but I don't know how to do it.
Click to expand...


You can use an expression in a query's GROUP BY clause. In
this case I think you want to use the expression in a
report's Sorting and Grouping window. Either way, use
something like this:
=Left(customerfield, 3)

But this is a long way from a general solution to the
problem of different spellings for the same name. Better to
find a way to prevent the duplicate, but different, names
from being entered in the first place (possibly by providing
a combo box of customer names). Or, at least, providing a
form where users can set the two names to the same spelling.
 
I appreciate the feedback on the name of the customer being different. The
source of the data is not the Access database, it is from an oracle database.
I'm just using access to slice up the raw data into reports for the top
brass of the company.

Thanks again.
Frank
 
Is it possible to design a query that will group records where the first n
characters match on multiple records?

I'm trying to summarize sales data by customer and when there are 10 sales
for "XYZ" and 10 sales for "XYZ Inc", both customers are the same company, I
need all 20 sales to be totaled on one line. If I could match on the first 3
characters, that would do the trick, but I don't know how to do it.
Click to expand...

You could put a calculated field in the query

FirstThree: Left([Customer], 3)

and group by it.

However, this will lump sales to Sears Roebuck & Co. together with
sales to Seagrams Distillers. Maybe not the best idea!

To group by the first word, up to the first blank, you can do a bit
more:

FirstWord: Left([Customer], InStr([Customer] & " ", " ")

This is a VERY good example of why a Sales table should have a unique
CustomerID rather than relying on names, which are notoriously
unreliable for matching.

John W. Vinson[MVP]
 
Back
Top