Look up a value from left to right (1 Viewer)

sour

Registered User.
Local time
Today, 03:50
Joined
Oct 26, 2018
Messages
21
Hello i have a DB called week numbers it consists of a order number and 52 fields from Week Number1 to 52. I also have various forms, sub forms and query's. What i am after for each order number the first week from left to right that contains a value (number field), once a value if round the result would be the week number that that value is in, Example

Order Number Result
1 Week 10
2 Week 8
3 Week 45
4 Week 50
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:50
Joined
Oct 29, 2018
Messages
21,357
Hi. Can you provide some sample data, so we can better understand what you're asking? Thanks. I'm not sure your example above makes sense as it is.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:50
Joined
Sep 21, 2011
Messages
14,038
Spreadsheet format with 52 weeks in columns.

Bit of a matrix ?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:50
Joined
Feb 19, 2013
Messages
16,553
sounds like your table is constructed incorrectly if you have week numbers as field names.

To do what you want with that structure will require an iif statement 52 levels deep (and I don't think you can have that number of levels anyway), or some extremely complex vba, or a 52 level union query (and I don't think that is doable either).

Your table should be constructed much as the result you require. I can provide you with example code to achieve that if you don't know how
 

sour

Registered User.
Local time
Today, 03:50
Joined
Oct 26, 2018
Messages
21
The DB is a Production Schedule, the week numbers table is a calculation tool for the number of hours in that week, loading is then produced from these figures. What i am after is the fisr week of manufacture this will come from the first week with a value.
Not sure how i could have designed the table any other way, yes if there is some code, lookup, then i am prepared to try anything.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:50
Joined
Sep 21, 2011
Messages
14,038
Here is my attempt to get you started.
Change the table name to suit

If this produces what you want, then we can progress to creating a table to put the data in rather than display the data.

FWIW I believe you should have one record of OrderNum and WeekNo and then placed the relevant number in the WeekNo field.

This assumes there is an Autonumber field, if not reduce the numbers by 1

Code:
Public Sub GetWeekNumber()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim iIndex As Integer

Set db = CurrentDb()
Set rst = db.OpenRecordset("select * from Transactions")
Do While Not rst.EOF
    For iIndex = 2 To 53
        If Nz(rst(iIndex), 0) > 0 Then
            Debug.Print rst(1) & " week " & rst(iIndex)
            Exit For
        End If
    Next
    rst.MoveNext

Loop
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub
 

sour

Registered User.
Local time
Today, 03:50
Joined
Oct 26, 2018
Messages
21
ok thanks for your help where would the code go in a form?
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:50
Joined
Sep 21, 2011
Messages
14,038
Behind a button?

I'd run it from debug to start with. That is what I did to see it produced what I thought it would.

I've updated it now to add the data to a table, in my case Table3
That would have
ID Autonumber
OrderNo Number, Long
WeekNo Number, Integer

Code:
Public Sub GetWeekNumber()
Dim db As DAO.Database
Dim rst As DAO.Recordset, rst1 As DAO.Recordset
Dim iIndex As Integer


Set db = CurrentDb()

' Delete any previous records
db.execute "Delete * from table3", dbFailOnError

Set rst = db.OpenRecordset("select * from Transactions")
Set rst1 = db.OpenRecordset("Table3", dbOpenDynaset)

Do While Not rst.EOF
    For iIndex = 2 To 53
        If Nz(rst(iIndex), 0) > 0 Then
            'Debug.Print rst(0) & " week " & rst(iIndex)
            rst1.AddNew
            rst1!TestDate = rst(1)
            rst1!TestData = rst(iIndex)
            rst1.Update
            Exit For
        End If
    Next
    rst.MoveNext

Loop
rst.Close
rst1.Close
Set rst = Nothing
Set db = Nothing

End Sub

This is a workaround though, plus no error trapping
HTH
 

sour

Registered User.
Local time
Today, 03:50
Joined
Oct 26, 2018
Messages
21
Ok I will try it tonight just one question in your code you have added a field called weekno, I have 52 of these fields
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:50
Joined
Sep 21, 2011
Messages
14,038
That is in a different table.?

Your table is not normalised, which is why you have to jump through all these hoops to get what you want.
All that code does is populate a table with the format you originally asked for.?

It could be just as easily turned into a function which you could use in a query to get the same format, to perhaps put in a report?

You have not said what the end result is.?

In fact thinking about it now, it could all be in the same table?
WeekNo holds a value that exists from WeekNo1 to WeekNo52 and is the first value it locates left to right? However if that structure is comping from another application that would be hard to modify.

OrderNo WeekNo
1 10
2 8
3 45
4 50
 

Users who are viewing this thread

Top Bottom