How to extract part of a field?

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

I'm writing a query on inventory records. Each unique
inventory item is identified by a 10-digit number. We are
a private label manufacturer, handling customer-supplied
materials. The first 5 digits of the 10-digit inventory ID
identifies the customer who owns that material. When we
query one customer's material we use parameter queries
that ask for a range of item numbers as (nnnnn*), with the
* wildcard including any digits after the first five. Now
we need a report of ALL inventory, grouped by customer.
How can I do that?
 
You need to create a query where one of the fields is a calculated value:

Left([MyField],5)
 
Thanks, Michael
I'll try that.
Howard
-----Original Message-----
You need to create a query where one of the fields is a calculated value:

Left([MyField],5)

--
Michael Hopwood


I'm writing a query on inventory records. Each unique
inventory item is identified by a 10-digit number. We are
a private label manufacturer, handling customer-supplied
materials. The first 5 digits of the 10-digit inventory ID
identifies the customer who owns that material. When we
query one customer's material we use parameter queries
that ask for a range of item numbers as (nnnnn*), with the
* wildcard including any digits after the first five. Now
we need a report of ALL inventory, grouped by customer.
How can I do that?
Click to expand...


.
Click to expand...
 
I'm writing a query on inventory records. Each unique
inventory item is identified by a 10-digit number. We are
a private label manufacturer, handling customer-supplied
materials. The first 5 digits of the 10-digit inventory ID
identifies the customer who owns that material. When we
query one customer's material we use parameter queries
that ask for a range of item numbers as (nnnnn*), with the
* wildcard including any digits after the first five. Now
we need a report of ALL inventory, grouped by customer.
How can I do that?
Click to expand...

Michael's solution is the way to go; but to be properly normalized,
you should have all your fields "atomic" - in particular, you should
have TWO fields, a customerID and an inventoryID. You can make these
two fields a joint two-field Primary Key and can concatenate them for
display purposes, and you can sort or search them independently.
 
Back
Top