Need help converting a MS Access query to MS SQL - Definitely need an expert for this one.

it looks ok for me, just to highlight for char comparison, try use single quotes ' instead of double quotes, such try something like this instead:

WHERE (((Parts4Sales.Quantity)>0) AND ((ComponentMaster.searchNumber) Is Not Null And (ComponentMaster.searchNumber)<>'') AND ((Parts4Sales.InventoryType)='Available') AND ((Origin.Status)='active') AND ((Parts4Sales.DanDE)='0'));

Select allOpen in new window

another thing is to make sure the field's data type are converted properly so that to minimize any potential syntax errors

There are some small differences in syntax. SQL Server doesn't require as many parentheses, and it uses apostrophes instead of quotation marks around strings.

You can use aliases on tables to make the code less repetetive.

This is the first part of the view, you should be able to make the same changes to the rest of the code. I have used aliases for the table names, but that is not needed if you don't want to. I formatted the code to make it a bit more readable, but that's not required either.

SELECT
   p.Quantity,
   m.ShortName AS MFG,
   c.OrderNumber AS MPN,
   p.DateCodeYear AS DC,
   p.SalesNote AS Comment,
   p.ConditionCategory AS Condition,
   p.InventoryType,
   p.DanDE
FROM
   Parts4Sales p
   LEFT JOIN ManufacturerMaster m ON p.LineID = m.LineID
   INNER JOIN Origin o ON p.OriginCode = o.OriginCode
   INNER JOIN ComponentMaster c ON p.SearchNumber = c.SearchNumber AND p.LineID = c.LineID
WHERE
   p.Quantity > 0 AND
   c.searchNumber Is Not Null And
   c.searchNumber <> '' AND
   p.InventoryType = 'Available' AND
   o.Status = 'active' AND
   p.DanDE = '0'

UNION ALL

...

Select allOpen in new window

Thanks to both of you for offering help but I"m not there yet...

I copied Goran's exact code into my system and ran it.
I got the following error:

Msg 102, Level 15, State 1, Line 27
Incorrect syntax near 'ALL'.

I didn't see this anywhere:
INSERT INTO PCC_ALL SELECT [qryPCC_NJ-OEMEXCESS-Union].* FROM [qryPCC_NJ-OEMEXCESS-Union]; 

I"m not sure how to combine the two.

I am sure that your original query in Access has a setting for distinct rows or values:
In that case I don't see the reason of UNION ALLs just try this:

SELECT  Parts4Sales.Quantity,
        ManufacturerMaster.ShortName AS MFG,
        ComponentMaster.OrderNumber AS MPN,
        Parts4Sales.DateCodeYear AS DC,
        Parts4Sales.SalesNote AS Comment,
        Parts4Sales.ConditionCategory AS Condition,
        Parts4Sales.InventoryType,
        Parts4Sales.DanDE
FROM Parts4Sales
INNER JOIN Origin            ON  Origin.OriginCode = Parts4Sales.OriginCode
INNER JOIN ComponentMaster   ON  ComponentMaster.SearchNumber = Parts4Sales.SearchNumber
                             AND ComponentMaster.LineID = Parts4Sales.LineID
LEFT JOIN ManufacturerMaster ON  ManufacturerMaster.LineID = Parts4Sales.LineID
WHERE Parts4Sales.Quantity > 0
AND Parts4Sales.InventoryType = 'Available'
AND Origin.Status = 'active'
AND Parts4Sales.DanDE = '0'
AND (
       ComponentMaster.searchNumber <> ''
    OR ComponentMaster.AlternatesearchNumber <> ''
    OR ComponentMaster.AlternatesearchNumber2 <> ''
    OR ComponentMaster.AlternatesearchNumber3 <> ''
    OR ComponentMaster.AlternatesearchNumber4 <> ''
    OR ComponentMaster.AlternatesearchNumber5 <> ''
)

Select allOpen in new window

1. Instead of concatenating 6 queries by UNION ALLs use only one above
2. You don't need to use filter IS NOT NULL because <> '' already filters out NULLs
3. If you want to use UNION ALLs then remove semicolons between queries

Why do you need 5 unions? One a little bit more complex WHERE will solve it all:

INSERT INTO PCC_ALL
SELECT Parts4Sales.Quantity, ManufacturerMaster.ShortName AS MFG, 
       ComponentMaster.OrderNumber AS MPN, 
       Parts4Sales.DateCodeYear AS DC, 
       Parts4Sales.SalesNote AS Comment, 
       Parts4Sales.ConditionCategory AS Condition, 
       Parts4Sales.InventoryType, Parts4Sales.DanDE
 FROM  Parts4Sales 
 LEFT JOIN ManufacturerMaster ON Parts4Sales.LineID = ManufacturerMaster.LineID
INNER JOIN Origin ON Parts4Sales.OriginCode = Origin.OriginCode 
INNER JOIN ComponentMaster ON Parts4Sales.SearchNumber = ComponentMaster.SearchNumber AND Parts4Sales.LineID = ComponentMaster.LineID
WHERE Parts4Sales.Quantity > 0 
  AND Parts4Sales.InventoryType = 'Available' 
  AND Origin.Status = 'active' 
  AND Parts4Sales.DanDE = '0'
  AND (   ComponentMaster.searchNumber <> '' 
       OR ComponentMaster.AlternatesearchNumber <> '' 
       OR ComponentMaster.AlternatesearchNumber2 <> ''  
       OR ComponentMaster.AlternatesearchNumber3 <> ''
       OR ComponentMaster.AlternatesearchNumber4 <> ''
       OR ComponentMaster.AlternatesearchNumber5 <> ''
      )

Select allOpen in new window

Hmm... Máté is faster

I am so sorry, I misspoke when I stated:
<<It's not as complex as it at first seems.. only the first two clauses in the WHERE differ in each section.  >>
I should have said:
only the MPN portion of the SELECT and the first two clauses in the WHERE differ in each section. 

Sometimes manufacturer's use different Manufacturer Part Numbers for the same part. I call the primary MPN OrderNumber, the second AlternateOrderNumber, the third AlternateOrderNumber2, and so forth.

The purpose of this query is to create on table that has one row for each ordernumber.  Note that the WHERE clause does not use OrderNumber be SearchNumber instead.. which is the Order Number with special characters removed to simplying searching...

PLEASE.. don't beat me up for this database design.. I created this structure in the days of Access 95... when many of you were'nt even born yet.. I wouldn't do it this way today.. but I'm not about to make a change at this point!  

Thanks for the continued help... but I don't think the queries submitted will work.. will they?

(another chance pcelba??? you've been a big help to me in the past!)

Máté  
<<I am sure that your original query in Access has a setting for distinct rows or values: >>
I didn't understand your comment.. I am sure I am using the query defaults???
I generate my queries in code so I never see the query properties...

<< You don't need to use filter IS NOT NULL because <> '' already filters out NULLs>>
Just to be clear.. are you saying inT-SQL both empty '' and null match ''?
Definitely not the case in Access??  If so, I guess this is an area of non-compatibility I need to watch for??

In Union queries the 2 things that matter are:
The "sub" queries are executing correctly and they all return exactly the same number of fields
So work on each of the queries one by one and you should be able to work it out.
With a quick glance i thin you can do it with out a query but with out the underlying data i can't be certain.

OK, then you would need a small update in the SELECT list:

SELECT Parts4Sales.Quantity, ManufacturerMaster.ShortName AS MFG, 
       CASE WHEN ComponentMaster.searchNumber <> ''
            THEN ComponentMaster.OrderNumber 
            WHEN ComponentMaster.AlternatesearchNumber <> ''
            THEN ComponentMaster.AlternateOrderNumber 
            WHEN ComponentMaster.AlternatesearchNumber2 <> ''
            THEN ComponentMaster.AlternateOrderNumber2 
            WHEN ComponentMaster.AlternatesearchNumber3 <> ''
            THEN ComponentMaster.AlternateOrderNumber3 
            WHEN ComponentMaster.AlternatesearchNumber4 <> ''
            THEN ComponentMaster.AlternateOrderNumber4 
            WHEN ComponentMaster.AlternatesearchNumber5 <> ''
            THEN ComponentMaster.AlternateOrderNumber5 
       END AS MPN, 
       Parts4Sales.DateCodeYear AS DC, 
       Parts4Sales.SalesNote AS Comment, 
       Parts4Sales.ConditionCategory AS Condition, 
       Parts4Sales.InventoryType, Parts4Sales.DanDE
 FROM  Parts4Sales 
 LEFT JOIN ManufacturerMaster ON Parts4Sales.LineID = ManufacturerMaster.LineID
INNER JOIN Origin ON Parts4Sales.OriginCode = Origin.OriginCode 
INNER JOIN ComponentMaster ON Parts4Sales.SearchNumber = ComponentMaster.SearchNumber AND Parts4Sales.LineID = ComponentMaster.LineID
WHERE Parts4Sales.Quantity > 0 
  AND Parts4Sales.InventoryType = 'Available' 
  AND Origin.Status = 'active' 
  AND Parts4Sales.DanDE = '0'
  AND (   ComponentMaster.searchNumber <> '' 
       OR ComponentMaster.AlternatesearchNumber <> '' 
       OR ComponentMaster.AlternatesearchNumber2 <> ''  
       OR ComponentMaster.AlternatesearchNumber3 <> ''
       OR ComponentMaster.AlternatesearchNumber4 <> ''
       OR ComponentMaster.AlternatesearchNumber5 <> ''
      )

Select allOpen in new window

Of course, the above query does not provide more rows having different MPNs when several order numbers exist and we are back in UNIONs ...

As I said, I only converted the first part of the query. Do the same adjustments to the rest of the query.

You can put the query in a view (named qryPCC_NJ-OEMEXCESS-Union) and use it like this:

INSERT INTO PCC_ALL SELECT * FROM qryPCC_NJ-OEMEXCESS-Union

Select allOpen in new window

You can also just put this in front of the query:

INSERT INTO PCC_ALL

Select allOpen in new window

@pcelba...
<<the above query does not provide more rows having different MPNs when several order numbers exist and we are back in UNIONs>>
Yes, we need to provide additional rows when more than one MPN exists.  These different part numbers are in the AlternateOrderNumber fields.. All parts will have an Ordernumber.. up to five optional Alternate numbers will be in the database...

I'm taking time to understand all the code submitted..  not sure how/where we would put the Unions back in.. I've never used a Union with SQL???.

@John.. this is an SQL conversion of a Union query that I build in Access and have been using for some while.  I'm pretty sure you helped me build the original query.. it was my first experience with Union.... I'm now forced to migrate to SQL... Thanks!

@Göran 
Thanks.. I've never created a view before.. obviously I've heard of them :-)
I was thinking along that line... thanks.

@Pat my memory is not at my best but probably i helped in getting it working not "optimizing" it.
Start slowly...just copy paste the 1st subquery to SQL Server Management Studio, execute it and see where it encounters issues...the errors you will fix should be applicable to all the others
From another quick examine and formatting

SELECT Parts4Sales.Quantity
   ,ManufacturerMaster.ShortName AS MFG
   ,ComponentMaster.OrderNumber AS MPN
   ,Parts4Sales.DateCodeYear AS DC
   ,Parts4Sales.SalesNote AS Comment
   ,Parts4Sales.ConditionCategory AS Condition
   ,Parts4Sales.InventoryType
   ,Parts4Sales.DanDE
FROM (
   (
      Parts4Sales LEFT JOIN ManufacturerMaster ON Parts4Sales.LineID = ManufacturerMaster.LineID
      ) INNER JOIN Origin ON Parts4Sales.OriginCode = Origin.OriginCode
   )
INNER JOIN ComponentMaster ON (Parts4Sales.SearchNumber = ComponentMaster.SearchNumber)
   AND (Parts4Sales.LineID = ComponentMaster.LineID)
WHERE (
      ((Parts4Sales.Quantity) > 0)
      AND (
         (ComponentMaster.searchNumber) IS NOT NULL
         AND (ComponentMaster.searchNumber) <> ""
         )
      AND ((Parts4Sales.InventoryType) = "Available")
      AND ((Origin.STATUS) = "active")
      AND ((Parts4Sales.DanDE) = "0")
      );

Select allOpen in new window

just copy paste it...
Does it work ?
Assuming NO, just remove all the Where Conditions...just leave it the simple SELECT with joins ...it should work..if not check the keys and their datatypes...
Back to Where...add condition one by one...each time check if it works...do it one step at a time...

John..working on it now.. give me five minutes

John.. I had to replace the double quotes with single quotes.. then it runs fine.. at least it creates a query as I would expect..

The problem before was when it hit the Union All.
I thought I posted the code but don't see it.. will have to do so again.

A

Here is the exact code I used with hope to be able to save it as a view.
The errors I received are:

Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'UNION'.
Msg 156, Level 15, State 1, Line 58
Incorrect syntax near the keyword 'UNION'.
Msg 156, Level 15, State 1, Line 85
Incorrect syntax near the keyword 'UNION'.
Msg 156, Level 15, State 1, Line 112
Incorrect syntax near the keyword 'UNION'.
Msg 156, Level 15, State 1, Line 139
Incorrect syntax near the keyword 'UNION'.
Msg 156, Level 15, State 1, Line 166
Incorrect syntax near the keyword 'UNION'.

Completion time: 2021-01-02T15:33:31.4792228-05:00

use [Inventory_ComponentMaster_SQL]
go

SELECT
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity,
Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG,
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.OrderNumber AS MPN,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE

FROM
Inventory_ComponentMaster_SQL.dbo.Parts4Sales LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID

WHERE
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity>0 AND
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.searchNumber Is Not Null And
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.searchNumber<>'' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType='Available' AND
Inventory_ComponentMaster_SQL.dbo.Origin.Status='active' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE='0';

UNION ALL



SELECT
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity,
Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG,
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber AS MPN,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE

FROM
Inventory_ComponentMaster_SQL.dbo.Parts4Sales LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID

WHERE
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity>0 AND
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber Is Not Null And
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber<>'' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType='Available' AND
Inventory_ComponentMaster_SQL.dbo.Origin.Status='active' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE='0';

UNION ALL

SELECT
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity,
Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG,
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber2 AS MPN,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE

FROM
Inventory_ComponentMaster_SQL.dbo.Parts4Sales LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID

WHERE
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity>0 AND
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber2 Is Not Null And
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber2<>'' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType='Available' AND
Inventory_ComponentMaster_SQL.dbo.Origin.Status='active' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE='0';

UNION ALL

SELECT
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity,
Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG,
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber3 AS MPN,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE

FROM
Inventory_ComponentMaster_SQL.dbo.Parts4Sales LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID

WHERE
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity>0 AND
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber3 Is Not Null And
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber3<>'' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType='Available' AND
Inventory_ComponentMaster_SQL.dbo.Origin.Status='active' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE='0';

UNION ALL

SELECT
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity,
Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG,
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber4 AS MPN,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE

FROM
Inventory_ComponentMaster_SQL.dbo.Parts4Sales LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID

WHERE
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity>0 AND
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber4 Is Not Null And
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber4<>'' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType='Available' AND
Inventory_ComponentMaster_SQL.dbo.Origin.Status='active' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE='0';

UNION ALL

SELECT
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity,
Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG,
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber5 AS MPN,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType,
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE

FROM
Inventory_ComponentMaster_SQL.dbo.Parts4Sales LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID

WHERE
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity>0 AND
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber5 Is Not Null And
Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber<>'' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType='Available' AND
Inventory_ComponentMaster_SQL.dbo.Origin.Status='active' AND
Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE='0';

UNION ALL

remove the ";"

SELECT Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity
   ,Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG
   ,Inventory_ComponentMaster_SQL.dbo.ComponentMaster.OrderNumber AS MPN
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE
FROM Inventory_ComponentMaster_SQL.dbo.Parts4Sales
LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID
WHERE Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity > 0
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.searchNumber IS NOT NULL
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.searchNumber <> ''
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType = 'Available'
   AND Inventory_ComponentMaster_SQL.dbo.Origin.STATUS = 'active'
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE = '0'


UNION ALL


SELECT Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity
   ,Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG
   ,Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber AS MPN
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE
FROM Inventory_ComponentMaster_SQL.dbo.Parts4Sales
LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID
WHERE Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity > 0
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber IS NOT NULL
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber <> ''
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType = 'Available'
   AND Inventory_ComponentMaster_SQL.dbo.Origin.STATUS = 'active'
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE = '0'


UNION ALL


SELECT Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity
   ,Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG
   ,Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber2 AS MPN
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE
FROM Inventory_ComponentMaster_SQL.dbo.Parts4Sales
LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID
WHERE Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity > 0
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber2 IS NOT NULL
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber2 <> ''
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType = 'Available'
   AND Inventory_ComponentMaster_SQL.dbo.Origin.STATUS = 'active'
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE = '0'


UNION ALL


SELECT Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity
   ,Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG
   ,Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber3 AS MPN
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE
FROM Inventory_ComponentMaster_SQL.dbo.Parts4Sales
LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID
WHERE Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity > 0
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber3 IS NOT NULL
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber3 <> ''
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType = 'Available'
   AND Inventory_ComponentMaster_SQL.dbo.Origin.STATUS = 'active'
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE = '0'


UNION ALL


SELECT Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity
   ,Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG
   ,Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber4 AS MPN
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE
FROM Inventory_ComponentMaster_SQL.dbo.Parts4Sales
LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID
WHERE Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity > 0
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber4 IS NOT NULL
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber4 <> ''
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType = 'Available'
   AND Inventory_ComponentMaster_SQL.dbo.Origin.STATUS = 'active'
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE = '0';


UNION ALL


SELECT Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity
   ,Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.ShortName AS MFG
   ,Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternateOrderNumber5 AS MPN
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DateCodeYear AS DC
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SalesNote AS Comment
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.ConditionCategory AS Condition
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType
   ,Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE
FROM Inventory_ComponentMaster_SQL.dbo.Parts4Sales
LEFT JOIN Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ManufacturerMaster.LineID
INNER JOIN Inventory_ComponentMaster_SQL.dbo.Origin ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.OriginCode = Inventory_ComponentMaster_SQL.dbo.Origin.OriginCode
INNER JOIN Inventory_ComponentMaster_SQL.dbo.ComponentMaster ON Inventory_ComponentMaster_SQL.dbo.Parts4Sales.SearchNumber = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.SearchNumber
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.LineID = Inventory_ComponentMaster_SQL.dbo.ComponentMaster.LineID
WHERE Inventory_ComponentMaster_SQL.dbo.Parts4Sales.Quantity > 0
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber5 IS NOT NULL
   AND Inventory_ComponentMaster_SQL.dbo.ComponentMaster.alternatesearchNumber <> ''
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.InventoryType = 'Available'
   AND Inventory_ComponentMaster_SQL.dbo.Origin.STATUS = 'active'
   AND Inventory_ComponentMaster_SQL.dbo.Parts4Sales.DanDE = '0'

Select allOpen in new window

Yes, this should work. I would just highly recommend to replace the three part table qualification by local aliase.

I'll give it a try now.. I'm still trying to figure out how to use alias's

I really don't know how to award points on this one.. so many of you helped so much.. and there were so many bumps along the way.. but with all of your help.. I got it working.  

Thank you so much.  Words can't describe...

BTW.. the query in Access running as a FE/BE through a LAN... with 300,000 records with a join to a database with 10 million records.. took about 45 minutes to run... and often gave "System Resources Exceeded" message.

With your help.. the query now runs less than 20 seconds!  It seems like something must be missing but I haven't found it yet!

THANKS!
I awarded to PCCLBA because that we the last changes made which got the process to run.. and because I think I now understand how to and why to use an alias.

Thanks for the points. JFYI, you may mark several answers as the solution and I would guess this option is still open but I am not sure.

The difference between Access and SQL Server is in the available query optimizations. SQL Server engine can do miracles when appropriate indexes are present.