linking tables

[Carol (Australia)]

Linking Tables

I’m having trouble linking Tables in a Form.

Sales manager has a monthly budget
TBLAreaBudget
contains fields IDAreaBudget Autonumber
IDMonth number
IDProducts number
Style text
PlannedRevenue currency
Comments memo

The Area manager needs to breakup the above Table between a number of Sales
Reps

TBLSalesRepBudgets
Contains fields IDSalesRep autonumber
IDMonth number
IDProducts number
Style text
RevenueToAchieve currency
Comments memo

Do I need to create another table to join these two Tables or is possible to
join as is?
I’ve tried, but can’t find a way to do it. As you can see I’m only new at
this. Help would be appreciated.

 

[Stefan Hoffmann]

hi Carol,

Sales manager has a monthly budget TBLAreaBudget
The Area manager needs to breakup the above Table between a number of Sales
Reps TBLSalesRepBudgets
Do I need to create another table to join these two Tables or is possible to
join as is?
I’ve tried, but can’t find a way to do it. As you can see I’m only new at
this.

This depends on the real structure of manager to reps. If it's strict
hierarchical then you don't need another tablem cause it is a 1:m
relationship (each rep has exactly one manager):

TBLAreaBudget <-1:m-> TBLSalesRepBudgets

With TBLSalesRepBudgets having a field IDAreaBudget as foreign key.

Otherwise, when reps work for more than one manager, e.g. they serve
more areas, then you have a m:n relationship. In this case you need the
following table structure:

TBLAreaBudget <-1:m-> HelpTable <-n:1-> TBLSalesRepBudgets

With HelpTable as consisting of the fields IDAreaBudget and IDSalesRep
(both fields are the primary key of this table).


mfG
--> stefan <--

 

[Carol (Australia)]

thanks Steve, i'll give this a go, but when I design the Form to hold these
Tables it won't allow me to continue as it says it is not linked.

 

[Stefan Hoffmann]

hi Carol,

thanks Steve, i'll give this a go, but when I design the Form to hold these
Tables it won't allow me to continue as it says it is not linked.

Okay, what have you done so far and where does it exactly throws this
message?


mfG
--> stefan <--

 

[Carol (Australia)]

Sorry Stefan (I typed your name incorrectly)

I created a form and made the TBLAreaBudget the main form only using the
Month and Year field to set the individual Sales Reps' budget. I attached a
subform from the TBLSalesRepBudgets joining the forms by the IDAreaBudget
foreign & primary keys. This worked, but when I did a query on a variance
for the Sum of the Revenue from both tables the amounts were incorrect.


The Area Budget for the Month has a number of Products and these Products
have a number of different Styles which all have different budget figure
amounts allocated the the Reps. Would this make the difference as this might
be a many to many relationship?

If I have to make as you suggested before a Help Table to join the two
Tables how do I go about this. I tried, but got completely lost.

 

[Stefan Hoffmann]

hi Carol,

The Area Budget for the Month has a number of Products and these Products
have a number of different Styles which all have different budget figure
amounts allocated the the Reps. Would this make the difference as this might
be a many to many relationship?

I think you have understood the basic principels, but the devil's in the
detail. Can you place a sample .mdb somewhere in the web?


mfG
--> stefan <--

 

[Carol (Australia)]

Stefan
How do I go about placing a sample on the web?
Many thanks
Carol (Australia)

 

[Stefan Hoffmann]

hi Carol,

How do I go about placing a sample on the web?

Do you have free web space somewhere?


mfG
--> stefan <--