Re: Do I need an Union Query for this?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Richard (zxfiles_at_NOSPAMyahoo.com)
Date: 03/02/05 

Date: Wed, 2 Mar 2005 13:24:16 -0800

Thanks for the help. I tried that equation and it only seems to touch the
first record for the query and then assign that value to every record in the
list. This is how I modified it:

TheAddressToUse: Nz(DLookup("ContactType", "tblAddresses",
"ContactType='Home'"),Nz(DLookup("ContactType", "tblAddresses",
"ContactType='Business'"),"no address"))

I wanted it to return the ContactType anyway. However, when I ran this I got
back "Home" for all of them, which is not correct. Some only have "Business".
So I decided to edit it. I took out "ContactType" and replaced it with
another frield "AddressID" which I mentioned below. When I do this, it
returns the same number for all rows.

So, I tested it one more time. I put "ContactType" back in the first DLookup
and put "AddressID" in the second DLookup. If it hit with "Home" then it
would return the ContactType, but if it hit with Business, then it would
return AddressID for that item. It just returned "Home" for all of them.

Here is an image of the query relationship if that helps:
http://img148.exs.cx/img148/4724/access11xt.jpg

Am I doing something wrong here? Thanks.

"Ken Snell [MVP]" wrote:

> Shouldn't need a union query. Just use a DLookup function and the Nz
> function to get the desired address info:
>
> TheAddressToUse: Nz(DLookup("AddressInfo", "tblAddresses",
> "AddressType='Home'"),Nz(DLookup("AddressInfo", "tblAddresses",
> "AddressType='Business'"),"no address")
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Richard" <zxfiles@NOSPAMyahoo.com> wrote in message
> news:7532BA19-9639-42ED-9C47-B471394F24F8@microsoft.com...
> >I admit that Access is not my strong suit. I am just now starting to work
> > with it on a regular basis. With that said, I need some help with this one
> > problem.
> >
> > There is a database that contains multiple tables. Each one is linked by a
> > ContactID number. One table has the names and personal information. One
> > table
> > contains the addresses, because one ContactID can have multiple addresses
> > (home and business). One table contains how much they have donated.
> >
> > I have created a query linking these tables together so I can get a list
> > of
> > all the donations made this year and their addresses, so we can form a
> > report
> > later on. Here is the problem. I need to only get one address per
> > ContactID
> > and their home takes priority over their business, if they have both.
> > Currently, the query will list their information twice if they have two
> > AddressID's. Make sense?
> >
> > For example, I have a ContactID number for the Jones family. In our
> > database
> > that ContactID has two separate AddressID's associated with it, because
> > they
> > listed their home and business. In this database, that is designated by
> > ContactType with a text field for "Home" and "Business".
> >
> > When I run the query now, it will list two rows for the Jones -- one for
> > the
> > home and one for the business. In our case this is redundant and not
> > needed,
> > because their donations are the same and are being listed twice. I thought
> > I
> > could fix this with a IIF statement in the query, but no luck. Someone
> > else
> > mentioned using a SQL Union and somehow picking just the first entry for
> > each
> > ContactID. But I don't know how to do that.
> >
> > Here is the logic if you need it:
> >
> > If ContactID.ContactType has both Home and Business
> > Return only Home
> > Else
> > Return Business
> >
> > Any help would be appreciated. Thanks. If you need any more information
> > from
> > me please ask.
>
>
>

Relevant Pages

  • Re: Do I need an Union Query for this?
    ... Shouldn't need a union query. ... Just use a DLookup function and the Nz ... because one ContactID can have multiple addresses ... > and their home takes priority over their business, ...
    (microsoft.public.access.queries)
  • Re: Do I need an Union Query for this?
    ... > Here is an image of the query relationship if that helps: ... >> <MS ACCESS MVP> ... because one ContactID can have multiple ... >>> (home and business). ...
    (microsoft.public.access.queries)
  • Re: Do I need an Union Query for this?
    ... You'll need to do an additional change to your query. ... is coming via the DLookup function instead. ... It said something about ContactID being in multiple tables. ... Normally, they are Home and Business, ...
    (microsoft.public.access.queries)
  • Do I need an Union Query for this?
    ... There is a database that contains multiple tables. ... ContactID number. ... and their home takes priority over their business, ... the query will list their information twice if they have two ...
    (microsoft.public.access.queries)
  • Re: Do I need an Union Query for this?
    ... Don't apologize. ... It said something about ContactID being in multiple tables. ... Normally, they are Home and Business, ... >> first record for the query and then assign that value to every record in ...
    (microsoft.public.access.queries)