Different addresses must be accessed depending on time of year.

Mailings from this seasonal community need to go to different addresses for
individual members depending on whether the members are still in the area or
have returned to their winter homes. I suspect that queries will play a role
in this, but I also imagine that the tables need to be set up with this
requirement in mind. Do I set up separate tables for summer and winter
addresses?

Do I set up separate tables for summer and winter addresses?
No, add two DateTime fields for SeasonStart and SeasonEnd.
Then your query to have calculated field -- Todays: Date() and criteria
Between SeasonStart AND SeasonEnd

--
Build a little, test a little.


"Judith9" wrote:

Mailings from this seasonal community need to go to different addresses for
individual members depending on whether the members are still in the area or
have returned to their winter homes. I suspect that queries will play a role
in this, but I also imagine that the tables need to be set up with this
requirement in mind. Do I set up separate tables for summer and winter
addresses?

Judith

Are you saying that one "person" (member) can have more than one address?
From a purist point of view, you'd use a table of members, a table of
addresses, and a junction table to show which member "owned" which
address(es).

But if you would only ever need two addresses (summer address, winter
address), and would never ever EVER need more than two, you could save
yourself some work by putting both addresses in the same table, in different
fields.

Either way, you're going to need some way of "knowing" (or having Access
'know') when to use which address. Do you have DepartDate and ReturnDate
values for each member? Is it always the same days, or can it change from
year to year?

I think we'll need a bit more information to help us understand enough to
offer more specific suggestions...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Judith9" wrote in message
...
Mailings from this seasonal community need to go to different addresses
for
individual members depending on whether the members are still in the area
or
have returned to their winter homes. I suspect that queries will play a
role
in this, but I also imagine that the tables need to be set up with this
requirement in mind. Do I set up separate tables for summer and winter
addresses?

Hello Judith,

You have a one-to-many relationship between members and addresses so you
need an address table that looks like:
TblMemberAddress
MemberAddressID
MemberID
FromDay
FromMonth
ToDay
ToMonth
Address
City
State
ZipCode

Now for each member you can enter a summer address and a winter address. In
fact you can enter more than just summer and winter addresses if a member
has more than just the two addresses at different times of the year.

Now to find an address for a member for a date, you are correct that a query
is needed. The query needs to be based on TblMemberAddress and include all
the fields. The criteria for MemberID needs to be appropriately set to
identify the specific member. Criteria must be set for each of the four date
fields:
FromDay =Day(Date())
FromMonth =Month(Date())
ToDay =Day(Date())
ToMonth =Month(Date())

Steve




"Judith9" wrote in message
...
Mailings from this seasonal community need to go to different addresses
for
individual members depending on whether the members are still in the area
or
have returned to their winter homes. I suspect that queries will play a
role
in this, but I also imagine that the tables need to be set up with this
requirement in mind. Do I set up separate tables for summer and winter
addresses?

I would go with Steve's approach of using an Address table but keep in mind
winter seasonal addresses and the different logic for comparision (example
Dec thru Feb and today is Jan 5th) If populated they represent a seasonal
address. I would probably store the date range in 2 dates fields but the
concept is the same (you only care about the days and months because it's
the same every year). Also possibly an AddressType field storing a string
such as "business addresss", "home address", "summer home address" etc....
Also good to have a PrimaryAddress field that indicates which ONE address is
the primary mailing address. Only one record per member can be the primary
mailing address. Also Address2 and possibly Country.

See page 4 of this pdf file for a good screenshot:
http://www.missionresearch.com/giftw...0-WhatsNew.pdf

I'm not positive the best approach when querying, it sounds like this
company runs an update query to update the primaryAddress field
appropriately to set the ONE address that should be used for mailing. Then
you always just join to a query that selects the ONE primaryaddress record
for each member. There might be a better way for the querying end. It's
too late for me to think about a winter seasonal address or any address that
goes from one year to the next vs. an address that stays in the same year
and the extra complexity.

Hoping someone else answers this post I will probably need to do this one
soon myself.

My two cents,
Mark Andrews
RPT Software
http://www.rptsoftware.com




"Steve" wrote in message
...
Hello Judith,

You have a one-to-many relationship between members and addresses so you
need an address table that looks like:
TblMemberAddress
MemberAddressID
MemberID
FromDay
FromMonth
ToDay
ToMonth
Address
City
State
ZipCode

Now for each member you can enter a summer address and a winter address.
In fact you can enter more than just summer and winter addresses if a
member has more than just the two addresses at different times of the
year.

Now to find an address for a member for a date, you are correct that a
query is needed. The query needs to be based on TblMemberAddress and
include all the fields. The criteria for MemberID needs to be
appropriately set to identify the specific member. Criteria must be set
for each of the four date fields:
FromDay =Day(Date())
FromMonth =Month(Date())
ToDay =Day(Date())
ToMonth =Month(Date())

Steve




"Judith9" wrote in message
...
Mailings from this seasonal community need to go to different addresses
for
individual members depending on whether the members are still in the area
or
have returned to their winter homes. I suspect that queries will play a
role
in this, but I also imagine that the tables need to be set up with this
requirement in mind. Do I set up separate tables for summer and winter
addresses?

You should add a fudge factor to allow for mail processing. If you are
within a week of a change of address, then send it to the next address.

John... Visio MVP
"KARL DEWEY" wrote in message
...
Do I set up separate tables for summer and winter addresses?
No, add two DateTime fields for SeasonStart and SeasonEnd.
Then your query to have calculated field -- Todays: Date() and
criteria
Between SeasonStart AND SeasonEnd

--
Build a little, test a little.


"Judith9" wrote:

Mailings from this seasonal community need to go to different addresses
for
individual members depending on whether the members are still in the area
or
have returned to their winter homes. I suspect that queries will play a
role
in this, but I also imagine that the tables need to be set up with this
requirement in mind. Do I set up separate tables for summer and winter
addresses?