Access Queries

[Guest_imported]

I am having a lot of difficulties working on a school project. I need to design a working system using access. I have 12 tables and I am having difficulties with setting the relationships properly. For example, when I want to place an order, my line-item table has a column called ItemNumber. I also have PRODUCT.ItemNumber (this is where all of the products are listed. Okay, I can pull down the menu for LINE-ITEM.ItemNumber and place it in the column, but if another customer orders the same product, it will not allow that. I get the error that I cannot duplicate and need to adjust the index. I have tried changing to Duplicates OK (IN each table individually and also in both tables), I have also set it to not index LINE-ITEM.ItemNumber (this is only a primary key in the PRODUCT table), with the same results.
If this message (****DESPARATE CRY FOR HELP****) sound confusing, I apologize....but I am confused too X-)
Thanks in advance for any help. I am greatly appreciative of your time and consideration.
Deedlebug

 

[Robertd]

Hi Deedlebug,

mmm school project,...

Ok firstly lets look at a few basics for the orders we need to collect certian information. like for the order we need to know how who and when. so we have a table that contains for example

CUST-ORDER
OrderID Key
CustOrdNr
CustID
OrderDate

then we need to collect the items the customer ordered so we need

LINE-ITEM
OrderID Key
LineItem Key
UnitCost
Quantity

of course there are many ways of doing things but you may need to consider the following. as each order is a point in time transaction we are faced with storing the cost of the item in the OrderItems table as you don't want to update the items prices and change the value of previous orders do you, but this gets a bit more complicated ;-)

then there is the Products table

PRODUCT
Item-Number Key
Description
UnitCost
UnitSell

this is only an example, what you need to do is have the Key for LINE-ITEM to include both the OrderID and Line-Item
this allows you to have multiple same Line-Items so long as they (Line-Item's) are in different Customer orders. as for index's you likely don't need to specify them for the Key fields, you may want to add an index on Line-Item.OrderID and Line-Item.LineItem but allow duplicate's for these.

the relationships will look (something) like

Customer.CustID 1:M CUST-ORDER.CustID
CUST-ORDER.OrderID 1:M LINE-ITEM.OrderID
PRODUCT.Item-Number 1:M LINE-ITEM.Item-Number

The problem you likely have is that you need the LINE-ITEM Key to include Both the OrderID and Item-Number this alows you to include items in the customer's orders (so long as there are no duplicates for that customers Order but you may include the same Line-Item in other customer orders.

HTH

Robert Dwyer
rdwyer@orion-online.com.au

 

[Guest_imported]

Thank you so much for your response. I am going to go through everything and try to varify the relationships and make sure they are accurate and then go from there.
I think you just may have turned me in the right direction!
Thanks!!!!! *:->*
Deedlebug