It models the many-to-many relationship type between an Orders table and an Items table or similar, by resolving it into one-to-many relationship types. _ Ken Sheridan, Cannock, England "Don't write it down until you understand it!" - Richard FeynmanĪn OrderItems table or similar is necessary where an order can include more than one item. This little demo file uses a simple medical prescriptions database to illustrate how the relationship types are built up, and how they are then reflected in the user interface. If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar. Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. You'll find an example of how relationship types are built up across a database in this way in Relationships.zip in my public databases folder at: When an order or invoice is raised, the value of the UnitPrice column for the product/size is looked up in ProductSizes and assigned to the UnitPrice column in OrderDetails or InvoiceDetails.Īs regards the relationship types in your database you will need to model constraints such as which colours a product is available in, and which sizes a product is available in, as tables, and then which products of those colours and sizes in combination areĪvailable, as a further table modelling the relationship type between the first two relationship types. Consequently its important that an OrderDetails and/or InvoiceDetails table also Remember that unit prices are not time independently determined by product/size they will change over time. The primary key of the table is a composite of the two foreign keys. Prices are an attribute of the relationship type between products and sizes, so would be modelled by a table like this: