Access 2007 junction table issue

[dwilson]

I have been banging my brain out all week on this. I guess I haven’t had any access databaseswith this type of relationships before. Most of mine have just been two linked tables.

What I am trying to accomplish is setting up a databasefor 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?

[RuralGuy]

I believe you will find there is a 1:m relationship for the NEWFREEMAST and NEWFREEDET tables and also a 1:m relationship for the NEWFREEDET and NEWFREEVOUCH tables.

[dwilson]

Ok, I went back to the desgin without the junction table but I am still having the same issue. The ORDID from orders table will not go into the ORDID of vouchers table, so I cannot link these two. In the table relationships it shows that these two fields are linked.
I don't understand why the CLIID from the clients table will go into the CLIID in the order table, but the Order to Voucher ORDID will not.
Do I need to do some kind of compound index?

[RuralGuy]

How are you trying to place the DETID in the Voucher table?

[dwilson]

Thank you for trying to help me. It's working now! For some reason my forms were not flowing into the table, I deleted then inserting the subforms on the main form again, and now everything is working. The DETID field is going to the voucher table now.
It really helped to know I should get back to the simpler design.

[RuralGuy]

That's great! Are you ready to use the Thread Tools at the top on the thread and mark this thread as Solved?

[dwilson]

I thought it was solved but it is not. I believe the table design is correct now:
client table: CLIID Primary

Order Table ORDID Primary
CLIID foreign key

Voucher Table VOUCHID Primary
ORDID foreign key

I was trying to do the form as a tabbed form, with three different tabs
I can get the clients tab and the order tab to input correctly.
But the voucher tab I cannot get to work the way it needs to
I have tried linked forms, subform, etc.
I have tried basing it on a query which combines the two tables.

Basically, on the order form tab, if a date is entered, then on the voucher form a corresponding ordid and date should appear, and the user should be able to type in multiple voucher numbers. This does not work, all it shows me is the first date that was entered for orders.

I think now it's down to how to a problem with the form design.

[RuralGuy]

Normally you would deal with a 1:m relationship with a Form/SubForm arrangement.

[dwilson]

The form/subform works fine for clients and orders, I just can't get it right for orders and vouchers

[RuralGuy]

What did you set up as the LinkChild/MasterFields?

[dwilson]

Thanks for the suggestion. It is working now. It was all about how I set up the order/voucher form.

[RuralGuy]

Excellent! Glad we could help.