I am designing a database where I need to store upto 600 different individual pieces of separate data per client. I am very new to access and could do with a simple example of how to use either a subdatasheet or a linked table to allow the input of this amount of data. After pulling my hair out for 3 weeks I could really do with someones expertise!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
I Require more than 255 fields per client. 2
- Thread starter tricky29
- Start date
-
1
- #2
- Thread starter
- #3
The software is to collate 600 or so financial pieces of info on an individual , and all info collated on that individual is unique to that individual, only the fields are constant. What I need is help on how to design a database to allow the inputing and then retrieving of that individual data which is all linked to one ID number (Factfind ID) which is set as the primary key in the main database. The database is simple in its objectives as there is no need of cross referencing other variables, but unfortunately I do not have the expertise to know how to correctly set up a linked table to take the ID number across and store it in a linked table.
JerryDennison
IS-IT--Management
What Rick is trying to say is, are you sure you have the equivilant of 600 different FIELDS (each with it's own unique name) of information? This is unusual to the extreme. Or is it possible that you have groups of financial information where you might have an account type and then a transaction for that account. i.e. something similar to a checkbook or even a double entry ledger? If so, you wouldn't try to setup a field for each ledger entry or account. Speaking only for Rick and myself, we've never seen a case like this(granted I only have about 15yrs of experience developing db systems so I can't possibly have seen it all, but I've seen a lot) so we're trying to get a better understanding of what your need is.
-
1
- #5
It is extremely difficult to explain database normalization in this type of forum. I would suggest that you search for normalization whitepapers on the internet and study closely. I have never heard of a normalized database which contains that many fields in a table. Normalization tends to proliferate tables but at tremendous advantage to the user. I recently provided a presentation on database normalization in a powerpoint briefing and would be pleased to provide it to you on request. In the meantime you may want to look at which gives an overview of normalization.
Steve King
Steve King
- Thread starter
- #6
In response to JerryDennison I am indeed trying to store individual and unique info for each separate client in different fields. The Database is to be used for storing a Financial Services interview with a client, where although the same fields will be filled in there is no common answers ,the answers are all unique to that client. The collated info will then be cross linked into a various reports that will then print ready completed application forms based on the clients provided. Whilst I know very little in comparsion to yourself , I have used a commercially written database that our company commissioned for a similar project which uses around 240 fields already, I am looking to expand this to the next level
JerryDennison
IS-IT--Management
Ah, now I understand better. Typically a questionaire type db would be built around a "question engine", one or more tables that hold the questions and a "link" table that allows assigning the question(s) and subsequent answers to the questionee. Structuring the db in this fashion allows the questions to be extremely flexible and customizable for each questionee. Meaning, you can setup and ask 1 to a virtually unlimited number of questions for each questionee. By hardcoding the questions a fields in a table you are limited to only those questions for which you have a field established. This leaves "holes" in existing questionaires whenever you add a question as those records that already exist will have a null value for the field added. Not an efficient use of database resources, nor is this flexible in regards to the questionee. I hope this makes sense to you.
I've been watching this rather intriguing forum for a couple of days now. I found it rather odd that someone would need more than 255 fields in one table as I've been doing this for about 6 years now.
I must admit though, after hearing the explanation, I now have a much better understanding as to why this would be needed.
I'm very curious to hear a little more about the structure Jerry talked about. Although I have no need for a "questionaire" database here, I'd still like to get a better understanding of what Jerry was talking about. So Jerry, if you wouldn't mind, could you give an oh-so-brief example of what the structure might look like? How many tables would there be? Surely not one for each question and answer, but would you want to categorize them or something. Enlighten me, please.
Thanks in advance. Kevin
slanek@ssd.fsi.com
I must admit though, after hearing the explanation, I now have a much better understanding as to why this would be needed.
I'm very curious to hear a little more about the structure Jerry talked about. Although I have no need for a "questionaire" database here, I'd still like to get a better understanding of what Jerry was talking about. So Jerry, if you wouldn't mind, could you give an oh-so-brief example of what the structure might look like? How many tables would there be? Surely not one for each question and answer, but would you want to categorize them or something. Enlighten me, please.
Thanks in advance. Kevin
slanek@ssd.fsi.com
tblRespondent
Primary Key = RespID
Name
Other ....
tblQuestion
Primary Key = QuestionID
Text
tblResponse
Primary Key (Concatenated) RespID/QuestionID
Response
The respondent can now answer an unlimited number of questions without having a field in a table for each response. In normalization this is called 'repeating groups' and should result in the creation of a second table (tblResponse) which relates (hence the word relational database) back to the respondent and the question. This is just a very short explanation and could actually be solved other ways but NOT PROPERLY without creating a second table to hold the responses.
Relationships are one-many from the primary key in tblRespondent to the foreign key which comprises part of the concatenated key in tblResponse. Then a one-many from the primary key in tblQuestions to the foreign key which comprises part of the concatenated key in tblResponse. This method guarantees uniqueness of the response in that for any one respondent can only provide a response to any question only one time.
Hope this helps.
Steve King
Primary Key = RespID
Name
Other ....
tblQuestion
Primary Key = QuestionID
Text
tblResponse
Primary Key (Concatenated) RespID/QuestionID
Response
The respondent can now answer an unlimited number of questions without having a field in a table for each response. In normalization this is called 'repeating groups' and should result in the creation of a second table (tblResponse) which relates (hence the word relational database) back to the respondent and the question. This is just a very short explanation and could actually be solved other ways but NOT PROPERLY without creating a second table to hold the responses.
Relationships are one-many from the primary key in tblRespondent to the foreign key which comprises part of the concatenated key in tblResponse. Then a one-many from the primary key in tblQuestions to the foreign key which comprises part of the concatenated key in tblResponse. This method guarantees uniqueness of the response in that for any one respondent can only provide a response to any question only one time.
Hope this helps.
Steve King
With that many fields you may have a problem with Queries.
I did some QA work on an app that had jet 4.0 backend. When we ran queries on a dozen fields with several indexed the program pooched.
If the data is image related you may want to just include a pointer to each document in your database instead of re-entering the data in table fields.
I did some QA work on an app that had jet 4.0 backend. When we ran queries on a dozen fields with several indexed the program pooched.
If the data is image related you may want to just include a pointer to each document in your database instead of re-entering the data in table fields.
JerryDennison
IS-IT--Management
This to Kevin. Steve has demonstrated the minimum number of tables need to give you what I was describing. You could add another level ahead of the question to allow for grouping questions under logical(or illogical) groups. The only thing Steve didn't really explain was the compound primary key. By creating a compound primary key in the response table using a foreign key to each of the other two tables you prevent the same person answering the same question more than once. The drawback of using a compound key is searching or addressing a specific response. It is very difficult to search a compound key. This can be overcome quite simply by creating a pseudo primary key. An autonumber field set to index no duplicates. Since you've already assigned a primary key you cannot make this field your primary key but you can use it as if it were one. The only thing you can't do is set cascading updates/deletes with it.
Although totally agree with soluntion recommended by scking, there may be situations where you wish to split a logical record into several physical tables that you then link on a one-to-one basis:
tblRespondent_table1
Primary Key = RespID
field1
field2
....
fiel255
tblRespondent_table2
Primary Key = RespID
field256
field257
....
You can design separate forms based on each table.
Forms or reports could be based on queries instead of tables; this gives you more flexibility with changes; if current forms/reports are based on tables you can easily chage the data sourec property.
Such queries may retrieve fields from both tables (or from more than 2 tables). I have never tried this on tables with so many fields, hope that queries do not have the same restriction on 255 fields.
You can start from a main form where say a tab-control gives you access to the various tables or queries associated with one entity.
Whenever a logical record has too many fields it is practical for perfomance enhancement to have it split into several tables. In multi-user environments this has the advantage that an entire entity in not locked while a user accesses it. Suggest that the fields grouped on any one table should be logically & functionally related.
Such a solution may be used if you need to build upon an existing solution as you mentioned your case was.
tblRespondent_table1
Primary Key = RespID
field1
field2
....
fiel255
tblRespondent_table2
Primary Key = RespID
field256
field257
....
You can design separate forms based on each table.
Forms or reports could be based on queries instead of tables; this gives you more flexibility with changes; if current forms/reports are based on tables you can easily chage the data sourec property.
Such queries may retrieve fields from both tables (or from more than 2 tables). I have never tried this on tables with so many fields, hope that queries do not have the same restriction on 255 fields.
You can start from a main form where say a tab-control gives you access to the various tables or queries associated with one entity.
Whenever a logical record has too many fields it is practical for perfomance enhancement to have it split into several tables. In multi-user environments this has the advantage that an entire entity in not locked while a user accesses it. Suggest that the fields grouped on any one table should be logically & functionally related.
Such a solution may be used if you need to build upon an existing solution as you mentioned your case was.
JerryDennison
IS-IT--Management
In deference to Calator, it would be far better to take the time and migrate the existing data into a normalized table structure. For several reasons, not the least of which is performance. The other reasons are stated above but one that wasn't touched on is performance. In any SQL system performance(i.e. querying, sorting, record retrieval speeds) is vastly improved when you have many tables with few fields than when you have few tables with many fields.
As for migrating the existing table structure into a normalized one, this can be done relatively automatically using append and update queries. So no existing data needs be lost. I would probably not try to convert the existing application but would create a new one and migrate the data only.
As for migrating the existing table structure into a normalized one, this can be done relatively automatically using append and update queries. So no existing data needs be lost. I would probably not try to convert the existing application but would create a new one and migrate the data only.
- Thread starter
- #15
In the above example I assume I would set the ID in the 1st table to Autonumber and the second table ID to Number , Will this automatically take the ID number across to the second table or do I need a query or macro to update the ID Number in the second table so as to match the value of the record in the first table. I have already tried a one to one relationship, but couldn't make the second table automatically store the ID value from the first table into the second table
tricky,
you are right all the way and this won't happen automatically. You need to write some piece of code to do this; insert the code in one of the events attached to the main table where a record is first inserted. Possibly queries or macros could be used but I prefer code attached to events.
For ono-to-one the idea would be that once a record was successfully inserted in the main table, your code programmatically inserts a record with the same id in the related table(s).
For one-to-many you can design a form with sub-form where the relating field is declared in the sub-form properties so that "childs" on the "many" side are automatically created by Access with the correct ID (there is a wizard that will create such forms for you and also a lot of info in the Help)
you are right all the way and this won't happen automatically. You need to write some piece of code to do this; insert the code in one of the events attached to the main table where a record is first inserted. Possibly queries or macros could be used but I prefer code attached to events.
For ono-to-one the idea would be that once a record was successfully inserted in the main table, your code programmatically inserts a record with the same id in the related table(s).
For one-to-many you can design a form with sub-form where the relating field is declared in the sub-form properties so that "childs" on the "many" side are automatically created by Access with the correct ID (there is a wizard that will create such forms for you and also a lot of info in the Help)
Interesting article...applies to this discussion
Kevin
slanek@ssd.fsi.com
Kevin
slanek@ssd.fsi.com
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
