Creating a Join Table -Square One

[CanFlightSim]

I am not very strong in databases, so you must forgive any obvious
stupidity.
After having a good look at our Sales Managemend database, we decided
there is something basially wrong.

Our Agents sell financials to only a few customers. The database was
set up as...
Table Table (Join)
Table
Agent (AgentID ) (AgentID)( >Customer(CustomerID)

But we have Agents who have not sold yet and will always have that
scenario.

So we are creating

Table Table (Join)
Table Table
Agent (AgentID ) (AgentID)(CustomerID) >Customer(CustomerID)>Product
Type)(UnitsSold)(CustomerID)

I am trying to figure out my joins as to make sure we have our form
queries correct.

If I run a Make Table query with the old relationships, I can get the
(AgentID)(CustomerID)
Table.
What I will be missing is 400+ records. The Agents who have no sales
or orphan Austomers who have no agents.

Am I going about this the right way?
Sure would like a little guidance

[pietlinden]

CanFlightSim wrote:

I am not very strong in databases, so you must forgive any obvious
stupidity.
After having a good look at our Sales Managemend database, we decided
there is something basially wrong.

Our Agents sell financials to only a few customers. The database was
set up as...
Table Table (Join)
Table
Agent (AgentID ) (AgentID)( >Customer(CustomerID)

But we have Agents who have not sold yet and will always have that
scenario.

So we are creating

Table Table (Join)
Table Table
Agent (AgentID ) (AgentID)(CustomerID) >Customer(CustomerID)>Product
Type)(UnitsSold)(CustomerID)

I am trying to figure out my joins as to make sure we have our form
queries correct.

If I run a Make Table query with the old relationships, I can get the
(AgentID)(CustomerID)
Table.
What I will be missing is 400+ records. The Agents who have no sales
or orphan Austomers who have no agents.

Am I going about this the right way?
Sure would like a little guidance

Sounds like the database design is probably OK. You need an _outer_
join instead of an inner join in your query. Inner joins "lose"
records when there are no matches between the two tables. (only the
records with matches on both sides show). If you want to show all the
records from one table, click on the link in the query and change it to
"Show all..." and set it to your Agents.

[Bas Cost Budde]

My intuition is that "products sold" is a property of the relation
between an agent and a customer. So, I think you must have some sort of
Y-shaped relations window:

both Agent and Customer are parents of the intermediate table, which I'd
name AgentCustomer; and below this table there will be the intermediate
table to Product, I'd name this either AgentCustomerProduct or Sale,
depending on my manager ;)

But, if you want to be able to trace some history, and there can be more
than one contact between an Agent and (the same) Customer, you need
another step.

Hope I'm not shooting over your head here.
--
Bas Cost Budde
Holland

[Wild Bill]

Bas Cost Budde wrote:

My intuition is that "products sold" is a property of the relation
between an agent and a customer. So, I think you must have some sort of
Y-shaped relations window: ....

Well a lot of water has gone under the bridge ...

I restructured as some of your suggestion.

Agents----AgentInvestorJoin-----Investor

For the main tables. Works real clean.

Agents--- ContactDetails

Is a one to many contact details with date and what was done.

Investors ---- Purchases

One to many Purchases

I can query through the join table to Have all the Purchases that are
owned by a broker
In addition, I have put in a delete flag and front ended a module to
handle "deletions"

Things seem to make more sense because there are times that an Agent
does no have investors. And an investor looses an agent.

Thanks for your help. Met a lot of folks from the Netherlands in my
city this summer. Over 100 or so.

Wild Bill