Access 2007 Junction Table issue

[cicoa]

I have been banging my brain out all week on this. I guess I haven?t had any access databases
with this type of relationships before. Most of mine have just been two linked tables.
What I am trying to accomplish is setting up a database for a transportation voucher program,
so we can eliminate the Excel spreadsheet the user is using.
So I need three basic tables: client info, orders, vouchers. They are called NEWFREEMAST,
NEWFREEDET, NEWFREEVOUCH
Client table newfreemast: CLIID autonumber for the primary key.
It has the address, phone, etc for the client
Order details table newfreedet: DETID Autonumber as primary key
Fields: Order date
CLIID ? to link to client table , number field
A few other fields
Voucher table newfreevouch: VOUCHID autonumber as primary key
Voucher #
DETID ? to link to order table, number field
CLIID ? to link to both tables. When I didn?'t include this field I got errors on relationships and
the forms.
A few other fields of data
So each client can have several orders, each order can have several vouchers.
Relationships:
I assume this means a one to many relationship between Client and Orders (Orders being the
many side)
A many to many relationship between ORDERS and VOUCHERS
So I tried to set up a junction table.

I can enter the data on all three forms (I designed it using tabbed forms), but the voucher table
does not link up to the orders table, so I could never run a report to get what vouchers are
connected to what orders.

I have tried multiple setups using using multiple primary indexes for orders and vouchers, but
this is my latest design because I at least don?t get any errors.
Can someone tell me where my design flaw is?
If I don?t have CLIID in junction and vouch tables I had errors.

I have an image but couldn't figure out how to load it in:
The relationship looks like:
NEWFREEMAST NEWFREEDET Junction NEWFREEVOUCH
CLIID - P DETID - P DETID - P VOUCHID - P
Lastname Order Date VOUCHID-P Voucher #
Firstname CLIID CLIID - P DETID
Address, etc Other fields CLIID
Other fields
Join lines go from:
newfreemast cliid to newfreedet cliid
newfreedet detid to juncion detid
newfreevouch vouchid to junction vouchid

My problem seems to be the relationship between newfreedet and newfreevouch

 

[SkipVought]

hi,

you would make out a lot better posting in an MS Access forum like forum700

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]