Recordsets 2

[Knicks]

I have 2 tables in Access2000 that are linked via the ID field. Table 1 has a (1) to many relationship with table 2. I need to sort of de-normalize the database so that fields a, b, and c in table 1 are filled with records from table 2 where the ID's match. Essentially what I am trying to do is take those records in table 2 and fill in table the primary, secondary, and 3rd diagnosis for a medical database where the Id's match.

I need to open 2 recordsets and I believe an SQL statement and a counter. Any help getting started in the right direction would be great.

Thanx in advance

 

[JoyInOK]

Go to design view of your Diagnosis table, and see if the datatype of the field VisitID is a text field, or a number field. Mine is a text field. If yours is a number field, replace
strSQLDiag = strSQLDiag & "'" & rstVisits3.Fields(0) & "'));"
with
strSQLDiag = strSQLDiag & rstVisits3.Fields(0) & &quot);"

and take out those lines we added to get strSQL to print (or you can just turn them into comments by putting a ' in front of each line.

 

[Knicks]

I still get an error "3021" No current Record

rstDiagnosis.MoveLast 'force Access to get accurate recordcount

 

[JoyInOK]

What is the data type of VisitID in the Diagnosis table? (Text? Number?). We know it is autonumber in the Visits table.

 

[JoyInOK]

I had a stray '3' in the middle of that last line of code. The table name is 'Visits' not 'Visits3'

Also, when you get that error and you hit debug, type
Print strSQLDiag
and
PRint rstVisits.Fields(0).name
and
print rstVisits.fields(0).value
in the immediate window, to see what field name and criteria are being used that result in no records.

 

[scriverb]

Create a UNION query with the following SQL:
SELECT Diagnosis.VisitID, First(Diagnosis.DiagID) AS DiagID, 1 AS [Order]
FROM Diagnosis
GROUP BY Diagnosis.VisitID, 1
ORDER BY Diagnosis.VisitID, 1
UNION
SELECT Diagnosis.VisitID, Last(Diagnosis.DiagID) AS DiagID, 3 AS [Order]
FROM Diagnosis
GROUP BY Diagnosis.VisitID, 3;
and name it qryFirstAndLast

Create another UNION query with the following SQL:
SELECT qryFirstAndLast.VisitID, qryFirstAndLast.DiagID, Diagnosis.Diagnosis AS Diag, qryFirstAndLast.Order
FROM Diagnosis INNER JOIN qryFirstAndLast ON Diagnosis.DiagID = qryFirstAndLast.DiagID
ORDER BY qryFirstAndLast.VisitID, qryFirstAndLast.Order
UNION
SELECT Diagnosis.VisitID, Diagnosis.DiagID, Diagnosis.Diagnosis as Diag, 2 AS [Order]
FROM qryFirstAndLast RIGHT JOIN Diagnosis ON (qryFirstAndLast.VisitID = Diagnosis.VisitID) AND (qryFirstAndLast.DiagID = Diagnosis.DiagID)
WHERE (((qryFirstAndLast.DiagID) Is Null) AND ((qryFirstAndLast.VisitID) Is Null));
And name this query qryOrderedDiagnosis

This provides us with a Recordset of up to three records per visit in the order of diagnosis 1,2,or 3. We can now use this query to create a temporary table. Use the following SQL to create a new query:
SELECT qryOrderedDiagnosis.VisitID, qryOrderedDiagnosis.DiagID, qryOrderedDiagnosis.Diag, qryOrderedDiagnosis.Order INTO tblTempOrderedDiagnosis
FROM qryOrderedDiagnosis;
Name this query qryMTOrderedDiagnosis

We can now update the appropriate fields in table Visits with the Diagnosis data. Use the following SQL to create an ACTION query(UPDATE) that will post the diagnosis to the appropriate fields:
UPDATE Visits INNER JOIN tblTempOrderedDiagnosis ON Visits.VisitID = tblTempOrderedDiagnosis.VisitID SET Visits.[Primary] = NZ(Switch([tblTempOrderedDiagnosis]![Order]=1,[tblTempOrderedDiagnosis]![Diag]),[Visits]![Primary]), Visits.Secondary = NZ(Switch([tblTempOrderedDiagnosis]![Order]=2 And [tblTempOrderedDiagnosis]![Diag]<>[Visits]![Primary],[tblTempOrderedDiagnosis]![Diag],[tblTempOrderedDiagnosis]![Order]=3 And IsNull([Visits]![Secondary]) And [tblTempOrderedDiagnosis]![Diag]<>[Visits]![Primary],[tblTempOrderedDiagnosis]![Diag]),[Visits]![Secondary]), Visits.Third = NZ(Switch([tblTempOrderedDiagnosis]![Order]=3 And Not IsNull([Visits]![Secondary]),[tblTempOrderedDiagnosis]![Diag]),[Visits]![Third]);
Name this query qryUpdVisits

To execute this process use the following code:
DoCmd.SetWarnings False
DoCmd.OpenQuery &quot;qryMTFirstAndLast&quot;
DoCmd.OpenQuery &quot;qryUpdVisits&quot;
DoCmd.SetWarnings True

This process will create a temporary table of Diagnosis and number their order 1,2,3. The last query will use that table to update the table Visits with the Diagnosis data. If there is only 1 diagnosis for a Visit then only the Primary is updated. If only 2 diagnosis per visit then only Primary and Secondary. If any of the diagnosis for a visit are duplicated it will be skipped.

Give this a try as with my text data it will give you exactly what you want. Bob Scriver

 

[scriverb]

Sorry after all that checking I missed a typo:
To execute this process use the following code:
DoCmd.SetWarnings False
DoCmd.OpenQuery &quot;qryMTOrderedDiagnosis&quot;
DoCmd.OpenQuery &quot;qryUpdVisits&quot;
DoCmd.SetWarnings True

This is just a copy and paste process so give it a try.
Bob Scriver

 

[Knicks]

ScriverB

Wow, nice piece of work. I have created all of the above. I am getting an error message when running

Run Time Error &quot;3079&quot; The Specified field Diagnosis.diagnosis could refer to more than 1 table listed in your from clause of your SQL statement

DoCmd.OpenQuery &quot;qryMTORDEREDDIAGNOSIS&quot;

 

[Knicks]

cont.

If I try to just run the sql query &quot;qryOrderedDiagnosis&quot; on its own I get the same error message.

Thank you very much for all the help so far!!!!

 

[Knicks]

Ok

I changed diagnosis.diagnosis to diagnosis.diag and that cleared up the error messages.

When running it everything appears fine. I went to check my visits table and it seems to have filled out the primary field only. I almost certain there should be a couple of visits that had at least a secondary diagnosis too.

 

[Knicks]

I like the simpler approach with the SQL but I think there is some flaw in here. The temporary table shows just as many records with an order of (1) or (3) and with the same diag code number often. Very few (2) appear though I would imagine that there should be more of them than (3). I am wondering if so how nulls might be throwing the SQL statement off.

 

[scriverb]

The idea here was to create a table of the first, second, and third diagnosis and number them accordingly. The queries that I created first of all create a recordset of the First and Last which correspond to 1 and 3 in the order column. Now if there were only 2 diagnosis for a particular visit then 1 and 3 is all you will get as the first is number 1 and the last is number 3. When I then added the number 2's there wouldn't be any. That is why you see visits with 1 and 3 with no 2's. Don't worry because it is okay to have it that way because when I actually update your Visits table I move the 3's up to 2 if there wasn't a #2 on its own.

The second issue is you see a number of visits with 1 and 3 diagnosis where the diagnosis is the same. This will also happen when building the temporary table as when you have a visit with only 1 diagnosis the queries will build a 1 and a 3 record off of the same diagnosis record becuase the First and Last aggregate function will grab the same record. This is also okay because if there are duplicate diagnosis records as in this example the second one is ignored and not updated.

Please check the above instances comparing the questionable records in the temporary table to the final results for the visits to verify that what I have said should happen is in fact happening. Also, check out a number of visits that have 1, 2, and 3 diagnosis records. Make sure that they are updated properly.

If you have any further problems please get back to me as the data tables that I created are limited but I think I have created all of the above scenarios to test out these issues and they should update your tables properly. This was a fun and challenging project.

Bob Scriver

 

[scriverb]

Thanks for finding that typo in qryOrderedDiagnosis I had found that and fixed it in my queries when I ran but had already pasted it into the posting by hadn't sent it on yet. Sorry.

New qryUpdVisits SQL:
UPDATE Visits INNER JOIN tblTempOrderedDiagnosis ON Visits.VisitID = tblTempOrderedDiagnosis.VisitID SET Visits.[Primary] = NZ(Switch([tblTempOrderedDiagnosis]![Order]=1,[tblTempOrderedDiagnosis]![Diag]),[Visits]![Primary]), Visits.Secondary = NZ(Switch([tblTempOrderedDiagnosis]![Order]=2 And [tblTempOrderedDiagnosis]![Diag]<>[Visits]![Primary],[tblTempOrderedDiagnosis]![Diag],[tblTempOrderedDiagnosis]![Order]=3 And IsNull([Visits]![Secondary]) And [tblTempOrderedDiagnosis]![Diag]<>[Visits]![Primary],[tblTempOrderedDiagnosis]![Diag]),[Visits]![Secondary]), Visits.Third = NZ(Switch([tblTempOrderedDiagnosis]![Order]=3 And Not IsNull([Visits]![Secondary]) And [tblTempOrderedDiagnosis]![Diag]<>[Visits]![Primary] And [tblTempOrderedDiagnosis]![Diag]<>[Visits]![Secondary],[tblTempOrderedDiagnosis]![Diag]),[Visits]![Third]);

This updated SQL makes sure there are no duplicate diagnosis between the three diagnosis fields in table Visits.

Will be waiting for your test results.



Bob Scriver

 

[Knicks]

Bob,

I updated that last SQL statement and it pretty much ran like before without any error messages but it is only filling in the Primary diagnosis. I checked a random primary diagnosis and it appears fine. But the code is not updating any secondary diagnosis or third diagnosis.

Thanx for all your help!

 

[scriverb]

Before run this again make sure that you run a query to clear out any previous Diagnosis data field entries in the table Visits

UPDATE Visits SET Visits.[Primary] = Null, Visits.Secondary = Null, Visits.Third = Null;
as the code is checking for Null values in these fields to determine if there is a 2nd and 3rd diagnosis already entered. you have to run this query before each run of your process.

Also remember that you must run both the make-table query and the update query each time. If you are just running the qryUpdVisits you would still be using the old temp table each time. The database that I have is in fact updating 1, 2 and 3 diagnosis fields. It is not updating duplicate diagnosis if they exist.

Let me know. If you still are having problems send me a sample database with just the tables and queries as you have them setup and I will take a look for you. See my email in my profile.
Bob Scriver

 

[Knicks]

Yes, I have been setting the primary diagnosis back to Null and deleting the temporary table and still the only diagnosises being set is the primary in visits.

I have a form with a command button on click that runs the code you specified

DoCmd.SetWarnings False
DoCmd.OpenQuery &quot;qryMTOrderedDiagnosis&quot;
DoCmd.OpenQuery &quot;qryUpdVisits&quot;
DoCmd.SetWarnings True

I can see 2 queries being run in the bottom of my screen, no error messages, runs pretty quickly, yet only the primary diagnosis is being set. The diagnosis.diag is a number field that has a corresponding lookup table for diagnosis description...I don't think this should be a problem. The only difference is my visits table is actually called visits3 and I changed the code wherever referenced.

I can send the data to you if you like
Thanx for all your help

Charles Falk

 

[scriverb]

Sure, send the data and I will take a look. Bob Scriver

 

[scriverb]

I received the data files via email. Thanks.

The problem seems to be with your data field types between your two tables. The Visits table has the three Diagnosis fields Primary, Secondary, and Third as Text(50). While your Diagnosis table has the Diag field type as a Number(Long Integer). Now the simple fix is to sync these fields up by changing the number data type to a Text(50). Actually you could make it a Text(10) or less depending upon the maximum size of your diagnosis codes. ACCESS will convert them for you after you save the table.

When you do that you will get everything to work for you.

If for some reason you want to leave it as a number than we can modify the query qryMTOrderedDiagnosis to this SQL:
SELECT qryOrderedDiagnosis.VisitID, qryOrderedDiagnosis.DiagID, CStr([qryOrderedDiagnosis]![Diag]) AS Diag, qryOrderedDiagnosis.Order INTO tblTempOrderedDiagnosis
FROM qryOrderedDiagnosis;

This query would then convert the Numeric(Long Integer) Diag data to a String(Text) in the temporary table. By doing that all of the comparison code in the qryUpdVisits will then work as before this query was having problems with type comparisons between the two tables diag fields.( text vs Long Integer).

I recommend that you just change your datafield type for Diag to the Text(10) size. Also, you could adjust the three fields in Visits to Text(10) also rather than 50.

One more thing the data integrity of you files should be cleaned up. The Diagnosis table has blank records meaning records with blank VisitID codes(necessary to match the Visits table) and blank Diag codes. Also, the Visits table has some blank VisitDate fields. These kind of records cause all sorts of problems when performing updates.

Good luck with your project. Bob Scriver

 

[JoyInOK]

Good advice, Bob. Looks like the datatype of the Diag field is what was throwing off the code above. My Diag field was text. Changing he Diag field to text, and correcting the null values, would probably make the code work as well.

 

[scriverb]

Let me know if you need any more assistance. This was a interesting problem to work on. Bob Scriver

 

[Knicks]

Bob,

Thanx for all your help! I knew the problem was difficult for me to figure out. I wasn't sure how challenging it would be to the programmers out on Tek-Tips. I learned a bunch of great new code and I got my quirkly denormalized problem solved. I love this site and the wealth of professional advice on it.

I'd also like to thank JoyInOk for getting this started and keeping the interest up. It was great to see the 2 different approaches one using more VBA, the other using SQL. From a novice perspective, the SQL was simpler for me to understand with its structured language. I hope to be a little hipper using both in the future.

Thanx again to both of you!