Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-11-2019, 10:46 AM   #1
sour
Newly Registered User
 
Join Date: Oct 2018
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
sour is on a distinguished road
Look up a value from left to right

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

sour is offline   Reply With Quote
Old 02-11-2019, 10:55 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,446
Thanks: 19
Thanked 362 Times in 354 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Look up a value from left to right

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 02-11-2019, 10:57 AM   #3
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,270
Thanks: 358
Thanked 558 Times in 540 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Look up a value from left to right

Spreadsheet format with 52 weeks in columns.

Bit of a matrix ?

__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 02-11-2019, 11:01 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,696
Thanks: 40
Thanked 3,470 Times in 3,358 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Look up a value from left to right

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-11-2019, 11:09 AM   #5
sour
Newly Registered User
 
Join Date: Oct 2018
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
sour is on a distinguished road
Re: Look up a value from left to right

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.
sour is offline   Reply With Quote
Old 02-11-2019, 11:28 AM   #6
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,270
Thanks: 358
Thanked 558 Times in 540 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Look up a value from left to right

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 02-11-2019, 12:30 PM   #7
sour
Newly Registered User
 
Join Date: Oct 2018
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
sour is on a distinguished road
Re: Look up a value from left to right

ok thanks for your help where would the code go in a form?

sour is offline   Reply With Quote
Old 02-11-2019, 12:36 PM   #8
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,270
Thanks: 358
Thanked 558 Times in 540 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Look up a value from left to right

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 02-11-2019, 11:05 PM   #9
sour
Newly Registered User
 
Join Date: Oct 2018
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
sour is on a distinguished road
Re: Look up a value from left to right

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
sour is offline   Reply With Quote
Old 02-12-2019, 01:56 AM   #10
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,270
Thanks: 358
Thanked 558 Times in 540 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Look up a value from left to right

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

__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Help with Left BelCraig Reports 2 09-22-2015 04:22 AM
text entering right to left not left to right. suepowell Forms 2 08-19-2009 05:39 AM
Left Outer Join Query Not Returning Left Records When No Right Match M_S_Jones Queries 1 06-08-2009 12:50 AM
Left join with filter looses "left" part bdubuc Queries 0 01-29-2002 06:45 AM




All times are GMT -8. The time now is 06:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World