Query using a partial match from one table and pulling in full details from another

[dobbs]

Hi

I wonder if anyone can help. I am new to access & stuggling with a simple query

I one table I have the following

PART LEADTIME
QT1234 8
QT123 6
QT2345 8

another table has full part numbers

DESCRIPTION PART NUMBER
QT1234-001 123456
QT1234-002A 485797
QT123-9578A 458798

What I want to end up with is a query that returns:

DESCRIPTION LEADTIME
QT1234-001 8
QT1234-002A 8

Any help would be greatly appriciated

Thanks
Dobbs

 

[JamesMcS]

That's a fairly basic query - but you need common fields in both tables to get out what you want (i.e. part number). Once you've got that sorted, create a query based on both tables, join them by part number, drag down the fields you want to see and run it. Presto!

 

[dobbs]

Thanks for this James, but the problem I have is I don't have a common field in the 2 tables. the description is the only field that are nearly the same, hence why I am looking for a partial match
Thanks
Dobbs

 

[JamesMcS]

Hmmm, that's going to cause you a problem then. It's not possible, as far as I know, to join two tables together and expect a partial match to come up.

So - we need to get a part number field in the other table. Are they all the same length? If they are you could try using the Left function

 

[vbaInet]

Adding to what James has suggested, if they aren't the same length InStrRev() would come in handy too.

 

[Brianwarnock]

Use 2 queries the first creates Aliases from your second table the bit before the - for the join and then the bit upto the space to get the rest of the data, then its simple access.
Got to go but will be back in about 1 hour

Brian

 

[Brianwarnock]

Ignore that, forget the join, just use a Where clause

SELECT [description] & " " & [leadtime] AS yourfieldname
FROM tbldesc, tblleadtime
WHERE (Left([description],InStr([description],"-")-1)=[part]);

Sorry for the delay Grandson sitting today


Brian

 

[vbaInet]

Sorry for the delay Grandson sitting today


Brian

I bet he's keeping you on your toes