Error in Query How to fix

[Basshopper]

I am trying to match up through this query of 2 table on 2 fields Mfg # and Description from both tables. I keep getting an error on can't join any suggestions. Here is my code

SELECT [t_Quote BOM].[Customer ID], [t_Quote BOM].[Assembly #], [t_Quote BOM].

, [t_Quote BOM].[Line #], [t_Quote BOM].[MFG #], [t_Max Part Master Updated].[Ascentron #], [t_Max Part Master Updated].Description, [t_Max Part Master Updated].[Std Cost], [t_Max Part Master Updated].[Last Cost], [t_Max Part Master Updated].[Last Cst Qty], [t_Max Part Master Updated].Date
FROM [t_Max Part Master Updated] INNER JOIN [t_Quote BOM] ON ([t_Max Part Master Updated].[Mfg #] = [t_Quote BOM].[MFG #]) OR ([t_Max Part Master Updated].Description = [t_Quote BOM].Description)
WHERE ((([t_Quote BOM].[Customer ID]) Like [forms]![Parameter]![Customer ID] & "*") AND (([t_Quote BOM].[Assembly #]) Like [FORMS]![Parameter]![Assembly #] & "*") AND (([t_Quote BOM].

) Like [FORMS]![Parameter]!

& "*"))
ORDER BY [t_Quote BOM].[Line #], [t_Max Part Master Updated].Date;

 

[Basshopper]

copied wrong code here is the one I am useing


SELECT [t_Quote BOM].[Customer ID], [t_Quote BOM].[Assembly #], [t_Quote BOM].

, [t_Quote BOM].[Line #], [t_Quote BOM].[MFG #], [t_Max Part Master Updated].[Ascentron #], [t_Max Part Master Updated].Description, [t_Max Part Master Updated].[Std Cost], [t_Max Part Master Updated].[Last Cost], [t_Max Part Master Updated].[Last Cst Qty], [t_Max Part Master Updated].Date
FROM [t_Max Part Master Updated] INNER JOIN [t_Quote BOM] ON ([t_Max Part Master Updated].[Mfg #] = [t_Quote BOM].[MFG #]) OR ([t_Max Part Master Updated].Description = [t_Quote BOM].Description)
WHERE ((([t_Quote BOM].[Customer ID]) Like [forms]![Parameter]![Customer ID] & "*") AND (([t_Quote BOM].[Assembly #]) Like [FORMS]![Parameter]![Assembly #] & "*") AND (([t_Quote BOM].

) Like [FORMS]![Parameter]!

& "*"))
ORDER BY [t_Quote BOM].[Line #], [t_Max Part Master Updated].Date;

 

[lespaul]

umm, i know that you can use an AND in the join statement to join on multiple fields, but I don't think you can use an OR to join on alternate fields.



Leslie

 

[PHV]

You may try this:
SELECT [t_Quote BOM].[Customer ID], [t_Quote BOM].[Assembly #], [t_Quote BOM].

, [t_Quote BOM].[Line #], [t_Quote BOM].[MFG #], [t_Max Part Master Updated].[Ascentron #], [t_Max Part Master Updated].Description, [t_Max Part Master Updated].[Std Cost], [t_Max Part Master Updated].[Last Cost], [t_Max Part Master Updated].[Last Cst Qty], [t_Max Part Master Updated].Date
FROM [t_Max Part Master Updated], [t_Quote BOM]
WHERE ([t_Max Part Master Updated].[Mfg #]=[t_Quote BOM].[MFG #] OR [t_Max Part Master Updated].Description=[t_Quote BOM].Description)
AND ((([t_Quote BOM].[Customer ID]) Like [forms]![Parameter]![Customer ID] & "*") AND (([t_Quote BOM].[Assembly #]) Like [FORMS]![Parameter]![Assembly #] & "*") AND (([t_Quote BOM].

) Like [FORMS]![Parameter]!

& "*"))
ORDER BY [t_Quote BOM].[Line #], [t_Max Part Master Updated].Date;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Another way is to use an UNION query:
SELECT ...
FROM [t_Max Part Master Updated] INNER JOIN [t_Quote BOM] ON ([t_Max Part Master Updated].[Mfg #] = [t_Quote BOM].[MFG #])
...
UNION SELECT ...
FROM [t_Max Part Master Updated] INNER JOIN [t_Quote BOM] ON ([t_Max Part Master Updated].Description = [t_Quote BOM].Description)
...
ORDER BY 4, 11;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Basshopper]

Hi PH; the first code you gave me works great no erros, it runs a little slow and once I get lots of data it might run alot slower. Are you suggesting on your 2nd post to insert that code in place of which lines. the first one no problem, where does the union select statement go

thanks

 

[PHV]

SELECT [t_Quote BOM].[Customer ID], [t_Quote BOM].[Assembly #], [t_Quote BOM].

, [t_Quote BOM].[Line #], [t_Quote BOM].[MFG #], [t_Max Part Master Updated].[Ascentron #], [t_Max Part Master Updated].Description, [t_Max Part Master Updated].[Std Cost], [t_Max Part Master Updated].[Last Cost], [t_Max Part Master Updated].[Last Cst Qty], [t_Max Part Master Updated].Date
FROM [t_Max Part Master Updated] INNER JOIN [t_Quote BOM] ON ([t_Max Part Master Updated].[Mfg #] = [t_Quote BOM].[MFG #])
WHERE ((([t_Quote BOM].[Customer ID]) Like [forms]![Parameter]![Customer ID] & "*") AND (([t_Quote BOM].[Assembly #]) Like [FORMS]![Parameter]![Assembly #] & "*") AND (([t_Quote BOM].

) Like [FORMS]![Parameter]!

& "*"))
UNION
SELECT [t_Quote BOM].[Customer ID], [t_Quote BOM].[Assembly #], [t_Quote BOM].

, [t_Quote BOM].[Line #], [t_Quote BOM].[MFG #], [t_Max Part Master Updated].[Ascentron #], [t_Max Part Master Updated].Description, [t_Max Part Master Updated].[Std Cost], [t_Max Part Master Updated].[Last Cost], [t_Max Part Master Updated].[Last Cst Qty], [t_Max Part Master Updated].Date
FROM [t_Max Part Master Updated] INNER JOIN [t_Quote BOM] ON ([t_Max Part Master Updated].Description = [t_Quote BOM].Description)
WHERE ((([t_Quote BOM].[Customer ID]) Like [forms]![Parameter]![Customer ID] & "*") AND (([t_Quote BOM].[Assembly #]) Like [FORMS]![Parameter]![Assembly #] & "*") AND (([t_Quote BOM].

) Like [FORMS]![Parameter]!

& "*"))
ORDER BY 4, 11;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Basshopper]

Excellant speed are us.


thanks again for your help