Enourmous counts of fields, and relation to single master table

[Zerothi]

Hi all!!

I have a problem concerning many fields and their linking to each other via relations.

I have one master table with an PrimaryKey (an autonumber), called intID. in this master table one enters additional parameters.

In addition i have 15 tables with over 100 fields which all needs to point to the PrimaryKey in the main table. Each of these tables have a primary key which is meant to have the same number as in the master table.

I cannot get Access to automatic generate the autonumber from the master table into all the others (into the field called intRoomId), i've tried Left-To-Right, but that didn't seem to work.

I've tried connecting them all simultaneously to the master, and also consequitively (which i only tried cause the other didn't work.

If there is any one that can find the error it would be much appreciated.

Zerothi

An attachment of only the master and three of the tables is attached.

 

[boblarson]

You REALLY need to read up on normalization. This is going to be one huge nightmare for you (and us) if you don't fix this now.

http://support.microsoft.com/kb/283878
http://r937.com/relational.html

 

[Zerothi]

Hi boblarson

I'm not quite sure if i misdelivered my post...

What i meant was that all 1500 fields are unique for each entry (yes i know, this sounds odd, but it's true!!! ). So essentially i needed a table which had the possibilty of 1500 fields. This can not be done in Access.

Therefore i needed to split them up to several tables (and a common ID). And so all fields are needed to be "attached" to the master table. None of them are the same for any of the entries (like a town could be to several inhabitants of a country).

Therefore i havn't got any repeating information (except for the primarykey intID).

I just want to attach data to the mastertable through an ID...

Hope that made it clearer...

I have read the normalization principles, but i don't see the link to me problem, as this is mainly on simplifications on fields that are used in several entries.

Thanks for the reply Hope you can help me!!

 

[neileg]

Hi Zerothi. You misunderstood Bob's advice.

You don't need 1500 fields, you need however many (up to 1500) records in a related table. Even if you managed to create 1500 fields, you could never bring them together since the 255 field limit relates to queries as well as tables.

Normalisation involves having tables that are long and narrow not short and wide.

 

[boblarson]

Can you explain further as to what you are needing to accomplish and what this represents? We might be able to give you some better way of doing it because currently this design will never fly due to the physical limitations of Access.

 

[Zerothi]

What i basically have is a house. That house needs to be described in any possible way. That means 1500 individual specifications... So if i have one long table, how do i then make 300 houses? How do i assign an individual value for one of the houses to one of the rows without needing 1500 fields? In what i understand from your posting i would then need 1500 tables, and one master which designates which rows belongs to whom...?

I'm a little bit confused on your posts... Sorry!

I know i can't never bring all fields into one table.. But that isn't so much the problem.

I just cant see how i can assign individual data in the 1500 data-entry points..

I'll try to shetch this:

First house -> 1500 entries...
Second house -> another 1500 entries (none of them same to entries in the First house)
Third house -> another 1500 entries (none of them same to the entries in neither the First nor Second house)
.
.
.
300th house -> another 1500 entries (none of them same to the entries in any of the 299 houses before)

From what i see with your suggestion i have

One table which defines the name of the possible data-entries for each house.
1
2
3
4
.
.
.
1500

But then as I see it i need to have 1500 tables for each parameter?

Or is the meaning that i create a table for each house with 1500 rows? Actually that might be alot easier!! But i would still need 300 tables...

 

[neileg]

No, you add 1500 rows to the same table for each house. Each set of 1500 holds the ID of the house so you can extract the correct 1500 records. So for 300 houses you would have 450,000 records.

Depending on what you want to record, there could be an argument for grouping the specifications so that you might have more than one table.

 

[Zerothi]

Ahhh i see... I wouldn't have guessed that from normalization... I thought of that as splitting tables up, which had numerous reoccuring information...

Many thanks for the replies... I will look in to the modification of my database for such a solution!!

 

[doco]

Forgive me for asking Zerothi, but I am a bit curious - and this is a bit off topic. I deal in real estate and real estate appraisal and ad valorem property tax assessment. So, data about houses, garages, outbuildings, accessory improvements, etc. is something I know a little about - not everything mind you - but a little. I am trying to imagine 1500 datapoints for an individual house and those being individually unique from every other house is kind of strange. For instance if you were programming a house class to be instanced into an object, I am very nearly certain that class could be instanced as a barn or garage or commercial structure, etc. because of the unavoidable similarities of building construction, etc.

So, could you humor an old guy and ellaborate a bit on what it is you're collecting? Just curious...

 

[Zerothi]

Well actually it's the plumming/electrical/shafts/airconditioning installations in the building... Those being very specific. Down to what name of the model of device in every place...

If i wanted i could make a table with presets for every fabricant on the earth... But that might be a BIIIIIIG database, and that would require one person on fulltime to keep up to date. Thats why it's so much easier to just make them all unique. Because typically we know which parts we have to deal with. And then a database of devices isn't needed.

Even if there are two garages in the same building, the setup of the two necessarily wouldn't be the same!!! Just imagine the plumming

Hope that was enough humor for the day?

 

[Pat Hartman]

I looked at the sample you posted and the structure will cause nothing but trouble and adds significant complexity to any queries you may want to do.

The suggestion to use lookup tables for parts is a good one. It will minimize data entry errors and provide a better platform for reporting. If there is a recall for instance, wouldn't you like to be able to find all instances of a particular component and not worry about missing one due to a typo?