Help with Job Equipment Database

[Eleventy]

So I am attempting to create a database containing bid/equipment/cost information for an agricultural irrigation company.

I have 11 different tables, that are kind of broken into two separate "Areas" of technical information/equipment.

tblCustomers
*Farm_Corporation|First_Name|Last_Name....
tblEmployee
*EmployeeID|First_Name|Last_Name....
tblFieldSpecs
*Legals|FarmCorporation|GPS_Coordinates...
tblWell
*WellID|Water_Report|Chlorination...
tblDealer
*Dealer_Name|City|States|Address...
-----------------------------------------
tblEquipment
*Equipment_Type|Equipment_Description
tblSupplier
*Supplier_Name|Contact_Name|Phone|E-mail|Fax|Address...
tblValve
*Valve_Model|Equipment_Type|Supplier_Name|Size|GPM...
tblTape
*Tape_Model|Equipment_Type|Supplier_Name||Inner_Diameter...
tblFilter
*Filter_Model|Equipment_Type|Supplier_Name|Size...
tblController
*Controller_Model|Equipment_Type|Supplier_Name|Description
------------------------------------
tblBid
*BidID|EmployeeID|Legals|Dealer_Name|Season|Year|...|Valve_Model_1|Number_of_Model_1|Valve_Model_2|Number_of_Model_2|Valve_Model_3|Number_of_Model_3...

I guess I have just arbitrarily added the maximum amount of different types of valves/filter etc.. that would be used for each individual bid but I have no idea how to do it.

What I would like to do is to create a form that allows the user to enter the different types of equipment/amounts/type etc... for each bid but I cannot figure out how to do it in a table.

Do I need to do a query for each job instead of using a table?

 

[dhookom]

Your structure isn't normalized. I wouldn't do any more work on it until the tblBid was normalized. I would also at least consider making one table for valves, tape, filter, and controllers.

There are lots of great resources on the web regarding normalization.

Duane
Hook'D on Access
MS Access MVP

 

[Eleventy]

I understand what Normalization is and I know my table is not normalized but I was hoping for some advice on how to achieve this normalization.

 

[dhookom]

I would remove all the equipment related fields from the tblBid. Create a new table
[tt][blue]
tblBidItems
===================
BidItemID autonumber primary key
BidID
EquipID primary key from your combined equipment table
Quantity[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[Eleventy]

The end game is to have a system set up where you would have a data entry system that you can select the number of different types of valves (as an example), then the number of each type and the types of valves. How would I go about normalizing/implementing that?

 

[dhookom]

If I were you, I would open the Northwind sample mdb and see how products are added to an order. From what I know about your system (which is very little) this should give you some idea.

Duane
Hook'D on Access
MS Access MVP

 

[BNPMike]

I understand what Normalization is"

I don't think you do.

It's not that difficult but you really are at a disadvantage doing relational databases if you haven't spend some time reading about it.