Compare query with table. Output based on portion of field of table.

  • Thread starter Thread starter cerell
  • Start date Start date
C

cerell

I have a query that contains 1 field and a table with 2 fields. See
below:

Query

PN

AR33
WE22
AR45
QW32
AR46

Table

Prefix Location

AR Front Shelf
WE Rear Shelf
QW Upstairs

Resulting query I am looking for:

PN Location

AR33 Front Shelf
AR45 Front Shelf
WE22 Rear Shelf
QW32 Upstairs
AR46 Front Shelf

I am using Access 2003. I'm not sure how to implement this proceedure.
Any insight would be helpful. Thanks.
 
It sounds like your query concatenates location with some type of
identifier.

One approach would be to take only the left-most two characters
(Left([YourField],2) and join that to your lookup table of locations,
returning both the full (concatenated) field and the Location.

Consider posting the SQL statement of that query ... perhaps you can use
THAT query to return the information with suitable modification.

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/




I have a query that contains 1 field and a table with 2 fields. See
below:




Prefix          Location
Click to expand...
AR             Front Shelf
WE            Rear Shelf
QW            Upstairs
Click to expand...
Resulting query I am looking for:
Click to expand...
PN            Location
Click to expand...
AR33        Front Shelf
AR45        Front Shelf
WE22       Rear Shelf
QW32       Upstairs
AR46        Front Shelf
Click to expand...
I am using Access 2003. I'm not sure how to implement this proceedure.
Any insight would be helpful. Thanks.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Jeff,

The (Left([YourField],2) Did it. Thank you for your help.
 
It sounds like your query concatenates location with some type of
identifier.

One approach would be to take only the left-most two characters
(Left([YourField],2) and join that to your lookup table of locations,
returning both the full (concatenated) field and the Location.

Consider posting the SQL statement of that query ... perhaps you can use
THAT query to return the information with suitable modification.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Back
Top