Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Using Criteria to select an Address (2002)

    I have a database that tracks various client information. I have it set up with seperate tables: Client, Address, Contacts, Plan Info etc.. In my Address table there are several address: Physical, P.O. Box, Secondary (because our clients could have both a P.O. BOx and a Primary address). What I would like to do is in my merge query, show the P.O Box address if there is one, if not show the Physical address. I do not know how this is done, In my address table, I have a field, AddressCategory, which is a list box that you can pick from. All I seem to be able to do is to sort by one or the other. I hope this makes sense...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Criteria to select an Address (2002)

    If you have separate fields for Physical address and for P.O. Box, you can use an expression such as

    MergeAddress: Nz([P.O. Box],[Physical]

    or

    MergeAddress: IIf(IsNull([P.O. Box],[Physical],[P.O. Box])

    (I'm not sure what the role of the AddressCategory field is if you have separate address fields)

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using Criteria to select an Address (2002)

    Well I don't have a separate field I think that is the problem. The Address table holds any address that are related to the client, then the contact table is set up with a field called "Address" that links it to the appropriate Address.. (Hence the Address Category) For instance, The John Doe Company has three address: each address is a separate entry in the Address table, linked to the company by the companyID. The field AddressCategory tells us what type of address it is. To complicate things more, we have several contacts for that same company, we have owners, Main Contacts, Secondary Contacts etc. Each having a separate entry in the Contacts table. The contact also is linked to the Address table by an address field. We can choose which address to link to the contact by the Address Category. So it wouldn't be beneficial for me to have separate fields. The purpose for this is to do mass mailings, We want to use the P.O.Box if there is a P.O. Box entry in the Address Table, else use the Physical address entry.. Does this help any???

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Criteria to select an Address (2002)

    Thanks for the explanation. In fact, your design is better than one with separate fields for different addresses; it conforms to relational database design ansd it is more flexible.

    To get the address you want, you'll have to create several queries:
    1. <LI>Create a query returning all addresses for which AddressCategory corresponds to PO Box. Let's say you call this qryPOBox.
      <LI>Create a query returning all addresses for which AddressCategory corresponds to a Physical address. Let's say you call this qryPhysical.
      <LI>Create a query based on your contacts table and these two queries.
      <LI>Link the table to each of the two queries on the appropriate field (CompanyID or ContactID or whatever).
      <LI>Double click each of the join lines and select the options to return all records from the table and only related records from the query.
      <LI>Add the fields from the table that you need.
      <LI>Create a calculated column:

      MergeAddress: Nz([qryPOBox].[Address],[qryPhysical].[Address])

      <LI>Check whether this query returns what you want.
    Depending on your exact setup, you may have to change this a bit, but it should give you the basic idea.
    If you cannot get it to work, you could perhaps attach a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using Criteria to select an Address (2002)

    Thank you Hans, Unfortunately I will not be able to try this till tomorrow. But Thank you so much for your help! It is very much appriciated. I will be posting back if I run into any trouble! Thanks again!

  6. #6
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using Criteria to select an Address (2002)

    Hans you are fantastic! Thank you it worked perfectly! I couldn't wait till tomorrow! I had to try it tonight it was eating at me. Oh the possiblities with this new trick!

    Thanks again,

Posting Permissions

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