Union Query

[LG]

I currently have the following union query set up:
SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_COMMERCIAL]

UNION SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_PHARMACIES]

UNION SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_CHRYSLER]

UNION SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_GE];
What I would like is it to total the number of records in each platform by
date.
Also, how would i get it so when the user clicks a button they are prompted
for a date(s) so they get the information of how many entries per platform
for a date or date range?

 

[Jeff Boyce]

Based on your query, you appear to have one table per company (and per
'type' - commercial..., and per industry - pharmacies). This design is not
well-normalized. It looks rather like a spreadsheet (one sheet per ...).

So what, you ask? So Access is a relational database and 'expects'
well-normalized data. Both you and Access will have to work overtime to
overcome the problems you'll have with trying to feed it 'sheet data.

Yes, you can use a UNION query. Yes, you can use parameters (look in Access
HELP for "parameter query").

(but you'll be saving yourself a lot of work later if you work on
normalizing your data first...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LG]

I understand that it would be easier to put all into table the problem lies
is there are many users in it entering data into each table daily. There is
also a team of people to do corrections therefore easier for each form to be
set up to a seperate table for correction purposes. Some fields in the
tables are slightly different but are identified as a batch id that a
processor uses. Do you have any suggestions where if I combine them how
would I make a form for corrections where they are not looking through too
many records. Also, annually there will be over million entries with all the
tables.
Any suggestions

Based on your query, you appear to have one table per company (and per
'type' - commercial..., and per industry - pharmacies). This design is not
well-normalized. It looks rather like a spreadsheet (one sheet per ...).

So what, you ask? So Access is a relational database and 'expects'
well-normalized data. Both you and Access will have to work overtime to
overcome the problems you'll have with trying to feed it 'sheet data.

Yes, you can use a UNION query. Yes, you can use parameters (look in Access
HELP for "parameter query").

(but you'll be saving yourself a lot of work later if you work on
normalizing your data first...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I currently have the following union query set up:
SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_COMMERCIAL]

UNION SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_PHARMACIES]

UNION SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_CHRYSLER]

UNION SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_GE];
What I would like is it to total the number of records in each platform by
date.
Also, how would i get it so when the user clicks a button they are
prompted
for a date(s) so they get the information of how many entries per platform
for a date or date range?

 

[LG]

The messge I get is invalid SQL statment;expected 'DELETE', 'INSERT',
'PROCEDURE', 'SE:ECT', or 'UPDATE'.
Would you also suggest putting these tables together and matching up all
fields?

To count all within a date range for which the user is prompted, returning
all rows if the parameters values are not entered :

PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT [date], [platform], COUNT(*) AS Total
FROM
(SELECT [date], [platform]
FROM [tbl_commercial]
UNION ALL
SELECT [date], [platform]
FROM [tbl_pharmacies]
UNION ALL
SELECT [date], [platform]
FROM [tbl_chrysler])
WHERE ([date] >= [Enter start date:]
OR [Enter start date:] IS NULL)
AND ([date] < DATEADD("d",1",[Enter end date:])
OR [Enter end date:] IS NULL)
GROUP BY [date], [platform];

Note that the references to the column names in the outer query must be to
the names of the columns in the first part of the UNION ALL operations in the
subquery. In this case all three use the same column names of course.

Also note that the parenthesising of the OR operations in the WHERE clause is
crucial to force them to evaluate independently of the AND operations.

You can of course reverse the group order to [platform],[date] if you prefer.

NB: Date is a bad choice for a column name as it’s the name of a built in
function. Wrapping it in square brackets as above should avoid any
misinterpretation of it by Access, but its best to use a more specifically
descriptive term when naming columns, e.g. TransactionDate.

Ken Sheridan
Stafford, England

I currently have the following union query set up:
SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_COMMERCIAL]

UNION SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_PHARMACIES]

UNION SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_CHRYSLER]

UNION SELECT [BATCH_ID], [DATE], [PLATFORM]
FROM [TBL_GE];
What I would like is it to total the number of records in each platform by
date.
Also, how would i get it so when the user clicks a button they are prompted
for a date(s) so they get the information of how many entries per platform
for a date or date range?

 

[John Spencer MVP]

You can use the saved UNION query as if it were a table.

SELECT Platform, [Date], Count(Batch_ID) as RecordCount
FROM qUnion
GROUP BY Platform, [Date]

If you wish to specify a date range then

Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Platform, [Date], Count(Batch_ID) as RecordCount
FROM qUnion
WHERE [Date] Between [Enter Start Date] and [Enter End Date]
GROUP BY Platform, [Date]

If you only know how to use the query design tool (query grid)
-- Open a new query
-- Add the union query to the new query (instead of a table)
-- add fields Platform, Date, and Batch_id to the fields to display
-- Select View: Totals from the menu
-- Change GROUP BY to COUNT under Batch_ID

To add the where
-- Add Date a second time to the field list
-- Change GROUP BY to WHERE under this additional field
-- Type the following into the criteria
Between [Enter Start Date] and [Enter End Date]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County