I have an Access query, listing invoices and related items in subdatasheet:
SELECT DISTINCTROW OutgoingInvoice.InvoiceID, OutgoingInvoice.Sum, OutgoingInvoice.Complete, Sum(Products.IncomingSUM) AS [Sum Of Products_SUM], [OutgoingInvoice].[Sum]-[Sum Of Products_SUM] AS Különbözet, OutgoingInvoice.Note, OutgoingInvoice.ID FROM OutgoingInvoice INNER JOIN Products ON OutgoingInvoice.[ID] = Products.[OutgoingInvoice] GROUP BY OutgoingInvoice.InvoiceID, OutgoingInvoice.Sum, OutgoingInvoice.Complete, OutgoingInvoice.Note, OutgoingInvoice.ID;
The problem, that invoices without items not shown: How can I manage to display all the rows from OutgoingInvoice (even if they do not have joined subitem)
Advertisement
Answer
SELECT OutgoingInvoice.InvoiceID, OutgoingInvoice.Sum, OutgoingInvoice.Complete, Sum(Products.IncomingSUM) AS [Sum Of Products_SUM], [OutgoingInvoice].[Sum]-[Sum Of Products_SUM] AS Különbözet, OutgoingInvoice.Note, OutgoingInvoice.ID FROM OutgoingInvoice LEFT JOIN Products ON OutgoingInvoice.[ID] = Products.[OutgoingInvoice] GROUP BY OutgoingInvoice.InvoiceID, OutgoingInvoice.Sum, OutgoingInvoice.Complete, OutgoingInvoice.Note, OutgoingInvoice.ID;