Tutorial: use MSQuery to create an analysis-ready SALES file in Sage. By David Carter

Dong

Cannot recall whether L50 allows service invoices to be raised (i.e. without stock). If it does not allow service invoices then it should only be possible to generate an invoice containing stock items - ergo the query will never drop records because it will always find the relationship

The point is that to be on the safe side one wants all invoices to appear irrespective of whether they contain a stock item. It is no good having things disappear because one of the tables doesn't contain a related item.

This is all down to the correct JOINS - so really it is nothing to do with missing stock items because the query should be sufficiently robust to accomodate this

There is always the possibility that L50 allows stock items to be deleted after invoices have been generated - in which case L50 is at fault but then that should not really come as a surprise to anyone

Below are the s3 records and none are stock related items. Surely this is the "simple" reason why they disappeared when you added the stock table?

Consultancy Fee
Installation of six user network
Installation of computer system
Repairs
Network Installation

One assumes that a Parent/Child relationship exists between Invoice and Invoice_Item.

With this in mind the JOIN should really start at the top level (Invoice) and cascade down; by adopting this approach one can perform LEFT, RIGHT (INNER/OUTER joins) and use a LEFT JOIN between Invoice and Invoice_Item (i.e. all Invoice records irrespective of whether there are Invoice_Item child records)

Explanation - in theory it should be possible to have an Invoice record without an Invoice_Item record - but not the other way round

The reason S3 records are being lost is probably because of the wrong starting point - Invoice_Item instead of Invoice; this in turn is causing the JOIN problem

Once again the reason for 'dropping' records is probably the wrong starting point

See the following example (only a few fields) with all 4 tables joined

SELECT INVOICE.INVOICE_DATE, INVOICE.INVOICE_NUMBER, INVOICE_ITEM.NET_AMOUNT, INVOICE_ITEM.TAX_AMOUNT, STOCK.DESCRIPTION, SALES_LEDGER.NAME
FROM ((INVOICE LEFT JOIN INVOICE_ITEM ON INVOICE.INVOICE_NUMBER = INVOICE_ITEM.INVOICE_NUMBER) LEFT JOIN STOCK ON INVOICE_ITEM.STOCK_CODE = STOCK.STOCK_CODE) LEFT JOIN SALES_LEDGER ON INVOICE.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF
ORDER BY INVOICE.INVOICE_DATE DESC;

Just copy & paste the above into Access - it should also work in other apps provided there is somewhere to enter SQL

A generic way of working around the lack of being able to outer join when there are more than two tables is to do a union query.  The union adds a query including all the tables except the stock table with the additional criterion of INVOICE_ITEM.STOCK_CODE=S3.  This query would have to include dummy column entries for any stock table columns in the main query.  Unfortunately the Sage ODBC driver doesn't seem to support this construct. Plan B then is to have a completely separate querytable immediately below the first one where the combined output mimics what a single union query would have achieved.  Of course, now you have to refresh two querytables, not one. :(