how would you do this? (1 Viewer)

[dreamdelerium]

heres the scenario: i have a table with about 100 records. each record has 50 fields. i need to create a function (or a couple of functions) that will go through every record and compare the field in column 2. if the there is more than one that has the same value i want to traverse the rest of the columns to compare there values (ie between records that are equall in column 2, compare fields in column 3 do something, then move to column 4 do something... column 50 do something).

eventually i want it to see if the values in each column arent equal between records. if not, i want to make a composite of the multile records, based on a list of rules (ie, if across all records in column 3 the value = 1 then make no changes, if one of the fields does not = 1, then change to two...). but first i just want to get the first part working. anyone have any ideas?

i was thinking of using the getrows function to load the table into an array. im just having trouble moving through the records and doing the comparisons

thanks

 

[jal]

If you have a column called LastName, and you want to find out if a name repeats:

Smith
Smith

Then you can probably do this:

Dim rs as DAO.Recordset
set rs = CurrentDb.OpenRecordSet "SELECT Count(LastName) as theCount, LastName FROM Customers GROUP BY LastName HAVING Count(LastName) > 1"
if rs.RecordCount > 0 Then ..... 'this column has some dup names.

 

[gemma-the-husky]

1 query to find them all, as a start

how many columns do you want to test?

just put them all into a total query, do a group by on all the fields, and add the id column as a count, then select rows with count>1

this will identify the rows that HAVE duplicates, and the suplicate data - then you can investigate the detailed rows.

 

[dreamdelerium]

that might work. so, after i pick out the records that are duplicates, how would i move to the next column and compare their values?
ie:
table:

LastName Gender City State
smith M City1 PA
smith f City1 PA
smith f City4 NJ
Jack M City2 NJ

i want to pick the records that have duplicates (here it would be smith). if there are duplicate move to next column and compare all gender column that have smith. if one of them is male then do something, if they are all male, do something else, if one is femal... then move to city and do the same

of course i wouldnt know to look for smith, only to look for duplicates.

thanks
jason

 

[jal]

It's still hard to understand you, so let's take this one step at a time. My query will give you a recordset with all the duplicates, however, my result set will only have two columns - it won't have the male/female column. If your source data is this:

Smith
Smith
Jones
Jones
Jones

Then my result set would be

Smith....2
Jones....3

One question I have is, is it possible for your data to have three Jones? Or the max is two? And suppose one is male, the other female, what exactly do you want to do next?

 

[dreamdelerium]

im sorry, i guess im not so good at describing problems. i appreciate your help.

yes, its very possible to return more then two rows (i can return 1-4 rows, actually). i need to be able return all columns. let me try to explain again:

table:
LastName Gender City State
smith M City1 PA
smith f City1 PA
smith f City4 NJ
Jack M City2 NJ
Jack M City2 NJ
Jones M City2 NJ

1. i want the function to put the table into an array (or recordset).
2. then, go through all records and find those records that have duplicates of the first column (in this example, lastName).
3. if there are duplicates (say all smiths) then compare each of the fields in the next column (gender) and do a comparison (example, move to column gender. if any of the gender are male then the new value is male. if any is female then the new value is female) this is just an example. in my real table is list of questionare response. there can be 1-4 questionares from a facility. there are 56 facilities. i need to make 1 composite recordset from those 1-4 questionares in each facility. each column will have its own rules. for example, column 13 has possilbe values of 1,2,88 (1=yes, 2= no, 88= dont know). if any of the 1-4 responses =1 then the value for the new composite recordset will be 1


4. now put that new row into a new array.
5. now have the array replace the old table so that there is no duplicates and the composite values are in the fields

i hope i was able to make more sense. maybe theres an easier way to do this and im just making this more complicated? thanks again

 

[gemma-the-husky]

i still think you can do what i said (and Jal in a different way) (but use it differently)

do a totals query to identify all the different combinations you have

then save these into a different table (with a maketable query)

------
in general always try and find a way to get a bulk query to retrieve what you want - its safer, quicker, and easier

 

[jal]

I am trying to improve on my first query because, even though it gives you a list of repeating last names, it does not return the entire record.

SELECT Customers.*
FROM Customers as C
INNER JOIN
(
SELECT LastName FROM Customers GROUP BY LastName HAVING Count(LastName) > 1
) as RepetitiveNames
ON RepititiveNames.LastName = C.LastName
ORDER BY C.LastName

Suppose you save this query as qryGetRepetitiveNames.

Dim rs as DAO.Recordset
set rs = CurrentDb.OpenRecordset "qryGetRepetitiveNames"

Chances are this recordset is not updateable, but at least it's a start. You can loop throug it checking to see if any are male. You said if any are male then the new value is male. Are you saying the whole recordset is "male" if one row is male? Or do you rather mean that all the Smiths are male if one Smith is male, and all the Jones are female if one Jones is female?