Append Union Queries

[Steph_canoe]

Hello,

I'm creating reports using multiple append queries to add data to a table
which I export in Excel and send over to customers. My table has 16 fields
from "Field1" to "Field16".

My concern is that I can probably combine multiple queries in once using
UNION ALL instead of running 6 queries in a Macro.

I've tried without any success :

1st query : To add text "Monthly Report" on line 1

INSERT INTO [Monthly Report] ( Field1 )
SELECT "Monthly Report" AS Field1;


2nd query : To add the time period

INSERT INTO [Monthly Report] ( Field1 )
SELECT "From" & (Date()-7)-Weekday(Date())+1 & " to " &
(Date()-7)-Weekday(Date())+7 AS Field1;

3rd query : To add the company name

INSERT INTO [Monthly Report] ( Field1 )
SELECT "Report generated for: Addendum" AS Field1;

4th query : To add columns headers

INSERT INTO [Monthly Report] ( Field1, Field2, Field3, Field4, Field5,
Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14,
Field15, Field16 )
SELECT "Client Group Package " AS [Groupe Client Forfait], "File Number" AS
[# de Dossier], "Company" AS Compagnie, "Contact" AS Contact, "Creation Date"
AS [Date de Création], "Position Number" AS [# du Poste], "Reference Number"
AS [Numéro de référence], "City-Region" AS VilleRégion, "Posting Title" AS
[Nom du Poste], "Posting Days" AS Affichage, "Broadcast" AS Diffusion,
"Broadcasted Emails" AS [Emails diff], "Publication" AS Publication,
"Featured Job" AS [Emplois Vedette], "Jobboom Applications" AS [Nombre de
postulation], "External Applications" AS [Nombre de postulation externe];

5th query : To add the data under column headers

Code is too big here

6th query : To add total (SUM) for columns containing integers.

Is there a way to combine some queries into Union queries to ease the
process a little bit ?

I've tried with 1st and 2nd queries but all I got is an error saying that
Query input must contain at least one table or query. (Error 3067)

Thanks

 

[Rob Wills]

one of the easiest ways to do this is to create 1 query that houses your
standard Union Query

[SELECT *
FROM tableA

UNION SELECT *
FROM tableB
etc. etc]

then create your append query which looks at the query that you have just
built...

However you need to be careful that this does not omit any items that may be
seen as Duplicates by the database engine - or you may need to use [UNION
ALL] as opposed to [UNION]

HTH's
Rob

 

[Rob Wills]

.....though re-reading your current SQL statement - I think you need to put a
little more thought into how to build your report, as Union queries should
all be using the same fields.....

e.g.
[SELECT FirstName, SecondName, Age
FROM tableA

UNION SELECT FirstName,SecondName,Age
FROM tableb]

The SQL you listed below looks much more like a text writing process,
especially seeing as most of the fields in the first 4 queries are values not
fields from a table