Multiple joins to query null values

[Guest]

Tried this question first in the Query discussion group, didn't receive a
response, and since I'm a newby perhaps this is the more appropriate forum
for my problem.

I currently have two queries which both return the desired values; however,
I’m unable to successfully join the two to get the desired results. In my
normalized database’s relationships, there are no defined joins between the
two queries’ underlying tables. Let me digress:

qryCemeteries is constructed solely from tblCemeteries and includes the
fields: CemeteryID (autonumbered, key); CityID (populated from tblCities),
and CountyID (populated from tblCounties) and each field is populated on
every record. There are also numerous other fields in both the tbl and qry
that deal with limited cemetery-specific info including a website hyperlink
field.

qryActiveServices similarly contain three fields: CemeteryID; CityID; and
CountyID—all populated from tblSales, which I assume (and in fairness I
should point out that assumptions are something I routinely screw up) need to
join to the fields of the same name in qryCemeteries (the data types match).
However, in each qryActiveServices record only one ID field will have a value
or none at all (null values then populating all three). Remaining query
fields include website hyperlinks, graphic file hyperlinks, contact info, etc
(all from tblContacts). Some cemeteries will have no related records in
qryActiveServices. If all three qryActiveServices ID record fields are null,
but a qryActiveServices record exists then I’ll unhide the report control for
the field: WebSite from tblCemetery.

What’s needed is a datasheet to include all the cemeteries without
duplicates (from qryCemeteries) and a subdatasheet (from qryActiveServices)
with website and graphic file hyperlink addresses for a specific related
cemetery, a cemetery located in a related city, or a cemetery located within
a related county via the joined ID fields in the two queries. I’ve tried
relating the queries with inner, outer, and 1-to-1 joins without any success,
and it is obvious my limited skills are entirely too woeful to figure this
out. Can someone take pity on me and point me in the right direction
concerning correct joins and anything that will help me build a solution to
this complex problem? Thanks.

 

[Wayne Morgan]

Right off-hand, do you need all three ID fields in each table? The
Cemeteries table should have unique ID for each Cemetery. This table would
also have which City and County that cemetery is in. If you then have the
CemeteryID in the other tables, you would be able to retrieve the City and
County IDs when you need them from the Cemeteries table, the City and County
IDs don't need to be in the subsequent tables.

To do this, take tblCemetaries and tblActiveServices and place them in the
same query. Link the two tables on the CemeteryID field. Also add tblCity
and tblCounty. Link each of these tables to tblCemetaries on their
associated ID fields. Select the desired fields.

Depending on how much of this data you needed for any particular display,
you would choose only the tables needed for that display and create a query
using those tables, linking them appropriately.

 

[Guest]

Thanks for respondingWayne. Unfortunately, I tried your solution without
success, the error reference being either "ambiguous joins" or a dataset with
field headings but no data (I tried every variation of joins without
success...).

To answer your question: yes, it's necessary to have all three ID fields in
each table. In tblCemeteries the ID fields are strictly address related.
However, in tblActiveServices the ID fields identify unique sales to an
actual Cemetery (in tblCemeteries), or to a non-cemetery customer for
service related to a single cemetery included in tblCemeteries, OR to for
services covering the same geographic area (city or county) that many
Cemeteries in tblCemeteries reside in. Hence, my goal is a dataset that
includes a complete list of cemeteries, but with customer web addresses (and
other pertinent info) that is either related to either the cemetery itself,
or the city or the county in which the cemetery resides. Therefore, in
tblActive services, only 1 of the ID fields (Cemetery, City or County) can be
non-null, Or all three will be Null.

Suggestions?

 

[Wayne Morgan]

It sounds as if you need a field to describe who the services went to. You
will then still have only the ID field of the Cemetery and could retrieve
the other two IDs as needed by linking to the Cemetery table. You would use
the new field to determine which of the other data you need to retrieve.

I don't know if this example will help, but hopefully it will give you an
idea to help set up your tables. This is unrelated to the suggestion above,
but appears to be something you're going to be getting into. To set up a
many-to-many relation ship you actually need 3 tables. The two "outside"
tables will tell about the data and the "middle" table is the linking table
that creates multiple records linking the data. In the following, it is
possible for each cemetery to have more than one type of service and for
each type of service to be available at more than one cemetery.

Example:
tblCemeteries
CemeteryID
CemetaryName

tblActiveServices
ActiveServicesID
TypeOfService

tblCemeteryActiveServices
CemteryID
ActiveServicesID

tblCemeteryActiveServices would be linked to tblCemeteries on the CemeteryID
field and to tblActiveServices on the ActiveServicesID field. tblCemeteries
and tblActiveServices would not be linked to each other.

 

[Guest]

Thanks again Wayne. I appreciate your example which may, in fact, relate to
what I already have, but which I didn't go into in the interests of not
making the assistance request more complex than need be.

I have tblSales, tblCustomers, tblCustomerCategories, tblProducts,
tblContacts, tblContactFunction, and tblBillingPeriod in addtion to the
aformentioned tblCemeteries, tblCounties, and tblCities (and their respective
ID fields) we've been discussing. I'm sure you can accurately surmise the
essential details included in each table by its title. qryActiveServices
successfully creates a dataset of appropriate info for my purposes from the
various database tables (excepting tblCemeteries which is the only unrelated
table in the database). As I've said, I have what I believe to be relateable
fields between qryCemeteries (exactly the same as tblCemeteries) and
qryActive Services which I think in principle corresponds to your example,
but I just can't get a conceptual handle on what I'm missing here to get the
desired result.

Here are my fields in qryActiveServices (which works great):

tblSales!SaleExpires
tblCustomerCategories!CustomerType
tblSales!CemeteryID
tblSales!CityID
tblSales!CountyID
tblCustomers!HoursofOperation
tblCustomers!WebPageAddress (hyperlink)
tblCustomers!AdGraphic (hyperlink)

Here are my qryCemeteries fields (which also works great):

tblCemeteries!CemeteryID
tblCemeteries!CemeteryName
tblCemeteries!CityID
tblCemeteries!CountyID

Can you figure out what I'm missing? Sorry I'm so dense Wayne, and thanks
for enduring my ignorance.

 

[Wayne Morgan]

Can you figure out what I'm missing? Sorry I'm so dense Wayne, and thanks

for enduring my ignorance.

Not at all, doing this with just text messages instead of sitting down
side-by-side is sometimes very difficult.

qryActiveServices doesn't list the linking fields. Will you post the SQL
view of this query?

 

[Guest]

Here you go:

SELECT tblSales.SaleExpires, tblCustomerCategories.CategoryName,
tblSales.CemeteryID, tblSales.CityID, tblSales.CountyID, tblCustomers.Hours,
tblCustomers.CustomerWebPage, tblCustomers.CustomerAdGraphic
FROM (tblCustomerCategories INNER JOIN tblCustomers ON
tblCustomerCategories.CategoryID = tblCustomers.CategoryID) INNER JOIN
tblSales ON tblCustomers.CustomerID = tblSales.CustomerID
WHERE (((tblSales.SaleExpires)>Date()))
ORDER BY tblCustomerCategories.CategoryName;

 

[Wayne Morgan]

Ok, the more I look at this, the more I come up with the same thing.

A cemetery is in a certain location. Therefore the city and county for that
cemetery ought to be fixed. This ought to be a one-to-may relationship, for
the most part. I could see a few exceptions where the cemetery may be
located on a boundary line, in which case, this could be a many-to-many
relationship.

You are having a problem designating who you sold to. You want to determine
if it is a Cemetery, City, or County. This shouldn't be done by filling in 3
different fields. Instead, you should have one list of customers, whether
they be any of the 3 above, with a single unique customer ID of each
customer. You would then add a field to the customer table that tells you
what type of customer (cemetery, city, or county) that customer is.

If, in making your sale, 2 or more of these customers is paying a portion of
the same bill, then the sales would need to be tracked as a many-to-many
relationship also with a field that gives the breakdown of the payments.

 

[Guest]

Thanks for your help Wayne. I'll redo my design to incorporate your
suggestions.