Combined address in one table "but Only default address" into a qu

T

Terra

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. Can anyone show me how I can
go about doing this?

TY
Terra
 
N

NetworkTrade

what is it that indicates which is the default? ...the 1 or 2 ??

if so then you need a query with the criteria for 1 that displays only Work
info for those records with a 1

then you need a query with the criteria for 2 that displays only Home info
for those records with a 2

so your mailing list is actually 2 mailing lists...you can put them onto one
report using a subreport for one or the other....
 
T

Terra

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
mailing report that merges with word but only for the default address. I have
to separate queries one for the default "Home" and one for "Work", I need to
unify them in to one qry.
 
J

John W. Vinson

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. Can anyone show me how I can
go about doing this?

TY
Terra
Click to expand...

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

Terra

John,
Thank you for your reply, is it very much appreciated. I tried the
calculated query, as you suggested, but it only gave me the default 2
(HomeAddress) in the column and it did not pull the WorkAddress field.
What did you mean by If – as you should!
Below is what I put in the query. When you mean repeat this, do you 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

Thank you!
Terra

John W. Vinson said:
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. Can anyone show me how I can
go about doing this?

TY
Terra
Click to expand...

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.
Click to expand...
 
T

Terra

I want to thank 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.

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

Terra said:
John,
Thank you for your reply, is it very much appreciated. I tried the
calculated query, as you suggested, but it only gave me the default 2
(HomeAddress) in the column and it did not pull the WorkAddress field.
What did you mean by If – as you should!
Below is what I put in the query. When you mean repeat this, do you 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

Thank you!
Terra

John W. Vinson said:
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. Can anyone show me how I can
go about doing this?

TY
Terra
Click to expand...

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.
Click to expand...
Click to expand...
 
Top