Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Access 2007 Relationship Understanding


(!)

larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
19-Jun-2012, 05:11 PM #1
Solved: Access 2007 Relationship Understanding
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
File Type: xlsx Example.xlsx (8.4 KB, 47 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
20-Jun-2012, 06:41 AM #2
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.
__________________
OBP
I do not give up easily
larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
20-Jun-2012, 11:35 AM #3
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
20-Jun-2012, 01:06 PM #4
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?
larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
20-Jun-2012, 01:20 PM #5
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
20-Jun-2012, 01:40 PM #6
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.
larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
20-Jun-2012, 01:46 PM #7
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
20-Jun-2012, 02:29 PM #8
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
File Type: zip Example Relationships.zip (44.7 KB, 8 views)
larrydavid's Avatar
larrydavid larrydavid is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Jun 2012
22-Jun-2012, 01:47 PM #9
OBP, thank you very much for taking the time to do this for me; I finally understand it!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
22-Jun-2012, 01:54 PM #10
Good, let's hope you can now produce some nice databases.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
access, relationships

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑