Results 1 to 4 of 4

Thread: Append Query?

  1. #1
    Join Date
    Apr 2008
    Posts
    13

    Unanswered: Append Query?

    I have three tables in my Access 2007 database:

    [CustomerInfo], [Products], [Inventory]

    The [CustomerInfo] table relates to [Products] through the CustomerID field.
    The [Products] table relates to the [Inventory] table through the ProductID field.
    My [Inventory Table] consists of three fields, CustomerID, ProductID & Quantity

    My objective is each time I add a new product in the [Product] table (or through a form), I want to add a record for every customer in the [CustomerInfo] table to the [Inventory] table to show they have a 0 quantity of the product, and I can do some calculations from there...

    Am I approaching this all wrong? I'm thinking I need to approach it with an Append Query, but have no idea how to tell Access what I'm wanting to do. Any thoughts, comments are 100% appreciated.

    Thank You!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds to me like your design is wrong, but it's impossible to say since you haven't given us your goals... what you want to achieve with the entire database. It is also impossible to tell what your structure really is... can you post your ERD?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2008
    Posts
    13

    Relationships

    Okay, I attached my ERD
    For simplicity, in my example I stated the table names as:
    Inventory, Product and Customer

    This translates to tables: InvestorPositions, Trades and InvestorData respectively in my database.

    Each time I add a Trade to my Trade table, I want to have a record added for every investor in the investordata table to the investorpositions table showing they have a 0 quantity of the new trade.

    I have a formula to calculate based on trade confidence and equity risk how many trades I need to add for the investor to keep them within certain guidelines.
    Attached Thumbnails Attached Thumbnails ERD.JPG  

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, I think you're after an APPEND query. However, there are a number of concerns I'd have over your ERD, but I don't have time to get into specifics atm.

    Basically, I am always concerned when I see Fieldname1, Fieldname2 etc... it usually indicates a bad relationship, a 1 where there should be an M. Also, I see no reason for the 1:1 relationships.

    To move ahead with your append, make a query that gets all the records you want to add, use a calculated field for those zeros. When you can see the data you want to add, turn the query into an Append Query, select the destination table and match the fields up.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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