MS Access Table Setup

[StupidRalph]

My question concerns on how to set up my tables. I have three tables. (Distributor, Wheels, Company) I want to set up my DB so that I know exactly which specific wheel each distributor carries. As well as also which company that wheel is made by. I have the CompanyID as a foreign key in the wheel table so receiving the Company info from the wheel table is not a problem. The problem is each company has more than one distributor and each distributor supplies more than one company . I know I need a many to many relationship just didn't know how to define it. I was thinking of using a junction table but I don't really know which primary keys to insert inside of it. Or what I need to do.


If you read my post you know how I got the name

[raf]

I already knew that from just reading the title ;)

Why post this here? It's a MySQL forum (MySQL is a database-environnement, just like MsAccess is one).
If your going to build a webapp on top of this db, then head for the ASP or PHP forum (depending on your chosen language --> probably ASP since you're planning on, using access ?
Most of us have been playing with Access when we were kids :D, so here's my view on things

In you compagnie table, you need a primarey keyfield (companyID). The same for the distributors table (distibuterID). In your wheel table, you’ll have a primary key (wheelID), all the wheelinfo, and the distibutorID and company ID both as foreign keys.
Thats it.(for your two questions.)
You can now select the info using an INNER JOIN between these three tables.

If your going to add an order table (i don’t know…) then you just need to insert the wheelID there, and you’ll be able to get all the linked info (distibuter, compagnie etc) using an extra inner join.

I don’t define the relations between the tables in my db. I just do it in my application.

If you’re uncomfortable whith making joins, just use the query-design view and then swich to the sql view to look at the statement that access came up with. Theres also quite a good section on sqlin there helpfunction.


About the junction table: there’s no need to go to a real star scheme, i think. Since each wheel can/will only have one compagnie and distibutor, this will only result in a extra table with three foreign keys (wich will be the same as the weels table, but without the weelinfo)

Does this clarify somwhat ?

[StupidRalph]

Thanks a lot it gave me some perspective on my situation. Although I may receive a wheel from more than one distributor. And I needed a distributor table to populate a dropdown list. I will humbly gather my pride and take my post to the ASP forum. The reason I posted here is as you can probably figure out is because I'm a newbie. I don't know better. I figured Access is a DB MySQL is a DB why not. But take notice that I did atleast have the courtesy to state in the title of my post that my topic was pertaining to MS Access

[Jeewhizz]

No worries, ralph, i'll move it over for you to save creating another topic.

Don't worry about posting it in the wrong topic, everyone has to learn at some point!

Jee

[whammy]

However, your question in itself probably defines your database structure (although you'd know better than I would what that is), i.e.:

"I want to set up my DB so that I know exactly which specific wheel each distributor carries."

Perhaps this may point you in the right direction:

TBL_WHEEL_COMPANY
company_id

TBL_DISTRIBUTOR
dist_id|dist_name|company_id

TBL_WHEELS
company_id|dist_id||wheel_id|wheel_desc

Obviously this may very well be flawed, and perhaps contains useless fields, and may not contain necessary fields... that's because I don't know what the relationship is supposed to be, exactly what you're trying to accomplish (although your explanation was not too bad), and the heirarchy of said relationships... can you explain that?!?

If you have the same luck I do, you were probably not given the information necessary to complete the task at hand in the first place - don't worry, that's universal when dealing with management. ;)

[StupidRalph]

I have even worse luck than that Whammy. I am doing this site for a family member. But I think I have it figured out. I have been going at it for some time now. But thank you I will be sure to post something else I am just getting my feet web in building web apps.