Conditional join? - only part of the field matches... 1

[BC98121]

I am using Access to organize data from various spreadsheets and generate some information. I'm running into issues with a multi-table query where some of the data in one table is entered as 123456-7890 and in another it is 123456. Those same columns also have data that matches exactly, i.e. 123456789012 in both columns. So to boil it down, if there is a hyphen in the data then the match will only be on the first 6 characters; if there is not a hyphen then the match will be on the whole field.

To help figure this out I started a very simple query with just the 2 specific tables and I'm trying to do it piece by piece. I thought the code below might work but I get a "Syntax error in join operation" message. Once I get that part figured out I need to add the standard join (where the records in both tables match).

SELECT tblMaterialChanges.Drawing
FROM tblMaterialChanges INNER JOIN tblRequesters
ON (tblMaterialChanges.Drawing = Left(tblRequesters.[Number], 6) WHERE (((tblRequesters.Number) = "*-*"))

I'm obviously still learning so it's entirely possible my thinking is off track. Any help would be appreciated. Thanks.

 

[PHV]

Have you tried this ?

ON tblMaterialChanges.Drawing = Left(tblRequesters.Number,6) OR tblMaterialChanges.Drawing = tblRequesters.Number

or even this ?

ON tblMaterialChanges.Drawing In (Left(tblRequesters.Number,6),tblMaterialChanges.Drawing)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BC98121]

Thanks, PH! I made that change and changed the join to a left join and got all the records I expected to see. (With the inner join I only saw those records without the hyphen; not sure why that is but I'll take it...)

Here's what worked:

SELECT tblMaterialChanges.Drawing
FROM tblMaterialChanges LEFT JOIN tblRequesters ON tblMaterialChanges.Drawing = Left(tblRequesters.Number, 6) OR tblMaterialChanges.Drawing = tblRequesters.Number

Thanks again!

BC

 

[BC98121]

Actually I spoke too soon. I no longer get syntax errors but if I expand my query and add another column (Originators) from tblRequesters, it only shows those Originators in the records without hyphens, when there should be an Originator in almost every record.

SELECT tblMaterialChanges.Drawing, tblRequesters.Originator
FROM tblMaterialChanges LEFT JOIN tblRequesters ON tblMaterialChanges.Drawing = Left(tblRequesters.Number, 6) OR tblMaterialChanges.Drawing = tblRequesters.Number

Any ideas?

Thanks.

BC

 

[PHV]

What about this ?

SELECT tblMaterialChanges.Drawing, tblRequesters.Originator
FROM tblMaterialChanges, tblRequesters
WHERE (tblMaterialChanges.Drawing = Left(tblRequesters.Number, 6) OR tblMaterialChanges.Drawing = tblRequesters.Number)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BC98121]

When I try that new code I get the 8 records where the data matches exactly. And I just realized that there are 3 records in both tables where the data is exactly six characters. And if I take out the OR and everything after it (see below), those 3 records are all that I get.

SELECT tblMaterialChanges.Drawing, tblRequesters.Originator
FROM tblMaterialChanges, tblRequesters
WHERE (tblMaterialChanges.Drawing = Left(tblRequesters.Number, 6))

Thanks.

BC

 

[BC98121]

Hello. I am making some progress but there's still something missing. Apparently there are records in tblMaterialChanges.Drawing that aren't in the tblRequesters table, and I do need those records to be displayed as well. I could do that with a left join but I'm not sure how to integrate that with the code below, which does give me those records that have the hyphen (in tblMaterialChanges but not in tblRequesters) as well as the records that are an exact match.

SELECT tblMaterialChanges.Drawing, tblRequesters.Originator
FROM tblRequesters, tblMaterialChanges
WHERE (((IIf([tblMaterialChanges.Drawing] Like "*-*",Left([Drawing],6)))=[tblRequesters].[Number])) OR (((tblMaterialChanges.Drawing)=[tblRequesters].[Number]));

Any help would be greatly appreciated.

Thanks.

BC