Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2011
    Posts
    1

    Unanswered: Multiple Fields but one primary Key

    Hello,
    Im newb to database and writing a simple invoicing software in access with following fields


    PO.No
    Po.Date
    Item
    Qty
    Unit
    Rate
    Amount
    total Qty
    total amount

    And Later i want it to be tracked by Po.No. Problem i facing is that i can only add one item per P.o.No i want to add multiple items with different quantity rate and amount and later show totals ... ! but all of these records should belong to single Po.No any solutions or ideas ??

    regards

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This scenario calls for a classic One-to-Many Relationship. Basically you need two tables.

    First table stores information such as PO Number, PO date, company/person ordering, etc.

    Second table stores the line item data, such as Item, Qty, Unit, Rate, Amount and PO Number.

    The PO Number will be used to link the two tables.

    You will then need to use a Main Form/Subform to enter and display your data.

    The Main Form will display information from the first table. PO Number, PO Date, etc.

    SubForm will display the information from the second or line item data table. Item, Qty, Unit, Rate, Amount and PO Number.

    total Qty and total amount would typically be calculated and placed in a field in the Footer section of the SubForm, then referenced by an Unbound Textbox in the Main Form.

    Also, since you're in the early stages here, note this about names in Access.

    You cannot use names with periods in them, such as PO.No and Po.Date. You really should avoid all special characters except the Underscore _.

    You should avoid names with spaces, as this requires special handling in Access and frequently leads to problems. Instead of total Qty use total_Qty or totalQty.

    And avoid Reserved Words, such as Date. This, too leads to all kinds of problems. Use PODate or PO_Date instead.

    Linq ;0)>
    Last edited by Missinglinq; 02-23-11 at 11:25.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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