Structure for ref numbers?

[CW]

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]

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

 

[CW]

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