Wondering if relationships are correct 2

[jstew]

I am needing help with relationships on my tables. Here is the rundown of my dbase.

tblContact PK=ContactID
tblCompany PK=CompanyID
tblQuote PK=QuoteID
tblJob PK=JobID

tblCompany 1 to many tblContact using PK CompanyID
tblCompany 1 to many tblQuote using PK CompanyID
tblQuote 1 to many tblJob using PK QuoteID

Company - info only about the company
Contact - info about contacts and also be able to view company info
Quote - info about quote numbers, product specs. Would like to be able to view company information from here as well as contacts associated with that company.
Job - Quotes that have been made jobs recieve a new number.(example: Quote Number: T1321 Job Number: 1321T)

I have tried to explain this the best I can with me being a newbie to Access. I would appreciate any help.
Thanks
jstew

 

[JerryDennison]

Are you sure it's a one-many relationship between quotes and jobs? I would think that a job might get several quotes, but that a quote would be specific to a single job. This would be a one-many between jobs and quotes.

Anyway, what is the nature of the question? Can you be a little more specific as to what you're looking for?

 

[sko]

You're bidding the jobs, right? I'm wondering about the quote/job relationship also. It seems to me that you might have a one to one relationship. Each quote is unique and if it's accepted you add a job number? When the quote becomes a job is there more information you collect about the job? Something that would call for multiple records relating to the job? Maybe hours worked? If that's the case I could see the need for a Job and JobDetails table, one to many. I guess what I'm really asking is how does the quote information differ from the job information?

 

[jstew]

Thanks Jerry and sko for the quick response and sharing your knowledge.

Here is a little more information about what I am trying to accomplish.

The Quote table will have many quotes and data will change from quote to quote. After the quote is accepted it turns into a job. The main difference between the Job table and the quote table is the number. A quote number is labled like this T1212 and the job number is like this 1212T with the T only changing positions. Something that I would like to track with the Job is the shipping date, but that is about it. I would like to be able to input a job number or quote number and then recieve all pertinent information such as Company names, Sales price, Contact information etc... After the job ships I also need to keep track of service and warranty information which I have not even begun to tackle because I am struggling with this (any suggestions on that would be appreciated).

The easiest analogy I can think of to explain what I am doing is Commercial Car Sales.

For Instance I have a company that needs a new truck so I get all information regarding that company(tblCompany). Within that company I may have one, two, or ten contacts with information regarding each individual(tblContact). Now I start the quoting process and may give them several quotes(tblQuote). Finally the customer decides on the which quote w/the options they desire and buy. Now I have a job sold and need to give it a job number and know when it ships(tblJob). After the job ships the customer needs it serviced and brings it in or needs warranty work done on it and my company needs to keep records on it (tblService).

I hope this explains to all exactly what I'm trying to accomplish. Thanks again for all your help.
jstew
mailto:jstewart@turblex.com

 

[sko]

Unless you can reuse a quote on another job,(and it doesn't sound like you can) you shouldn't have a one to many relationship between quotes and jobs. What you have is a one to one between the accepted quote and the job.

Since the only added information is the ShipDate I'm wondering if you need tblJob. I'm thinking you could add a field to tblQuote -> Accepted y/n and show it as a checkbox on the form along with a ShipDate field. When querying for jobs, you would query for quotes that are accepted. The down side would be you may have a lot more quotes than jobs in the table.

If you decide a separate job table is the better option, are you thinking you need to add a JobID that's similar to the QuoteID just to match quotes with jobs or are you duplicating a process that's already in place? i.e. does it matter to you if the QuoteID and JobID are the same?


As for tracking service calls, you would need a one to many between tblQuotes (if you decide to use the checkbox idea, otherwise tblJobs) and tblService

tblService
PK = RecordNum (This could be a work order number)
FK = JobID (Or QuoteID if you use the checkbox idea)
Date
ServiceDescription
Warranty Y/N
etc.

Suppose you want parts and labor details. Add tblParts and tblLabor, one to many tblService->tblParts and one to many tblService->tblLabor.


My 2 cents

 

[montrose]

jstew, You've gotten some good ideas here! Would be curious to now what structure you end up with for this. Thanks, Montrose Learn what you can and share what you know.