1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access Database design/form issue with 3 linked tables

Discussion in 'Business Applications' started by dwilson, Aug 6, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. dwilson

    dwilson Thread Starter

    Joined:
    Aug 6, 2011
    Messages:
    23
    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. I am using Access 2007 but I converted this database to 2003 since I read somewhere on the site it was best to send in that format.
    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 TBNFCLI, TBNFORD, TBNFVOUCH.
    Each client can have multiple orders, and each order can have multiple vouchers. So I assume there is a one to many relationship between TBNFCLI and TBNFORD (TBNFORD being the many side)
    And a one to many relationship between TBNFORD and TBNFVOUCH (TBNFVOUCH being the many side)
    Originally I tried using junction tables but was advised I didn’t need to because the relationship between orders and voucher is really one to many, since there can be only one order date per client.
    Here is the design now:


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


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


    Voucher table TBNFVOUCH
    VOUCHID autonumber as primary key
    Voucher #
    ORDID – to link to order table, number field

    A few other fields
    I have tried adding CLIID to this table as well but it didn't seem to help




    Table Relationships:
    Join lines go from:
    tbnfcli cliid to tbnford cliid
    tbnford to tbnfvouch ordid

    My problem seems to be the relationship between tbnford and tbnfvouch.

    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 none of that worked either.

    When the user is inputting an order, they also need to type in the voucher numbers at the same time, so I want to make it easy to flow from one screen to another.

    If you go to forms, mainmenu, I have a link on this menu for the tabbed form.
    I also have another form I tried that used the client and order tables on the same form.
    It works, but again I can’t get a design that works for the voucher table.
    I have tried designing forms based on queries as well as tables.

    When the user clicks on the voucher tab, it should display the order that they were on for the previous screen.

    For an example, Sally Smith may have an order for 8/1/2011, and order 6 vouchers.
    In the voucher table they will need to type the exact voucher numbers (100,101,102,103,104,105,106) and these have to be separate rows because there is other information recorded for each voucher.



    Any help would be appreciated.
     

    Attached Files:

  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    I can't look at it right now as I am going to bed... but from the sounds of it you approach is wrong, and it might make sense after I say this....

    You need a Order Header table and a Order detail table... the detail table is for the "many" vouchers... the Header table will select the client used for the Order... The Client Tables sole purpose is to define the client. The main relationship is between the order header and the order header's foreign "KEY" in the Details table., the clients will relate through the client header table... k gotta run..
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I have looked at the database and the table design is OK, but your form design is not correct.
    The mainfrom & first tab should be based on the Client table as you have it, the Order tab form should be set to normal Single Form view and the Vouchers should be a Subform on the Order form and set to Continuous Forms Mode.
    I have set it up and entered some vouchers for the first record, which your query "QYLINKORDVOUCH" now shows.

    Your Report should reflect the same kind of layout, i.e. using Sub reports.
     

    Attached Files:

  4. dwilson

    dwilson Thread Starter

    Joined:
    Aug 6, 2011
    Messages:
    23
    Thank you for the response. I realize now my issue was all with the way I designed the forms. This is my first time using this web site and it's fantastic!
     
  5. dwilson

    dwilson Thread Starter

    Joined:
    Aug 6, 2011
    Messages:
    23
    I cannot thank you enough! I literally have spent about 40 hours on this and was so frustrated that I couldn't figure it out. What you did makes perfect sense, my problem was in the form design. I think I was getting confused becasue I was trying to display too much information at one time. I can have another form that displays a lot more detail with another view.
    Anyway, you went above and beyond by actually modifying the database for me. Now I can spend next week getting the form design to look the way I want and converting data, instead of just spinning my wheels!
    Enjoy the rest of your weekend - I will now!
     
  6. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    I don't agree with the design. The client table should be entered on it's own and not related directly to the order. Your clients are your contacts and should be managed in it's own table separate from the order entry.

    eg..

    when you enter an order, you use a lookup to the client...select the client in the order header as a field in the order header table...the client ID is stored in the order header, I then use undound fields to relate other fields (if I want to show them for visual). The current design will force you to re-enter the client for every order... that is a lot of fields isn't it? as opposed to selecting the client from a single field? Also in your client table, those additional fields you have... they should only be there if they are specific to the client....if they are specific to an order then they should be in the order header table.

    I also removed the TABs as I see no point for them, also in the query I add the client table to relate the ID and display the client data in a report.
     

    Attached Files:

  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ziggy, did you look at what I provided, it works. It only needs the Find Combo and Key field in the Mainform Header.
    Your version with separate forms is less efficient than the tabbed form that the OP was originally using.
     
  8. dwilson

    dwilson Thread Starter

    Joined:
    Aug 6, 2011
    Messages:
    23
    Thank you, I just looked at your design.
    I read something in other Internet sites about lookup fields not being a good way to do things, but I can see that it works.
    We don't have to reenter everything. The design that OBP provided just combined the order/voucher table. I originally had them in three tabs, and that was confusing and extra work (never mind that it didn't work!) My main issue seemed to be that I needed a single form for order, then a continuous form for vouchers.
    Anyway, this will be a good reference for me in the future of an alternative way to design a database.
     
  9. dwilson

    dwilson Thread Starter

    Joined:
    Aug 6, 2011
    Messages:
    23
    Yes, I like the design with two tabs. I use the display name at the top header so the user can see what client they are on when they click on the order tab.
    Anyway, it's good for me to see how people design things differently becuase I can always learn a new way of doing things.
    I appreciate you both taking the time to help me!
     
  10. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    yes I was looking your copy, but at first glance I thought it was creating a new Client record... I tried it out again and it does work good... so now I agree :) good job.
     
  11. dwilson

    dwilson Thread Starter

    Joined:
    Aug 6, 2011
    Messages:
    23
    Great, glad you agree!
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ziggy & dwilson, thank you, I liked tabbed forms as they save opening and closing forms, instead it works more like Excel worksheets, which lots of people are used to.
    You can bring a lot of info together on the tabbed form and then use ordinary forms for any background info that rarely changes.
     
  13. dwilson

    dwilson Thread Starter

    Joined:
    Aug 6, 2011
    Messages:
    23
    Yes, I think it's easier for the user also to have the tabbed form.
    She will also need to see the data in a different way when she enters info from the vouchers when they are returned, and this form will be more like a datasheet in order of the latest voucher number. I did a separate form for that.
    Again, thank you both so much for your help!
     
  14. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    oh I only added "my 2 cents" OBP did the "leg work" ;) ... I like Tabs also, but ussually to hold my subs (2 or more).
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1011147

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice