address query help

[67anirt]

Hi All



I have a mail out query that I need help with. the query links from a couple of tables, ycdetails, mailout, and geographical area. The mail out list brings me up all the young people who are currently having a service with names and addresses. Some young people need information sending to a 2nd address, some to there home address and some to both addresses. I have two tick boxes (use 1st address and use 2nd address) how to I alter my query to send to the first address if that is selected, 2nd if that is selected and both if both are selected? Addresses are set in fields address1 address2 area3 and postcode4 the same with the 2nd address details ie, 2ndaddress1, 2ndaddress2 etc.

Would really appreciate any help with this as I'm tearing my hair out!!

[JoeM]

It sounds like what you are really dealing with is an issue with your database design, specifically your data tables are not normalized, which makes tasks which should be easy much more difficult than they have to be. Basically, you should NOT repeat fields in your table design (i.e. an address details for your primary address and separate address details for your secondary address all in the same record). How it really should be structured is your should just have a single set of address details for each record, but then a field that indicates if it is the primary or secondary address (and your check box determining whether or not they want it mailed there). So if someone had both primary and secondary address details, they would have two records in your table. For more on database design and rules of normalization, check out this link: http://www.deeptraining.com/litwin/d...aseDesign.aspx

Now, if you are unable to change the structure of your table, here is one way you could approach this:
1. Create two queries that follow the same structure, one for people who want stuff sent to their primary address, and one who want stuff sent to their secondary address (and people may be found in both queries).
2. Create a Union query which combines the records from the previous two queries into a single query (see here if you need help on that: http://office.microsoft.com/en-us/ac...010206109.aspx).

This is a great example of why you want to normalize your data tables. If you do, you see it would only take one query to get what you want, as opposed to having to use three queries (or a Union query with multiple subqueries) to get what you want.

[67anirt]

Thanks so much for that!