Need to know how to specify/differentiate multiple relationships with respect to...

[ehazel2001]

Ok, so I have this gigantic database that I have created to deal with catering jobs for a restaurant. It has multiple functions, it tracks menu items, catering equipment, personnel & clientele. All these are represented as such with tables:

Tables
Customer
Equipment
Menu Item
New Job
Staff

The New Job Table (& subsequent form) takes all of the pertinent information from the other four tables to create a new job record, which has the following fields:

ID: Autonumber
Customer: Lookup to Customer name on Customer Table
Event Location: Text
Number of Guests: Number
Menu Item: Lookup to Menu Item Name on Menu Item Table
Staff: Lookup to Staff name on Staff Table
Event Date: Date
Event Time: Text
Subbed Items: Text
Notes: Text
Directions to Event: Text
Equipment Needed: Currently a lookup
Estimated Food Cost: Currency
Estimated Labor Hours: Currency
Estimated Rental Cost: Currency
Estimated Beverages: Currency
Estimated Total: (Not in table, but on form as a running total of the above estimated costs so that this form can be printed in a report as a "service quote invoice"

The problem that I'm having is with the Equipment Needed. In the Equipment Table, there are multiple records of equipment items with their price per unit attached to each record (read: the only columns are Equipment Name & Price). I need to be able to select more than one piece of equipment per new job, I had been doing a multiple value combo box and that works ok, but it does not allow me to specify a quantity of each item, and I would also like for each item selected, the price per be shown on the form (or in a report) so that I can make a box for an equation that multiplies the quantity of each menu item (input by user for each new job) to its corresponding price (as represented in its record in the table Equipment). Once those totals are figured out (with equations in text boxes) I know how to create a running total that be ouput to the Estimated Rental Cost field in the New Job Table....


PLEASE HELP!

 

[pbaldy]

I think you need another table. Your situation is like a typical "sales" application, which usually uses an "invoice master" table for basic info about the sale (Invoice number, date, customer, etc) and an "order detail" table for the items sold (invoice number, product, quantity, price, etc). You would use a form/subform to represent them.

 

[Pat Hartman]

Staff, menu item, and equipment needed each have a 1-m relationship with event and so should be managed in separate tables. You will need three subforms if you want to show all the data on one form or three subreports so that everything can be shown on a single report.

It is not possible to create a single query to join all this data together due to the three independent 1-m relationships. That's why you need subforms and subreports.

Do a little reading on normalization to help you understand the structure you need.

 

[ehazel2001]

So I get that I have a many to many relationship between my New Job & Equipment tables....I created a junction table called Equipment Details...It contains the following fields (ID, Job ID, Equipment ID, Quantity, Price) How do I set up the M2M relationships between Job ID & Job ID, Equipment ID & Equipment ID, Price & Price as related to Equipment ID...and represent this in a subform on the New Job form so that I can enter the number and types of equipment needed in order to create an extended price that can be transferred down the form to the field "Estimated Rental Cost"

 

[Pat Hartman]

Include the Price field in the EquipmentID combo. In the AfterUpdate event of the combo, copy the price field to the new table. This will allow you to have a basic price associated with the equipment but override it if necessary.

Me.UnitPrice = Me.cboEquipmentID.Column(2)

The RowSource of the combo is a zero-based array so .Column(2) is actually the third column of the RowSource. Adjust it if that is not the case with your query.

Extended price should not be stored. It should be calculated in your query.

Select ....., UnitPrice * Quantity As ExtendedPrice, ......

this will allow you to have a bound column for ExtendedPrice and its value will change automatically each time you change the value of UnitPrice or Quantity.