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]

Could you post sample data for the first few rows of each table (with field names) and an example of what those rows table one would look like after the operation? That would really let me know if I understand what you're trying to do. Does it matter which diagnosis is primary and which is secondary, or is it first come, first serve?

 

[Knicks]

Hi, thanx for responding.

Table 1: Visits
Table 2: Diagnosis

Linked by VisitID (where you have multiple Diagnosis per visit)

Visits fields:

VisitID (autonumber primary)
Primary (currently empty awaiting update from Diagnosis tbl)
Secondary (same as above)
Third (same as above)

Diagnosis fields:

DiagID (autonumber primary)
VisitID (used to link to Visits tbl)
Diag (diagnosis given i.e 311.4, V68.1)

The big answer to your question is yes it matters, I would like to take the first record in the diagnosis table with matching VisitID to be the Primary, the second record to be the Secondary, and the third to be the Third. Some matching records may only have 1 diagnosis record to go into the Primary, other records may have 2 or 3.

I'm pretty sure I will need a counter and somehow I've will need to open 2 table record sources, unless that is impossible I suppose then I will need to create query with both tables and open that recordset? I will need to create some sort of where statement for the matching VisitID's.

Let me know if this helps or if you need more
Thanx!!

 

[JoyInOK]

Try this in the click event of a command button on a form.
Read all the comments before running the code.

Private Sub cmdUpdateDiagnosis_Click()
' Update the primary, secondary and third diagnois codes for a given visit
'be sure to set a reference to Microsoft DAO 3.6 if it is not already referenced
'in your db
Dim rstVisits As DAO.Recordset
Dim strSQLVisit As String
strSQLVisit = "SELECT Visits.VisitID, Visits.Primary, Visits.Secondary, Visits.Third FROM Visits;"
Dim rstDiagnosis As DAO.Recordset
Dim strSQLDiag As String
strSQLDiag = "SELECT Diagnosis.DiagID, Diagnosis.Diag, Diagnosis.VisitID FROM Diagnosis WHERE (((Diagnosis.VisitID)=" ' this is not a complete SQL statement. We will concatenate the diagnosisID we're
'looking for within the loop

Set rstVisits = CurrentDb.OpenRecordset(strSQLVisit)
rstVisits.MoveLast 'force Access to get accurate recordcount

rstVisits.MoveFirst
Dim intVisit As Integer 'integer to loop through recordset
For intVisit = 1 To rstVisits.RecordCount
'set the SQL statement to pull all the diagnosis fields for this visit
strSQLDiag = strSQLDiag & "'" & rstVisits.Fields(0) & "'));"
'open a recordset with all the diagnosis fields for this visit
Set rstDiagnosis = CurrentDb.OpenRecordset(strSQLDiag)
rstDiagnosis.MoveLast 'force Access to get accurate recordcount
rstDiagnosis.MoveFirst
Dim intDiag As Integer 'loops through the diagnosis records
For intDiag = 1 To rstDiagnosis.RecordCount
If Not IsNull(rstDiagnosis.Fields("Diag&quot.Value) Then
rstVisits.Edit
'this presumes that the fields in the table Visits are in the order you specified,
'so that the autonumber is field(0), the primary is field(1), etc.
rstVisits.Fields(intDiag).Value = rstDiagnosis.Fields("Diag&quot.Value
rstVisits.Update
End If
strSQLDiag = "SELECT Diagnosis.DiagID, Diagnosis.Diag, Diagnosis.VisitID FROM Diagnosis WHERE (((Diagnosis.VisitID)=" ' reset the partial SQL statement so we can concatenate the next DiagID

rstDiagnosis.MoveNext
Next intDiag
rstVisits.MoveNext
Next intVisit
End Sub

 

[ant33]

Try including something like this:
-----------------------------------------------------------
'Set up SQL to query Diagnosis table
sDiagSQL = "SELECT Diagnosis.VisitID, Diagnosis.DiagID, Diagnosis.Diag" _
& " FROM Diagnosis, Visits" _
& " WHERE Diagnosis.VisitID = Visits.VisitID" _
& " ORDER BY Diagnosis.VisitID, Diagnosis.DiagID"

Set rstDiag = gDBS.OpenRecordset(sDiagSQL)

'If nothing to process - pack up and go home
If rstDiag.BOF Or rstDiag.EOF Then
MsgBox "There are no Diagnoses to process", vbOKOnly
GoTo Exit_thisprocedure
End If


'Open 2nd connection for updating Visits
Set dbsVst = CurrentDb

'Initialise variable used in loop
iLastVisit = 0

'Loop for all Diagnoses
Do While Not rstDiag.EOF

'Read the VisitID of current diagnosis record
iThisVisit = rstDiag("VisitID&quot

If iLastVisit <> iThisVisit Or iLastVisit = 0 Then

'New Visit, so initialise to 1st diagnosis
iDiagnosis = 1

Else

'Same as last visit, so this is next diagnosis
iDiagnosis ++

End If

'Which column on VISTS is to be updated?
sVstCol = Choose(iDiagnosis, _
&quot;Primary&quot;, &quot;Secondary&quot;, &quot;Third&quot

'Build SQL to update VISITS table
sVstSQL = &quot;UPDATE Visits &quot; _
& &quot; SET &quot; & sVstCol _
& &quot; = '&quot; & rstDiag(&quot;Diag&quot _
& &quot; WHERE VisitID = &quot; & iThisVisit

'Update VISITS table
dbsVst.Execute (sVstSQL)

'Move to next Diagnosis
iLastVisit = iThisVisit
rstDiag.MoveNext

Loop

-----------------------------------------------------------
Note that there's no check for >3 diagnoses, but you'll know whether it's necessary.

Also I've just typed this in - so there may be small errors, but I hope you get the drift.

Good Luck

 

[Knicks]

Thanx alot to the above posters. I have a meeting this morning but this afternoon I will try the code and I will get back to you both!

 

[Knicks]

JoyInOK

I tried running it and I get an error &quot;Run Time Error #6, overflow&quot;

The line it highlights is the loop:

&quot;For intVisit = 1 To rstVisits.RecordCount&quot;

Any suggestions? Thank you again! As this code you constructed looks time consuming

 

[beetee]

try a long instead of an int

 

[Knicks]

Beetee

I'm in way over my head here. I do some coding here and there &quot;to get it done&quot;. If you could be a little less cryptic that would help

Thanx

 

[beetee]

sorry 'bout that

your line of code said:

For intVisit = 1 To rstVisits.RecordCount

and you got an overflow... it could be because you have a very large number of records in rstVisits, so instead of saying

dim intVisit as integer

say

dim lngVisit as long

that *might* fix the problem.

 

[Knicks]

Thanx!

It got past that first error and now instead I get an error #3464 &quot;Data type mismatch in criteria expression&quot;

Set rstDiagnosis = CurrentDb.OpenRecordset(strSQLDiag)

 

[beetee]

you need to post what's in strSQLDiag to debug this problem.

Say something like
Debug.Print strSQLDiag

 

[Knicks]

Dim strSQLDiag As String
strSQLDiag = &quot;SELECT Diagnosis.DiagID, Diagnosis.Diag, Diagnosis.VisitID FROM Diagnosis WHERE (((Diagnosis.VisitID)=&quot; ' this is not a complete SQL statement. We will concatenate the diagnosisID we're

 

[beetee]

OK, the SQL string didn't quite make it in.

However, here's how you can debug an SQL string.

You can view a query in SQL view as well as design view. Put together a similar query in design view, then switch to SQL view to see how Access builds the SQL string.

Alternately, you can put in portions of your sql string until you discover exactly which section is causing the trouble.

A data type mismatch means just that, you're comparing strings to integers or some such thing. Often it's due to missing some single quotes in a string expression, e.g.

WHERE [MyField] = this is a test

instead of the correct

WHERE [MyField] = 'this is a test'

 

[Knicks]

The 2nd part that it ties to is pretty complex

strSQLDiag = strSQLDiag & &quot;'&quot; & rstVisits3.Fields(0) & &quot;'));&quot;

I've had trouble before with referencing string expressions, VBA doesn't seem very logical when it comes to it. But the way this is constructed in 2 parts to complete the SQL statement is complex. Not quite sure which part to fiddle with first

 

[beetee]

&quot;SELECT Diagnosis.DiagID, Diagnosis.Diag, Diagnosis.VisitID FROM Diagnosis WHERE (((Diagnosis.VisitID)= '&quot; & rstVisits3.Fields(0) & &quot;'));&quot;

It looks like VisitID is numeric, so you don't need the quotes. This might solve your problem:

&quot;SELECT Diagnosis.DiagID, Diagnosis.Diag, Diagnosis.VisitID FROM Diagnosis WHERE Diagnosis.VisitID= &quot; & rstVisits3.Fields(0) & &quot;;&quot;

I also got rid of some extraneous parenthesis that Access likes to add.

 

[Knicks]

Thanx Beetee!

Got past that one only to encounter a new error: &quot;Run time error 3021. No current record&quot;

Debug
rstDiagnosis.MoveLast

 

[beetee]

ok, now it looks like whatever condition you've entered doesn't match any records.

debug.print the value of rstVisits3.fields(0) and see if it actually matches any Diagnosis.VisitID values.

I'm calling it a night, but I'll check in tomorrow morning.

Go warriors

 

[JoyInOK]

Hi Knicks, sorry to leave you with buggy code. I've been out of the office. I built a baby db with the tables and fields you gave me, and that's how I made this code. Since it worked in my test, let's see if we can figure out what's different in your db.
Here's the trick beetee was referring to. It's really cool and you will use it all the time once you learn it.
If the immediate window is not open in your code window (it looks like a blank frame with the title &quot;Immediate&quot choose to show it by clicking View/Immediate Window.
Add these three lines of code right before
Set rstDiagnosis = CurrentDb.OpenRecordset(strSQLDiag)


debug.print strSQLDiag
set rstVisits = nothing
exit sub

Run the code, then switch to View Code. The value of str SQLDiag is now printed in the immediate window. Copy it, then return to the regular Access window.
Start a new query in design view. From the drop down that lets you choose design view or form view, choose SQL view.
Paste your SQL statement there.
Attempt to switch to form view. Access will give you the same error message as it did when you ran the code, but this time it will highlight the part of the SQL statement that is causing the error. It's probably that I set up one of my fields as data type Text or Number and your version is a different datatype.
Let us know which field it highlights, and we'll modify the code.

 

[Knicks]

Here is what showed up in the Immediate window. When I ran it in SQL I got a data type mismatch. I think we (you) are getting very close to solving the problem


SELECT Diagnosis.DiagID, Diagnosis.Diag, Diagnosis.VisitID FROM Diagnosis WHERE Diagnosis.VisitID='28704';

Diag is a number field in my database as it is used to lookup to a Diagnosis1 lookup table.