Link to home
Start Free TrialLog in
Avatar of Joseppi4Life
Joseppi4LifeFlag for Canada

asked on

Database Upgrade

Based on reporting requirements, a major change to my table structure results in a need to transfer data from a single combobox to individual checkboxes.

Currently data is stored in a field called Category.  I need to take the data and transfer it into individual Yes/No fields.

For example;
I need to change Category field from "(Cat 3) Malicious Code" to a Yes in field Cat3
I need to change Category field from "(Cat 4) Inappropriate Usage" to a Yes in field Cat4
I need to change Category field from "(Cat 5) Attempted Access or Reconnaissance" to a Yes in Cat5
etc., etc.

Is this possible or do I have a lot of re-entering to do?
Avatar of Kev
Kev
Flag of Australia image

Hi,

Yes I believe this is possible, although it depends on your DB structure. As I do not know what your DB structure is I will provide very generic advice and make some assumptions.

Assumptions.
You have an ID field in table1(where Category field is)
You have an ID field in table2 (where you want the yes/no values to go to)

You need only create an update query to update the values in table2 with the values in table1 where ID = ID and table1.Category = 'Malicious Code'
See example below.
If I knew more about your DB structure I could provide a better answer.

Kev
UPDATE table1 INNER JOIN table2 ON table1.ID = table2.ID SET table2.Cat3 = Yes
WHERE (((table1.Category)="MaliciousCode"));

Open in new window

Just a quick question: That doesn't sound good from a normalization viewpoint. Will you only ever have six categories?

Could you use an option group instead?
Avatar of Joseppi4Life

ASKER

I've attached my db. The table name is tblIncidents.

It is now the only table that requires this function to work on (No longer two tables) as the new check boxes and original "category"  are contained in the same table.

Empty-Sample.mdb
jimpen,

Presently I have nine (9) categories. As we identify more types of incidents, additional categories will likely be created.

An option group will not work a I need to use this table to create a mail-merged document that must be completed in a specific format.

It uses check boxes and I have not been able to figure a way to take a value from a combo box and convert it into check box in a Word doc.
Hi,

I agree totally with jimpen, your new structure is not good practice from a normalisation standpoint. In fact I would strongly urge against it, especially if you believe you have to add more categories as time goes by. This would mean changing the DB structure every time you need to add/delete a category. A better solution is having a parent/child (and 1 to many) relationship on 2 tables whrer table 2 contains the incident type.

Having said that, the following will transfer your data as requested. I have provided two examples so you can see the pattern. You need only create an update query with the following:

Kev
UPDATE tblIncidents SET tblIncidents.Cat3 = True
WHERE (((tblIncidents.Category)="(Cat 3) Malicious Code"));
 
UPDATE tblIncidents SET tblIncidents.Cat4 = True
WHERE (((tblIncidents.Category)="(Cat 4) Inappropriate Usage"));

Open in new window

Avatar of Jim Dettman (EE MVE)
<<Based on reporting requirements, a major change to my table structure results in a need to transfer data from a single combobox to individual checkboxes.>>
  Just to be a bit clearer on something; reports can be based on a query, which can give you a different view of the data other then the way it is physically stored.
  In your table design, you want everything normalized (you follow a few rules for good relational design) so your database works well and you can process the data easily.  
 You can then use queries to "flatten" data (de-normalize), substitute, change, modify, etc data as needed for reporting or with forms.
 Relational databases are designed to work with sets of records and everything revolves around that.  As Jim Penn already pointed out, if you added another category, your table design wound need to change.  The repeating fields in tblIncidents is an indication that these should be broken out into a sperate table.
But looking at your database, it looks like the catagory fields are just there to tell you which category the incident fell into?  In other words an incident can only have one category and you just need to know which one?
Your category table should look like this:
tblCategory
CatID - Number - PK
Description - Text (50) -  Current 'CatergoryID'
Definition - Memo
and tblIncidents then would have:
IncidentID - Autonumber - PK
CaseNumber
OpenDate
...
CatID - Long - FK to tblCategory


  If you needed the description, you would use a query to join the two tables together and fields from both tables would be available for output.

  With the setup above, CatID would serve as the number, and you could do this on a report or in a query:

Cat1: IIf([CatID]=1,True,False)
and end up with a "field" on a report called Cat1 showing true/false, yes/no, or serve as an input for a check box.
HTH,
JimD.
Can you show us a few dozen rows of the current table using Attach Code Snippet below?
I gotta say...
You've totally lost me!  I only know enough about Access to "make me dangerous"
I know that I would love to use an option group in those spots where I need my users to make a choice
BUT
I have no idea how to take that stored data and merge it into the mandated Word Document.  Attached is a very small portion of my existing database as well as the report that I need to fill out.
If you can show me what you mean, I would greatly appreciate it.
 

ISSIR-v2-Template.doc
Empty-Sample.mdb
ASKER CERTIFIED SOLUTION
Avatar of Kev
Kev
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial