Having trouble with a recordset/array in selecting and appending values from 2 column

[mt1013]

I have a table of 12,000 records with 10 columns (using Access 2007). I am trying to do a recordset to select from 2 columns and put those values in a column at the end of the table (both columns are sorted due to the nature of the data). One column is an ID column and the other column is a name column. While I am running the recordset, if the first ID column looks at the next row in the ID column and sees match AND if the Name column looks at the next row in the Name column and sees that there is a matching Name, then the ID column value is populated to an empty column at the end of the table. If any of those columns deviate from that parameter (one column shows no match/both columns show no match), then the ID column and the name column from that row are appended and placed in a different column at the end. Basically the idea is that each row looks ahead to the next row to see what the field value is (those columns contain values that are date based). Would anyone be able to help me with this code? Do I set it up with a muti-dimensional array or? Thank you.

[Dal Jeanis]

You're clearly committing spreadsheet here. This request is the sign of a database that has not been designed as a relational database, but as something else.

You haven't explained what you meant by the "next" row - there's no key or order in the questions you asked so far.

Since your two different conditions are completely non-overlapping, you can solve each of them as a discrete step, and run them in either order, and you'll get the desired result.

[mt1013]

The columns needed are sorted ascending (the fields are based on runs from the previous week) so I'm looking at the next row for the data I need. This file was originally in an excel spreadsheet and formulas did most of the data crunching. So the file was turned over to me so that I could automate the process in Access. Thank you. You're help is appreciated.

You're clearly committing spreadsheet here. This request is the sign of a database that has not been designed as a relational database, but as something else.

You haven't explained what you meant by the "next" row - there's no key or order in the questions you asked so far.

Since your two different conditions are completely non-overlapping, you can solve each of them as a discrete step, and run them in either order, and you'll get the desired result.

[ssanfu]

Be aware that tables do not have an order.

A table is a "bit bucket". There is no inherent order. While you may import a "sorted" Excel spreadsheet, the order you add the records is not necessarily the same order that you will view the data. Also, there is nothing that guarantees the table will stay in the imported order. You may open the table 10, 50, 100 times and the order will be in the order it was imported. But the next time the table is opened, the table might be in a different order.

I'm just saying......

[Dal Jeanis]

It would be much preferable to analyze the data and determine what it actually represents, rather than trying to weld a spreadsheet functionality into a relational database. I'm not sure what functionality you are trying to achieve by linking changes of the ID/Name to different columns, but it's pretty obvious that you're making your life difficult. That functionality belongs in a different table, but we can't tell what kind of other table until we understand the actual application.

If you post a sample screenshot of the spreadsheet layout, with any sensitive data replaced with fake data, and explain how you want to use the resulting data - what reports you want, what functionality you want to support, what the purpose is - then we can help you determine the migration route from spreadsheet to relational tables.

[Dal Jeanis]

It would be better to start with a fresh design, but this would do what you asked to do. Do all this in a junk version of your database, until you are sure it worked.

Caveats - not knowing anything about your data, just knowing your request, and assuming that combinations of ID and name won't repeat later in the data. There's a check step at the end for that.

First, if you are importing from a spreadsheet, then make sure that, during import, Access assigns an autokey. That way, the initial row order will be preserved.

Assuming you did that, your import table would look then like this

tblWorkSheet
   WorkPK             PK autonumber added during import  
   WorkID             Number from Worksheet
   WorkName           Text from worksheet
   Work03 thru 10     Other columns from worksheet

STEP ONE:
You create a second table tblKeys

tblKeys 
   GroupPK            PK autonumber   
   WorkID             Number from tblWorkSheet
   WorkName           Text from tblWorkSheet
   WorkMinPK          Number
   WorkMaxPK          Number
Add these fields to tblWorksheet
   GroupFK            Number - will load after a few steps below
   MatchID            Number - will load after a few steps below
   UnMatchID          Number - will load after a few steps below
   UnMatchName        Text - will load after a few steps below

STEP TWO:
Then you can do an insert to populate the tblKeys table.

Query1I:
   INSERT INTO tblKeys (WorkID, WorkName, WorkMinPK, WorkMaxPK)
   SELECT TW.WorkID, TW.WorkName, MIN(TW.WorkPK), MAX(TW.WorkPK)  
   FROM tblWorkSheet AS TW
   GROUP BY TW.WorkID, TW.WorkName 
   ORDER BY TW.WorkID, TW.WorkName;

And now you have to test to see if there were any odd situations, where a given ID and Name occurred in multiple groupings.

SELECT  
   TM1.GroupPK, TM1.WorkMinPK, TM1.WorkMaxPK,
   TM2.GroupPK, TM2.WorkMinPK, TM2.WorkMaxPK
FROM tblMinMax AS TM1, tblMinMax AS TM2
WHERE TM2.GroupPK = 
   (SELECT Min(TM3.GroupPK) 
    FROM tblMinMax AS TM3
    WHERE TM3.GroupPK > TM1.GroupPK
    AND TM3.WorkMinPK < TM1.WorkMaxPK);

If that query has no results, then the following steps will get you your results. If not, then you'll probably need VBA to deal with it.
STEP THREE:
Then assign the resulting autokey field back to tblworksheet, and also set all rows to think that the following row will match.

Query1U:
UPDATE tblworkSheet AS TW, tblKeys AS TK
SET TW.GroupFK = TK.GroupPK,
    TW.MatchID = TK.WorkID
WHERE TW.WorkID = TK.WorkID
AND  TW.WorkName = TK.WorkName;

STEP FOUR:
Now we have to correct the ones that don't.

Query2U:
UPDATE 
   tblworkSheet AS TW 
   INNER JOIN 
   tblKeys AS TK 
   ON TW.WorkPK = TK.WorkMaxPK
SET TW.MatchID = 0,
    TW.UnMatchID = TW.WorkID
    TW.UnMatchName = TW.WorkName;

[mt1013]

It would be better to start with a fresh design, but this would do what you asked to do. Do all this in a junk version of your database, until you are sure it worked.

Caveats - not knowing anything about your data, just knowing your request, and assuming that combinations of ID and name won't repeat later in the data. There's a check step at the end for that.

First, if you are importing from a spreadsheet, then make sure that, during import, Access assigns an autokey. That way, the initial row order will be preserved.

Assuming you did that, your import table would look then like this

tblWorkSheet
   WorkPK             PK autonumber added during import  
   WorkID             Number from Worksheet
   WorkName           Text from worksheet
   Work03 thru 10     Other columns from worksheet

STEP ONE:
You create a second table tblKeys

tblKeys 
   GroupPK            PK autonumber   
   WorkID             Number from tblWorkSheet
   WorkName           Text from tblWorkSheet
   WorkMinPK          Number
   WorkMaxPK          Number
Add these fields to tblWorksheet
   GroupFK            Number - will load after a few steps below
   MatchID            Number - will load after a few steps below
   UnMatchID          Number - will load after a few steps below
   UnMatchName        Text - will load after a few steps below

STEP TWO:
Then you can do an insert to populate the tblKeys table.

Query1I:
   INSERT INTO tblKeys (WorkID, WorkName, WorkMinPK, WorkMaxPK)
   SELECT TW.WorkID, TW.WorkName, MIN(TW.WorkPK), MAX(TW.WorkPK)  
   FROM tblWorkSheet AS TW
   GROUP BY TW.WorkID, TW.WorkName 
   ORDER BY TW.WorkID, TW.WorkName;

And now you have to test to see if there were any odd situations, where a given ID and Name occurred in multiple groupings.

SELECT  
   TM1.GroupPK, TM1.WorkMinPK, TM1.WorkMaxPK,
   TM2.GroupPK, TM2.WorkMinPK, TM2.WorkMaxPK
FROM tblMinMax AS TM1, tblMinMax AS TM2
WHERE TM2.GroupPK = 
   (SELECT Min(TM3.GroupPK) 
    FROM tblMinMax AS TM3
    WHERE TM3.GroupPK > TM1.GroupPK
    AND TM3.WorkMinPK < TM1.WorkMaxPK);

If that query has no results, then the following steps will get you your results. If not, then you'll probably need VBA to deal with it.
STEP THREE:
Then assign the resulting autokey field back to tblworksheet, and also set all rows to think that the following row will match.

Query1U:
UPDATE tblworkSheet AS TW, tblKeys AS TK
SET TW.GroupFK = TK.GroupPK,
    TW.MatchID = TK.WorkID
WHERE TW.WorkID = TK.WorkID
AND  TW.WorkName = TK.WorkName;

STEP FOUR:
Now we have to correct the ones that don't.

Query2U:
UPDATE 
   tblworkSheet AS TW 
   INNER JOIN 
   tblKeys AS TK 
   ON TW.WorkPK = TK.WorkMaxPK
SET TW.MatchID = 0,
    TW.UnMatchID = TW.WorkID
    TW.UnMatchName = TW.WorkName;

Dal

This looks like what I"m looking for. I'm going to give it a shot. You're right, if step 3 doesn't work I'll try some VBA. Thanks for your help. I really appreciate it.

mt1013

[mt1013]

Forgot. Will do. Thanks.

[Dal Jeanis]

mt1013 - No big deal this time, but please make a practice to cut "quoted" text down to the minimum amount of prior posts that would be useful to the reader. Some people make comments inline, and trying to review everything that person A wrote, and determine what Person B added, becomes a bit of a chore.

Also, if you got what you needed, please mark the thread solved. Top of page, under Thread Tools.

[mt1013]

Dal

This looks like what I"m looking for. I'm going to give it a shot. You're right, if step 3 doesn't work I'll try some VBA. Thanks for your help. I really appreciate it.

mt1013

Still working on your code. Question: I don't think you went over the tables,
tblMinMax ,tblMinMax. Could elaborate a little more on this?

Thanks

[mt1013]

mt1013 - No big deal this time, but please make a practice to cut "quoted" text down to the minimum amount of prior posts that would be useful to the reader. Some people make comments inline, and trying to review everything that person A wrote, and determine what Person B added, becomes a bit of a chore.

Also, if you got what you needed, please mark the thread solved. Top of page, under Thread Tools.

Will Do. Thanks. Not to sure how to reply. I'm new here. Didn't know if I should reply to your email or....

[Dal Jeanis]

Sorry, those "tblMinMax" should all be "tblKeys". Name changed while coding.

[mt1013]

Sorry, those "tblMinMax" should all be "tblKeys". Name changed while coding.

Dal:

Hello.

"TM1" and "TM2", what is the relationship to the code ( "TM3" as well). Are they tables?

Thank you.

[Dal Jeanis]

This SQL code

FROM tblKeys AS TM3

Says to make a virtual copy of tblKeys, and call it TM3. Anywhere you use TM3 in the query, it means that particular copy of tblKeys.

I probably would have called it TK3 instead, had I notice that I had renamed the underlying table...

[mt1013]

This SQL code

FROM tblKeys AS TM3

Says to make a virtual copy of tblKeys, and call it TM3. Anywhere you use TM3 in the query, it means that particular copy of tblKeys.

I probably would have called it TK3 instead, had I notice that I had renamed the underlying table...

So "TM1" AND "TM2" considered virtual copies of tblWorksheet?