P
Paul
I apologise in advance if this information is in a previous post but I
know so little about databases and Access that I'm not even sure what
I'm looking for. I'm working my way through Access using a "For
Dummies" book but have got stuck. Let me first describe the database:
3 tables (fields are in brackets):
Invoices (Inv #; Inv Date; Cust Name)
Sales (Inv #; Item #)
Items (Item #; Item Desc; Item Price)
Inv # is a primary key for the Invoices table and the foreign key for
Sales
Item " is a primary key for the Items table and the foreign key for
Sales
This is essentially a many-to-many relationship using two one-to-many
relationships. The Sales table will grow huge, listing every item sold.
Unlike, for example, a book shop where the same item is sold
repeatedly, this is a graphic designer and each item (or project) will
have a unique identity and price.
Questions:
1) Is this a suitable design for the database?
2) How do I set up a form/subform that allows me to enter invoice
details and item details with the following outcome:
- The invoice details entered (I imagine in the main form) become an
entry in the Invoices table
- All items entered (I imagine in a subform) become entries in the
Items table
- The correct item/invoice associations become entries in the Sales
table
I'm sorry if this is a really big and general question but I'm so new
to this that I can't trim the question into anything more specific.
Hope someone can help me!
Paul
know so little about databases and Access that I'm not even sure what
I'm looking for. I'm working my way through Access using a "For
Dummies" book but have got stuck. Let me first describe the database:
3 tables (fields are in brackets):
Invoices (Inv #; Inv Date; Cust Name)
Sales (Inv #; Item #)
Items (Item #; Item Desc; Item Price)
Inv # is a primary key for the Invoices table and the foreign key for
Sales
Item " is a primary key for the Items table and the foreign key for
Sales
This is essentially a many-to-many relationship using two one-to-many
relationships. The Sales table will grow huge, listing every item sold.
Unlike, for example, a book shop where the same item is sold
repeatedly, this is a graphic designer and each item (or project) will
have a unique identity and price.
Questions:
1) Is this a suitable design for the database?
2) How do I set up a form/subform that allows me to enter invoice
details and item details with the following outcome:
- The invoice details entered (I imagine in the main form) become an
entry in the Invoices table
- All items entered (I imagine in a subform) become entries in the
Items table
- The correct item/invoice associations become entries in the Sales
table
I'm sorry if this is a really big and general question but I'm so new
to this that I can't trim the question into anything more specific.
Hope someone can help me!
Paul