[larrydavid]

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.

[OBP]

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.

[larrydavid]

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]

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]

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]

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]

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]

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.

[larrydavid]

OBP, thank you very much for taking the time to do this for me; I finally understand it!

[OBP]

Good, let's hope you can now produce some nice databases.