Why do we need union queries? (1 Viewer)

[mkaeser]

Hello all,

As mentioned before, I am going through a database designed by someone else and trying to salvage what I can. This database has about 10 union queries. I have never used them as I was under the impression they are quite slow, and I never needed to compare data from two queries. So my question is, what is the benefit of using union queries. Simply googling this gave me no answer, just instruction on how to do it! Thank you!

 

[pbaldy]

They are a tool that you may or may not need. Your question therefore is "why do I need a screwdriver?", to which the answer would be "when you need to turn a screw". Joking aside, union queries are often used to work around an un-normalized design. If a designer made separate tables for each month's data, they might end up with a union query when they needed the year's data for a report.

They can be used in a normalized db though. I've got a db open where I need to combine data from a driver activity table with data from a table where they get special credits. I want to combine credits from normal activity with the special credits for a report, so I use a union query.

 

[spikepl]

The answer on today's date: to disable the possibility for using the query grid editor and to confuse the bejesus out of all not in the know

 

[pbaldy]

The answer on today's date: to disable the possibility for using the query grid editor and to confuse the bejesus out of all not in the know

LOL! You need to know the secret handshake.

 

[Galaxiom]

The main use (or perhaps abuse) I have for union queries is in complex report RecordSource queries to stop Access trashing my carefully indented subquery formatting when the query is closed.

The last line of my query is something like this:

UNION ALL SELECT Null, Null, Null FROM Dummy

Put in as many Nulls as required to match the fields in the real part of the query. Dummy is just an empty table. The union has no effect on the data returned by the query and Access leaves the SQL alone.

 

[mkaeser]

hmmm ok then, guess I need to figure out if I need a screwdriver or a sledgehammer

So please help me understand the following sql:

SELECT [Address] & ", " & [City] & ", " & [State] & " " & [Zip] AS FullAddress, PCID, PMID, ServiceType, DateCompleted, pInvoice, nInvoice, ConvInvoice AS dInvoice, "Project" AS Type

FROM Customers INNER JOIN Projects ON Customers.CustomerID = Projects.CustomerID

WHERE (((Projects.PCID) Like IIf(IsNull([TempVars]![tmpPCID]),"*",[TempVars]![tmpPCID])) AND ((Projects.PMID) Like IIf(IsNull([TempVars]![tmpPMID]),"*",[TempVars]![tmpPMID])) AND ((Projects.DateCompleted) Between [TempVars]![tmpstartdate] And [TempVars]![tmpenddate]))

UNION SELECT [Address] & ", " & [City] & ", " & [State] & " " & [Zip] AS FullAddress, PCID, PMID, ServiceType, DateCompleted, pInvoice, nInvoice, ConvInvoice AS dInvoice, "ChangeOrder" AS Type

FROM Customers INNER JOIN ChangeOrders ON Customers.CustomerID = ChangeOrders.CustomerID

WHERE (((ChangeOrders.PCID) Like IIf(IsNull([TempVars]![tmpPCID]),"*",[TempVars]![tmpPCID])) AND ((ChangeOrders.PMID) Like IIf(IsNull([TempVars]![tmpPMID]),"*",[TempVars]![tmpPMID])) AND ((ChangeOrders.DateCompleted) Between [TempVars]![tmpstartdate] And [TempVars]![tmpenddate]));

First select query - table Customers inner join on table Projects, results are records showing the selected fields for the given date range and matching to the current variables for PCID and PMID (if assigned)

Second select query - table Customers inner join on table ChangeOrders, results are records showing the selected fields for the given date range and matching to the current variables for PCID and PMID (if assigned)

SOOO this would be used to show records that are in tables Customers, Projects, and ChangeOrders that have the same CustomerID, and have a completion date that falls with the date range being searched correct? So I would think, since this union query is titled JobsCompleted, this is used to run reports showing completed jobs. I don't see why you couldn't just use a selection query to select records that only have a completion date, unless it has to do with the PCID and PMID variables? Or am I just totally wrong?

 

[pbaldy]

I won't pretend to understand your data well enough to offer an intelligent opinion (which some would argue I'm not qualified to give anyway). My educated guess is that your query is doing what I first mentioned, drawing data together from un-normalized tables (Projects and ChangeOrders). If they contain similar information, there's a good chance they should be a single table with a field to distinguish them.

 

[Galaxiom]

I was under the impression they are quite slow,

No slower than the queries they are composed of if you use UNION ALL.

UNION is slow because it compares all the results and eliminates duplicate records.

 

[mkaeser]

Hey now, this is not MY data -. Looking at the two tables, there are a LOT of the same fields that are shared:
Tbl Projects
42 Fields

Tbl ChangeOrders
39 Fields

Matching fields
I stopped counting around 30
Good......ness.....

Well then, guess I know what Ill be doing, thanks for the help everyone!

 

[pbaldy]

Good luck!

 

[Galaxiom]

Hey now, this is not MY data -. Looking at the two tables, there are a LOT of the same fields that are shared:
Tbl Projects
42 Fields

I would also bet with so many fields that many should be implemented as multiple records in the same field in a related table.

 

[mkaeser]

I would also bet with so many fields that many should be implemented as multiple records in the same field in a related table.

This is like another language to me, but I am intrigued

Can you elaborate? A lot of the fields are for accounting purposes, so you have estimate date, estimate number, estimate price, invoice date, yada yada yada

 

[Galaxiom]

A lot of the fields are for accounting purposes, so you have estimate date, estimate number, estimate price, invoice date, yada yada yada

Without seeing your actual data it is difficult to make a detailed comment.

All the data about an invoice or estimate would normally be held in a separate table. Any reference from another table would be exclusively by the Primary Key of the Invoice table.

There is little difference between an invoice and an estimate. They are often held in the same table with a field to indicate if the record is an estimate, quote or invoice.

This allows invoices and estimates to be shown with the same query and form.

 

[mkaeser]

I believe the reason it is structure this way it to provide the ability to search a project by the invoice number. But even then, it wouldn't take much work to do that with the PK. Heck, I could probably make the PK the invoice number as those shouldn't be repeating. The plan is to integrate this with quickbooks, so it would make more sense to me to have an Invoice Table instead of cramming everything into the Projects table.

 

[Galaxiom]

it would make more sense to me to have an Invoice Table instead of cramming everything into the Projects table.

Yes, especially if you want to be able to have multiple invoices for a staged project.

 

[mkaeser]

So, I would think the Invoice records would need a customerID associated with them, UNLESS, like you mentioned before, there is a way to have the customer table and a field called "Invoices", which would show the PK for invoice 1,2 & 3 associated with that customer, and that field would retain those invoiceIDs as independent numbers (as opposed to the multiple values appearing as a string).

https://support.office.com/en-ca/ar...d-fields-7c2fd644-3771-48e4-b6dc-6de9bebbec31

Meh?

 

[mkaeser]

Well, the more I look up multivalue fields, the more I don't like em. Seemed like such a great idea....
Anywho, I guess this leaves me with these two options:
Creating a field in tblInvoices with a CustomerID

OR

Creating a 3rd table with a many to many relationship

Unless there is another option I am not seeing? Thanks!

 

[jdraw]

Heck, I could probably make the PK the invoice number as those shouldn't be repeating.

Absolutely correct! A PK is a unique identifier -- so no repeating.

 

[Galaxiom]

Well, the more I look up multivalue fields, the more I don't like em. Seemed like such a great idea....

They are not suited to this task. They are meant for a relatively small number of choices among options available to many records.

Moreover they are only in Access so subsequently upsizing to a database server backend requires a complete rework.

Anywho, I guess this leaves me with these two options:
Creating a field in tblInvoices with a CustomerID

Yes. Each invoice has precisely one customer it belongs too.

OR
Creating a 3rd table with a many to many relationship

This would not normally be necessary since the one invoice is not issued to multiple customers.

 

[mkaeser]

Ok well it would be best to design this with the possibility of moving to an sql server so I will stay away from the multivalue field and just stick to assigning a customerID to each invoice. Thank you all for the great advice, this forum is a lifesaver!