If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append Union Queries
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 |
#2
|
|||
|
|||
Append Union Queries
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 "Steph_canoe" wrote: 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 |
#3
|
|||
|
|||
Append Union Queries
.....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 "Steph_canoe" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|