Database Structure - Please Help

[smboyd]

I have one table that I have been using to store all my data in a database.
It is getting too large and I need to split the tables into several different
tables. I have no problem separating the tables, but I have become very
frustrated trying to figure out how the info would link together to be usable
in the forms I have created. I would greatly appreciate anyones
help/suggestion.

A condensed list of the fields I have now are:

Job # (autonumber field)
Job Name
Date Entered
Department
Entered By
Originator
Status
Work Type
Contract Type
Project Manager

Owner Name
Owner Contact
Owner Address
Owner City State Zip
Owner Phone
Owner PO #

Bidding To(Lists 6 customers)
Bidding To, Bidding to1, Bidding to2...etc.
Bidding To Contact
Bidding To Phone
Bidding To Fax

Estimator
Bid Date
Bid Time
Pre-Bid Meeting Date

Contract Date
Contract Amount
Est. Start Date
Est. Completion Date
Billing Contact

Low Bidder
Bid Price
2nd Bidder
Bid Price 2
3rd Bidder
Bid Price 3

etc

 

[JackP]

Job Table
Job # (autonumber field)
Job Name
Date Entered
Department
Entered By
Originator
Status
Work Type
Contract Type
Project Manager
Job Owner (Person ID from Person Table)

Person Table
Person ID (autonumber)
Owner Name
Owner Contact
Owner Address
Owner City State Zip
Owner Phone
Owner PO #

not sure whether your bid ties into a job (do you receive bids for a job?)
or where else.
Assuming each job has multiple bids with it, (and each bid is only
associated with one job, and only with one customer), you would have a Bids
table like this

Bids Table
Bid ID (autonumber)
Job ID
Customer_ID (Customer ID from Customer table)
Bid Estimator (either text or Person ID from Person table if appropriate)
Bid Date
Bid Time
Pre-Bid Meeting Date
Contract Date
Contract Amount
Est. Start Date
Est. Completion Date
Bid Price (you then use a query to compare different bid prices for the same
job)

Customer Table
Customer ID (autonumber)
Customer Name
Customer Contact
Customer Phone
Customer Fax
Customer Billing Contact

 

[smboyd]

Ok - I think I get it

For starters I made a Customer Table using a make table query that took the
Job #, Job Name and all the customer info into a new table. The problem I
have now is the I have a form based on a query in which I used both tables.
I entered a new "test" job number and when I went to the customer form to
enter the customer info it isnt recognizing the new job number. The customer
info is a subform. The main form uses the job number table. am I going
about this totally wrong?

 

[JackP]

Ok - I think I get it

For starters I made a Customer Table using a make table query that took the
Job #, Job Name and all the customer info into a new table. The problem I
have now is the I have a form based on a query in which I used both tables.
I entered a new "test" job number and when I went to the customer form to
enter the customer info it isnt recognizing the new job number. The customer
info is a subform. The main form uses the job number table. am I going
about this totally wrong?

 

[smboyd]

Does it make any difference if I could have several "customers" to each job?

 

[smboyd]

We actually bid each project. One price for each project. But would also
like to record our competitors bid price for each project.