Pull information from one table to another (1 Viewer)

[ValleyGirl]

I am brand new to building a database. I am not even sure that a database is what I am looking for.

What I want is a database to store Quote, Job and Invoicing information. We receive quotes first and then they can, but don't always, turn into jobs. We can also receive a job without quoting it.

We currently have two spreadsheets. One is for Quotes and the other is for Jobs.

QUOTES INFORMATION
Quote #
Date
Customer Name
Part #
Part Name
Quote Due Date
Qty
Lead Time
Price
Unit

JOBS INFORMATION
Job #
Qty
Quote #
Customer Name
PO#
Part #
Part Name
Est Hours
Start Date
Due Date
Price

As you can see a lot of the information in the Quote spreadsheet is also used in the Job spreadsheet. (Bold represents duplicated items) We currently type the information into each spreadsheet.

Then there are different forms that are filled out for quotes and jobs that contain the information in the spreadsheets.

Is there a way that I can have the QUOTE Table automatically populate the JOB Table information?

I hope this makes sense.

 

[recyan]

Is there a way that I can have the QUOTE Table automatically populate the JOB Table information?

There is no need to have duplicate fields in both the tables or to automatically populate the JOB table information.

Thanks

 

[ValleyGirl]

I thought that might be the answer. So the quote/job table should be combined?

 

[recyan]

I thought that might be the answer. So the quote/job table should be combined?

Usually, I would think Yes,
but in this case, I would prefer them as separate tables with a 1-1 relationship, as they appear to me, 2 separate entities.

Till someone comes along, just a few off the cuff thoughts (assuming - one quote is for one and only one PartID & one job is for single quote only):

tblCustomers
CustomerID - PK
CustomerName
....

tblParts
PartID - PK
PartName
UnitID - FK
....

tblUnits
UnitID - PK
Unit
...

tblPartPrices - need for this table, based on assumption that standard prices for parts are used & they are subject to change over time.
PriceID - PK
PartID - FK
Price
PriceChangeDate

tblQuotes
QuoteID - PK
QuoteDate
Quote Due Date
CustomerID - FK
PriceID - FK
Qty
Lead Time


tblJobs
JobID - PK
QuoteID - FK
PO - Do not know what this stands for
Qty
Est Hours
Start Date
Due Date
Price - Not needed, assuming the Quote Price / Part Price, is going to be the Job Price. Needs to be thought out

Edit : Take a look at the suggestions in below thread.
http://www.access-programmers.co.uk/forums/showthread.php?t=230979

Thanks

 

[Alansidman]

This diagram may be helpful

http://www.databaseanswers.org/data_models/orders_and_parts/index.htm

Alan

 

[ValleyGirl]

Usually, I would think Yes,
but in this case, I would prefer them as separate tables with a 1-1 relationship, as they appear to me, 2 separate entities. Till someone comes along, just a few off the cuff thoughts (assuming - one quote is for one and only one PartID & one job is for single quote only):

Thanks

Thanks for your answer. I think you are getting the idea. We are an aerospace suppler. We do a lot of prototype work. Most jobs consist of one Part and we never see that part again. There are times when we do get an order for the same part number. This happens maybe 4 times a year. There are instances when a quote comes in for 2 different part #'s. We could use a letter behind the quote number in these instances. I can't think of anytime we have combined quotes for a job. (I'm not gonna say never though

I'll take a look at the link you supplied. Thanks for taking your time to respond to me

 

[ValleyGirl]

This diagram may be helpful


Alan

Thanks Alan - This will be very helpful. We are in the aerospace industry