Table with multiple 1 to many relations

[KingBroil]

I have a table named [Brand] and several tables named [Brand@@@Models], one for each field in the Brand table.
I want each field from the [Brand] table to be related to it's specific Model table. I thought like that so I don't have a Brand#1 related to Brand#2 models, etc.. I didn't want to have all the models for all the brands in a single table, was I right to think like this?

What's the correct way to do what I want to achieve? I can't seem to make it work like I want.

Thanks a lot to everybody taking the time to read this!

 

[Cronk]

For a start, don't use non apha characters or spaces in table names. It will save making errors down the track when you forget to include square brackets.

Your tblBrands would have a key field, BrandID and your tblBrandModels, a key field of ModelID, and a foreign key BrandID used to link the particular model to the particular brand.

 

[CJ_London]

I didn't want to have all the models for all the brands in a single table, was I right to think like this?

No - investigate normalisation - sounds like your brand table needs some work as well

 

[KingBroil]

Thanks but I might have explained too fast, here's my structure:

The table Brands contains those fields: Honda, Chevy, Kia and Porshe.
And I have these tables:
HondaModels: Civic, Accord, CRV,
ChevyModels: Silverado, Cruze, Cobalt,
KiaModels: Sorento, Forte,
PorsheModels: 911, Cayenne, GT2RS

It's just an example (I'm a car guy) but my question is this: Am I right to put the models in 4 different tables or should I have one table with all the models regardless of their related Brand and use a query to link them to their respective Brand ?

I'm fairly new to this forum and Access so it's a biginner issue.

Thanks!

 

[CJ_London]

should I have one table with all the models regardless of their related Brand and use a query to link them to their respective Brand ?

yes

tblBrands
BrandPK autonumber
BrandName text

tblModels
ModelPK autonumber
BrandFK long
ModelName text

link brandPK to BrandFK

 

[plog]

Again, no you are not right in having all those tables and allow me to confirm CJ's suspicions about your Brands table being incorrect as well. Brands shouldn't have fields named after each brand, it should have a just one field where those values go.

You need to start from the beginning and that means reading up on normalization (http://en.wikipedia.org/wiki/Data_normalization).

 

[KingBroil]

Ok, thanks. That answer my question. My brand table is correct, I just put it the wrong way in my post. Anyway, I already read the normalization document, one year ago. Access get so confusing sometimes and I only use it periodically but when it works, it's awesome. I find that I can accomplish much more complicated tasks over time and when I come back to simple tasks like basic table structure, I get easilly confused..