update the 0s into its group ranking nbr

[hance]

Hi everyone,

I'm stuck here for days. Can anyone help me with the table below please?
The table has columns from "Item_Desc" to "Row". I would like to add another column "Rank2". It updates "0"s in blue cells into its group ranking numbers highlighted in yellow. And I would like to add
add Text1, Text2, Text n ...in the first column. I know this can be done in excel, but in that way the queries will not be able to automated in access by one click. I appreciate your help!

Item_Desc Detail Rank Row Rank2
DisplayName 3yyy 1 1 1
Address 4566 1 2 1
Enabled False 1 3 1
Database asfads 1 4 1
Mailbox 23r23r 1 5 1
Text werr 1 6 1
T1 (was blank) asfads 0 7 1
T2 (was blank) 23r23r 0 8 1
T3 (was blank) werr 0 9 1
DisplayName 3yyy 2 10 2
Address 4566 2 11 2
Enabled False 2 12 2
Database asfads 2 13 2
Mailbox 23r23r 2 14 2
Text werr 2 15 2
DisplayName 3yyy 3 16 3
Address 4566 3 17 3
Enabled False 3 18 3
Database asfads 3 19 3
Mailbox 23r23r 3 20 3
Text werr 3 21 3
T1 (was blank) 4546 0 22 3

 

[MarkK]

In some ways it seems that you are thinking of a table in Access as if it is a spreadsheet, and this is not the case. A single row in a database table should represent a single instance of a thing, and the fields in that that row are dimensions of the thing being represented. In addition, in a database table there is no inherent order to records except if you happen to assert one when your retrieve records. In respect to these observations, your fields Rank and Row don't really belong in a database table. Rank and Row are dynamic and should be calculated when the list is retrieved, not stored as fixed dimensions of individual data points.

Maybe if you say more about what you are trying to accomplish someone might be able to offer more suggestions.

 

[hance]

Thank you for your reply lagbolt! the raw data extract I got is a txt file. I imported it into Access and would like to build some report on it to run weekly. The first column has 6 distinct values from DisplayName to Text and they repeat themselves. For the Text records, its value in Detail column sometimes it got cut off and roll into next records or rows. So, I added Rank and Autocount and tried to transpose the data(see attached JPG file), but now I need help to add Text1 and text2 in the first column...and group them properly by looking at prevoius rank for those cut off text. Maybe only VBA can do the trick?

 

[MarkK]

What report do you need to run? Maybe you can fill this data in when printing the report. Certainly if you want sequential numbering, that is the time to implement that.
Cheers,

 

[hance]

Are there anybody can take a look at my problem here? Thanks!

 

[nschroeder]

Where do the Rank & Row values come from? Are they being entered manually in the txt file to keep the data grouped together, or is that the way it comes? Also, is the data import a one-time thing, or will new data be pulled in each week?

 

[hance]

yes, the raw data is extracted weekly, but I just replace the old file in the same location. no need to import to Access again and again.
raw data doesn't have the rank and RowID. I copy and paste an emtpy table of the imported table and added a autocount column as rowid. Then, append the raw data into the newly built table. then, did a selfjoin query to do the ranking. but as you can see for those records has no value in the first column. The ranking won't work, it is all 0s.
Don't know how to solve this problem.

 

[hance]

Never mind. The problem has been solved by myself! I have been so close to the right point

 

[nschroeder]

Sounds like you don't need this reply, but I already had it worked out, so I'll send anyway. You probably already realized that in order to work with that data (in Access or Excel), the rows need to be transposed into fields. To accomplish this, create two tables: ImportData and Data. All fields are Text values.

ImportData (the Row and Rank fields you have are not needed)

ItemDesc
Detail​

Data

ID (Autonumber pk)
DisplayName
Address
Enabled
Database
Mailbox
Text
etc.​

Create a form with a button (cbUpdateData). Then add the code to the form:

Option Compare Database
Option Explicit
Dim db As Database
Dim rsImport As Recordset
Dim rsData As Recordset

Sub cbUpdateData_Click()
    Set db = CurrentDb()
    Set rsImport = db.OpenRecordset("ImportData", dbOpenTable)
    Set rsData = db.OpenRecordset("Data", dbOpenTable)

    With rsData
        Do Until rsImport.EOF
            Select Case rsImport!ItemDesc
                Case "DisplayName"
                    .AddNew
                    !DisplayName = rsImport!Detail
                Case "Address":   !Address = rsImport!Detail
                Case "Enabled":   !Enabled = rsImport!Detail
                Case "Database":  !Database = rsImport!Detail
                Case "Mailbox":   !Mailbox = rsImport!Detail
                Case "Text"
                    !Text = rsImport!Detail
                    .Update
            End Select
            rsImport.MoveNext
        Loop
    End With

    Set rsImport = Nothing
    Set rsData = Nothing
    Set db = Nothing
End Sub

Clicking UpdateData will transpose ImportData and append it into Data. Hope this helps.

 

[hance]

Thank you nschroeder! I will definately try your code out! By the way, below is the SQL I used, just in case anybody else need it in the future.
Again, I appreciate the time you spent on the VBA code. I always hope I can do that too...

SELECT wTbl02_Grouping_By_Ranking.Item_Desc, wTbl02_Grouping_By_Ranking.Detail, wTbl02_Grouping_By_Ranking.Rank, wTbl02_Grouping_By_Ranking.Row, (select max([Rank] ) from [wTbl02_Grouping_By_Ranking] as b where b.Row <= [wTbl02_Grouping_By_Ranking]![Row] ) AS Rolling_Max_Rank INTO wTbl03_Rolling_Max_Rank
FROM wTbl02_Grouping_By_Ranking;