Hi all,
I am currently in the design stages of a database for work. I have come
up with a way to get informaion I need using a union query but was wandering
what effects this will have on performance.
To give you an insight,
I am using Access XP
The database will be split into FE/BE. FE on local machine, BE on network
drive
I have a table (iinord) which will contain ASN (a delivery of goods)
information. To log the progress of each ASN, I have included 4 check boxes
which will get ticked once an ASN has reached a certain stage. Managment
have asked that they be able to have a report which will show how many ASN's
are due in on any particular day and how many are at each stage.
I have the following query which works but I have been led to believe that
unions are not too efficient. Could anyone offer an advice?
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], Count(iinord.iinordid)
AS pod, 0 AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.pod)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod,
Count(iinord.iinordid) AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.dtc)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc,
Count(iinord.iinordid) AS conf, 0 AS put, 0 AS tot
FROM iinord
WHERE (((iinord.conf)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc, 0
AS conf, Count(iinord.iinordid) AS put, 0 AS tot
FROM iinord
WHERE (((iinord.put)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS
dtc, 0 AS conf, 0 AS put, Count(iinord.iinordid) AS tot
FROM iinord
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]));
TIA,
Mark
4  1583 
Mark wrote: Hi all,
I am currently in the design stages of a database for work. I have come
up with a way to get informaion I need using a union query but was wandering
what effects this will have on performance.
To give you an insight,
I am using Access XP
The database will be split into FE/BE. FE on local machine, BE on network
drive
I have a table (iinord) which will contain ASN (a delivery of goods)
information. To log the progress of each ASN, I have included 4 check boxes
which will get ticked once an ASN has reached a certain stage. Managment
have asked that they be able to have a report which will show how many ASN's
are due in on any particular day and how many are at each stage.
I have the following query which works but I have been led to believe that
unions are not too efficient. Could anyone offer an advice?
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], Count(iinord.iinordid)
AS pod, 0 AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.pod)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod,
Count(iinord.iinordid) AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.dtc)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc,
Count(iinord.iinordid) AS conf, 0 AS put, 0 AS tot
FROM iinord
WHERE (((iinord.conf)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc, 0
AS conf, Count(iinord.iinordid) AS put, 0 AS tot
FROM iinord
WHERE (((iinord.put)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS
dtc, 0 AS conf, 0 AS put, Count(iinord.iinordid) AS tot
FROM iinord
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]));
TIA,
Mark
I see nothing wrong with union queries. Sometimes it's impossible to
get results unless you use them. The only "problem" I've encountered is
the desire to edit records when Unioned...and you can't. So you present
a single record if editing is required.
"Mark" <ma*********@ntlworld.com> wrote in message
news:jr************@newsfe5-win.ntli.net... Hi all,
I am currently in the design stages of a database for work. I have come
up with a way to get informaion I need using a union query but was
wandering what effects this will have on performance.
To give you an insight,
I am using Access XP
The database will be split into FE/BE. FE on local machine, BE on network
drive
I have a table (iinord) which will contain ASN (a delivery of goods)
information. To log the progress of each ASN, I have included 4 check
boxes which will get ticked once an ASN has reached a certain stage.
Managment have asked that they be able to have a report which will show
how many ASN's are due in on any particular day and how many are at each
stage.
I have the following query which works but I have been led to believe that
unions are not too efficient. Could anyone offer an advice?
SELECT Format([ardat],'dd/mm/yy') AS [Expected date],
Count(iinord.iinordid) AS pod, 0 AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.pod)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod,
Count(iinord.iinordid) AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.dtc)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc,
Count(iinord.iinordid) AS conf, 0 AS put, 0 AS tot
FROM iinord
WHERE (((iinord.conf)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc,
0 AS conf, Count(iinord.iinordid) AS put, 0 AS tot
FROM iinord
WHERE (((iinord.put)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS
dtc, 0 AS conf, 0 AS put, Count(iinord.iinordid) AS tot
FROM iinord
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]));
You may be better off using subqueries to do this. You'll definitaly be
better off using a separate table to hold your individual time slices, (days
in your case), and joining to this table, rather than grouping on a
calculated value. Also you can then move the criterion out of a HAVING
clause to a WHERE clause, which means the restriction will be applied before
the GROUP BY - much more effivient.
"Mark" <ma*********@ntlworld.com> wrote in message news:<jr************@newsfe5-win.ntli.net>... Hi all,
I am currently in the design stages of a database for work. I have come
up with a way to get informaion I need using a union query but was wandering
what effects this will have on performance.
To give you an insight,
I am using Access XP
The database will be split into FE/BE. FE on local machine, BE on network
drive
I have a table (iinord) which will contain ASN (a delivery of goods)
information. To log the progress of each ASN, I have included 4 check boxes
which will get ticked once an ASN has reached a certain stage. Managment
have asked that they be able to have a report which will show how many ASN's
are due in on any particular day and how many are at each stage.
I have the following query which works but I have been led to believe that
unions are not too efficient. Could anyone offer an advice?
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], Count(iinord.iinordid)
AS pod, 0 AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.pod)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod,
Count(iinord.iinordid) AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.dtc)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc,
Count(iinord.iinordid) AS conf, 0 AS put, 0 AS tot
FROM iinord
WHERE (((iinord.conf)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc, 0
AS conf, Count(iinord.iinordid) AS put, 0 AS tot
FROM iinord
WHERE (((iinord.put)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS
dtc, 0 AS conf, 0 AS put, Count(iinord.iinordid) AS tot
FROM iinord
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]));
TIA,
Mark
Whoa. Since all the data is coming from iinord, why not just use OR's
instead of unions?
SELECT...
FROM...
WHERE Condition1
OR Condition2
OR Condition3...
GROUP BY...
HAVING...
Thanks all for your suggestions and comments.
Regards,
Mark
"Mark" <ma*********@ntlworld.com> wrote in message
news:jr************@newsfe5-win.ntli.net... Hi all,
I am currently in the design stages of a database for work. I have come
up with a way to get informaion I need using a union query but was
wandering what effects this will have on performance.
To give you an insight,
I am using Access XP
The database will be split into FE/BE. FE on local machine, BE on network
drive
I have a table (iinord) which will contain ASN (a delivery of goods)
information. To log the progress of each ASN, I have included 4 check
boxes which will get ticked once an ASN has reached a certain stage.
Managment have asked that they be able to have a report which will show
how many ASN's are due in on any particular day and how many are at each
stage.
I have the following query which works but I have been led to believe that
unions are not too efficient. Could anyone offer an advice?
SELECT Format([ardat],'dd/mm/yy') AS [Expected date],
Count(iinord.iinordid) AS pod, 0 AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.pod)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod,
Count(iinord.iinordid) AS dtc, 0 AS conf, 0 AS put, 0 as tot
FROM iinord
WHERE (((iinord.dtc)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0,0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
union
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc,
Count(iinord.iinordid) AS conf, 0 AS put, 0 AS tot
FROM iinord
WHERE (((iinord.conf)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION
SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS dtc,
0 AS conf, Count(iinord.iinordid) AS put, 0 AS tot
FROM iinord
WHERE (((iinord.put)=-1))
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]))
UNION SELECT Format([ardat],'dd/mm/yy') AS [Expected date], 0 AS pod, 0 AS
dtc, 0 AS conf, 0 AS put, Count(iinord.iinordid) AS tot
FROM iinord
GROUP BY Format([ardat],'dd/mm/yy'), 0, 0, 0, 0
HAVING (((Format([ardat],'dd/mm/yy'))=[Enter Date Please]));
TIA,
Mark This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: KENNY L. CHEN |
last post by:
Dear experts,
I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO)
and TEST1 (COL1,COL2,REC_NO).
Both tables are unique-indexed on (COL1,COL2,REC_NO).
I think the following...
|
by: Shaggy Dragon |
last post by:
Hi there, been looking for a solution to this for some time now. I've
a UNION query that produces a table called AllSecurities:
SELECT SecurityNumber, Book AS AllSecurities FROM Trades UNION...
|
by: M Stery |
last post by:
Have two base queries used for mailing labels with
id,name,address,city,state,zip that are combined via a union query.
One base query uses home address; the other base query uses biz
address. I...
|
by: Lyn |
last post by:
This one is difficult to explain, so I will cut it down to the basics.
I have a major table 'tblA' which has an autonum field 'ID-A' as primary key
(of no significance to users). 'tblA' contains...
|
by: marco |
last post by:
Dear List,
as it seems, MS SQL as used in Access does not allow a select INTO
within a UNION query. Also, it seems that a UNION query can not be used
as a subquery.
Maybe my (simplified)...
|
by: BillCo |
last post by:
I've encountered a problem while using ADO to save query objects.
Union queries created normally (via the interface) appear in adox
catelog.procedures rather than catelog.views. This is reasonably...
|
by: wugon.net |
last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad
query performance
Env:
db2 LUW V8 + FP14
Problem :
We have history data from 2005/01/01 ~ 2007/05/xx in single big...
|
by: MLH |
last post by:
How can I turn the following into a make-table query?
SELECT & " " & AS Recipient FROM
tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID
= tblAddnlOwnrs.VehicleJobID WHERE...
|
by: kcdoell |
last post by:
Hello:
I am trying to create a union query but do not have a lot of experience. Basically I have the below tables:
The Tables:
Table Name = tblPrior
CreditRegIDFK; Number; Foreign Key...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
