Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008

    Unanswered: Linking invoice table to customers table

    I'm making a database for a friend who runs a home business and I currently have a table set up called All records which holds data from a form with Name, address, hours, total cost etc. But what I want to do is make another table and a form which will allow him to enter the address of his most common clients so all he has to do is enter a first name and maybe a last name in the all records form and the rest of the data such as phone number and address will be all filled out.

    I can get each field in the all records form to display all the entries from the clients table but I can't get it so that when I enter the first name (eg: John), it eliminates all others that aren't part of the "John" record (like last name, phone etc)

    I hope that I've made this sort of clear about what I want to achieve.


  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Sorry, but I am not going to help you make that mess.

    You have multiple design flaws in your tables. Working with forms now is pointless.

    What you want is an INVOICE table with invoice fields (date) in it along with a CustomerID. You also want to have a CUSTOMER table with a primary key field of CustomerID (autonumber will be fine) and all the fields which are specific to the customer in it. Then you can relate the two tables by the common field CustomerID.

    Then you only have to create a combo box in your invoice form to select the customer from a list and store the customer ID in the table. That way, there is NO duplicating data entry of any kind.

    And there's more too, much more. For example, each INVOICE would have many SALES entries on it, multiple products, quantities etc, and each SALE would have a selectable PRODUCT on it. Each of those would require a separate table.

    And you wouldn't have a form for "all records" you would have one for Invoices, one for Customers, one for Products and a subform for Sales.

    Do your friend a favour. Please. Study up on how to normalise tables and deal with relationships before you even look at forms. If you don't you'll only be doing your friend a disservice.

    Sorry to be harsh on this, but designing software for businesses should not be done by the untrained -- it ends up costing that business in the long run in fixing up all the problems after heaps of data is collected.
    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