The thread title says it all. I'll try to be as thorough as I can with the outline of the problem.
I'm quite new to Access and I'm playing with a project for a friend who builds computers for a living. I've set up a mutli-sheet Excel workbook where different sheets list components, roughly divided by category (Build Components, Printers & Scanners, Networking, Portable devices and Software. Each sheet has the products listed under the following column headings; Product Category, Order Code (from his supplier) Description, Cost.
I've set up a series of mutli-column listboxes accessable from a user form with command buttons which allow him to select items from any of the above sheets and transfer them directly to the invoice sheet of the same workbook. There are formulas to calculate financial amounts based on his profit margin, VAT, quantity of items and so on. It all works beautifully.
Now here's the problem. I have created the relevant ranges on the spreadsheet to allow me to link the range into Access and I can easily create the necessary append queries to pull the information into the customer order table of the database. However, this is where I go horribly wrong.
The first range/append query adds the customer contact information (name, address, tel no, etc) to the customer table in Access. No problems. The third range/append query adds the total order cost and total order VAT to the order details table. No problems. The second range/append query adds the list of products under their Product Category, Order Code, Description and Cost columns, but no mater what I do or how I link the tables in Access, I end up with each individual product item being classed as a new order with its own Primary Key ID generated by Access.
My tables are;
1. tblCustomers (CustomerID, Name, Surname, Address1, Address2, Town, County, Post Code, TelNo, FaxNo, Email)
2. tblOrders (OrderID, CustomerID, OrderDetailsID) with CustomerID being the link between them.
3. tblOrderDetails (OrderDetailsID, OrderID, Product Category, Order Codes, Description, ItemCost) with OrderDetailsID being the link between this and tblOrders.
I've tried using text and memo fields to no avail. Logic dictates that I must somehow have the table relationships set up incorrectly which is preventing me from counting one customer order as having many items in the OrderDetails table. I'm totally lost here and would appreciate any and all suggestions (as long as they remain polite of course).
My apologies for the length of this question but I wanted to include as much details as possible. Thanks in anticipation.
I'm quite new to Access and I'm playing with a project for a friend who builds computers for a living. I've set up a mutli-sheet Excel workbook where different sheets list components, roughly divided by category (Build Components, Printers & Scanners, Networking, Portable devices and Software. Each sheet has the products listed under the following column headings; Product Category, Order Code (from his supplier) Description, Cost.
I've set up a series of mutli-column listboxes accessable from a user form with command buttons which allow him to select items from any of the above sheets and transfer them directly to the invoice sheet of the same workbook. There are formulas to calculate financial amounts based on his profit margin, VAT, quantity of items and so on. It all works beautifully.
Now here's the problem. I have created the relevant ranges on the spreadsheet to allow me to link the range into Access and I can easily create the necessary append queries to pull the information into the customer order table of the database. However, this is where I go horribly wrong.
The first range/append query adds the customer contact information (name, address, tel no, etc) to the customer table in Access. No problems. The third range/append query adds the total order cost and total order VAT to the order details table. No problems. The second range/append query adds the list of products under their Product Category, Order Code, Description and Cost columns, but no mater what I do or how I link the tables in Access, I end up with each individual product item being classed as a new order with its own Primary Key ID generated by Access.
My tables are;
1. tblCustomers (CustomerID, Name, Surname, Address1, Address2, Town, County, Post Code, TelNo, FaxNo, Email)
2. tblOrders (OrderID, CustomerID, OrderDetailsID) with CustomerID being the link between them.
3. tblOrderDetails (OrderDetailsID, OrderID, Product Category, Order Codes, Description, ItemCost) with OrderDetailsID being the link between this and tblOrders.
I've tried using text and memo fields to no avail. Logic dictates that I must somehow have the table relationships set up incorrectly which is preventing me from counting one customer order as having many items in the OrderDetails table. I'm totally lost here and would appreciate any and all suggestions (as long as they remain polite of course).
My apologies for the length of this question but I wanted to include as much details as possible. Thanks in anticipation.
)