Array recordset (1 Viewer)

[sha7jpm]

Hi all,

I have a table with multiple records per individual..
I need to search through all of their results to flag up those in a query that do not match the approved list..

what is the best way to do this?
I am trying a recordset loop but am struggling a bit...

here is my code so far

Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String
Dim strPscale As String

strSQL = "SELECT Eng_Speaking, Eng_speaking from qry_Sub1_crosstab_Crosstab;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Me.Text1 = ""

Do While Not rst.EOF
strPscale = rst.Fields("Eng_Speaking") & ";"


rst.MoveNext

Loop
Me.Text1 = strPscale
Set rst = Nothing
End Sub


but I need to somehow incorporate an array into this to check the score...

any ideas?

ta

John

 

[Pat Hartman]

Sorry, I don't understand your question. I don't understand why the same field is listed twice in the select clause of the query. I don't understand why you are concatenating values into the variable. Rather than processing the crosstab, you might find it easier if you processed the underlying recordsource. You may be able to do the calculation you want in a query and not need code at all.

 

[The_Doc_Man]

This question appears (prima facie) to be "flat-file" oriented. Your database might not be properly normalized if you have to do this. Please consider a bit of study on "normalization." It will make your subsequent work much easier.

The reason I say that is that you are describing somethat that smells suspiciously like what we call a "repeating group" - which means the table isn't normalized. The other possibility is that you allow duplicate records for individuals, which means you will not be able to establish relationships involving the table with duplicates as your base table. You can't establish a one-to-many relationship when neither side of the "-to-" has a usable prime (no dups) key.

Stated another way, you are running into problems because of data design. Fix the design and some of your problems will go away because they can no longer happen.

 

[sha7jpm]

ta!

dear both, thanks for the replies..

apols if my query was a bit garbled.. we were left in the lurch by a colleague who went on holiday without finishing his java data checking applet, so I had to come up with a quick solution.. we had an xml import into access which after a crosstab put the data into the correct format.. I then had the bright idea of cycling through the results to flag up rogue entries.

After posting, I did indeed scrap it and use SQL to write a query that performed the same function as the recordset. with more time I would have looked at normalizing the dbase but panic buttons were being put left right and centre..

for future note if you are project managing and have a risk log ensure that you put in.. dbase designer could split up with wife and run away with new girlfriend for snap holiday whilst not doing any work and speaking in hushed tones down the phone all day during a 2 wk period!!

thanks again

john

 

[The_Doc_Man]

split up with wife and run away with new girlfriend for snap holiday whilst not doing any work and speaking in hushed tones down the phone all day during a 2 wk period

Where I work, the part before the "whilst" doesn't seem to be required for the other part to occur anyway.