Structure for ref numbers?

[Guest]

I need to set up a two-level reference number structure as follows:
When we take an inquiry it is given one single number (say 123).
That inquiry may lead to several costings and quotes for differing services,
which we would number 123-1, 123-2, 123-3 or similar.
The customer may accept one or more of those quotes which would then become
a booked job, which we would handle under its specific ref such as 123-2. The
quotes 123-1 and 123-3 would be flagged Lost in this example.
The issue is that if a customer books one or more quotes, we need to have
some form of linking between the jobs that these trigger, as much of the info
(customer details, possibly even the invoice) will be common to the various
jobs.
Can anyone suggest a structure (generated through auto-numbering firstly
when an inquiry is added and secondly when a quote is accepted/a job is
booked) that will accommodate this?
Many thanks!
CW

 

[Joseph Meehan]

I need to set up a two-level reference number structure as follows:
When we take an inquiry it is given one single number (say 123).
That inquiry may lead to several costings and quotes for differing
services, which we would number 123-1, 123-2, 123-3 or similar.
The customer may accept one or more of those quotes which would then
become a booked job, which we would handle under its specific ref
such as 123-2. The quotes 123-1 and 123-3 would be flagged Lost in
this example.
The issue is that if a customer books one or more quotes, we need to
have some form of linking between the jobs that these trigger, as
much of the info (customer details, possibly even the invoice) will
be common to the various jobs.
Can anyone suggest a structure (generated through auto-numbering
firstly when an inquiry is added and secondly when a quote is
accepted/a job is booked) that will accommodate this?
Many thanks!
CW

Well there could be several approaches.

I would start by having two fields One quote number and the other the
quote sub-number. You might want to consider if you want to use number
fields or text fields. Both have advantages here. I suggest that you may
want to add some sort of verification as well to prevent invalid numbers.

If you have large amounts of data, you might consider normalizing the
data. That is making two tables one for the quote and one for the sub quote
and their respective data. Doing that may answer many of your questions
about how to sort display and use the data.

With out having a better idea of the data, the quality of the data, the
skill levels of the people entering the data and the uses the data will be
put to, that is about the best I can come up with.

 

[JK]

If I got you right, my suggestion

tblCustomers
----------------
Customer_ID Key
Customer Name text
etc

TblJobs
-----
Job_ID Key
Customer_ID Foreign Key
JobNumber Long Indexed, No duplicates (this is your 123 part)
IsCurrent Yes/No ????
etc

One-To-many relation between tblCustomers and tblJobs on Customer_ID

tblQuotes
-------
Quote_ID Key
Job_ID Foreign Key
QuoteNumber Number, Integer (this is 1,2,3 part)
JobAmount number, Double
IsAccepted Yes/No
etc

One-to-many relations between tblJobs and tblQuotes on Job_ID

Create a form say frmJobs, and its SubForm sfmQuotes

In fmJobs enter the job number 123 (or automate it)
In The SubForm sfmQuotes enter the QuoteNumber 1,2,3 (or Automate it)

There is no need for a field to store 123-1 123-2 etc, you can always
display it in a calculated field in a form or in a query:

=[JobNumber] & "-" & [QuoteNumber]

Hope this is of help

Regards/JK

 

[Guest]

Thanks, JK - I appreciate your suggestion and the detail you have gone into -
I'll give it a try!
CW

If I got you right, my suggestion

tblCustomers
----------------
Customer_ID Key
Customer Name text
etc

TblJobs
-----
Job_ID Key
Customer_ID Foreign Key
JobNumber Long Indexed, No duplicates (this is your 123 part)
IsCurrent Yes/No ????
etc

One-To-many relation between tblCustomers and tblJobs on Customer_ID

tblQuotes
-------
Quote_ID Key
Job_ID Foreign Key
QuoteNumber Number, Integer (this is 1,2,3 part)
JobAmount number, Double
IsAccepted Yes/No
etc

One-to-many relations between tblJobs and tblQuotes on Job_ID

Create a form say frmJobs, and its SubForm sfmQuotes

In fmJobs enter the job number 123 (or automate it)
In The SubForm sfmQuotes enter the QuoteNumber 1,2,3 (or Automate it)

There is no need for a field to store 123-1 123-2 etc, you can always
display it in a calculated field in a form or in a query:

=[JobNumber] & "-" & [QuoteNumber]

Hope this is of help

Regards/JK

I need to set up a two-level reference number structure as follows:
When we take an inquiry it is given one single number (say 123).
That inquiry may lead to several costings and quotes for differing
services,
which we would number 123-1, 123-2, 123-3 or similar.
The customer may accept one or more of those quotes which would then
become
a booked job, which we would handle under its specific ref such as 123-2.
The
quotes 123-1 and 123-3 would be flagged Lost in this example.
The issue is that if a customer books one or more quotes, we need to have
some form of linking between the jobs that these trigger, as much of the
info
(customer details, possibly even the invoice) will be common to the
various
jobs.
Can anyone suggest a structure (generated through auto-numbering firstly
when an inquiry is added and secondly when a quote is accepted/a job is
booked) that will accommodate this?
Many thanks!
CW

 

[JK]

My pleasure

Thanks, JK - I appreciate your suggestion and the detail you have gone
into -
I'll give it a try!
CW

If I got you right, my suggestion

tblCustomers
----------------
Customer_ID Key
Customer Name text
etc

TblJobs
-----
Job_ID Key
Customer_ID Foreign Key
JobNumber Long Indexed, No duplicates (this is your 123 part)
IsCurrent Yes/No ????
etc

One-To-many relation between tblCustomers and tblJobs on Customer_ID

tblQuotes
-------
Quote_ID Key
Job_ID Foreign Key
QuoteNumber Number, Integer (this is 1,2,3 part)
JobAmount number, Double
IsAccepted Yes/No
etc

One-to-many relations between tblJobs and tblQuotes on Job_ID

Create a form say frmJobs, and its SubForm sfmQuotes

In fmJobs enter the job number 123 (or automate it)
In The SubForm sfmQuotes enter the QuoteNumber 1,2,3 (or Automate it)

There is no need for a field to store 123-1 123-2 etc, you can always
display it in a calculated field in a form or in a query:

=[JobNumber] & "-" & [QuoteNumber]

Hope this is of help

Regards/JK

I need to set up a two-level reference number structure as follows:
When we take an inquiry it is given one single number (say 123).
That inquiry may lead to several costings and quotes for differing
services,
which we would number 123-1, 123-2, 123-3 or similar.
The customer may accept one or more of those quotes which would then
become
a booked job, which we would handle under its specific ref such as
123-2.
The
quotes 123-1 and 123-3 would be flagged Lost in this example.
The issue is that if a customer books one or more quotes, we need to
have
some form of linking between the jobs that these trigger, as much of
the
info
(customer details, possibly even the invoice) will be common to the
various
jobs.
Can anyone suggest a structure (generated through auto-numbering
firstly
when an inquiry is added and secondly when a quote is accepted/a job is
booked) that will accommodate this?
Many thanks!
CW