Copy record, leaves out certain fields

[lloydmav]

Ok I have a massive task to perform as the main function of this database I'm developing however there is no way I'm going to figure this out without the help of you guys.

On a form I will have a TextBox with a "Change Type" value. Now when I click a button I need it to search the record in a different table with that change type. This record will then have about 50 Yes/No fields. What I want is for a record to be created in a different table with the change type value and action plan ID and then all of those 50 fields which have been checked 'Yes'. Leaving out all the fields that are checked "No". Is this possible?

Basically the original table shows all the change types and each Yes/No field for a record determines whether a task is recommeded for that chage type, its like a matrix!, it is not an ideal design but the only one that serves the purpose. This table serves as the master recommended tasks against change types.
The record will then be copied into this new table but only the fields (tasks) fields that are "Yes" because this means they are confirmed as needed by the manager. So in the new table the action plan will be specific to the action plan whereas the master table will be undisturbed ready for the next action plan of the same change type.

Can this be done? Is there anyone who can help me with this?

Thanks

 

[The_Doc_Man]

This record will then have about 50 Yes/No fields. What I want is for a record to be created in a different table with the change type value and action plan ID and then all of those 50 fields which have been checked 'Yes'. Leaving out all the fields that are checked "No". Is this possible?

Your description is a bit complex, so I'm guessing from incomplete data. Having provided appropriate disclaimer, I think the answer is NO, not possible.

You can choose to copy or not copy records, but Access does not allow you to pick-and-choose fields within a record. There WILL be a value in EVERY field in EVERY record (if you count NULL as a value). A record must have a constant format. )Do not confuse the varying size of MEMO fields with other types of format changes.) If field A is in any record in the table, it is in ALL records in that table, even if only null. And yes, a Yes/No field CAN have a NULL value which differs from either YES or NO under at least some circumstances.

If this is not what you meant, then try asking your question a different way.

If I were working this problem, I might take a different approach.

For each change type and action-plan ID and "checked" field, I would make an entry in a table structured like:

tblChgActionChecks
-- Change type
-- Action-plan ID
-- {code number corresponding to checked box}

where the table either has NO prime key, or all three keys form a single combo-key. You would make NO entry for any box not checked. Thus, the yes/no quality becomes a present/not-present quality in the new table. I.e. you can do a query such as DCount( "[ChgTyp]","tblChgActionChecks", "[ChgType] = " CStr(change-type-code) & " AND [ActID] = " & CStr(action-plan-ID) & " AND [BoxCode] = " & CStr(check-box-code-number) )

Or something like that.

 

[RV]

Why creating a whole new table containing data which you already have?

Create a query to retrieve the data you need for your operations plan based and your masterplan data.

RV

 

[lloydmav]

Thanks for all the replys,

PAT, I will go over my design and relationships again to see if I can improve the normalisation.
DocMan thats a good idea with the new table and one I will try to implement.

RV the reason for creating a new table is because;
There are a list of tasks that are generally used for information change. A change type must have a suggested list of tasks for that change type. Now when an action plan is generated the requirement is that the manager must beable to say whether each task is applicable or not! This must be created in a new table because when that change type is required again we need the original list of the tasks which are applicable.