Combined Address

[thuzinec]

I have one tblContacts that has "Home" and "Work" address included within it, I also have an option button 1="Work" and 2="Home". I need to prepare a mailling report, but only for the defaut address.
Someone suggested that I create a calculated field. I tried this but all that it is doing is pulling the HomeAddress into the column, and I need both combined. His suggestion was
Base the report on a Query with a calculated field:

UseAddress: IIF([Forms]![YourForm]![YourOptionGroup] = 1, [Workaddress],
[Homeaddress])

If - as you should! - the address consists of multiple fields (Address1,
Address2, City, State, Postcode) then you'll need to repeat this logic for
each field.
I did not quite understand him, I set up my calculated field like this:
Below is what I put in the query. He said to repeat this, does he mean I
need to write in the same calculated field the
“IIF([Forms]![frmContacts]![DefaultAddress] = 1, [WorkCity] and all this in
the same calculated field “MainAddress” query’s?
This is what I put in the query below.
This is the calculated field-“MainAddress”:
“IIF([Forms]![frmContacts]![DefaultAddress] = 1, [WorkAddress], [HomeAddress]
The fields that I am addressing, which are in the same table are:
WorkAddress, WorkCity, WorkStateOrProvince, WorkPostalCode and
HomeAddress, HomeCity, HomeStateOrProvince, HomePostalCode

I would very much appreicate any help!
Thanks,
Terra

 

[BigRed1212]

Can you do?:

Select IIF([Forms]![frmContacts]![DefaultAddress] = 1, [WorkAddress], [HomeAddress]), IIF([Forms]![frmContacts]![DefaultAddress] = 1, [WorkCity], [HomeCity]), IIF([Forms]![frmContacts]![DefaultAddress] = 1, [WorkStateOrProvince], [HomeStateOrProvince]), IIF([Forms]![frmContacts]![DefaultAddress] = 1, [WorkPostalCode], [HomePostalCode])
From tblContacts

That's a guess.

 

[thuzinec]

Thanks for your response, I tried that but it does not work. I designed a qry that uses the table tblContacts and I have calculated fields named "MainAddress": MainAddress: IIf([Forms]![frmContacts]![DefaultAddress]=1,[WorkAddress],[HomeAddress]) and "MainCity": MainCity: IIf([Forms]![frmContacts]![DefaultAddress]=1,[WorkCity],[HomeCity]) and "MainState": MainState: IIf([Forms]![frmContacts]![DefaultAddress]=1,[WorkStateOrProvince],[HomeStateOrProvince]) and "MainZip: MainZip: IIf([Forms]![frmContacts]![DefaultAddress]=1,[WorkPostalCode],[HomePostalCode]) the only thing this returns is the "HomeAddress". I need to have it combined if "WorkAddress" is selected as the default to pull that address, and if "HomeAddress" pull that address. Understand that within my table "tblContacts" I have a DefaultAddress field, and I have placed this in the frmContacts as an option group with 1 meaning "WorkAddress" and 2 meaning "HomeAddress", sometimes the contact prefers to have mailing sent to home rather than the work address, and vice a versa.

Thanks for your help!
Terra

 

[Golom]

If You do this in a query

Select Contact, 'Work' As [AddressLocn], 
       WorkAddress As [Address], 
       WorkCity As [City], 
       WorkStateOrProvince As [StateOrProvince],
       WorkPostalCode As [PostalCode]
From myTable 

UNION

Select Contact, 
       'Home', 
       HomeAddress, 
       HomeCity, 
       HomeStateOrProvince, 
       HomePostalCode
From myTable

and call that "qryAddress"
Then

Select *

From myTable T INNER JOIN qryAddress Q
     ON T.Contact = Q.Contact 

Where Q.AddressLocn = IIF([Forms]![frmContacts]![DefaultAddress] = 1, "Work", "Home")

 

[Golom]

Probably better if it was

Select contact, Address, City, StateOrProvince, PostalCode

From myTable T INNER JOIN qryAddress Q
     ON T.Contact = Q.Contact 

Where Q.AddressLocn = IIF([Forms]![frmContacts]![DefaultAddress] = 1, "Work", "Home")

 

[HarleyQuinn]

Golom, that's nice and concise.

Not that it'll make a huge difference but if you used the numeric values rather than 'Work' and 'Home' (though more descriptive) you could eliminate the extra IIF statement.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[thuzinec]

Ok, the first Union query worked. It list all of the address in one column, not just the default but all combined in one column "work" and "home". I am assuming that when you refer to "Contact" I should list it as one of the fields in my tblContacts? Like "ContactID" or should it be the name of the table "tblContacts"? I could not get the second line of code that you wrote to work. Where am I to put this code? And should this also be a Union query? I am thrilled that someone finally figured out how to combined the two address! Now I need it to be just the default address based on what option the user selected.

Thank you so very much.
Terra

 

[HarleyQuinn]

Contact would be the name you'd be sending the mail to, I'd assume that'd come from your tblContacts.

If you save the UNION query as qryAddress then make a new query and use the second bit of SQL Golom posted that should sort this out for you.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[MajP]

And this would have been a lot easier if you data was normalized since and Address is and Address.

tblAddress
streetAdd
city
state
postalCode
addressType (Work, Buisness, other)
blnMailing (yes or no)
personID_fk (person ID foreign key)

And thus:

SELECT [street] & Chr(13) & Chr(10) & [city] & ", " & [state] & " " & [postalCode] AS MailingAdd, tblAdds.blnMailing
FROM tblAdds
WHERE tblAdds.blnMailing=True;

 

[thuzinec]

I want to thank all of you for trying to help me resolve my problem. I found out a way to solve this problem and I'm listing the code below, just in case anyone else did not structure their database correctly. Thanks All!
SELECT DefaultAdd.DefaultAddress , DefaultAdd.ContactID , DefaultAdd.AddressType , DefaultAdd.Address , DefaultAdd.City , DefaultAdd.StateProvince , DefaultAdd.PostalCode , DefaultAdd.CountryFROM (SELECT tblContacts.DefaultAddress , tblContacts.ContactID , tblContacts.AddressType , tblContacts.WorkAddress As Address , tblContacts.WorkCity AS City , tblContacts.WorkStateOrProvince AS StateProvince , tblContacts.WorkPostalCode As PostalCode , tblContacts.WorkCountry AS Country , IIf([defaultaddress]=1,"Work","Home") AS DefaultAddressType , "Work" AS UnionDefaultAddressType FROM tblContactsUNION ALL SELECT tblContacts.DefaultAddress , tblContacts.ContactID , tblContacts.AddressType , tblContacts.HomeAddress , tblContacts.HomeCity , tblContacts.HomeStateOrProvince , tblContacts.HomePostalCode , tblContacts.HomeCountry , IIf([defaultaddress]=1,"Work","Home") AS DefaultAddressType , "Home" AS UnionDefaultAddressType FROM tblContacts) AS DefaultAddWHERE (((DefaultAdd.DefaultAddressType)="home") AND ((DefaultAdd.UnionDefaultAddressType)="home")) OR (((DefaultAdd.DefaultAddressType)="work") AND ((DefaultAdd.UnionDefaultAddressType)="work"));