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
Each client can have multiple orders, and each order can have multiple vouchers. So I assume there is a one to many relationship between NEWFREEMAST and NEWFREEDET (details being the many side)
And a many to many relationship between NEWFREEDET and NEWFREEVOUCH
So I set up a junction table.
Here is the design:

Client table NEWFREEMAST:
CLIID autonumber for the primary key.
Lastname
Firstname
M
Address1
Address2
City
Etc


Order details table NEWFREEDET:
DETID Autonumber as primary key
Order date
CLIID – to link to client table , number field
Number vouchers requested
Payment Amount, etc


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
Date Voucher Redeemed, etc.


JUNCTION TABLE
DETID – Primary Key
VOUCHID – Primary Key
CLIID – Primary Key



Table Relationships:
Join lines go from:
newfreemast cliid to newfreedet cliid
newfreedet detid to junction detid
newfreevouch vouchid to junction vouchid

My problem seems to be the relationship between newfreedet and newfreevouch

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?

 

[MajP]

If a client can have many orders it is a one to many. If an order can have many vouchers it is another one to many. It would only be a many to many if a an order can have many vouchers and a voucher can be related to many orders. Can a voucher be related to multiple orders? If not it would simply be something like

tblClients
clientID

tblOrders
orderID
clientID_fK (foreign key)

tblVouchers
voucherID
orderID_fk



a query linking clientID to clientID_fk and orderID to

 

[cicoa]

Thank you for your reply. What you suggest makes sense, and I tried setting it up this way again, which was what I did initially. But I still can't get the ORDID number from order table to go into ORDID number in voucher table, even through they are linked in the table relationships.
If I do a query linking the three tables I get no results.

 

[cicoa]

It's working now. For some reason the data from the order and voucher forms were not flowing to the table. I fixed that and now it's working like it should. Thank you for your help, it got me on the right track!