Union query.

[Homegrownandy]

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.

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

[orange]

Wouldn't you process all the transactions in SAGE. That is the raw transactions, not summaries.
What exactly does SAGE software expect?

[June7]

Place the UNION ALL (note the space) in front of the second SELECT:

SELECT ...
UNION ALL SELECT ...;

Be sure to remove the semicolon from the first SELECT, should only be on the last SELECT. Up to 50 SELECT statements allowed.

The first SELECT defines field names so the aliases used in subsequent SELECT are ignored.


I also wonder why you are exporting aggregated data.

[Homegrownandy]

Orange: That's why they are being imported to sage. These are all required fields in sage to import a transaction (including the VAT).


Our database is linked to each job and person who's done it, When they went, what they did, what went wrong. Its simple enough to put on the database an associated order then one button produces a custom invoice. We could do without Sage totally to be honest but people are stuck in their ways.

Its simple enough like this anyway. I just log on to sage and import a file and leave the rest to accounts. Just trying to make it one file not two.

June: ill have a go thanks for the reply.

[Homegrownandy]

Took me a while to realise what you were meaning there Jane, Got there now!

Appreciate the help it works fine!