Table structure.

[peterod]

This is hopefully a straight forward question...

I have more columns than the table limit, nearly 400.

I can easily group the column names to split the data up.

What I want to know is the best way to set up the relationship between the new and existing tables. Do you need to do this.

The end result i want is to have forms for data view/entry that my team can use. nothing more than that.

any help would be appreciated.

 

[RuralGuy]

You need to read up on Normalization. Google is your friend.

 

[The_Doc_Man]

Having that many columns tells me, just as it told RuralGuy, that you have taken a wrong view of your DB. You are looking at Access as a spreadsheet when it is not. I work in the U.S. Government where databases less than terabytes in size are considered chump change. I've never seen a single table that truly needed 400 fields. Ever.

The problem is that there is no way for us to really tell what you want on something like that. Normalization is the way to reduce the number of fields that must appear in a single table.

 

[gemma-the-husky]

in order to do what you want, you need an id field that will be the same for each record

so split A, has columns 1-100 for each id, split B has fields 101-200, etc etc

you just construct a query joining the records with matching ID's

---------------
HOWEVER - as the above posts indicate, 400 columns generally implies there is something not correct with your table structure.

You will most likely have repeating groups of data, that are the same for many rows, and which should be moved into a separate table, and you may also have a lot of null values in many columns.

these obvious things, among others, point to normalisation issues

 

[peterod]

Ok.

So reading up a little on normalization and understand that I need more tables to cover the seperate groups if items in the database, here is a pic of the relationships that I am trying to create.

This should give an idea of what i am trying to do. I would really really like some help.

I want each record on the tasks table to have a corresponding record in each other table to the right. Is that possible or am I still not getting it.

 

[vbaInet]

You need to read up on Normalization. Google is your friend.

Oh my!! This is a rather big project Pete and it will require a lot of man hours to get it properly normalized.

Here are some links to read up on:

...

http://support.microsoft.com/kb/283878
http://www.deeptraining.com/litwin/d...aseDesign.aspx
http://www.databasedev.co.uk/databas...on_basics.html

 

[gemma-the-husky]

eg - now you have posted your current schema

in your tasks planning you have sections for planning revisions A, B, C etc. Assuming these are all different revisions, then these should be in a separate table

so you get

plan table

planning revisions table

doing it this way means you aren't limited to a fixed number of revisions per project.


I am sure there are many other instances of similar normalisatin problems.

 

[peterod]

After reading up on normalisation i see that more tables are required, thats relatively straight forward, its the relationship types that i am strugling with, more reading required

thanks for all the help to date

 

[RuralGuy]

Most relationships we define by putting a table's Primarykey (usually an AutoNumber) in another table as a ForeignKey (usually Long). You cannot *force* a relationship. Either there is one or there isn't.

 

[MarkK]

My observation is that you are getting your data confused with your structure.
This is your fundamental data structure, and I'm not sure what name you have for it, so I'll call it a DataLump...

[B]tDataLump[/B]
DataLumpID
RequestDate
IssueDate
TimeTaken
TBC
InvoiceDate [COLOR="Green"]'(probably goes in the invoice table)[/COLOR]
InvoiceNumber
Notes
[COLOR="Green"]'and the fields you need to add[/COLOR]
Revision
Type

Notice how Rev A, Rev B, Rev C, and Concept, Outline Planning, Planning are data, not structure. You've used these as names of fields, but they should be data in fields. See the difference?
You need more abstraction and less specificity. The repetion of the structure should be achieved by adding rows, not adding columns.
My 2c.