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 2007 Relationship Understanding

Discussion in 'Business Applications' started by larrydavid, Jun 19, 2012.

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

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    Hello, and thank you for taking the time to look at this post.
    The attachment has the data I will refer to. Currently, I have the attached data (with about 50k more lines) in Excel, and I use pivot tables to pull my data, but I know that in the long run Access will be much more efficient. I have been reading up on many-to-many tables and junction tables, but my mind has not been able to wrap itself around how I should setup the relationships for the data I have, and how many different tables I should break this data into, while not using duplicate data. Ultimately, I want to create queries that will return the Sum of "NetSales" for Preferred, HS, BULK, HS and Preferred, Lab and Private Brand, etc. But, as you can see, all of these fall under the same "sold to." Should I setup a different table for each column, and put the "sold to" number in each table as well? Sorry, my mind is a bit slow in putting this together any help is much appreciated. Thanks!

    I am using Access 2007 with Windows 7.
     

    Attached Files:

  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    larrydavid, welcome to the Forum.
    Yours is one of the most common problems with relational databases compared to Excel.
    I use some simple rules based on you should only enter data once and then Refer to it from then on.
    So the The rules are
    The main table should contain the data you want with the once only input in the required type of field, ie Number, Currency, Text etc.
    Any Duplicated data should be in "Number type Long Integer" fields which store only the Key Autonumber field values from the Sub table that contain single entries for the data that is duplicated in the main table. Examples of this are
    Departments
    Cities
    States
    Race
    So in your case it would be
    Fiscal Quarters
    Sold to
    Division
    Bus Unit
    Description & Assortment
    You would then set up those fields on the Form using Combo Boxes to select the data from the sub table and store the Key ID value in the Maintable.
    The data is then pulled together for Reports using a Query that Combines the tables.
    So your Maintable is the Many side of the one to many Relationship and the Subtables will be the 1 side of the relationship.
    If you have any real problems setting it up I can do it for you very quickly, transferring the data from the imported table takes a bit longer.
     
  3. larrydavid

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    Hi OBP and thank you for taking on my issue; I've been trying to figure this out for a few hours now, but the synapses in my brain are misfiring at a higher rate than normal, it seems. Here are my intial quesitons: Does the maintable contain all the columns listed in my attachment, but only one "row" of that data, and each column assigned the correct type of field? Do I have a different sub table for each of the columns that have duplicated data? Once I have the different tables setup correctly, would I need to manually select the Key ID for each line in the maintenance table? Sorry if I am still missing the entire concept and some of the questions don't even make sense! But thank you for your help! I know once I have the "ah ha!" moment it will all seem so easy.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Do you want me to set it up for you, or do you want me to try and show you how to do it on here?
     
  5. larrydavid

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    Whichever is easiest for you, I think I will be able to "put the pieces" together if I see the final product, or if you walk me thorough it. If I had to choose, I would say seeing the final product and then working backward would be best, but if that will take much longer for you then I can do it the other way. Again, thank you for your help, I really appreciate it.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I can do it very quickly, I will use your Excel worksheet as the data for now.
    I will post something for you tomorrow as I am in the UK and it getting late here.
     
  7. larrydavid

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    Excellent thank you; I will mess around with it some more and if I figure it out then I will post what I have on here.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, here it is, I have created a Query to show you how it brings all the data back together.
    The form Should use Combos based on the Sub Tables to select the data for the maintable.
     

    Attached Files:

  9. larrydavid

    larrydavid Thread Starter

    Joined:
    Jun 19, 2012
    Messages:
    13
    OBP, thank you very much for taking the time to do this for me; I finally understand it!
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Good, let's hope you can now produce some nice databases.
     
  11. 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/1057738