Re: Multiple joins to query null values

Tech-Archive recommends: Speed Up your PC by fixing your registry

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.

--
Tom


"Wayne Morgan" wrote:

> 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.
>
> --
> Wayne Morgan
> MS Access MVP
>
>
> "T. Utley" <TUtley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:75BEF0F0-8712-4572-B92E-3286A9186309@xxxxxxxxxxxxxxxx
> > 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?
> > --
> > Tom
> >
> >
> > "Wayne Morgan" wrote:
> >
> >> 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.
> >>
> >>
> >> --
> >> Wayne Morgan
> >> MS Access MVP
> >>
> >>
> >> "T. Utley" <TUtley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:65A60D11-4CAD-4E44-842A-A8137AEBF427@xxxxxxxxxxxxxxxx
> >> > 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 data*** to include all the cemeteries without
> >> > duplicates (from qryCemeteries) and a subdata*** (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.
> >> > --
> >> > Tom
> >>
> >>
> >>
>
>
>
.

Quantcast