Not including all records

Matthew

New Member
Joined
Apr 16, 2002
Messages
23
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Apr 16, 2002
Messages
23
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
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

New Member
Joined
Apr 16, 2002
Messages
23

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hmm, so you are using this SQL:

Code:
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

New Member
Joined
Apr 16, 2002
Messages
23

ADVERTISEMENT

Did that and got the same error.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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):

Code:
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

New Member
Joined
Apr 16, 2002
Messages
23
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,766
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top