I need some advice on the design for a sales lead database. I have 4
tables (salespersons/products/postcode area covered/sales leads).
Each salesperson can sell up to 3 products. Each postcode (=zip code)
can only have 1 salesperson. When a sales lead comes in, I want to be
able to assign it automatically to a salesperson.
I envisage a form to enter sales leads. When I enter the postcode from
where the lead originates and the product in which the customer is
interested, I want a subform to show with the details of the sales
person allocated.

I think I need at least 1 table resolver containing links between the
postcodes/ the products/the salespersons.  Is that right?  What ID
fields should it contain and  how do I populate it?

On 22 Oct, 09:19, Roger <lesperan... at (no spam) natpro.com> wrote:





On Oct 21, 12:06 pm, Gordon <gors... at (no spam) hotmail.com> wrote:

I need some advice on the design for a sales lead database. I have 4
tables (salespersons/products/postcode area covered/sales leads).
Each salesperson can sell up to 3 products. Each postcode (=zip code)
can only have 1 salesperson. When a sales lead comes in, I want to be
able to assign it automatically to a salesperson.
I envisage a form to enter sales leads. When I enter the postcode from
where the lead originates and the product in which the customer is
interested, I want a subform to show with the details of the sales
person allocated.

I think I need at least 1 table resolver containing links between the
postcodes/ the products/the salespersons.  Is that right?  What ID
fields should it contain and  how do I populate it?

this layout should allow you to do want you describe above
tblSalesrep
  salesrepId (pk)
  salesrep
  address
  postalCode
  .
  .

tblProduct
  productId (pk)
  product
  .
  .

tblSalesLead (pk)
  salesLeadId
  productId
  postalCode
  .
  .

tblSalesrepProduct
  salesrepId (pk) (fk)
  productId  (pk) (fk)

tblSalesrepProduct would be the recordsource for your subform

Roger,

Thanks for the quick response.  I think I get it but to be clear what
would be the link child and master fields for the subform? Presumably,
this would be a double field link (PostCode~ProductID)?
Both of these fields would need to be in both the main form (the sales
lead data entry form) and the subform (which would be based as you say
on the tblSalesrepProduct).  Have I got that right?

Gordon- Hide quoted text -

- Show quoted text -

On Oct 22, 6:13 am, Gordon <gors... at (no spam) hotmail.com> wrote:





On 22 Oct, 09:19, Roger <lesperan... at (no spam) natpro.com> wrote:

On Oct 21, 12:06 pm, Gordon <gors... at (no spam) hotmail.com> wrote:

I need some advice on the design for a sales lead database. I have 4
tables (salespersons/products/postcode area covered/sales leads).
Each salesperson can sell up to 3 products. Each postcode (=zip code)
can only have 1 salesperson. When a sales lead comes in, I want to be
able to assign it automatically to a salesperson.
I envisage a form to enter sales leads. When I enter the postcode from
where the lead originates and the product in which the customer is
interested, I want a subform to show with the details of the sales
person allocated.

I think I need at least 1 table resolver containing links between the
postcodes/ the products/the salespersons.  Is that right?  What ID
fields should it contain and  how do I populate it?

this layout should allow you to do want you describe above
tblSalesrep
  salesrepId (pk)
  salesrep
  address
  postalCode
  .
  .

tblProduct
  productId (pk)
  product
  .
  .

tblSalesLead (pk)
  salesLeadId
  productId
  postalCode
  .
  .

tblSalesrepProduct
  salesrepId (pk) (fk)
  productId  (pk) (fk)

tblSalesrepProduct would be the recordsource for your subform

Roger,

Thanks for the quick response.  I think I get it but to be clear what
would be the link child and master fields for the subform? Presumably,
this would be a double field link (PostCode~ProductID)?
Both of these fields would need to be in both the main form (the sales
lead data entry form) and the subform (which would be based as you say
on the tblSalesrepProduct).  Have I got that right?

Gordon- Hide quoted text -

- Show quoted text -

this is where more information is neeed,

if you're capturing the salesrep assigned to the lead, then we need to
add a field to this table

 tblSalesLead
   salesLeadId (pk)
   productId (fk)
   postalCode
   salesrepId (fk)

if your subform is meant to show the salesrep employee data, the link
would be salesrepId (where the subform shows data from tblSalesrep)

if is it meant to show a list of salesrep in that area, it would be
postalCode- Hide quoted text -

- Show quoted text -

On 22 Oct, 20:44, Roger <lesperan... at (no spam) natpro.com> wrote:





On Oct 22, 6:13 am, Gordon <gors... at (no spam) hotmail.com> wrote:

On 22 Oct, 09:19, Roger <lesperan... at (no spam) natpro.com> wrote:

On Oct 21, 12:06 pm, Gordon <gors... at (no spam) hotmail.com> wrote:

I need some advice on the design for a sales lead database. I have 4
tables (salespersons/products/postcode area covered/sales leads).
Each salesperson can sell up to 3 products. Each postcode (=zip code)
can only have 1 salesperson. When a sales lead comes in, I want to be
able to assign it automatically to a salesperson.
I envisage a form to enter sales leads. When I enter the postcode from
where the lead originates and the product in which the customer is
interested, I want a subform to show with the details of the sales
person allocated.

I think I need at least 1 table resolver containing links between the
postcodes/ the products/the salespersons.  Is that right?  What ID
fields should it contain and  how do I populate it?

this layout should allow you to do want you describe above
tblSalesrep
  salesrepId (pk)
  salesrep
  address
  postalCode
  .
  .

tblProduct
  productId (pk)
  product
  .
  .

tblSalesLead (pk)
  salesLeadId
  productId
  postalCode
  .
  .

tblSalesrepProduct
  salesrepId (pk) (fk)
  productId  (pk) (fk)

tblSalesrepProduct would be the recordsource for your subform

Roger,

Thanks for the quick response.  I think I get it but to be clear what
would be the link child and master fields for the subform? Presumably,
this would be a double field link (PostCode~ProductID)?
Both of these fields would need to be in both the main form (the sales
lead data entry form) and the subform (which would be based as you say
on the tblSalesrepProduct).  Have I got that right?

Gordon- Hide quoted text -

- Show quoted text -

this is where more information is neeed,

if you're capturing the salesrep assigned to the lead, then we need to
add a field to this table

 tblSalesLead
   salesLeadId (pk)
   productId (fk)
   postalCode
   salesrepId (fk)

if your subform is meant to show the salesrep employee data, the link
would be salesrepId (where the subform shows data from tblSalesrep)

if is it meant to show a list of salesrep in that area, it would be
postalCode- Hide quoted text -

- Show quoted text -

The subform is meant to display the sales rep for that area who
provides the product given in the sales lead. (There may not be
one !)  So, I think the link field need to be a combination of Postal
code and productID. I think that means the postalcode field has to be
in the tblSalesrepProduct.  You say the salesrepID needs to be added
to tblSalesLead.  Since that would not be known when the sales lead
form is filled out - only when the productID and postal code fields
have been entered and the subform updated - how would I populate that
salesrepID field?  After the subform is updated?

Gordon

Gordon- Hide quoted text -

- Show quoted text -

On Oct 23, 5:39 am, Gordon <gors... at (no spam) hotmail.com> wrote:





On 22 Oct, 20:44, Roger <lesperan... at (no spam) natpro.com> wrote:

On Oct 22, 6:13 am, Gordon <gors... at (no spam) hotmail.com> wrote:

On 22 Oct, 09:19, Roger <lesperan... at (no spam) natpro.com> wrote:

On Oct 21, 12:06 pm, Gordon <gors... at (no spam) hotmail.com> wrote:

I need some advice on the design for a sales lead database. I have 4
tables (salespersons/products/postcode area covered/sales leads).
Each salesperson can sell up to 3 products. Each postcode (=zip code)
can only have 1 salesperson. When a sales lead comes in, I want to be
able to assign it automatically to a salesperson.
I envisage a form to enter sales leads. When I enter the postcode from
where the lead originates and the product in which the customer is
interested, I want a subform to show with the details of the sales
person allocated.

I think I need at least 1 table resolver containing links between the
postcodes/ the products/the salespersons.  Is that right?  What ID
fields should it contain and  how do I populate it?

this layout should allow you to do want you describe above
tblSalesrep
  salesrepId (pk)
  salesrep
  address
  postalCode
  .
  .

tblProduct
  productId (pk)
  product
  .
  .

tblSalesLead (pk)
  salesLeadId
  productId
  postalCode
  .
  .

tblSalesrepProduct
  salesrepId (pk) (fk)
  productId  (pk) (fk)

tblSalesrepProduct would be the recordsource for your subform

Roger,

Thanks for the quick response.  I think I get it but to be clear what
would be the link child and master fields for the subform? Presumably,
this would be a double field link (PostCode~ProductID)?
Both of these fields would need to be in both the main form (the sales
lead data entry form) and the subform (which would be based as you say
on the tblSalesrepProduct).  Have I got that right?

Gordon- Hide quoted text -

- Show quoted text -

this is where more information is neeed,

if you're capturing the salesrep assigned to the lead, then we need to
add a field to this table

 tblSalesLead
   salesLeadId (pk)
   productId (fk)
   postalCode
   salesrepId (fk)

if your subform is meant to show the salesrep employee data, the link
would be salesrepId (where the subform shows data from tblSalesrep)

if is it meant to show a list of salesrep in that area, it would be
postalCode- Hide quoted text -

- Show quoted text -

The subform is meant to display the sales rep for that area who
provides the product given in the sales lead. (There may not be
one !)  So, I think the link field need to be a combination of Postal
code and productID. I think that means the postalcode field has to be
in the tblSalesrepProduct.  You say the salesrepID needs to be added
to tblSalesLead.  Since that would not be known when the sales lead
form is filled out - only when the productID and postal code fields
have been entered and the subform updated - how would I populate that
salesrepID field?  After the subform is updated?

Gordon

Gordon- Hide quoted text -

- Show quoted text -

so the sales lead has a product and a postal code
and you want to display a list of possible salesreps

so you can create a query based on tblSalesrepProduct and tblSalesrep
to get the product id, postal code, salesrepId, salesrep name

and then use that query for your subform- Hide quoted text -

- Show quoted text -