Union Query Help

[sueviolet]

I would like to create a Union query


My table relationship:



tbl_FW_Main

PubID (Primary KEY - unique code - text)
Title (text)
Department (text)
Date_Published (text)
Publisher (text)
Report_Location (text)
Authors (text)



Tbl_Waterbody

PubID (foreign key - text)
WaterbodyName (text)
Watershedcode (text)
AliasName (text)
LakeSeq (text)
WsCode_LakeSeq (text)



Tbl_Species


PUBID (foreign key - text)
Speciescode (text)



tbl_FW_Main - this table is the 'one' side of relationship. This table is linked to the other tables by "PUBID". There are no duplicate pubid in this table. For example, one pubID will link to many records in either tbl_species or tbl_waterbody

tbl_waterbody- this table is on the "many" side of the relationship
tbl_species - this table is on the "many" side of the relationship



I need to merge 2 queries into 1 because tbl_Waterbody and tbl_Species each have a relationship with tbl_FW_Main, they do not have a relationship with each other.


UNION queries are new to me, and am confused as how to create one. I need all fields from all three tables to be in the query output and I don't want duplicating records in the output as well.


I have read through example on UNION queries, but and still fairly confused.

 

[dcx693]

Are you sure you need a union query? If you can show the results of all three tables in query results, then unless you need to create a new table with those results, you don't need a union query.

Since tbl_Waterbody and tbl_Species each have a relationship with tbl_FW_Main you have a many-to-many relationship. They do not have a relationship with each other since they are both related to tbl_FW_Main.

Just add all three tables to the query design view. If you've defined the default relationships between the tables (using Tools, Relationships) Access will put in the join lines for you. If not, do it yourself. Then choose whatever fields you want and run it.

 

[sueviolet]

Thanks for your post

I have been trying to fix my problem for so long, everybody tells me something different!


If I create a query with all three tables - the output has repeating values.



For example:


PubID "LM506.3" in tbl_FW_Main should link to 2 waterbodies (GOLD CREEK, ALOUETTE LAKE) in tbl_waterbody and 5 species types in tbl_species.


Access query result for "LM506.3":

PubID WaterBodyName SpeciesCode Authors
LM506.3 ALOUETTE LAKE RB ROB KNIGHT
LM506.3 ALOUETTE LAKE "DV" ROB KNIGHT
LM506.3 ALOUETTE LAKE "KO" ROB KNIGHT
LM506.3 ALOUETTE LAKE "NSC" ROB KNIGHT
LM506.3 ALOUETTE LAKE "CSU" ROB KNIGHT
LM506.3 ALOUETTE LAKE "STICKLEBACK AND SCULPINS" ROB KNIGHT
LM506.3 ALOUETTE LAKE "RSC" ROB KNIGHT
LM506.3 ALOUETTE LAKE "PCC" ROB KNIGHT
LM506.3 ALOUETTE LAKE "LT" ROB KNIGHT
LM506.3 ALOUETTE LAKE Cutthroat ROB KNIGHT
LM506.3 GOLD CREEK RB ROB KNIGHT
LM506.3 GOLD CREEK "DV" ROB KNIGHT
LM506.3 GOLD CREEK "KO" ROB KNIGHT
LM506.3 GOLD CREEK "NSC" ROB KNIGHT
LM506.3 GOLD CREEK "CSU" ROB KNIGHT
LM506.3 GOLD CREEK "STICKLEBACK AND SCULPINS" ROB KNIGHT
LM506.3 GOLD CREEK "RSC" ROB KNIGHT
LM506.3 GOLD CREEK "PCC" ROB KNIGHT
LM506.3 GOLD CREEK "LT" ROB KNIGHT
LM506.3 GOLD CREEK Cutthroat ROB KNIGHT

As you can see there are 2 of each species type. (not good)

I would like not to have duplicate values in the query output - I don't want users to view repeating data.


At the moment I am outputting the result of 2 separate queries in a html table.


Query 1:

SELECT *

FROM tbl_FW_Main, tbl_Waterbody, tbl_Species
WHERE tbl_FW_Main.PubID=tbl_Waterbody.PubID
AND tbl_FW_Main.PubID=tbl_Species.PubID

and authors = "rob knight"
order by tbl_FW_Main.PubID, speciescode

Query 2:

SELECT *

FROM tbl_FW_Main, tbl_Waterbody, tbl_Species
WHERE tbl_FW_Main.PubID=tbl_Waterbody.PubID
AND tbl_FW_Main.PubID=tbl_Species.PubID

and authors = "rob knight"
order by tbl_FW_Main.PubID, speciescode

The first row of the first query does not correspond with the first row of the second query ) and I am hoping that outputting one query (instead of 2) will help solve this problem.

 

[jeremie_ingram]

The EASIEST way to create a union query is in the query builder. Select 1 of the tables, and add all of the fields you need. Switch to SQL view, highlight and copy the statement. Close the query and dont save it. Create a new query with the second table and add the fields. Now switch to SQL view and type UNION ALL then paste the original SQL statement in. This helps to aviod spelling mistakes AND saves time.

 

[dcx693]

jeremie_ingram's suggestion is correct as far as creating Union queries goes. I would just omit the "ALL" part of the union statement since that would instruct Access to include any duplicates it finds. Union queries, without the "ALL", filter out duplicates.

I didn't quite understand your post where you said

As you can see there are 2 of each species type.

Can you point to what data is repeated? Each record in the output display looks distinct. Can you post an example of how the data should look?

 

[sueviolet]

Thanks guys


How I would like data output to look


PUB ID

LM506.3



Species

RB
DV
KO
NSC
CSU
STICKLEBACK AND SCULPINS
RSC
PCC
LT
Cutthroat

(*the same species are repeated twice in the current output, once for each of the waterbodies)




Waterbody

ALOUETTE LAKE
GOLD CREEK



The pubid LM506.3 refers to a specfic report. This report contains information about the above 10 species and 2 waterbodies.

The way the output looks now, is look like that each waterbody is associated with the species types.


For example:


ALOUETTE LAKE RB
ALOUETTE LAKE "DV"
ALOUETTE LAKE "KO"
ALOUETTE LAKE "NSC"
ALOUETTE LAKE "CSU"
ALOUETTE LAKE "STICKLEBACK AND SCULPINS"
ALOUETTE LAKE "RSC"
ALOUETTE LAKE "PCC"
ALOUETTE LAKE "LT"
ALOUETTE LAKE Cutthroat
GOLD CREEK RB
GOLD CREEK "DV"
GOLD CREEK "KO"
GOLD CREEK "NSC"
GOLD CREEK "CSU"
GOLD CREEK "STICKLEBACK AND SCULPINS"
GOLD CREEK "RSC"
GOLD CREEK "PCC"
GOLD CREEK "LT"
GOLD CREEK Cutthroat

But they are not really. The species table relates to the Report table (ie: by PUB iD) and the waterbody table relates to the report table. (ie: by PUB iD). The species and waterbodies don't really relate to one another, except that they are both refered to in the report.


I think I should create 2 separate queries instead. But I need the first row of the first query to correspond with the first row of the second query, both queries being based on the same criteria. I don't know how to do this.

The records are display in a html table - so as you can imagine, repeating values are not very pleasing to the eye.

I really appreciate you taking the time to help me on this.

Thanks

 

[dcx693]

OK, I think I might be catching on now (takes me a while). Basically, the water bodies have no relationship at all to the species. Given that, it does sound like two queries is the way to go.

Here's the part I'm still confused about. You want the results on the same query, but that's usually not a good idea with data that isn't related somehow. I see how they're both related to a 3rd table.

True, a union query "mashes" together data and doesn't care about whether the results are related or not. But the combined union query will have a row and column field structure.

Maybe someone's got a better idea, but I'd suggest leaving them as two separate queries and outputting them separately.

 

[Pat Hartman]

I see you've started a new thread on this topic. A union query is the only way to do what you are asking but WHY are you so determined to output unrelated data in the same recordset. It really makes no sense. And because it makes no sense to us, we have all tried to talk you out of it. It's like trying to make a single list that contains all your household inventory plus the people on your Christmas card list. Yes, they both have a relationship with you. But, so what!

If you want to know what species live in what waterbodies, you need another table called a relation table. Someone was trying to help you do that in the other thread.

A union query simply appends one recordset to the end of another. That means that each separate recordset must have the IDENTICAL number of columns with IDENTICAL data types. The column names are irrelevant. Picture ripping out two pages from your telephone book and laying them out one above the other. That is exactly what a union query does plus in the process, it eliminates any duplicate rows unless you use UNION ALL.

 

[sueviolet]

see below

 

[sueviolet]

Thanks Pat,


I haven't been able to create a UNION query that gives me the result I want. That is why I keep asking for help.

Could you help me do this?

 

[sueviolet]

Pat,

In my previous post, you said:


"You simply cannot produce a single query that includes data from unrelated tables if you expect your results to make sense. You need a minimum of two queries."


Are you talking about a UNION query? (I assumed a UNION query to be a single query - something you said not to do)

I took it to be that you were suggesting to create 2 separate queries.

I am trying to follow the advice of this forum, I guess I just don't always understand it.

 

[Pat Hartman]

You can't understand our avice because we can't understand your problem. WHY do you insist on making a single recordset? More than one of us has told you that although you can use a union query to make a single recordset out of multiple recordsets, given your data it just desn't make sense. What are you trying to accomplish?

 

[dcx693]

Sue, is your aim just to be able to output the results on the same page?

You must understand that most of the people who try to help you in this forum have some database design "sense". They know what makes sense in most cases. What you are trying to accomplish may be possible and you have the union query technique to use in order to attempt it. We're just trying to help steer you in the right direction in terms of what makes sense - combining unrelated data typically does not.