Cross referencing record sets 2

[ninash]

Hi All

I have defined 3 record sets now I need to use 1 field in a record set to trap data related to that field in another record set. Once this is done then I can use the data in the 3rd record set to check the contents of the trapped records set data. That is very confusing....

First Record Set
Contains a field called [Survey ID].

Second Record Set
also has [Survey ID] Need to trap the data containing the same information as [Survey ID] from the first record set.

Third Record Set
Contains all the fields from the second record set with the exception of [Survey ID].

Once the trapped data is ready I need to loop through the records checking field [QuestionRef] against the second record set

If no match is made in the current search ([Survery ID] and [QuestionRef]) then the record from the Third record set needs to be added to the table that that record set defined from with the current [Survey ID] search data being pasted into field [Survey ID].

Then the whole process needs to repeat for the next [Survey ID] in the first record set.

This is database designed by someone that has left the company and is not very well written. If anyone can help me with the above process please do as I am totally lost.

Tony

 

[LittleSmudge]

Set rst1 = db.OpenRecordset("SELECT [Survey Id] FROM tbl1 ;&quot

WHILE NOT rst1.EOF
' If Survey Id is a number use
Set rst2 = db.OpenRecordset("SELECT field1, field2, etc FROM tbl2 WHERE [Survey Id] = " & rst1![Survey Id])
' If Survey Id is text use
Set rst2 = db.OpenRecordset("SELECT [Survey Id], QuestionRef, field3, field4, etc FROM tbl2 WHERE [Survey Id] = '" & rst1![Survey Id] & "'&quot

' Here my interpretation of what you want gets hazy ( No. to be honest I've totally lost it. )
You now have the 'Second Recordset'. If you want the third recordset to be the same as the second but without the [Survey Id] field then just remove [Survey Id] from the list of fields between SELECT and FROM
If not - what is the 'Third RecordSet meant to be?

If this is the 'trapped data' where do you now compare the QuestionRef field to?



rst1.MoveNext
Wend


G LS

 

[ninash]

Sorry littlesmudge
There are 3 tables

Survey details (recordset 1) [Survey ID] only required

Questionnaire (recordset 2) [Survey ID] & [QuestionRef] only required

Standard Questions (recordset 3) All fields required for update of questionnaire + the current [Survey ID] to update that field in Questionnaire

First I need to get the first [Survey ID](rst1) then compare that to [Survey ID] in rst2.
Next I need to compare the [QuestionRef] (rst2) to the [QuestionRef] in rst3.

If no match is found on the QuestionRef in rst3 then a copy of the current rst3 data needs to be Added to Questionnaire and the current [Survey ID] rst1 or rst2 (don't matter which as they are the same at this point) will be pasted into the Survey ID field for the new record in questionnaire.

I don't know if that is any clearer

Tony

 

[TrojanRabbit]

Survey Details, Questionnaire, Standard Questions.
My interpretation: Questionnaire refers to the questions for survey [Survey ID]. The questions are in Standard Questions, referred to by [QuestionRef]. If [QuestionRef] is not found in Standard Questions, the current Standard Questions record will be put into Questionnaire for [Survey ID].

Is this correct? How do you determine the current Standard Questions record?

 

[ninash]

Hi TrojanRabbit,

You have got it all right apart from the part listed below.

If [QuestionRef] is not found in Standard Questions, the current Standard Questions record will be put into Questionnaire for [Survey ID].

Should read.
If [QuestionRef] is not found in Questionnaire, the current Standard Questions record will be put into Questionnaire for [Survey ID].

I hope you can help this is beginning to get very silly.
the writting it back to the table is the thing that is really worrying me.

Tony

 

[TrojanRabbit]

Oh, OK. So, you have the survey and the questions. You need the questions put in Questionnaire for that survey if they're not already there.

You can use FindFirst with rst2. As LittleSmudge said, if Survey ID is text type, use ' or ""; if number type, use neither. I'll code as if its type is text. I'm assuming that you will only have one record for each survey ID.

rst2.FindFirst "[Survey ID] = '" & rst1![Survey ID] & "'"
'Add it if not already there
If rst2.NoMatch Then
rst2.Addnew
rst2![Survey ID] = rst1![Survey ID]
rst2![<field>] = rst2![<field>]
...
rst2.Update
rst2.FindFirst &quot;[Survey ID] = '&quot; & rst1![Survey ID] & &quot;'&quot; 'keep rst2 on right record
End If

If IsNull(rst2![QuestionRef]) Or rst2![QuestionRef] <> rst3![QuestionRef] Then
rst2.Edit
rst2![QuestionRef] = rst3![QuestionRef]
rst2![<field>] = rst3![<field>]
...
rst2.Update
End If

If you have more than one record for the survey ID, you'll naturally have to change the code a little. If I understand correctly, this method sounds like what you need.

 

[ninash]

Hi TroganRabbit,

Right all that makes sence to me even at this hour of the night. There are multiple questions to each Survey ID but I think I can make the loop to handle that. One more question,
the update lines will they write back to the table or will there have to be more code to output the record set into the table.

Sorry that sounds a really silly question but this is something I haven't done before.

Tony

 

[TrojanRabbit]

The Update lines will write to the table. With multiple questions per survey, you can actually cut the code to one FindFirst: rst2.FindFirst &quot;[Survey ID] = '&quot; & rst1![Survey ID] & &quot;' AND [QuestionRef] = '&quot; & rst3![QuestionRef] & &quot;'&quot;. Sounds like you know what you want to do.

 

[ninash]

Well what can I say but thanks and here's a star as both of you are anyway.

You have shown me a hole in my knowledge that I now intend to fill.

Thank you both.

Tony

 

[ninash]

Just one last thing... Sorry
If I wanted to exclude something from my record set, let say a field called type held a value of &quot;hot pot&quot; and I didn't want any records with hot pot in that field.

How would I do that???

Thanks again

Tony

 

[johnaregan]

ninash
You could use Filter in the recordset like:
rs2.Filter = &quot;type <> 'hot pot'&quot;

John