Skip to content
Advertisement

MSAccess subdatasheet

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: enter image description here 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;

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement