Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Location
    Stansted UK
    Posts
    5

    Unanswered: Using selected data in two tables

    I am a novice, so please be patient.

    I have an Access 2010 database that tracks the certification and contract details for our contractors. Ultimately I want to be able to use the database to run a mailmerge in Word to create new contracts, but before I get to that point I need to solve a problem with addresses.

    All of our contractors have their own limited companies, for which there is a registered address and in some cases this is the same as their home address, but not in all. I already have their home addresses in another related table, so I would like to be able to use a checkbox that when checked it either displays that address, or copies the address fields across.

    My question is, I know that duplicating data is undesirable in database design, so which is the most elegant solution - to copy or to display the home address? And in either case, how do I do it? Bearing in mind that the end result is that I need to be able to use the address information in a mailmerge.

    Thank you so much for any advice and help.

  2. #2
    Join Date
    Jan 2012
    Posts
    13
    Hi, MelanieB,
    I would try creating a query with a calculated field that looks something like this:
    IIF([RegisteredAddress] Is Null, [HomeAddress], [RegisteredAddress])

    Then you can use that query as the basis of your mail-merge.
    Let me know if this helps. Thanks.

  3. #3
    Join Date
    Apr 2013
    Location
    Stansted UK
    Posts
    5
    Thank you for your suggestion.

    Although that would work in principle, I'm concerned it will cause errors. The query would use the home address as the default for the registered address, but that's not the case. Sometimes the home address is the same as the registered address, but not always.

    Is there a way to filter the query so that if a tick box is ticked on a form, then it uses the home address, rather than using Is Null?

    Thank you for all your help.

  4. #4
    Join Date
    Jan 2012
    Posts
    13
    Quote Originally Posted by MelanieB View Post
    Thank you for your suggestion.

    Although that would work in principle, I'm concerned it will cause errors. The query would use the home address as the default for the registered address, but that's not the case. Sometimes the home address is the same as the registered address, but not always.

    Is there a way to filter the query so that if a tick box is ticked on a form, then it uses the home address, rather than using Is Null?
    Sorry about that.
    Try using the below formula for the address field in the query you will use for your mail-merge. (Note: in this example, "UseHomeAddress" is the name of the yes/no field that the check box would be bound to).

    MMAddress: IIf([UseHomeAddress]=True, [HomeAddress], [RegisteredAddress])

    The reason I first suggested the "Is Null" formula is because I was hoping the formula could automatically decide which address to use without you having to manually check a box for each contract.
    Anyway, let me know if the above works for you.

  5. #5
    Join Date
    Apr 2013
    Location
    Stansted UK
    Posts
    5
    Hi,

    That Worked!! Thank you so much for all your help. Very, Very much appreciated and saved a lot of head scratching!

    All the best,

    Melanie
    Last edited by MelanieB; 04-15-13 at 06:39. Reason: Tried again and the suggestion worked!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •