Will I need a junction table in this scenerio?

[markmarko]

I have a table with all our Sales contractors. It has names and their IdCode.

Also, there's a Sales Order table where all their sales will be entered. On
each Sales record, there is a place to identify the sales person, which is
related to the Salespersons PK.

The trick is that sometimes two sales reps share a sale. So far, I've put
two fields for contractors on the sales order table, and linked them both to
the Contractors table. In the relationship map, it makes a table called
Contractors_1.

My questions is... Should I instead use a junction table since it's a
many-to-many relationship? What sort of problems would I encounter if I kept
it as is?

 

[Jeanette Cunningham]

Hi again,
The way to look at it is to ask yourself :
What would happen if I had all the order info for a Sale, but I had 2
ContractorID's for this sale, what sort of questions would my database be
trying to answer.

Usually you would set up a junction table with 2 fields SalesOrderID and
ContractorID (and could have other info fields as well), as this gives you
the most flexibility in the future to answer questions about your data.

Jeanette Cunningham

 

[Jamie Collins]

The way to look at it is to ask yourself :
What would happen if I had all the order info for a Sale, but I had 2
ContractorID's for this sale, what sort of questions would my database be
trying to answer.

Usually you would set up a junction table with 2 fields SalesOrderID and
ContractorID (and could have other info fields as well), as this gives you
the most flexibility in the future to answer questions about your data.

Is flexibility the OP's aim here? It sounds to me like the OP has a
business rule, "each sale has exactly one or two associated sales
staff" and you have interpreted that as "each sale has an unlimited
number (or zero) associated sales staff". If your argument is that
moving to a 'junction table design' would result in the loss of
database constraints to enforce the stated business rules then I'd
have to say that this is not necessarily so.

Jamie.

--

 

[Klatuu]

Imposing a rule like "each sale will have one or two sales reps" is short
sighted. I would also violate your theorist rule that nulls are not
acceptable. One of the two sales rep fields would be left null. You could
have a default value other than null, but what would be the point?

I would suggest a junction table. I will bet that sooner or later, there
will be a circumstance where the users will want to add a 3rd rep to a sale.

 

[markmarko]

Ok, so I'm implementing a junction table for this scenario. I think it would
work now without doing so, but there may come some reason in the future where
we'd like the versatility.

Now I'm experiencing a new problem. All my orders with 2 sales reps appear
twice in the form's query results. To explain, as I scroll through the orders
I see order 123, then click next expecting to see order 124, but I see 123
again. It seems clear it's doing it since there are 2 reps on this order, but
I don't know how to keep that from happening.

Incidentally, I'm using a subform for entering the 2 reps. Is that kosher?

 

[Jeanette Cunningham]

Markmarko,
looks the query for the form needs some changes
Post the SQL you are using.

Jeanette Cunningham

 

[Pat Hartman]

You will need to use a subreport for the sales people just as you used a
subform for the data entry. And yes, using a subform is correct.

 

[markmarko]

Here's a significantly pared-down version of the query. It still does the
doubling anytime there's two sales people.

SELECT [Record-Orders-Sales].SalesOrderID, [Core-Contractors].PromoCode
FROM [Record-Orders-Sales] INNER JOIN ([Core-Contractors] INNER JOIN
[Junction-SalesOrder_Contractors] ON [Core-Contractors].ID =
[Junction-SalesOrder_Contractors].AssociatedContractorID) ON
[Record-Orders-Sales].SalesOrderID =
[Junction-SalesOrder_Contractors].AssociatedSalesOrderID
ORDER BY [Record-Orders-Sales].SalesOrderID;

 

[John W. Vinson]

Here's a significantly pared-down version of the query. It still does the
doubling anytime there's two sales people.

SELECT [Record-Orders-Sales].SalesOrderID, [Core-Contractors].PromoCode
FROM [Record-Orders-Sales] INNER JOIN ([Core-Contractors] INNER JOIN
[Junction-SalesOrder_Contractors] ON [Core-Contractors].ID =
[Junction-SalesOrder_Contractors].AssociatedContractorID) ON
[Record-Orders-Sales].SalesOrderID =
[Junction-SalesOrder_Contractors].AssociatedSalesOrderID
ORDER BY [Record-Orders-Sales].SalesOrderID;

Yes. Exactly what I would expect, and *that is the correct result*. You have
two composite records, one for each contractor. That's how Access (or any
other relational database) is designed to work.

What would you WANT to see?

If you want to see the sales order once, with two lines (or one, or three, or
none, depending on how many contractors are entered), use a Subform. On a
Report, use this query and use the Report's Sorting and Grouping property to
group by SalesOrderID; show the sales order specific fields in the group
Header and/or Footer, and the contractor information in the detail section.

John W. Vinson [MVP]

 

[markmarko]

Yes, I do need both sales reps on one order, shown at the same time. I am
using a subform for the sales reps. The main form is Single Form type . It
nevertheless shows two records for each order with 2 reps as I navigate thru
the records.

 

[Pat Hartman]

Change the query for the main form to remove the join to the reps table.
That is what is causing your duplication.

 

[John W. Vinson]

Yes, I do need both sales reps on one order, shown at the same time. I am
using a subform for the sales reps. The main form is Single Form type . It
nevertheless shows two records for each order with 2 reps as I navigate thru
the records.

The recordsource of the mainform should be JUST the sales table - *not* the
query joining the two tables.

Display the sales information (only!) on the mainform.
Display the reps information on the subform.
Do not use a query joining the two tables.

John W. Vinson [MVP]

 

[markmarko]

Thanks Jon & Pat.

Yes, that makes sense... After I converted from having the contractor info
in the sales form to a subform, I apparently left the old query selections
for contractors.

Thank you very much.