Three fields conditionally based on other fields

  • Thread starter Thread starter Josh
  • Start date Start date
J

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

Tom van Stiphout said:
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
Click to expand...
Click to expand...
 
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.

Josh said:
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

Tom van Stiphout said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

Josh said:
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

Tom van Stiphout said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks again foe the reply and your time. Unfortunately I'm not familiar
with alt.binaries. :(

Tom van Stiphout said:
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.

Josh said:
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
Click to expand...
Click to expand...
Click to expand...
 
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. :(

Tom van Stiphout said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top