Unique table per record?

[Poepol]

Hi all.

Is it possible to have unique tables per record, with it's own data. As of now if open the related table under the persons name I can change the data for him. This however gets copied to every other person in the rest of the table.

So is it possible to have access create a unique table for each person as they are added (table is ready made, no extra info will be added to it, except for the yes/no fields being clicked.).



Please let me know if there is a way to do this.

[pkstormy]

Yes it's possible but there is no 'easy' way. It would be much, much better (and much, much easier) to setup or keep it in 1 table and store the person's ID or Name as a field in a single relational table to separate by person versus a separate table for each person, even if it required adding fields to the 1 table which would only be used by some persons/records.

The only reason I can think of for having a separate table for each person (and deviate from a normal setup) is if those tables were each tracking different information (ie. they all had different field names with different kinds of info). Even in this scenario, it would still be much better to figure out a way to combine this into again, 1 relational table or have non-person/record specific relational tables grouping like fields together in static relational tables, even if it were to require creating 'grouping' type fields or additional fields (which may be unused for some persons/records) in that table (or even if additional tables/relationships were required). Ideally you want to develop a table structure which is not modified in code. This prevents many data inconsistency type problems (if the code isn't perfectly setup) including those that might happen for other users using the application. With almost every db design, your much better off letting MSAccess (or SQL Server) maintain the referential integrity of the relationships between tables (which means setting up the table relational structure correctly is KEY in every application! - ie. JOINS typically should be done correctly in the relational diagram versus trying to be maintained in the coding.)

The coding for creating/maintaining separate tables for each person would not only be a bit challenging (more into the advanced level) but could also be a pain to maintain, possibly mean creating multiple unnecessary-table specific queries (along with a lot of extra unnecessary coding and form design), and also lead to potential 'bloating'/relational problems with the mdb file itself (ie. each time it creates a new table.) You'd also most likely end up trying to find one work-around after another work-around for things that could otherwise very easily be done. If you end up trying the method of a separate table for each person, I'd also plan double to triple the coding time to not only work out conflicting issues, but also for all the extra designing that would be needed.

I would highly suggest re-thinking your logic on this and try to come up with a way to combine or keep these combined into 1 table. Otherwise you could be opening up a can of worms with this kind of structure that you'll most assurdly later regret (I did when I tried this many, many years ago.)

From your 1st paragraph, it sounds like your current table structure just needs to be modified a bit to account for new/correct logic (and possibly some code added to the form design). What you stated you wanted to do in this paragraph is typically done without the need to go to a separate table for each record type of design.

[Poepol]

Thanks for the info, and I agree one table is much easier to use and would be the better option. The only problem is that I have more than 255 assessments...

You can only create 255 fields in an access database as far as I know. Is there a way then to let access allow me more than 255 fields I literally need only 10 or 20 more.

[nicknameoscar]

I am not quite sure how to ask my question because I am not quite sure what sort of assessments you are talking about. I was wondering if they (the assessments) have characteristics that would allow them to be grouped. You could have say 50 similar assessments in one table, 50 assessments with slightly different characteristics in another table and so on. Just throwing out ideas.

[pkstormy]

If it's more than 255 fields, then create a MyTableName2 table with just the additional fields (and also with the same relational field/join as the first table has to your main table.)

This is of course going to require an additional form and a button to go to 'part 2' and possibly some extra queries but it's better than creating new tables for each record (Just remember that you really don't want the same field itself to represent 2 different kinds of data but on certain occasions you can do this for certain kinds of data. If you do this, then you'd want to have another field designating what 'kind' of data is being stored in the multi-purpose field. This works well but can be tricky to query.)

We have the same thing with our research data (typically they want to have more than 255 fields/questions for 1 survey). So I'll have dbo_tblSubjectsSurveyPart1 and dbo_tblSubjectsSurveyPart2 tables. Each table has the PersonID field (to join back to the dbo_tblSubjects table.) Occasionally, I need to store the ID field (ie. autonumber) from dbo_tblSubjectsSurveyPart1 into a Part1 ID type field in the dbo_tblSubjectsSurveyPart2 table. This depends on the circumstances. With survey/research data, it's difficult to form any grouping type fields in the table (otherwise this would be the best approach as nicknameoscar suggested.) But I have used the technique where a specific field designates what kind of data is being stored in another field for each record to bring the table within the 255 field limitation.

[Poepol]

The assessments can't be grouped really. The assessment is basically a score between 1 - 7. It's for a pre school and the assessment would look something like this: Can speak and read 3; Reads big word 3.

So no real way of grouping them. There are however 5 main subjects. So my idea is to make 5 tables (one per subject) and then a table with there names and then just link the auto number ID.

The reason for wanting a unique table per kid is to simplify the way it looks and works. Tables with MANY fields looks sloppy and complex. However the reality is sinking that this might be the only way to do this without very very complex coding and database relationships.

Again thanks for the help, it might not solve my problem. Yet it is nudging me in the right direction. So it does help A LOT.

[RuralGuy]

Each Assessment should be a record in a table (not a field in a record) with an identifying field for the individual.

[Poepol]

Then you have a hunderds of fields for each child? Also children can be added, assessments not. So there would definitly be way more children than assessments...

Thanks for the idea though

[pkstormy]

So my idea is to make 5 tables (one per subject) and then a table with there names and then just link the auto number ID.

Not a good idea at all!! Having a table for each subject defeats the purpose of even creating a database (you're better off using excel if this is the route you want to go) and opens up a lot, LOT of other problems (which sooner or later you (or the next developer who inherits your design) will come to regret). What might seem 'slopply' to you in a single table is the reason you setup a relational table structure.

If you need more than 255 fields in a single table, setup a relational table.