Not including all records

[Matthew]

HI all, hope someone can help with this.

I have an MS query with four data table in it linked, but when I run it not all the records from the files are pulled back. I am trying to create a back order report, the table are stock (STK), sales order line (SOE), sale order hearer (SOH), and purchase lines (POE).

They are linked are follows,

SOH to SOE by Order Number
SOE to STK by Part
STK to POE by Part

The problem is that not all the part are in the POE table (depending in on order or not), so it only brings back Parts that are in both tables, and as there are more than two tables I can not edit the link. So I end up missing some orders, from the sales side.

Any thought on how to get over this would be most welcome. Thanks in advance.

 

[Richard Schollar]

Hi

You need to use an outer join rather than an Inner one. Post the SQL you are currently using and we can advise you how to change it.

 

[Matthew]

Richard, thanks for this

This is the SQL.

SELECT "Sales Order entry file Co 01"."Order Number", "Sales Order entry file Co 01"."Date Required", "Sales Order entry file Co 01"."Part number", "Stock file Co 01".Description_, "Sales Order entry file Co 01"."Qty to follow", "Sales Order Header file Co 01"."Sales Rep", "Sales Order Header file Co 01"."Delivery A/c", "Sales Order Header file Co 01"."Delivery A/c Name", "Sales Order Header file Co 01"."Order Date", "Stock file Co 01"."P/O Outstdng", "Purchase entries file Co 01"."Order Number", "Purchase entries file Co 01"."Qty not received", "Purchase entries file Co 01"."Date Expected", "Sales Order Header file Co 01"."Order Type", "Stock file Co 01"."Main Stock"
FROM "Purchase entries file Co 01" "Purchase entries file Co 01", "Sales Order entry file Co 01" "Sales Order entry file Co 01", "Sales Order Header file Co 01" "Sales Order Header file Co 01", "Stock file Co 01" "Stock file Co 01"
WHERE "Sales Order Header file Co 01"."Order Number" = "Sales Order entry file Co 01"."Order Number" AND "Stock file Co 01"."Part number" = "Sales Order entry file Co 01"."Part number" AND "Purchase entries file Co 01"."Part number" = "Stock file Co 01"."Part number" AND (("Sales Order entry file Co 01"."Line Type"<>'T') AND ("Sales Order entry file Co 01"."Qty Required"<>0) AND ("Sales Order entry file Co 01"."Qty to follow">0) AND ("Sales Order Header file Co 01"."Order Type"<>'E') AND ("Purchase entries file Co 01"."Qty not received">0))

Thanks again, Matthew

 

[Richard Schollar]

Hi matt

Give the following a try - copy it exactly and do not make any changes. Paste it into the SQL window in MSQuery and hit OK:

SELECT T2."Order Number", T2."Date Required", T2."Part number", T4.Description_, T2."Qty to follow", T3."Sales Rep", T3."Delivery A/c", T3."Delivery A/c Name", T3."Order Date", T4."P/O Outstdng", T1."Order Number", T1."Qty not received", T1."Date Expected", T3."Order Type", T4."Main Stock"
FROM ("Stock Qfile Co 01" T4 LEFT JOIN "Purchase entries file Co 01" T1 ON T4."Part Number" = T1."Part Number") LEFT JOIN "Sales Order entry file Co 01" T2 ON T4."Part Number" = T2."Part Number", "Sales Order Header file Co 01" T3 
WHERE T3."Order Number" = T2."Order Number" AND ((T2."Line Type"<>'T') AND (T2."Qty Required"<>0) AND (T2."Qty to follow">0) AND (T3."Order Type"<>'E') AND (T1."Qty not received">0))

 

[Matthew]

Richard,

Thanks for coming back to me.

I did take and extra q in the stock file refferance, but get this error " Could not add the table '("Stoke file Co 01"'. "

Have tryed to adj it but get no further.

Any thoughts?

Matthew

 

[Richard Schollar]

Hmm, so you are using this SQL:

SELECT T2."Order Number", T2."Date Required", T2."Part number", T4.Description_, T2."Qty to follow", T3."Sales Rep", T3."Delivery A/c", T3."Delivery A/c Name", T3."Order Date", T4."P/O Outstdng", T1."Order Number", T1."Qty not received", T1."Date Expected", T3."Order Type", T4."Main Stock"  
FROM ("Stock file Co 01" T4 LEFT JOIN "Purchase entries file Co 01" T1 ON T4."Part Number" = T1."Part Number") LEFT JOIN "Sales Order entry file Co 01" T2 ON T4."Part Number" = T2."Part Number", "Sales Order Header file Co 01" T3 
WHERE T3."Order Number" = T2."Order Number" AND ((T2."Line Type"<>'T') AND (T2."Qty Required"<>0) AND (T2."Qty to follow">0) AND (T3."Order Type"<>'E') AND (T1."Qty not received">0))

and this results in the error (please humour me and paste in the above) - sorry about the spurious Q, a result of doing Find/Replace in Notepad...

 

[Matthew]

Did that and got the same error.

 

[Richard Schollar]

Hmm, I'm not in a position to test at the moment, so things I suggest may not do anything at all - try a slightly amended string (moving the opening bracket a little):

SELECT T2."Order Number", T2."Date Required", T2."Part number", T4.Description_, T2."Qty to follow", T3."Sales Rep", T3."Delivery A/c", T3."Delivery A/c Name", T3."Order Date", T4."P/O Outstdng", T1."Order Number", T1."Qty not received", T1."Date Expected", T3."Order Type", T4."Main Stock"  
FROM (   "Stock file Co 01" T4 LEFT JOIN "Purchase entries file Co 01" T1 ON T4."Part Number" = T1."Part Number"   ) LEFT JOIN "Sales Order entry file Co 01" T2 ON T4."Part Number" = T2."Part Number", "Sales Order Header file Co 01" T3 
WHERE T3."Order Number" = T2."Order Number" AND ((T2."Line Type"<>'T') AND (T2."Qty Required"<>0) AND (T2."Qty to follow">0) AND (T3."Order Type"<>'E') AND (T1."Qty not received">0))

This shouldn't change anything, but we'll give it a try. Report back if the error is any different.

 

[Matthew]

Richard

Tried the "(" in a few options and it had no difference, but if I remove the "()" then it seems to get further but has an error on the LEFT JOIN. Will keep looking.

Thanks again for your help.

Matthew