Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010

    Unanswered: Access 2007 Relationship/SQL question

    Hi - thanks for reading! I'm looking for some feedback on how to go about creating a query. It's a 2 table query -
    1.) tblAOpenOrder - list of customer orders
    these fields are imported - I left the field names
    AOOID - autonumber ID
    Customer PO # - text
    Gld Order # - text
    Order Date - Date/Time
    Part # - text
    Quantity Ordered - number

    2.) tblAMZBACKORDERS - back orders that have shipped
    these back orders come from an append query - I do not have an ID for these
    NENO - text
    CUSTPO - text
    INVDATE - Date/Time
    INVNO - text
    INVAMT - Number
    SHIPDATE - Date/Time
    APID - number (ID field from parts table)
    Part# - text
    QTY - text
    PRICE - number
    FRT - Number
    TAX - number

    I want to match the Gld Order #(tbl1) to the NENO(tbl2), then match by part#(tbls1 & 2), then deduct QTY from Quantity Ordered
    Gld Order # and NENO are matched values
    Customer PO # and CUSTPO are matched values
    Part # and Part# are matched values

    The tblAOpenOrder will repeat its Gld Order #, Customer PO #, Order Date for each Part # on the order.....there are anywhere from 1 - 22 part#'s on an order
    The tblAMZBACKORDERS will repeat NENO, CUSTPO for each shipment that is billed
    the unique value in tblAMZBACKORDERS would be INVNO which is indexed (it does need to duplicate per part#)

    Can anyone advise how these relationships should be set up - and what series of queries I would need to accomplish this?

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    first off redesign your tables and columns so you don't (ab)use reserved words and symbols.
    if you want to have something that is human friendly then set the columns appropriate caption property as part of the column definition.

    next learn how to design tables / columns that conform to the normalised approach used in relational databases. As you already state an order may be made up of 22 individual product lines, so your table design should reflect that (two tables, one defines the order header the other defines the products comprising each order.

    Why you need a tabel for back orders beats me, Id expect a table that identified what had been shipped.
    Id expect a table for products
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2010
    THANK YOU - this absolutely set me on the right track!

Posting Permissions

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