Query results from two sources of data

[piovesan]

Hi all;

I have a situation where I have a table that is linked to two sources for location information.

The first table is called APBLD and it has a one to many relationship with an address table called ADDR2 through the field ADDRKEY.

The other relationship is also a one to many between APBLD and a table called PARCEL through the field PARCLKEY

Basically, the APBLD table is a table of building permits issued, and they are either coded to an address (ADDR2) or to a proerty identifier (PARCEL) even though both may exist.

To make things a bit more complicated, the ADDR2 table and the PARCEL table have a one to many relationship through another table called ADDRPRCL which has both the ADDRKEY and the PARCLKEY.

So, what I want to do, is create a query that lists all permits and if the link with the APBLD table is through the ADDRKEY, I want to see the following fields from the ADDR2 table (STNO, STNAME) but if the link was through the PARCLKEY, I want to see the following field from the PARCEl table (PID).

It sounds kind of simple when I write it down but I can't seem to get it to run properly without getting an error because of my joins.... can any one help???

Thanks!

 

[PHV]

Why not using an Union query, the 1st with the ADDR2 join and the 2nd with the PARCEL join ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

(1) Is this true?
APBLD can be described as "permits"
ADDR2 can be described as "addresses"
PARCEL can be described as "parcels"
These names will may make it easier to discuss the problem.

(2) Is this true ?
One permit can have many addresses, or one permit can have many parcels. A permit will not have both addresses and parcels...its an either/or situation

(3) We want to pick up the record in permit, and the child records in either addresses or parcels.

If 1-3 are true, then I think that you should consider this solution:
A union query could join parcels and addresses. Then, a select query could display permits and its children in the union query.

 

[piovesan]

This might sound like a stupid question, but what is a Union query?? In the drop down menu, I see Select query, Make table, Update, Crosstab, Append and Delete Queries....am I missing something??

Thanks!

 

[PHV]

You may consider doing some research on your own, don't you ?

 

[OhioSteve]

That is not a dumb question. Years ago I read about union queries and I had the same question. For some unknown reason, MS did not include the verb "union" in its graphic interface.

I can give you an overview of this concept. A union query grabs a hunk of records from one table; then it grabs a hunk of records for another table; finally it smooshes the two globs of records together. I am choosing my language deliberately, because it is a very crude tool. You must tell the machine how the columns will synchronize (for example, you must include logic like "orderid from table1 smooshes together with orderName with from table2"). You must also specify whether you need all of the records or just the distinct records.

I don't have time to provide the syntax now. However, I'm sure that google, the access help system, or a textbook could get you started. I would suggest you make a couple of fake tables and experiment with them. Post again once you have done these experiments.

 

[lespaul]

SELECT FIELD1, FIELD2 FROM TableName Where Field1 = "Something"
UNION
SELECT FIELD3, FIELD4 From OtherTable WHERE Field4 = "Something Else"

you have to have the same number of fields in each select statement

hth



Leslie

 

[PHV]

In the Jet SQL help file take a look at UNION in the reserved words section.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

I am working from memory, so my recollection may not be right. However, I think that in les paul's example, field one would synchronize with field three. Also, his query would not include duplicate records. If you want ALL of the records, you write "...union all..."

So now you have a syntax example!

 

[piovesan]

Thanks everyone. I will try this ouit and respond with results.

Thanks!