Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Access Database

Locked

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,
cheers.

Topic

Reply To: Access Database

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.

Reply To: Access Database

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.

[Author]

Replies