Help Me? Please!!!

[ChrisDean]

I am trying to build a data base that has customers and equipment. The equipment has items that I would like to track. Example: Customer #1 has 2 different air conditioners, each has its own model # and serial #, each has a belt size, and filters that I need to keep track of. I would like to be able to look up each customer and see the information, and also I would like to be able to query all the filters for all customers that need to be ordered for the next month. or quarter. I have been able to build a table for customers. I can't understand how to put in multiple sizes of filters for the same equipment and customer. How do I put a quanity of filters for each unit. Example: unit #1 has 2 20x20x2 pleated filters and 4 16x25x2 pleated filters. Unit #2 has 1 20x20x2 polyester filter. Both are for the same customer. Now if I had 100 customers that had all different units and some similr size filters. Some of these filters would need to be ordered this month and every month but some would only need to be ordered every three months. Can this be done. I could not even figure out how to get a quantity of filters.

Chris

 

[ssmithri]

Chris,

There are several ways that you could go about this, but here is what I would do:

1) Modify my customer table so that I have only one record for each customer, and each customer has a unique ID.
2) Create a seperate table containing all of the equipment (EquipID, EquipName, EquipDesc, Size, etc...) and then populate this table with all of your equipment and the specific details.
3) Create an order table that contains an OrderID, EquipID, CustomerID, OrderDate, etc... (anything specific to the order) and populate this table with all of your orders (include the EquipID that matches the equipment table and the unique CustID from the customer table).

You can then build a form tying all of these together and making it easier to enter your new orders (By the way... there are wizards that will walk you through building all of these tables and forms).

When this is all complete, create a query that links CustID in customer table to customer id in orders, and link equipment ID in orders to the equipment ID in the equipment table. This will show you everything that your customers have as far as equipment and orders.

Hope this helps.

Stu


Then, you can c