Three fields conditionally based on other fields

[Josh]

I need to basically flatten out some Access data into an Excel spreadsheet.
I have three separate tables, one each for Name info (first, last, etc.),
Address, and Phone. How can I make the query so I get results with fields
like this:

Name:HomeAddress:BusinessAddress:HomePhone:Email...

where the home address field is pulled from the Address table and only pulls
the address if the [Address].[AddressType] = "Home". Same critera with
business address, home phone and email (emails are also located in the Phone
table with a PhoneType of "Email").

I think I'm on the right track so far using an IIF statement, but once I try
to use more than one the results get funky (multiple records per Name).

This is really frustrating, so thanks for the help!
Josh

 

[Tom van Stiphout]

On Tue, 18 Aug 2009 21:16:01 -0700, Josh

After reading your post I am still unclear about your data structure.
Please describe it. What are the relevant fields for this problem?
What are the PKs? Also, why have 3 tables with the same (?) structure
to hold the same (?) information?

-Tom.
Microsoft Access MVP

 

[Josh]

Thanks for the reply Tom.

I have a Contact table, an Address table, and a Phone table. The relevant
fields for each table are as follows:

Contact Table:
ContactID (PK)
FirstName
LastName

Address Table:
ContactID
AddressLine1
AddressLine2
City
State
Zip
AddressType

Phone Table:
ContactID
Phone
PhoneType

If I join the tables and a contact has more than one address or phone, then
the result will contain one Contact record for each address and/or phone.

So, I'd like my result to be:

[Contact].[FirstName]
[Contact].[LastName]
[Address].[AddressLine1]
[Address].[AddressLine2]
[Address].[City]
[Address].[State]
[Address].[Zip]
(where Address Type = Home for above Address fields)
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Home")
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Email")

Hope this helps.
Thanks,
Josh

On Tue, 18 Aug 2009 21:16:01 -0700, Josh

After reading your post I am still unclear about your data structure.
Please describe it. What are the relevant fields for this problem?
What are the PKs? Also, why have 3 tables with the same (?) structure
to hold the same (?) information?

-Tom.
Microsoft Access MVP

I need to basically flatten out some Access data into an Excel spreadsheet.
I have three separate tables, one each for Name info (first, last, etc.),
Address, and Phone. How can I make the query so I get results with fields
like this:

Name:HomeAddress:BusinessAddress:HomePhone:Email...

where the home address field is pulled from the Address table and only pulls
the address if the [Address].[AddressType] = "Home". Same critera with
business address, home phone and email (emails are also located in the Phone
table with a PhoneType of "Email").

I think I'm on the right track so far using an IIF statement, but once I try
to use more than one the results get funky (multiple records per Name).

This is really frustrating, so thanks for the help!
Josh

 

[Josh]

One correction on my last post, I do not need [Phone].[PhoneType] fields in
the results, but still need to base [Phone].[Phone] on PhoneType.

Thanks.

Thanks for the reply Tom.

I have a Contact table, an Address table, and a Phone table. The relevant
fields for each table are as follows:

Contact Table:
ContactID (PK)
FirstName
LastName

Address Table:
ContactID
AddressLine1
AddressLine2
City
State
Zip
AddressType

Phone Table:
ContactID
Phone
PhoneType

If I join the tables and a contact has more than one address or phone, then
the result will contain one Contact record for each address and/or phone.

So, I'd like my result to be:

[Contact].[FirstName]
[Contact].[LastName]
[Address].[AddressLine1]
[Address].[AddressLine2]
[Address].[City]
[Address].[State]
[Address].[Zip]
(where Address Type = Home for above Address fields)
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Home")
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Email")

Hope this helps.
Thanks,
Josh

On Tue, 18 Aug 2009 21:16:01 -0700, Josh

After reading your post I am still unclear about your data structure.
Please describe it. What are the relevant fields for this problem?
What are the PKs? Also, why have 3 tables with the same (?) structure
to hold the same (?) information?

-Tom.
Microsoft Access MVP

I need to basically flatten out some Access data into an Excel spreadsheet.
I have three separate tables, one each for Name info (first, last, etc.),
Address, and Phone. How can I make the query so I get results with fields
like this:

Name:HomeAddress:BusinessAddress:HomePhone:Email...

where the home address field is pulled from the Address table and only pulls
the address if the [Address].[AddressType] = "Home". Same critera with
business address, home phone and email (emails are also located in the Phone
table with a PhoneType of "Email").

I think I'm on the right track so far using an IIF statement, but once I try
to use more than one the results get funky (multiple records per Name).

This is really frustrating, so thanks for the help!
Josh

 

[Tom van Stiphout]

On Wed, 19 Aug 2009 11:16:01 -0700, Josh

I put a sample db up on alt.binaries, with subject "Access db for
Josh".
Essentially I created 3 queries: one to get the HomeAddress; one to
get the EmailPhone, and I outer-joined them with the Contact table to
get the output you desired.

-Tom.
Microsoft Access MVP

One correction on my last post, I do not need [Phone].[PhoneType] fields in
the results, but still need to base [Phone].[Phone] on PhoneType.

Thanks.

Thanks for the reply Tom.

I have a Contact table, an Address table, and a Phone table. The relevant
fields for each table are as follows:

Contact Table:
ContactID (PK)
FirstName
LastName

Address Table:
ContactID
AddressLine1
AddressLine2
City
State
Zip
AddressType

Phone Table:
ContactID
Phone
PhoneType

If I join the tables and a contact has more than one address or phone, then
the result will contain one Contact record for each address and/or phone.

So, I'd like my result to be:

[Contact].[FirstName]
[Contact].[LastName]
[Address].[AddressLine1]
[Address].[AddressLine2]
[Address].[City]
[Address].[State]
[Address].[Zip]
(where Address Type = Home for above Address fields)
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Home")
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Email")

Hope this helps.
Thanks,
Josh

On Tue, 18 Aug 2009 21:16:01 -0700, Josh

After reading your post I am still unclear about your data structure.
Please describe it. What are the relevant fields for this problem?
What are the PKs? Also, why have 3 tables with the same (?) structure
to hold the same (?) information?

-Tom.
Microsoft Access MVP


I need to basically flatten out some Access data into an Excel spreadsheet.
I have three separate tables, one each for Name info (first, last, etc.),
Address, and Phone. How can I make the query so I get results with fields
like this:

Name:HomeAddress:BusinessAddress:HomePhone:Email...

where the home address field is pulled from the Address table and only pulls
the address if the [Address].[AddressType] = "Home". Same critera with
business address, home phone and email (emails are also located in the Phone
table with a PhoneType of "Email").

I think I'm on the right track so far using an IIF statement, but once I try
to use more than one the results get funky (multiple records per Name).

This is really frustrating, so thanks for the help!
Josh

 

[Josh]

Thanks again foe the reply and your time. Unfortunately I'm not familiar
with alt.binaries.

On Wed, 19 Aug 2009 11:16:01 -0700, Josh

I put a sample db up on alt.binaries, with subject "Access db for
Josh".
Essentially I created 3 queries: one to get the HomeAddress; one to
get the EmailPhone, and I outer-joined them with the Contact table to
get the output you desired.

-Tom.
Microsoft Access MVP

One correction on my last post, I do not need [Phone].[PhoneType] fields in
the results, but still need to base [Phone].[Phone] on PhoneType.

Thanks.

Thanks for the reply Tom.

I have a Contact table, an Address table, and a Phone table. The relevant
fields for each table are as follows:

Contact Table:
ContactID (PK)
FirstName
LastName

Address Table:
ContactID
AddressLine1
AddressLine2
City
State
Zip
AddressType

Phone Table:
ContactID
Phone
PhoneType

If I join the tables and a contact has more than one address or phone, then
the result will contain one Contact record for each address and/or phone.

So, I'd like my result to be:

[Contact].[FirstName]
[Contact].[LastName]
[Address].[AddressLine1]
[Address].[AddressLine2]
[Address].[City]
[Address].[State]
[Address].[Zip]
(where Address Type = Home for above Address fields)
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Home")
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Email")

Hope this helps.
Thanks,
Josh

:

On Tue, 18 Aug 2009 21:16:01 -0700, Josh

After reading your post I am still unclear about your data structure.
Please describe it. What are the relevant fields for this problem?
What are the PKs? Also, why have 3 tables with the same (?) structure
to hold the same (?) information?

-Tom.
Microsoft Access MVP


I need to basically flatten out some Access data into an Excel spreadsheet.
I have three separate tables, one each for Name info (first, last, etc.),
Address, and Phone. How can I make the query so I get results with fields
like this:

Name:HomeAddress:BusinessAddress:HomePhone:Email...

where the home address field is pulled from the Address table and only pulls
the address if the [Address].[AddressType] = "Home". Same critera with
business address, home phone and email (emails are also located in the Phone
table with a PhoneType of "Email").

I think I'm on the right track so far using an IIF statement, but once I try
to use more than one the results get funky (multiple records per Name).

This is really frustrating, so thanks for the help!
Josh

 

[Tom van Stiphout]

On Thu, 20 Aug 2009 07:24:02 -0700, Josh

Send me an email (my .no.spam trap should be easy to avoid) and I will
email it to you.

-Tom.
Microsoft Access MVP

Thanks again foe the reply and your time. Unfortunately I'm not familiar
with alt.binaries.

On Wed, 19 Aug 2009 11:16:01 -0700, Josh

I put a sample db up on alt.binaries, with subject "Access db for
Josh".
Essentially I created 3 queries: one to get the HomeAddress; one to
get the EmailPhone, and I outer-joined them with the Contact table to
get the output you desired.

-Tom.
Microsoft Access MVP

One correction on my last post, I do not need [Phone].[PhoneType] fields in
the results, but still need to base [Phone].[Phone] on PhoneType.

Thanks.

:

Thanks for the reply Tom.

I have a Contact table, an Address table, and a Phone table. The relevant
fields for each table are as follows:

Contact Table:
ContactID (PK)
FirstName
LastName

Address Table:
ContactID
AddressLine1
AddressLine2
City
State
Zip
AddressType

Phone Table:
ContactID
Phone
PhoneType

If I join the tables and a contact has more than one address or phone, then
the result will contain one Contact record for each address and/or phone.

So, I'd like my result to be:

[Contact].[FirstName]
[Contact].[LastName]
[Address].[AddressLine1]
[Address].[AddressLine2]
[Address].[City]
[Address].[State]
[Address].[Zip]
(where Address Type = Home for above Address fields)
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Home")
[Phone].[Phone]
[Phone].[PhoneType]
(where PhoneType = "Email")

Hope this helps.
Thanks,
Josh

:

On Tue, 18 Aug 2009 21:16:01 -0700, Josh

After reading your post I am still unclear about your data structure.
Please describe it. What are the relevant fields for this problem?
What are the PKs? Also, why have 3 tables with the same (?) structure
to hold the same (?) information?

-Tom.
Microsoft Access MVP


I need to basically flatten out some Access data into an Excel spreadsheet.
I have three separate tables, one each for Name info (first, last, etc.),
Address, and Phone. How can I make the query so I get results with fields
like this:

Name:HomeAddress:BusinessAddress:HomePhone:Email...

where the home address field is pulled from the Address table and only pulls
the address if the [Address].[AddressType] = "Home". Same critera with
business address, home phone and email (emails are also located in the Phone
table with a PhoneType of "Email").

I think I'm on the right track so far using an IIF statement, but once I try
to use more than one the results get funky (multiple records per Name).

This is really frustrating, so thanks for the help!
Josh