Compare two fields each record of table, update record or not in new table

[aacadprogrmr via AccessMonster.com]

Let me see if I can describe the situation I have..
One table. No Primary Keys. (inherited code, gotta love it)
I want to compare records,

if the first field in record one matches the first field in record 2, then
make double sure the last field does NOT match (report error if so) then
'merge these records together by summing field 2(no others) and saving
rec2s field 6 along with the
'entire record 2 (maybe back in the same table??). How I get there is not a
big deal, I may have the wrong approach.
'Here is the data before.

example: Name them field 1 through 10 for this example.

65 1 1 2 2 4594
66 1 1 2 2 4506
66 1 1 2 2 4500
67 2 2 2 2 4511
68 2 2 2 2 4511
68 2 2 2 2 4500
68 3 2 2 2 4508
Here is the data after the function

65 1 1 2 2 4594
66 2 1 2 2 4506 4500
67 2 2 2 2 4511
68 7 2 2 2 4511 4500 4508

Here is the pseudo codeish I have written while thinking hope it helps.

'open rs in db (rs)
do while not eof
initialize empty temp array(ta)
get a record from table save to temparray
get second line from table

if ta.1 = rs.1 then
if ta.6 <> rs.6 then
if isnull(rs.7) then
(rs.2 = ta.2 + rs.2) and ta.6 goesinto rs.7
remove the first of the two records from the table (like by its index??)

elseif isnull(rs.8) then
(rs.2 = ta.2 + rs.2) and ta.6 goesinto rs.8
remove the first of the two records from the table (like by its index??)

else
'both are empty write error to complain and exit
endif
else if
'these should match write error to complain and exit
else 'the first fields do not match, all is good.

loop
cleanup/housekeeping then exit

***Before you roll on the floor laughing at this, I do not write in VBA, I
tend to lean towards C for AutoCAD instead but someone moved the cheese.
I am hoping advancing to the next record at the top of the loop will "save"
my changes and free up my array for more info. I know this stinks there are
holes all in it.
sorry

Thanks in advance!!!

 

[John Spencer (MVP)]

You seem to have several things going on here.

Your sample seems to
-- Keep Column 1,
-- Sum Column 2,
-- retain columns 3, 4, and 5,
-- and then append column 6 to succeeding columns when it differs.

If the values in columns 3, 4, and 5 vary within the group, what do you want
to do?

Also, if you have a pair of identical values based on columns 1 and 6 what do
you want to do?

How about if you have a pair of identical values and then one of more pairs
that are not identical? That is how would you handle this set of records -
exclude the entire set, exclude the duplicates, or some other rule?
68 2 2 2 2 4511
68 2 2 2 2 4511
68 3 2 2 2 4508

You could build a set of queries to do the work, but it could be very slow.
Also, how many records are you going to process and how large is the largest
group of records - you imply up to five records as the maximum in your example.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[aacadprogrmr via AccessMonster.com]

Hi John,

Thanks for your quick response.

Yes, I keep Column 1
Yes, I sum Column 1 (only if there is a match of column 1)
Yes, I retain all columns in between (there are several, I used 3, 4, and 5
for simplicity)
Yes, append column 6...

Columns in between should never vary, but "never" could always crash, so some
error msg here would be good.

Should the record match in both column 1 and 6, the code should complain and
exit.

How would I handle the example? I would compare column 1, if match compare 6,
if match, msgbox with error of fields that matched, and exit. If no match,
append the record.

I also need to add this: each record will have an original quantity (col2) I
will need to retain the qty for each col6.

This is a list part numbers with specs attached, qty, and location code. The
owner wants a report that differs from how all the other reports are done,
moreover differs from how the data is structured. The part number is for
(lets just say) a sink. He needs a total of how many to buy and then would
like a breakdown of how many for each room.

where
68 is the sink number
2 is the qty needed
2 is a dimension
2 is a dimension
2 is a dimension
4511 is the room number

so when I am done, i need to display
item qty ... loc1 qty1 loc2 qty2 loc3 qty 3
68 2 ... 4511 (2) 4508 (3) 4500 (1) * there will be no
more than three different locations.

Previously the data was stored with all locations in the same field separated
by spaces (not good)
So we broke it down to separate line items with a location diff only.

The max records could change..but for now..it is about 7000 records. With
only 150 dups.

I prefer the coded version, over a query, but would love to know both
thanks friend.

 

[aacadprogrmr via AccessMonster.com]

I have not heard anything from you. You still there???