I have seen previous threads relating to splitting tables but maintaining a 1 to 1 relationship and the big question is obviously "Why"?!
I need to do this as number of fields is 150 ish and it gotten to the point where you cannot add any more or, in some cases, edit the existing field design.
It's for a charity who want to have loads of tickbox fields as well as lots of fileds with lookups to control data entry.
Is there a way to enable the table to grow (although I am not sure how much they will add as time rolls on)?
Alternatively can anyone tell me how to create 2 tables so when a record is added to tblClient a single record is created in tblIncident for example.
I would be hard-pressed to find a legitimate reason for 150 columns table. It's quite rare that a properly normalized tables would have more than 30 fields, in fact.
Its to collect data and record hate crime information.
It's basically 3 sets of data (but there will only be 1 record per entry) Client details (including referral information) Incident details (loads of data collected) and Outcomes (again loads of data).
There are 30 yes/no fields, 5 memo fields and the rest are text but with associated lookup tables.
I'd bet that Client details should be in their own table, Incidents should be a table of its own, but without knowing the specifics of fields you want to store, I can't be 100% sure.
But here's a general rule and see if you can figure the rest out on your own (and if you're stuck, post back with more details.)
If you have any set of data where you know there will be more than one instance (e.g. a client can have more than one incidents), then we have a one-many relationships, so we need two table, one side table for Client, and many side table for Incidents.
Thanks - Yes I started splitting it into 3 tables Client/Incident/Outcome my issue is - when they create a Client I need a single Incident record and and a Single Outcome created all with the same id or link - they would not record multiple incidents or outcomes apparently.
Another tip you might want to consider, is that if there is any data that will be entered on a frequent basis, such as names and addresses, then a separate table for storing that data will do at least two things:
Limit the amount of data Entry required
Eliminate the potential for errors in data entry
An example of this might be to create a Persons Table, since a Person can be a victim, a perpatrator of the crime, a referring agent, or any other person involved in the incident, and the same basic information (name, address, contact info, etc) would be required for all. In addition, a referring agent, could have been (or even become) a victim.
I am sure that if you review the set of data, you will find other similarities as well. If we could see the structure (no data is required), that even better suggestions could be made
That's because they're one-to-one when you actually want one-many. The difference between one-one and one-many is thus:
One-One
tblClient
-ClientID (Autonumber PK)
{a bunch of fields}
tblIncidents
-ClientID (Number PK)
{some more fields}
One-Many
tblClient
-ClientID (Autonumber PK)
{a bunch of fields}
tblIncidents -IncidentID (AutonumberPK)
-ClientID (Number)
{some more fields}
When you create relationship between tblClient.ClientID and tblIncidents.ClientID (for many side table design I showed), the ClientID becomes a foreign key.
Sorry I am being very stupid - i do need one-to-one! There will only ever be one incident recorded for this purpose. It's maintaining a robust link between the Client and the Incident when entering the data I cannot fathom. Your one-to-one description makes sense - how could I enfore this through an entry form?
Well, one-one are quite very rare, and I'd be inclined to think that even though you may have one incidents per client, there's a chance that you will end up with a client who has two incident. Furthermore, one-many relationship still works even if there is only one record in the many-side table.
Can you explain a bit more about the incident you're reporting per client? It's possible I'm not understanding the 'incident' and 'client' in same sense as you are...
The way they are using the DB: They take calls from victims of hate crime and record who the "Client" is by demographics and record funders, borough etc.
They they record what happened, whether perpertrators were identified, if the police were involved (and the response) etc. They would add to this record and not created a second "Incident" for the same Client record. If there was a 2nd incident it woudl be a new Client (a totally new entry).
Once they have assisted the Client they record what they did and the outcome.
That's basically it - but due to all the various options and types of support/outcomes it makes a single table DB too big. And they will want to change/make additions in the future.
I am happy with giving them a one to many (but the will have one record on the many). Ideally they would use a tabbed entry form so when they create a new client and flick to the Incident tab they can just add the details and this is inextricably linked to the Client record....
The way they are using the DB: They take calls from victims of hate crime and record who the "Client" is by demographics and record funders, borough etc.
They they record what happened, whether perpertrators were identified, if the police were involved (and the response) etc. They would add to this record and not created a second "Incident" for the same Client record. If there was a 2nd incident it woudl be a new Client (a totally new entry).
Once they have assisted the Client they record what they did and the outcome.
That's basically it - but due to all the various options and types of support/outcomes it makes a single table DB too big. And they will want to change/make additions in the future.
I am happy with giving them a one to many (but the will have one record on the many). Ideally they would use a tabbed entry form so when they create a new client and flick to the Incident tab they can just add the details and this is inextricably linked to the Client record....
If a new incident represents a new client, then I understand why you think you need one table. Despite what appears to be the case, you should consider splitting the table anyway. What about a case where in a new record the name is spelled incorrectly. The current system gives you no chance for a historic lookup of a client to see if any previous relevant events have occurred.
You can allow the users to enter data via Continuous Forms that are displayed as datasheets, and based on a query that returns data that looks like their old table. This gives the users the appearance of having their data in one table, but allows you to store the data in as many tables as is required.
If (in the process), you change some of the entries to be combo boxes (names, crimes, anything else you can think of), you will not only make it easier for the users, but will eliminate the potential for error as described above.
Remember that this is nto a spreadsheet, and that users should not be accessing the data directly when a form can be provided for them.
It may be a little late to be suggesting this, but I would set it up something like this:
Client table, containing client details (one record per new person), with clientID as primary key
Incident table, containing things specific to the incident - date, location, etc with incidentID as primary key and clientID as a foreign key to the client table. There could be more than one incident record per client, if the same person is a victim of two separate attacks, or more.
IncidentAttributes table - containing many records for each incident (each identified by incidentID as a foreign key) - instead of all the checkboxes across the width of the table, you'd just add records to this table, listing all the relevant incident attributes.
(The incident attributes added here could be drawn from another table containing a list of all possible incident attributes)
So it would be client to incident to attributes - one to many to many.
If you are stuck with having 150 fields then doing Compact and Repair should mean you can change field types and add fields. Access will allow 255 fields.
But as the others have said 150 is large and could indicate other problems which could create difficulties in the future.
Compact and Repair might not help if you are getting close to the limit of record size (2048 bytes). If so, even splitting the table doesn't help because if you do a 1/1 split, you still have to reJOIN the tables to use them together and you go slam-bang into the record size limit again (this time for the query).
You need to re-think this design. If you cannot tell how big it will grow, then you are already caught. I am unsure of whether you can do this, but I know that your previous posts make it clear that you don't know what else they will need to add to this list.
According to what I have read (and correct me if I'm wrong on this), it really never happens that the same person calls in with two incidents because you are not tracking people, you are tracking demographics. You could keep the name for a while if you had to, but long-term, there is no "client" name of any importance in the database.
Try to imagine this structure and see if it makes sense:
tblIncRep (incident report)
IncID, PK, autonumber or whatever else you are using.
Demographics data for this client.
IncDate, when this incident occurred
RepDate, when this incident was reported,
etc...
Name if/only if you wanted it. OR put the name in a separate table, as
tblIncClient (client for an incident)
IncID, FK, link to tblIncRep
Client, name information about client.
This table either has no PK or has a meaningless (autonumber) PK as a trash field that you rarely/never use.
tblFlagCodes (list of possible attributes or flags of yes/no flavor)
FlagID, PK, autonumber
FlagText, explanation of the flag-code that this represents.
tblIncFlags (list of flags applicable to the incident)
IncID, FK, link to tblIncRep
FlagID, FK, link to tblFlagCodes
This table has no PK and you can, if you wish, make the combination of the flags an index with NoDups. If you do this, then the table will never have more than 1 record matching a given combination.
Now make tblIncFlags SPARSE (as opposed to DENSE or COMPLETE). If you would have checked the box for flag XYZ, there will be a record for that incident number and the code for flag XYZ. If not, there will be no such record. With me so far?
Now write a couple of queries. You need one for your data entry form if you use a separate client-name table, or perhaps you need that anyway as a matter of good practice. You need a query which includes three fields - the incident ID, the flag code, and a count of records in tblIncFlags that match the incident ID and flag code. This latter count should only ever be 0 or 1. You want the query to be a parameter query (which you can look up in the help manager) bound to the incident ID so that the cartesian product implied by this join gets radically reduced in size. Otherwise, your performance will go to hell in a handbasket.
Now we're close. Make a parent/child form. The parent form will be where you do the demographics part. The child form will be based on the query that counts the records matching the criteria. Make this form have datasheet view. Just to make it "pretty" include the descriptive part from the FlagCodes table. Now, let the child form scroll.
Inside the guts of the child form, put an OnClick routine that if you click it, looks at the underlying record to see whether you have zero or one records matching the code and incident number. If you have zero, use a recordset operation to create a new record with that combo. If you have one, use the recordset operation to delete the matching record. Don't actually update from the form. (You probably can't because I told you to include an SQL aggregate with it.)
Now, what happens is, if your user creates an incident report, the child form comes up with the description and a checkbox that probably looks grayed out. But the OnClick doesn't care. So using recordsets, update the IncFlags table and force a REQUERY and REFRESH.
This gives you a table in which you have records for attributes that would have been "true" or "YES" if you had separate checkboxes, but no records for attributes that would have been "false" or "NO" or not checked.
To build a report, build a query that joins the incident to its list of flags. This makes a linear list of incident, flag. (You can join the FlagCode table to get the descriptions if you like.) Now build your report with the flags as the detail section and the demographics as the incident header section.
The report will look like this, perhaps:
Code:
Incident #1 - reported 20-Feb-2009 - Poughkeepsie NY USA
Attributes:
Racial Epithets
Incident #2 - reported 21-Mar-2009 - Schenectady NY USA
Attributes:
Racial Epithets
Threat of violence
Incident #3 - report 22-Apr-2009 - Ycloskey LA USA
Attributes:
Racial Epithets
Threat of violence
Actual violence
etc...
OK, time for the summary reports. Build queries that count the number of times each code appears in the IncFlags table. Join that to the FlagCodes if you want translation and count. Restrict the range by joining the Incident report to the flags table on the incident number, and then place ranges on the incident date.
NEVER store something you know to be false. ALWAYS store something you know to be true. Then you can make assumptions such as, If I didn't store it, it must not have been reported. And that allows you to have the sparse DB.
Note that you will have to play with this for a while to optimize how you do the JOINS and selections. Always try to restrict the incident numbers first where practical because that will give you the fastest reduction in size of the remaining recordset. I.e. JOIN the incident to the incident flags where you have already restricted the incident number. Then maybe JOIN the query to the table of possible flags so that you get an OUTER JOIN and count of records matching the incident number for each flag.
Now... what does this buy you?
Well, there is now a very large limit to the number of attributes you can define. Not limited by the record size issue at all. If you want to add a new possible attribute, just update the tblFlagCodes with the new codes and descriptions. Everything else is automagically available as soon as the flag codes are available.
This is how you use the relational abilities of Access to resolve your problem. Think outside of the spreadsheet box.
Well, there is now a very large limit to the number of attributes you can define. Not limited by the record size issue at all. If you want to add a new possible attribute, just update the tblFlagCodes with the new codes and descriptions. Everything else is automagically available as soon as the flag codes are available.
This is how you use the relational abilities of Access to resolve your problem. Think outside of the spreadsheet box.
This is one of the most important aspects of proper database application design - making it so that changes to the detailed type of data (i.e. new kinds of incidents, new locations, new service level agreements, etc) do not require the program to be modified.
And one of the reasons it's important is that you will never be able to fully hand the thing to the users otherwise - and you'll end up not only being the database administrator and programmer, but the data entry clerk too.
