General discussion

  • Creator
  • #2280058

    Access Database


    by swahmad ·

    i want to design a invoice form in access database,but have few problems (1) how can i enter multiple orders under one invoice number. the invoice number is auto-number. (2) when i apply some formula the result is not stored in table, like i calculate profit so [income – exp = profit] but in form it show me the result but not stored in table. i checked the field name are same.
    thanx in advance,

All Comments

  • Author
    • #3365316

      Reply To: Access Database

      by cptomlly ·

      In reply to Access Database

      You cannot have multiple entries for an auto-number field, because autonumber must be unique for the whole table. I think that the most efficient way to handle this would be to have a relational d/b design. You will need at least 3 tables, one for customer info, one for invoice number, and one for the items on the invoice. tblCustomer will have a customer and store all the address, phone number, etc. CustID.tblCustomer is the unique primary field. The next table should be the tblInvoiceNumber, which will have at least two fields: CustID and InvNo, where InvNo.tblInvoiceNumber is the unique primary field and can be autonumbered. CustID.tblCustomer and CustID.tblInvoiceNumber should have a one-to-many relationship. The third table (tblOrders) will have a InvNo field and all other fields you need to record for the specific record. InvNo.tblInvoiceNumber and InvNo.tblOrders will again have a one-to-many relationship. InvNo.tblOrders is going to have the auto-number values generated by tblInvoiceNumber, but you will be allowed to have duplicates since its not a primary field (NOTE: tblOrders will NOT have a primary key). You will most likely find it beneficial to have more tables and fields than I have listed. You will of course need to adjust your form to reflect this new design.

      As for the issue for profit calculation, I think this would most effectively be handled in report or query, but its not my database after all. You might try having a hidden control that calculates the profit, then another control that displays and saves this to the table.

      Hope this helps.

    • #3365221

      Reply To: Access Database

      by tallbarb99 ·

      In reply to Access Database

      Not sure why you would want to store a value in a table that can be calculated in either a query or form or report. You just increase the size of your database by storing data rather than created dynamically when the form/query/report is opened.

Viewing 1 reply thread