Normalisation/ Many-to-Many relationship - I think I'm doing it wrong! Help!

[CarysW]

Could somebody please have a look at my DB (attached).

Following on from this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=177617

I've 'tried' but I think I've failed miserable and I just can't get it right in my head! I can't yet think like a programmer - is there a pill for this??

If I'm not giving enough info please give me a nudge for more.

Thanks

 

[boblarson]

Well, first of all you should not have fields for each brand. That is the big one right now. What is the data that tblMain_Brands is tracking?

 

[CarysW]

Well, first of all you should not have fields for each brand. That is the big one right now. What is the data that tblMain_Brands is tracking?

There you go, I told you I'm doing it wrong!!

tblMain_Brands is tracking which customers buy which brand. Customers are the Key and Brands are the brands...the customer data is kept in tblMain.

 

[boblarson]

Okay, a couple of other questions -

What is this "Key" and where does it come from (ex. 1003 1 )?

And what is the "SEARCH CODE" and where does it come from?

 

[CarysW]

The key is there as a Primary Key as it is the only thing will be different on each entry(The keys relate to the entries in tblMain) - the number on it's own denotes whether it is a branch or HQ, i.e 103 0 is the HQ 103 1 is a branch belonging to that HQ, 0 is always the HQ but that's irrelevant in terms of what I'm trying to do here.

 

[Scooterbug]

Carys,
I thought your name looked familiar...about a month ago you asked for help with the same database. I took a look at it and suggested some things to help normalize it.

http://www.access-programmers.co.uk/forums/showthread.php?t=176007

Is there any reason why the suggestions I made didn't work?

 

[boblarson]

The key is there as a Primary Key as it is the only thing will be different on each entry(The keys relate to the entries in tblMain) - the number on it's own denotes whether it is a branch or HQ, i.e 103 0 is the HQ 103 1 is a branch belonging to that HQ, 0 is always the HQ but that's irrelevant in terms of what I'm trying to do here.

Let's get away from keys with meaning. You can still have something there if you want to identify something but for the SYSTEM, let's use a simple autonumber. Keys with meaning tend to have problems as "exceptions" arise, changes happen, etc. It is a bad way to have a key which is only supposed to be used by the system anyway.

I'll post a revised structure soon, with how I THINK it should be. It isn't necessarily the end result because I don't know your data well enough and you have to make some decisions about the items.

 

[boblarson]

Actually, I think you should revisit ScooterBug's structure. I haven't looked into the file, but the posted structure on the thread looks about the same as what I would do.

 

[CarysW]

Carys,
I thought your name looked familiar...about a month ago you asked for help with the same database. I took a look at it and suggested some things to help normalize it.

http://www.access-programmers.co.uk/forums/showthread.php?t=176007

Is there any reason why the suggestions I made didn't work?

TBH I don't 100% understand what you were saying - I think I need an plain english explanation rather than someone doing my DB for me as I still don't understand what you've done with it.

So I give each store several entries showing which brands they stock - the brands are numbered....BUT, how do I link it to my actual store data so when I do a brand query they show up? I plan on creating a custom parameter form that will list the brands and certain other information about the stores i.e. which category they fall into or which country they are in.

As I said above, I'm struggling to think like a programmer.

 

[georgedwilkinson]

As I said above, I'm struggling to think like a programmer.

That's the last thing you want to do. Programmers too often want to solve everything with their favorite programming language. You are nowhere near using a programming language yet and I'm always disappointed when the first thing people think they need to do in Access is write code...a properly normalized design and good use of the wizards will avoid much of the need for VBA or macros (at least initially).

Could you post some more specific questions about what Scooterbug has recommended? Which thing that Scooterbug recommended are you having problems with?

 

[CarysW]

That's the last thing you want to do. Programmers too often want to solve everything with their favorite programming language. You are nowhere near using a programming language yet and I'm always disappointed when the first thing people think they need to do in Access is write code...a properly normalized design and good use of the wizards will avoid much of the need for VBA or macros (at least initially).

Could you post some more specific questions about what Scooterbug has recommended? Which thing that Scooterbug recommended are you having problems with?

These bits:

tblBrands
BrandID Primary Key
BrandName

tblBrandStock
BrandStockID Primary Key
BrandID Foreign Key
StoreID Foreign Key

And then how do I create the query to pick out the stores based on brands sold as well as other criteria such as country, category etc.

 

[georgedwilkinson]

I'm not sure what you don't understand about the table structure. Your quote recommends that you create 2 tables named tblBrands and tblBrandStock. It also assumes you already have another table with store information. The tables in question also have fields named BrandID and BrandName (in tblBrands) and BrandStockID, BrandID, and StoreID (in tblStock). Each table has a primary key; in this case, BrandID in tblBrands and BrandStockID in tblBrandStock. Additionally, tbleBrandStock is a junction table (required for M:M relationships--the topic of this thread) and has a foreign key from tblBrands (BrandID) and some other table with information about your stores (StoreID).

To easiest way to write a query if you're not sure how is to use the handy-dandy visual query tool built into Access (aka QBE). Select the table(s) that contain the data you want to query, sort, and filter on. You just click on the fields you want to filter or sort on to get what you want from the query.

In order to create the query you mention, you'll need to select from tables that contain all that data. Do you have a table with country? Category? What is/are that/those table(s) called?

 

[CarysW]

I know how to write a query. What I don't get it how to do this one.

I want my end user to choose a brand by name, and then choose whether they want to see just HQs or all and also a county or country(these will all be chosen via combo boxes on a custom parameter form).

So how would my query be structured?

I would have a table of brands with a key, a table of stores with a key and then a junction table which joined them together - each instance of a store stocking a brand would have a key..? My query would need to pick up the junction table key and work out both the brand from the key and the store from the key. I'm really struggling to get my head around how it would do this.

I really appreciate your plain english explanation George, thanks.

 

[Scooterbug]

Carys,
I apoligize if my help was off target. Wasn't my intention to do your database for you...I work better by showing examples that putting things in words

As for the query you are looking for...
Think of a foreign key as a reference number so that you can get additional data. In your case, the junction table (tblBrandStock) holds data for Brand information and Store information. But instead of putting in all the information on those two subjects (It saves a key because the information already exsist in the database), it saves a key.

You start your query out by adding the tblBrandStock. But since this table contains keys, you will need to add the tables that the keys relate to. So you would add tblBrands and tblStores. Once they are added, you need to let Access know how to relate the tables together. So you create a join between the Foreign Key from tblBrandStock (BrandID) and the Primary key of the table which holds the data (BrandID from tblBrand). You would then do the same for tblStores.

Once the tables are properly joined, you can add any field from Brands or Stores to the query.

 

[CarysW]

Once they are added, you need to let Access know how to relate the tables together. So you create a join between the Foreign Key from tblBrandStock (BrandID) and the Primary key of the table which holds the data (BrandID from tblBrand). You would then do the same for tblStores.

Once the tables are properly joined, you can add any field from Brands or Stores to the query.

Thanks Scooterbug,

When you say 'create a join' do you mean create a relationship? If so, which bits join to where?

Sorry if I sound like a complete idiot...

 

[Scooterbug]

You can create a relationship in the Relationship window, yes. (To open the Relationship window, there is an icon on the tool bar when you have the Database Window selected) To create a join, click and hold on one of the fields that need to be joined together. Drag it over to the field from the other table and let go of the mouse button when it's over the field. So for example, with tblBrandStock and tblBrand, you would click and hold the field BrandID from one table, drag and drop it onto BrandID in the other table.

If you create the relationship in the relationship window, when you add the tables to a query, the join will automatically be created for you. If not, the same procedure above will work in the Query Builder.

And asking questions is how you learn It's hard to gauge the experience level of ppl, so I apoligize if something isn't clear. I have no problem giving clarification

 

[CarysW]

Thanks all, I'm think I'm going to leave it for this week now. I have a lovely day off tomorrow.

Thanks again for your help, might be bugging you again on Monday!