Different addresses must be accessed depending on time of year.
in [Reports]
|
From: Judith9 on 22 Dec 2009 17:53 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?
From: KARL DEWEY on 22 Dec 2009 18:36 >>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?
From: Jeff Boyce on 22 Dec 2009 18:38 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" <Judith9(a)discussions.microsoft.com> wrote in message news:AAA87681-FF1D-44FC-9D41-29852E7B4D01(a)microsoft.com... > 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?
From: Steve on 22 Dec 2009 19:57 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 santus(a)penn.com "Judith9" <Judith9(a)discussions.microsoft.com> wrote in message news:AAA87681-FF1D-44FC-9D41-29852E7B4D01(a)microsoft.com... > 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?
From: Mark Andrews on 22 Dec 2009 23:40
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/giftworks/guides/giftworks2010/GiftWorks2010-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" <notmyemail(a)address.com> wrote in message news:OJ6oir2gKHA.1236(a)TK2MSFTNGP04.phx.gbl... > 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 > santus(a)penn.com > > > > "Judith9" <Judith9(a)discussions.microsoft.com> wrote in message > news:AAA87681-FF1D-44FC-9D41-29852E7B4D01(a)microsoft.com... >> 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? > > |