Hi Bob
First, I have a lot of respect for Jeremy. He has a lot of bareknuckles, common sense experience.
Per your transaction date table, I believe I have a good understanding on what you are trying do, it makes sense to use one table for this task - it does fit the rules of normalization, and it seems similar to a database I had to redesign (because the initial design was mainly one large table). In my case, the database was tracking the migration of 900+ applications to Windows XP environment for a corporation. Here are some things I did...
- Status of activity was stored on the master table. Instead of a yes/no, I used a status field because it providing more options -- accepted / rejected / pending.
- On the master table, I also had an unbound field that flagged any alert based on the transaction table.
- The date transaction table had...
date, activity code, comments (memo or text field depending on whether you want to search on the field), and a hyper-link. The activity code described the purpose of the tranaction. For me -- start testing, testing complete, issues, customer acceptance, etc. The table also had a hyperlink to link to any external documentation.
- More on the tranaction table...
Depending on the need, I have also include a "sort order" of events. For example, a check list would have a sequence of tasks to be carried out in a definitive process. I also occasionally use an urgency or level field - "1 high" ... "5 low". These information fields allow me to grab or sort pertinent information from the transaction table without having to scroll through dozens of rows. With Access, you can also use these codes to trigger an event where the field is colour field is shown - "red" for alert.
- Support table -- techie tip...
Quite early in life, I got real tired of creating suppot tables for every combo or list box; I got tired of editing combo or list boxes to add a new value or edit an existing value. I have seen lots of code in this forum and others for adding to ... combo and list boxes. But before then I developed the following approach which I find works real well for me.
tblVari (Variable)
fldCTL - text (15)
fldName - text (8 to 25 depending on need)
fldDate - date to track when the (Optional)
fldID - autonumber (Optional - depends on whether you intend to assign the reference number or the actual fldName to the table. For example, using the autonumber would allow you to change "LetReceived" "Letter Received" for all transactions if you stored the fldID instead of the text. Advantages and disadvantages to either solution.
Index - this is key, regardless of using the autonumber or not, you have to set the fldCTL + fldName to unique, no nulls to prevent duplicate entries.
Why do I perfer this. I can use it for any field using a code. For example for your letters, for letter_code for fldCTL, and then for fldName = "received", "sent", "phone", etc. For the status of the applicant, fldCTL = "AppStatus" and fldName could be "Accepted", "Rejected", "Reference Check", etc.
- Another table.
Will you need another supporting table? For example, if you are tracking job applications, it might be useful to have a skills table. Since one person can have many skills and many people have this skill, you would probably need a "join" table to support the many-to-many relationship.
tblSkills
skill_id (autonumber)
skill_desc (text)
tblSkillSet
applicant_ID (foreign key to applcant master table)
skill_id (foreign key to Skills table)
skill_level_code (text!!! - "1 subject expert", "2 expert"
comments (memo or text)
The primary key for this would be applicant_id + skill_id
Then on the applcant master file, you can display the skills either in a list box or a subform.
There are many variations of the above -- and there may be better ways to do some of these things -- this is just my thoughts based on the infromation you have provided.
Richard
