Junction table question

K

kraasty

I have two tables with a junction table that connects the two of them.

I tried to make a form with the wizzard that shows every record from
tableA related via the junctionTable to a record on tableB.
How can I see each related record only once , since there may
be a lot of entries in the junction table , relating the same records?

Thank you
Andreas
 
J

Jeff Boyce

Andreas

Not sure I understand what you are trying to do. By definition, the
junction table should have one row for each valid combination of a record in
your TableA and a record in your TableB.

When you ask to see "each related record only once" -- which of the three
tables are you talking about? And related to what, and how?
 
K

kraasty

Jeff, thank you for your reply.

I have one table named DRIVERS with the fields: DriverID(primary key),
DriverName , DriverLicenceNo ...
The second table called CARS with the fields: CarID (primary key), CarBrand
, CarModel ....
The junction table has three fields:
1.DriverID (one-to-many relationship with the DriverID field in the
DRIVERS table),
2.CarID (one-to-many relationship with the CarID field in the CARS
table),
3.Date (any valid date)
The three fields TOGETHER are the primary key of the junction table, so
there are
many records with the same CarID/DriverID combination on several days,
but not on the same day.

I have made a form whith the fields from the DRIVERS table, with a subform
in
datasheet view with the records from the CARS table.
When I browse the DRIVERS form with the navigation arrays,
the CARS subform displays the data of the same CarID multiple times
(in the same DRIVERS record),
because there are many records with this combination in the junction table
(on several dates)

What I want to see is a list with the drivers have ever driven the specific
car,
-or vice verca-
which cars have ever been driven by a certain driver.

P.S.
Thank you for your understanding, hope it wasn't too boring.
I tried to make it as clear as possible, but my English is very poor.

Andreas.
 
J

Jeff Boyce

On the contrary, your English is/was fine.

Since your junction table has the association between car and driver, I'd
suggest using a query for your subform. The query connects the junction
table with the car table (on CarID), includes the driverID, and information
from the car table that you'll want displayed on the subform.

On your main form, use a combobox to pick a driver (and use the DriverID as
the first column, width = 0). On the subform Parent/Child properties, use
the DriverID for both. On the subform, show only the car information.

When you pick another driver in the main form, the subform "looks" for the
query records that have that driverID.

Hope that helps...

Jeff Boyce
<Access MVP>
 
K

kraasty

Jeff, thanks for your advice
Sorry for the delayed reply , but I was on a trip last
week so I haven´t had the time to try what you suggested.

Andreas
 
Top