Do I need an Union Query for this?

[Guest]

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.

 

[PC Datasheet]

Look up the IIF function in the Help file. You will need to use it in your
query like this:
ContactAddress:IIF(IsNull(HomeAddress],"BusinessAddress","HomeAddress")

If there is no home address, BusinessAddress will be used and if there is a
home address, HomeAddress will be used.

 

[Ken Snell [MVP]]

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")

 

[Guest]

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]]

No, the problem is that I omitted one of the criteria tests in my post. My
apologies.


TheAddressToUse: Nz(DLookup("AddressInfo", "tblAddresses",
"AddressType='Home' And ContactID=" & [ContactID]),Nz(DLookup("AddressInfo",
"tblAddresses",
"AddressType='Business' And ContactID=" & [ContactID]),"no address"))

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

I don't see how to implement this. Maybe I am missing it, because I have been
staring at the code for a while. But if the IsNull expression returns a false
then that means there is no address type there. In that scenario, I don't
want it to return Business.

Maybe this will help. The exact variable name is ContactType. It contains
either a text value of "Home" or "Business". That ContactType variable is
also associated with a AddressID number. Each AddressID number is associated
with a ContactID number. Sometimes, the ContactID number has two different
AddressID's associated with it.

Based on your expression above, I would write:

ContactAddress: IIF(IsNull([ContactType]), "Business", "Home")

IsNull checks the ContactType field and see it's not null, because it has a
"Business" entry. But the expression reads that and spits out "Home". Then if
there is some entry there that doesn't have any address, it will spit out
"Business".

Am I overlooking something?

Thanks for the help.

Look up the IIF function in the Help file. You will need to use it in your
query like this:
ContactAddress:IIF(IsNull(HomeAddress],"BusinessAddress","HomeAddress")

If there is no home address, BusinessAddress will be used and if there is a
home address, HomeAddress will be used.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.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.

 

[Guest]

Don't apologize. Thanks for the help!

I tried this new code, but I got an error at first with ContactID written
like that. It said something about ContactID being in multiple tables. So I
wrote it for tblContacts.ContactID instead. It seems to run now. But it
replaces the "Business" with "Home". Here is screen capture of the example:

http://img20.exs.cx/img20/903/access21yh.jpg

You see two entries for those names. Normally, they are Home and Business,
but after running it with that expression they are both Home now.

Thanks for your help with this.

No, the problem is that I omitted one of the criteria tests in my post. My
apologies.


TheAddressToUse: Nz(DLookup("AddressInfo", "tblAddresses",
"AddressType='Home' And ContactID=" & [ContactID]),Nz(DLookup("AddressInfo",
"tblAddresses",
"AddressType='Business' And ContactID=" & [ContactID]),"no address"))

--

Ken Snell
<MS ACCESS MVP>

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.

 

[Guest]

I forgot to mention, but the expression does replace entries that don't have
an address with "no address" so something is working right. And this is the
way I ended up writing it:

TheAddressToUse: Nz(DLookup("ContactType", "tblAddresses",
"ContactType='Home' And ContactID=" &
[tblContacts.ContactID]),Nz(DLookup("ContactType", "tblAddresses",
"ContactType='Business' And ContactID=" & [tblContacts.ContactID]),"no
address"))

Don't apologize. Thanks for the help!

I tried this new code, but I got an error at first with ContactID written
like that. It said something about ContactID being in multiple tables. So I
wrote it for tblContacts.ContactID instead. It seems to run now. But it
replaces the "Business" with "Home". Here is screen capture of the example:

http://img20.exs.cx/img20/903/access21yh.jpg

You see two entries for those names. Normally, they are Home and Business,
but after running it with that expression they are both Home now.

Thanks for your help with this.

No, the problem is that I omitted one of the criteria tests in my post. My
apologies.


TheAddressToUse: Nz(DLookup("AddressInfo", "tblAddresses",
"AddressType='Home' And ContactID=" & [ContactID]),Nz(DLookup("AddressInfo",
"tblAddresses",
"AddressType='Business' And ContactID=" & [ContactID]),"no address"))

--

Ken Snell
<MS ACCESS MVP>

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.

:

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>

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.

 

[Ken Snell [MVP]]

You'll need to do an additional change to your query. Delete the
tblAddresses table from the query. Using the DLookup function, you no longer
need to have tblAddresses in the query as a data source -- the address info
is coming via the DLookup function instead. So long as you continue to have
that join, just as you noted in your first attempt, you'll get two records.

--

Ken Snell
<MS ACCESS MVP>

Don't apologize. Thanks for the help!

I tried this new code, but I got an error at first with ContactID written
like that. It said something about ContactID being in multiple tables. So
I
wrote it for tblContacts.ContactID instead. It seems to run now. But it
replaces the "Business" with "Home". Here is screen capture of the
example:

http://img20.exs.cx/img20/903/access21yh.jpg

You see two entries for those names. Normally, they are Home and Business,
but after running it with that expression they are both Home now.

Thanks for your help with this.

No, the problem is that I omitted one of the criteria tests in my post.
My
apologies.


TheAddressToUse: Nz(DLookup("AddressInfo", "tblAddresses",
"AddressType='Home' And ContactID=" &
[ContactID]),Nz(DLookup("AddressInfo",
"tblAddresses",
"AddressType='Business' And ContactID=" & [ContactID]),"no address"))

--

Ken Snell
<MS ACCESS MVP>

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.

:

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>

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.

 

[Guest]

This is where my lack of knowledge of Access is showing. I don't quite
understand the concepts here, but I will read some more to learn.

I removed the tblAddresses from the query and you seem to be correct. I had
440 records before and now I have 434. Those six records seem to be the
second entries.

But now since the tblAddresses is removed, I cannot list the addresses. LOL
Some fields in that table I needed, such as the address, city, state, etc.
Is there another way I can take care of this?

Thanks.

You'll need to do an additional change to your query. Delete the
tblAddresses table from the query. Using the DLookup function, you no longer
need to have tblAddresses in the query as a data source -- the address info
is coming via the DLookup function instead. So long as you continue to have
that join, just as you noted in your first attempt, you'll get two records.

--

Ken Snell
<MS ACCESS MVP>

Don't apologize. Thanks for the help!

I tried this new code, but I got an error at first with ContactID written
like that. It said something about ContactID being in multiple tables. So
I
wrote it for tblContacts.ContactID instead. It seems to run now. But it
replaces the "Business" with "Home". Here is screen capture of the
example:

http://img20.exs.cx/img20/903/access21yh.jpg

You see two entries for those names. Normally, they are Home and Business,
but after running it with that expression they are both Home now.

Thanks for your help with this.

No, the problem is that I omitted one of the criteria tests in my post.
My
apologies.


TheAddressToUse: Nz(DLookup("AddressInfo", "tblAddresses",
"AddressType='Home' And ContactID=" &
[ContactID]),Nz(DLookup("AddressInfo",
"tblAddresses",
"AddressType='Business' And ContactID=" & [ContactID]),"no address"))

--

Ken Snell
<MS ACCESS MVP>


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.

:

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>

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.

 

[Ken Snell [MVP]]

Yes, this can be done by changing your query around.

To help you, I'll need you to post the SQL statement of the original query
(before you removed the tblAddresses table but that had my DLookup function
in it). I'll assist you in rewriting it to eliminate duplicates and to give
you the desired addresses.

--

Ken Snell
<MS ACCESS MVP>

This is where my lack of knowledge of Access is showing. I don't quite
understand the concepts here, but I will read some more to learn.

I removed the tblAddresses from the query and you seem to be correct. I
had
440 records before and now I have 434. Those six records seem to be the
second entries.

But now since the tblAddresses is removed, I cannot list the addresses.
LOL
Some fields in that table I needed, such as the address, city, state,
etc.
Is there another way I can take care of this?

Thanks.

You'll need to do an additional change to your query. Delete the
tblAddresses table from the query. Using the DLookup function, you no
longer
need to have tblAddresses in the query as a data source -- the address
info
is coming via the DLookup function instead. So long as you continue to
have
that join, just as you noted in your first attempt, you'll get two
records.

--

Ken Snell
<MS ACCESS MVP>

Don't apologize. Thanks for the help!

I tried this new code, but I got an error at first with ContactID
written
like that. It said something about ContactID being in multiple tables.
So
I
wrote it for tblContacts.ContactID instead. It seems to run now. But it
replaces the "Business" with "Home". Here is screen capture of the
example:

http://img20.exs.cx/img20/903/access21yh.jpg

You see two entries for those names. Normally, they are Home and
Business,
but after running it with that expression they are both Home now.

Thanks for your help with this.

:

No, the problem is that I omitted one of the criteria tests in my
post.
My
apologies.


TheAddressToUse: Nz(DLookup("AddressInfo", "tblAddresses",
"AddressType='Home' And ContactID=" &
[ContactID]),Nz(DLookup("AddressInfo",
"tblAddresses",
"AddressType='Business' And ContactID=" & [ContactID]),"no address"))

--

Ken Snell
<MS ACCESS MVP>


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.

:

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>

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.

 

[Guest]

Here is the SQL statement copied directly from the window:

SELECT tblAllEvents.EventsID, tblContacts.LastName, tblContacts.ContactID,
Nz(DLookUp("ContactType","tblAddresses","ContactType='Home' And ContactID=" &
[tblContacts.ContactID]),Nz(DLookUp("ContactType","tblAddresses","ContactType='Business'
And ContactID=" & [tblContacts.ContactID]),"no address")) AS TheAddressToUse,
tblAllEvents.Event, tblAllEvents.EventDate,
GetFullName([prename],[firstname],[middlename],[lastname],[postname]) AS
FullName, tblEvents.Amount, tblEvents.Paid, tblEvents.PlansToAttend,
tblEvents.Attending, tblEvents.Auction,
IIf(IsNull([amount]),0,[amount])+IIf(IsNull([auction]),0,[auction]) AS
TotalContribution, tblAddresses.Address1, tblAddresses.Address2,
tblAddresses.City, tblAddresses.State, tblAddresses.Zipcode
FROM (tblContacts INNER JOIN tblAddresses ON tblContacts.ContactID =
tblAddresses.ContactID) INNER JOIN (tblAllEvents INNER JOIN tblEvents ON
tblAllEvents.EventsID = tblEvents.EventID) ON tblContacts.ContactID =
tblEvents.ContactID
ORDER BY tblAllEvents.EventsID, tblContacts.LastName, tblContacts.ContactID;

Thanks so much for the help.

Yes, this can be done by changing your query around.

To help you, I'll need you to post the SQL statement of the original query
(before you removed the tblAddresses table but that had my DLookup function
in it). I'll assist you in rewriting it to eliminate duplicates and to give
you the desired addresses.

--

Ken Snell
<MS ACCESS MVP>

This is where my lack of knowledge of Access is showing. I don't quite
understand the concepts here, but I will read some more to learn.

I removed the tblAddresses from the query and you seem to be correct. I
had
440 records before and now I have 434. Those six records seem to be the
second entries.

But now since the tblAddresses is removed, I cannot list the addresses.
LOL
Some fields in that table I needed, such as the address, city, state,
etc.
Is there another way I can take care of this?

Thanks.

You'll need to do an additional change to your query. Delete the
tblAddresses table from the query. Using the DLookup function, you no
longer
need to have tblAddresses in the query as a data source -- the address
info
is coming via the DLookup function instead. So long as you continue to
have
that join, just as you noted in your first attempt, you'll get two
records.

--

Ken Snell
<MS ACCESS MVP>



Don't apologize. Thanks for the help!

I tried this new code, but I got an error at first with ContactID
written
like that. It said something about ContactID being in multiple tables.
So
I
wrote it for tblContacts.ContactID instead. It seems to run now. But it
replaces the "Business" with "Home". Here is screen capture of the
example:

http://img20.exs.cx/img20/903/access21yh.jpg

You see two entries for those names. Normally, they are Home and
Business,
but after running it with that expression they are both Home now.

Thanks for your help with this.

:

No, the problem is that I omitted one of the criteria tests in my
post.
My
apologies.


TheAddressToUse: Nz(DLookup("AddressInfo", "tblAddresses",
"AddressType='Home' And ContactID=" &
[ContactID]),Nz(DLookup("AddressInfo",
"tblAddresses",
"AddressType='Business' And ContactID=" & [ContactID]),"no address"))

--

Ken Snell
<MS ACCESS MVP>


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.

:

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>

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.

 

[Ken Snell [MVP]]

Let's try this and see how it works:

SELECT tblAllEvents.EventsID, tblContacts.LastName, tblContacts.ContactID,
tblAllEvents.Event, tblAllEvents.EventDate,
GetFullName([prename],[firstname],[middlename],[lastname],[postname]) AS
FullName, tblEvents.Amount, tblEvents.Paid, tblEvents.PlansToAttend,
tblEvents.Attending, tblEvents.Auction,
IIf(IsNull([amount]),0,[amount])+IIf(IsNull([auction]),0,[auction]) AS
TotalContribution, tblAddresses.Address1, tblAddresses.Address2,
tblAddresses.City, tblAddresses.State, tblAddresses.Zipcode
FROM (tblContacts INNER JOIN tblAddresses ON tblContacts.ContactID =
tblAddresses.ContactID) INNER JOIN (tblAllEvents INNER JOIN tblEvents ON
tblAllEvents.EventsID = tblEvents.EventID) ON tblContacts.ContactID =
tblEvents.ContactID
WHERE
tblAddresses.ContactType=Nz(DLookUp("ContactType","tblAddresses","ContactType='Home'
And ContactID=" &
[tblContacts.ContactID]),Nz(DLookUp("ContactType","tblAddresses","ContactType='Business'
And ContactID=" & [tblContacts.ContactID]),"no address"))
ORDER BY tblAllEvents.EventsID, tblContacts.LastName, tblContacts.ContactID;

--

Ken Snell
<MS ACCESS MVP>

Here is the SQL statement copied directly from the window:

SELECT tblAllEvents.EventsID, tblContacts.LastName, tblContacts.ContactID,
Nz(DLookUp("ContactType","tblAddresses","ContactType='Home' And
ContactID=" &
[tblContacts.ContactID]),Nz(DLookUp("ContactType","tblAddresses","ContactType='Business'
And ContactID=" & [tblContacts.ContactID]),"no address")) AS
TheAddressToUse,
tblAllEvents.Event, tblAllEvents.EventDate,
GetFullName([prename],[firstname],[middlename],[lastname],[postname]) AS
FullName, tblEvents.Amount, tblEvents.Paid, tblEvents.PlansToAttend,
tblEvents.Attending, tblEvents.Auction,
IIf(IsNull([amount]),0,[amount])+IIf(IsNull([auction]),0,[auction]) AS
TotalContribution, tblAddresses.Address1, tblAddresses.Address2,
tblAddresses.City, tblAddresses.State, tblAddresses.Zipcode
FROM (tblContacts INNER JOIN tblAddresses ON tblContacts.ContactID =
tblAddresses.ContactID) INNER JOIN (tblAllEvents INNER JOIN tblEvents ON
tblAllEvents.EventsID = tblEvents.EventID) ON tblContacts.ContactID =
tblEvents.ContactID
ORDER BY tblAllEvents.EventsID, tblContacts.LastName,
tblContacts.ContactID;

Thanks so much for the help.

Yes, this can be done by changing your query around.

To help you, I'll need you to post the SQL statement of the original
query
(before you removed the tblAddresses table but that had my DLookup
function
in it). I'll assist you in rewriting it to eliminate duplicates and to
give
you the desired addresses.

 

[Guest]

Hi Ken,

Thanks so much! That seems to have fixed it. I have been away working on
other items today so I was not able to get back to you. I have input the SQL
code and am in the process of checking it with my original output, because
the code seems to have dropped the number of people from 440 to 395, which is
good, but unexpected. I thought the number would not have gone that low. I
want to make sure I am not leaving someone out. One thing I did notice is
that the "no address" people are not on this list, but I see that option in
the code. That is minor, becuase those people are from years ago.

Thanks again for the much needed help. I will report back to let you know
the final outcome, in case you check the post.

Richard

Let's try this and see how it works:

SELECT tblAllEvents.EventsID, tblContacts.LastName, tblContacts.ContactID,
tblAllEvents.Event, tblAllEvents.EventDate,
GetFullName([prename],[firstname],[middlename],[lastname],[postname]) AS
FullName, tblEvents.Amount, tblEvents.Paid, tblEvents.PlansToAttend,
tblEvents.Attending, tblEvents.Auction,
IIf(IsNull([amount]),0,[amount])+IIf(IsNull([auction]),0,[auction]) AS
TotalContribution, tblAddresses.Address1, tblAddresses.Address2,
tblAddresses.City, tblAddresses.State, tblAddresses.Zipcode
FROM (tblContacts INNER JOIN tblAddresses ON tblContacts.ContactID =
tblAddresses.ContactID) INNER JOIN (tblAllEvents INNER JOIN tblEvents ON
tblAllEvents.EventsID = tblEvents.EventID) ON tblContacts.ContactID =
tblEvents.ContactID
WHERE
tblAddresses.ContactType=Nz(DLookUp("ContactType","tblAddresses","ContactType='Home'
And ContactID=" &
[tblContacts.ContactID]),Nz(DLookUp("ContactType","tblAddresses","ContactType='Business'
And ContactID=" & [tblContacts.ContactID]),"no address"))
ORDER BY tblAllEvents.EventsID, tblContacts.LastName, tblContacts.ContactID;

--

Ken Snell
<MS ACCESS MVP>

Here is the SQL statement copied directly from the window:

SELECT tblAllEvents.EventsID, tblContacts.LastName, tblContacts.ContactID,
Nz(DLookUp("ContactType","tblAddresses","ContactType='Home' And
ContactID=" &
[tblContacts.ContactID]),Nz(DLookUp("ContactType","tblAddresses","ContactType='Business'
And ContactID=" & [tblContacts.ContactID]),"no address")) AS
TheAddressToUse,
tblAllEvents.Event, tblAllEvents.EventDate,
GetFullName([prename],[firstname],[middlename],[lastname],[postname]) AS
FullName, tblEvents.Amount, tblEvents.Paid, tblEvents.PlansToAttend,
tblEvents.Attending, tblEvents.Auction,
IIf(IsNull([amount]),0,[amount])+IIf(IsNull([auction]),0,[auction]) AS
TotalContribution, tblAddresses.Address1, tblAddresses.Address2,
tblAddresses.City, tblAddresses.State, tblAddresses.Zipcode
FROM (tblContacts INNER JOIN tblAddresses ON tblContacts.ContactID =
tblAddresses.ContactID) INNER JOIN (tblAllEvents INNER JOIN tblEvents ON
tblAllEvents.EventsID = tblEvents.EventID) ON tblContacts.ContactID =
tblEvents.ContactID
ORDER BY tblAllEvents.EventsID, tblContacts.LastName,
tblContacts.ContactID;

Thanks so much for the help.

Yes, this can be done by changing your query around.

To help you, I'll need you to post the SQL statement of the original
query
(before you removed the tblAddresses table but that had my DLookup
function
in it). I'll assist you in rewriting it to eliminate duplicates and to
give
you the desired addresses.

--

Ken Snell
<MS ACCESS MVP>

This is where my lack of knowledge of Access is showing. I don't quite
understand the concepts here, but I will read some more to learn.

I removed the tblAddresses from the query and you seem to be correct. I
had
440 records before and now I have 434. Those six records seem to be the
second entries.

But now since the tblAddresses is removed, I cannot list the addresses.
LOL
Some fields in that table I needed, such as the address, city,
state,
etc.
Is there another way I can take care of this?

Thanks.

 

[Ken Snell [MVP]]

Glad to hear it.

The "no address" option appears in the DLookup function, but it likely won't
return any records because "no address" will be the result of the DLookup
function only when there is no Home or Work address in the table (and as
such there is no value in the ContactID field because there is no record). I
left it in the DLookup function only to avoid an error that would occur if
there are no address records, as the query will "choke" if you try to do a
test of
ContactID = Null

So this test is just an error trap, not to actually find/display records
that don't exist.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks so much! That seems to have fixed it. I have been away working on
other items today so I was not able to get back to you. I have input the
SQL
code and am in the process of checking it with my original output, because
the code seems to have dropped the number of people from 440 to 395, which
is
good, but unexpected. I thought the number would not have gone that low. I
want to make sure I am not leaving someone out. One thing I did notice is
that the "no address" people are not on this list, but I see that option
in
the code. That is minor, becuase those people are from years ago.

Thanks again for the much needed help. I will report back to let you know
the final outcome, in case you check the post.

Richard

Let's try this and see how it works:

SELECT tblAllEvents.EventsID, tblContacts.LastName,
tblContacts.ContactID,
tblAllEvents.Event, tblAllEvents.EventDate,
GetFullName([prename],[firstname],[middlename],[lastname],[postname]) AS
FullName, tblEvents.Amount, tblEvents.Paid, tblEvents.PlansToAttend,
tblEvents.Attending, tblEvents.Auction,
IIf(IsNull([amount]),0,[amount])+IIf(IsNull([auction]),0,[auction]) AS
TotalContribution, tblAddresses.Address1, tblAddresses.Address2,
tblAddresses.City, tblAddresses.State, tblAddresses.Zipcode
FROM (tblContacts INNER JOIN tblAddresses ON tblContacts.ContactID =
tblAddresses.ContactID) INNER JOIN (tblAllEvents INNER JOIN tblEvents ON
tblAllEvents.EventsID = tblEvents.EventID) ON tblContacts.ContactID =
tblEvents.ContactID
WHERE
tblAddresses.ContactType=Nz(DLookUp("ContactType","tblAddresses","ContactType='Home'
And ContactID=" &
[tblContacts.ContactID]),Nz(DLookUp("ContactType","tblAddresses","ContactType='Business'
And ContactID=" & [tblContacts.ContactID]),"no address"))
ORDER BY tblAllEvents.EventsID, tblContacts.LastName,
tblContacts.ContactID;

--

Ken Snell
<MS ACCESS MVP>

Here is the SQL statement copied directly from the window:

SELECT tblAllEvents.EventsID, tblContacts.LastName,
tblContacts.ContactID,
Nz(DLookUp("ContactType","tblAddresses","ContactType='Home' And
ContactID=" &
[tblContacts.ContactID]),Nz(DLookUp("ContactType","tblAddresses","ContactType='Business'
And ContactID=" & [tblContacts.ContactID]),"no address")) AS
TheAddressToUse,
tblAllEvents.Event, tblAllEvents.EventDate,
GetFullName([prename],[firstname],[middlename],[lastname],[postname])
AS
FullName, tblEvents.Amount, tblEvents.Paid, tblEvents.PlansToAttend,
tblEvents.Attending, tblEvents.Auction,
IIf(IsNull([amount]),0,[amount])+IIf(IsNull([auction]),0,[auction]) AS
TotalContribution, tblAddresses.Address1, tblAddresses.Address2,
tblAddresses.City, tblAddresses.State, tblAddresses.Zipcode
FROM (tblContacts INNER JOIN tblAddresses ON tblContacts.ContactID =
tblAddresses.ContactID) INNER JOIN (tblAllEvents INNER JOIN tblEvents
ON
tblAllEvents.EventsID = tblEvents.EventID) ON tblContacts.ContactID =
tblEvents.ContactID
ORDER BY tblAllEvents.EventsID, tblContacts.LastName,
tblContacts.ContactID;

Thanks so much for the help.

:

Yes, this can be done by changing your query around.

To help you, I'll need you to post the SQL statement of the original
query
(before you removed the tblAddresses table but that had my DLookup
function
in it). I'll assist you in rewriting it to eliminate duplicates and to
give
you the desired addresses.

--

Ken Snell
<MS ACCESS MVP>

This is where my lack of knowledge of Access is showing. I don't
quite
understand the concepts here, but I will read some more to learn.

I removed the tblAddresses from the query and you seem to be
correct. I
had
440 records before and now I have 434. Those six records seem to be
the
second entries.

But now since the tblAddresses is removed, I cannot list the
addresses.
LOL
Some fields in that table I needed, such as the address, city,
state,
etc.
Is there another way I can take care of this?

Thanks.