Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2011
    Posts
    23

    Unanswered: Building Access Query...need help

    I need to put some data from a database and not quite sure how to do it or if it is possible:

    Data is stored in a Table called Addresses. ID fields link this table to another table. The Table contains the following fields: ID, TYPE, Address1, Address2, City, St, Zip. Each ID can have multiple addresses (Mailing, Shipping, Rink, Etc.) but not have all of these. So ID# 5 may have 3 records in this table, ID# 2 may have 2, etc.

    I need to pull address for shipping items via UPS so address can't be a PO Box.

    Here is my laymans explanation of what I need:

    All records that have a Shipping Address, if no shipping address use Mailing Address; if mailing address is a PO Box, use Rink Address.

    Is there a way to do this and if so how would I go about it.

    Thanks in advance for any help

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    You should really have a primary key setup on your table... You'll run into difficulties later if you omit one now.

    A primary key being a UNIQUE ID for each record in a table.


    What do you actually need?

    put some data from a database
    Doesn't make sense... Do you need a query that will return every shipping address held on the table, failing that, their mailing address, etc... So that every ID has an address returned with it?
    Looking for the perfect beer...

  3. #3
    Join Date
    Aug 2011
    Posts
    23
    Quote Originally Posted by kez1304 View Post
    You should really have a primary key setup on your table... You'll run into difficulties later if you omit one now.

    A primary key being a UNIQUE ID for each record in a table.


    What do you actually need?



    Doesn't make sense... Do you need a query that will return every shipping address held on the table, failing that, their mailing address, etc... So that every ID has an address returned with it?
    I agree on the primary key, however I didn't build the database, I was brought in to help them make changes to it because no one who uses it knows Access. Let me say, I am not by any means an Access Guru...I know it and can design and build basic databases. So I may have other questions for this fourm And I agree it's confusing, I'm still trying to come up to speed and understand what they need and how this all works.

    Let me see if I can describe the database first...
    It contains records of organizations that the company calls "members" There are multiple tables all linked to each other by an Access ID field. There are some linking issues as well because not only are the organizations in the table but individuals as well, that then need to be linked to the Org (in the same table ). My first thought is there should be two main tables: Orgs and Indv but I think that is for another topic.

    For this problem I'm asking for help with, they send items via UPS to these organizations all over the world. There is an address table that links back to each organization and in the address table there are up to 5 different records for each organization. Each record is a different type of address (Shipping, Mailing, Rink, Home, Other)

    So right now they pull three queries - Shipping, Mailing & Rink - this pulls about 11000 records, sometimes it's filtered down depending on what they are sending. They then have to put those results in Excel and go thru and manually delete duplicates of addresses that have Shipping addresses that also have mailing and rink addresses; then they have to look at the ones that don't have Shipping but have Mailing and use the Mailing address as the Shipping address, but if the Mailing address is a PO Box, they then have to look at the Rink address and use the Rink address instead.

    My initial thought is that the addresses not be in a sep table but in the main table and have shipping address, mailin address...fields, would it be easier to pull using if/then queiries this way?

    Not sure, open to any and all suggestions

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by clpayton View Post
    My first thought is there should be two main tables: Orgs and Indv
    No: if the same kind of information is collected in both cases (Orgs and Indv) there should be only one table, possibly with a column that contains a value making the distinction between an individual and an organisation. Doing otherwise would be against the rules that define database normalization.

    Back to you question, there is no CASE... WHEN... construction in Access SQL (contrarily to what can be used with a SQL Server), so the simplest solution would be to use a serie of imbricated IIf() statements. Something like:
    Code:
    SELECT IIf(IsNull([Shipping]),
               IIf(IsNull([Mailing]),
                   IIf(IsNull([POBox]),[Rink],[POBox]),
               [Mailing]),
           [Shipping]) AS ShipTo
    FROM Adresses;
    Which coud be translated as:
    Code:
    If there is no shipping address then
        If there is no Mailing address then
            If there is no POBox address then use the Rink address else use the POBox address
        else use the Mailing address
    else use the Shipping address
    Have a nice day!

  5. #5
    Join Date
    Aug 2011
    Posts
    23
    Quote Originally Posted by Sinndho View Post
    No: if the same kind of information is collected in both cases (Orgs and Indv) there should be only one table, possibly with a column that contains a value making the distinction between an individual and an organisation. Doing otherwise would be against the rules that define database normalization.

    Back to you question, there is no CASE... WHEN... construction in Access SQL (contrarily to what can be used with a SQL Server), so the simplest solution would be to use a serie of imbricated IIf() statements. Something like:
    Code:
    SELECT IIf(IsNull([Shipping]),
               IIf(IsNull([Mailing]),
                   IIf(IsNull([POBox]),[Rink],[POBox]),
               [Mailing]),
           [Shipping]) AS ShipTo
    FROM Adresses;
    Which coud be translated as:
    Code:
    If there is no shipping address then
        If there is no Mailing address then
            If there is no POBox address then use the Rink address else use the POBox address
        else use the Mailing address
    else use the Shipping address
    Thanks so much, I will try this

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Micro$oft need to catch up with the times a little bit... Nested IIF statements aren't nice things to have to use, and as no alternative is provided, is a real pain.

    Just look into normalising the DB and you should find that future endevours are a lot more easy going.
    Looking for the perfect beer...

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Please notice however that it was just a draft. You'll certainly need to refine the expression in the query.
    Have a nice day!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @kez1304: I know but the only alternative I know consists in writing very complex queries, and it's not always possible (the table(s) cannot have the proper structure or the expression in the query is too complex and the Jet engine rejects it).
    Have a nice day!

  9. #9
    Join Date
    Aug 2011
    Posts
    23
    Thanks for input on this......due to timing they had to do it manual again this time. This is not going to be in my scope of things for this assignment, they may revisit it later.

Tags for this Thread

Posting Permissions

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