INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ACCESS 2007 Junction Table issue

ACCESS 2007 Junction Table issue

(OP)
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?






 

RE: ACCESS 2007 Junction Table issue

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  

RE: ACCESS 2007 Junction Table issue

(OP)
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.

RE: ACCESS 2007 Junction Table issue

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close