Union Query

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?

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" wrote in message
...
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?

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

"Jeff Boyce" wrote:

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" wrote in message
...
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?

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

LG wrote:
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?

--
Message posted via http://www.accessmonster.com

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?

"KenSheridan via AccessMonster.com" wrote:

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

LG wrote:
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?

--
Message posted via http://www.accessmonster.com

You've raised two issues he

1. That of restricting each corrector's form to the relevant 'industry type'
when the data is combined in a single table, which is easily done by basing
the form on a query which restricts the results to the 'industry type' in
question. This can be done by a parameter in the query, e.g.

WHERE IndustryType = [Enter industry type:]

but would be by done by opening the form from a dialogue for with a combo or
list box of industry types which the bound form's query references as the
parameter, e.g.

WHERE IndustryType = Forms!frmIndustryDialog!cboIndustry

Your tables would appear to represent some sort of transactions, with each
table being restricted to those for a particular 'industry type'. So if
within each 'industry type' there are many different companies (or whatever)
of each 'industry type' then not only should these tables be combined into
one there should also be a combined table of companies which includes an
IndustryType column. Your combined Transactions (or whatever) table would
include a CompanyID column as a foreign key which references the primary key
of the Companies table. The Transactions table would not have an
IndustryType column (that would introduce redundancy) but the query on which
you corrector's form is based would join the Transactions and Companies table
on the CompanyID columns so that the query could be restricted on the
InsustryType column from Companies.

However, the process of normalization doesn't stop there as you also need a
table with one row per industry type which your combined Companies table can
then be related to on the IndustryType column, enforcing referential
integrity and cascade updates. This protects the integrity of the data, but
also provides a source for the combo box on the dialogue form, whose
RowSource can then be;

SELECT IndustryType
FRO IndustryTypes
ORDER BY IndustryType;

2. If when you say "some fields in the tables are slightly different" you
mean one table might represent an entity type which has different attributes
to the entity types then what you have is a Type/Sub-types scenario. The
Type represents all industry types, so is represented by your combined table.
The columns of this table would represent the attributes which all 'industry
types' share in common, including your platform and date attributes by the
look of things. The Sub-types would be represented by a set of tables, each
with columns which represent the differing attributes of each individual
'industry type' which it does not share with all others. These sub-type
tables would have a primary key which is also a foreign key referencing the
primary key of the (super) IndustryTypes table, i.e. the relationship is one-
to-one.

So putting all of the above together the model would look like this:

Transactions---Companies---IndustryTypes---MultipleIndustrySubTypeTables

You'll see from this that by the process of 'decomposition' into a number of
related tables has eliminated any redundancies, as each 'fact' is now stored
in one place only, and is stored as a value at a column position in a row in
a table. It’s a fundamental principle of the database relational model that
data is stored in that way and no other way, known as the 'information
principle'. Its how the integrity of data is protected. The entity types
which I've assumed as examples probably don't tally completely with your real
world entity types, but the principles will be the same.

Ken Sheridan
Stafford, England

LG wrote:
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
[quoted text clipped - 35 lines]
for a date(s) so they get the information of how many entries per platform
for a date or date range?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

It sounds like you've made some error in entering the SQL statement; a query
of the type whose SQL I posted should work. It helps when you get this sort
of error if you copy the miscreant SQL statement into your post.

As regards combining the tables, the answer is broadly speaking 'Yes', but
see my other reply for a more detailed analysis.

Ken Sheridan
Stafford, England

LG wrote:
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 :
[quoted text clipped - 52 lines]
for a date(s) so they get the information of how many entries per platform
for a date or date range?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

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

LG wrote:
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?