Append Query Help to fix an Un Normalized table 1

[iuianj07]

Hello guys,

I have a current db that has a table which is not normalized. Currently it is setup as (well these are just fields that relate to the append query):

WorkFlow table:

WorkFlowID
Test1NOIDSCR
Test1NCFDSCR
Test2NOIDSCR
Test2NCFDSCR
Test3NOIDSCR
Test3NCFDSCR

So obviously this is not the normalized way, because a workflowID will always need to have at least one test NOI and NCF, and Test 2 and 3 is only there in case we need to re-test that certain workflowID.

so I created new table with this structure:

tblTestResults

TrID -- PK
WorkFlowID -- PK of WorkFlow table
NOIDSCR
NOIDSCR

then I wrote an append query to insert all WorkFlowID and its Test1 NOIDSCR and Test1 NCFDSCR:

INSERT INTO
  tblTestResults (WorkFlowID, NOIDSCR, NCFDSCR)
SELECT
  Workflow.WorkFlowID,
  WorkFlow.[Test 1: NOI_DSCR],
  WorkFlow.[Test 1: NCF_DSCR]
FROM
  WorkFlow;

And it did what I want, my question now is how will I append records for WorkFlowIDs that have values in Test2NOIDSCR Test2NOIDSCR Test3NOIDSCR and Test3NOIDSCR?

My guess would be (since I doubt myself) is to write this query:

INSERT INTO
 tblTestResults ( WorkFlowID, NOIDSCR, NCFDSCR )
SELECT
 WorkFlow.WorkFlowID,
 WorkFlow.[Test 2: NOI_DSCR],
 WorkFlow.[Test 2: NCF_DSCR]
FROM
 WorkFlow
WHERE 
 WorkFlow.[Test 2: NOI_DSCR] Is Not Null
AND
 WorkFlow.[Test 2: NCF_DSCR] Is Not Null;

is this the right approach? and just do the same for Test3?

With this new table structure, I am going to create a subfrm and put it on the main form and link it via WorkFlowID and set as continous form, so going forward it will just create a child record whenever a WorkFlowID needs to be tested more than once...

But my main question is how would I be able to append Test2etc.. and Test3etc...

Any help is greatly appreciated.

Thank you,

 

[dhookom]

I would create a table like:
[tt]
tblWorkFlowDetails
================
WorkFlowDetailID autonumber primary key
WorkFlowID foreign key to existing table
WorkFlowCategory
WorkFlowDate I think you are storing dates but you didn't say
WorkFlowCatNum Don't know why this is needed:
[/tt]
Then create a normalizing union query:

SELECT WorkFlowID, "NOIDSCR" as WorkFlowCategory, Test1NOIDSCR As WorkFlowDate, 1 As WorkFLowCatNum
FROM WorkFlow
WHERE Test1NOIDSCR Is Not Null
UNION ALL
SELECT WorkFlowID, "NOIDSCR", Test2NOIDSCR, 2
FROM WorkFlow
WHERE Test2NOIDSCR Is Not Null
UNION ALL
SELECT WorkFlowID, "NOIDSCR", Test3NOIDSCR, 3
FROM WorkFlow
WHERE Test3NOIDSCR Is Not Null
UNION ALL
SELECT WorkFlowID, "NCFDSCR", Test1NCFDSCR, 1
FROM WorkFlow
WHERE Test1NCFDSCR Is Not Null
UNION ALL
SELECT WorkFlowID, "NCFDSCR", Test2NCFDSCR, 2
FROM WorkFlow
WHERE Test2NCFDSCR Is Not NullUNION ALL
SELECT WorkFlowID, "NCFDSCR", Test3NCFDSCR, 3
FROM WorkFlow
WHERE Test3NCFDSCR Is Not Null;

Then create an append query based on the union query to append records into the normalized table.

Duane
Hook'D on Access
MS Access MVP