I have a table that lists invoice entries that I then import to sage accounting. from this information I have two querys:
1. takes all invoices with no "sage import" date.
2. takes all Credits with no "sage import" date.
They cant be on the same query as we use an expression to calculate the final figures. The credit calculation is slightly different.
At the moment my solution is to do the import process twice. I was wondering if someone could help me put these together. I've tried adding "unionall" after watching a video. Apparently its not that simple.
Any help appreciated, the SQL from both is below.
Code:
SELECT sage_Import.Import_Date AS [date], sage_Import.Audit_Type AS type, sage_Import.description AS description, sage_Import.invoice_num AS reference, Sum(sage_Import.subtotal) AS net, Sum(sage_Import.VAT) AS tax, sage_Import.[tax Code] AS [Tax Code], sage_Import.Customer_Ref AS [account ref], sage_Import.Nominal_Code AS Nominal
FROM sage_Import
GROUP BY sage_Import.Import_Date, sage_Import.Audit_Type, sage_Import.description, sage_Import.invoice_num, sage_Import.[tax Code], sage_Import.Customer_Ref, sage_Import.Nominal_Code
HAVING (((sage_Import.Audit_Type)="SI"));
SELECT sage_Import.Import_Date AS [date], sage_Import.Audit_Type AS type, sage_Import.description AS description, sage_Import.invoice_num AS reference, Sum([subtotal]*-1) AS net, Sum([VAT]*-1) AS tax, sage_Import.[tax Code] AS [Tax Code], sage_Import.Customer_Ref AS [account ref], sage_Import.Nominal_Code AS Nominal
FROM sage_Import
GROUP BY sage_Import.Import_Date, sage_Import.Audit_Type, sage_Import.description, sage_Import.invoice_num, sage_Import.[tax Code], sage_Import.Customer_Ref, sage_Import.Nominal_Code
HAVING (((sage_Import.Audit_Type)="SC"));
UNIONALL
Thanks, Andy