By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 448,925 Members | 1,815 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,925 IT Pros & Developers. It's quick & easy.

# query: Count lines until value reached

 P: n/a Ok, this is a tricky question for the pro's: My access sheet: line, id a, id b, val% ---------------- 1, a, ac, 0.04 2, a, ac, 0.28 3, a, ac, 0.015 4, a, ac, 0.205 5, a, ac, 0.18 6, a, ac, 0.02 7, a, ac, 0.2 8, a, ax, 0.4 .... 50, b, ac , 0.32 .... ... .. each line contains an order to a different supplyer. val% means percentage of the participarion to deliver one product, so all the lines with "a, ag" have a sum of 100% = 1, same for "a, ax" and so on. query: now, the sheet sould be grouped by id a and id b (product codes) - no problem. But: in the forth row (val%) I want to have the number of lines needed, until the sum of minimal 0.8 is reached, what means: id a, id b, lines_needed ----------------------- a, ac, 4 a, ag, ... .... "4" because of 0.28 + 0.26 + 0.205 + 0.16, so it counts at first the biggest numbers. The sense is, that I will count in our database, how many suppliers are needed for the delivery of 80 percent of each of our products. Thank you very much. Matthias Nov 13 '05 #1
Share this Question
3 Replies

 P: n/a Matthias Haffke wrote: Ok, this is a tricky question for the pro's: My access sheet: line, id a, id b, val% ---------------- 1, a, ac, 0.04 2, a, ac, 0.28 3, a, ac, 0.015 4, a, ac, 0.205 5, a, ac, 0.18 6, a, ac, 0.02 7, a, ac, 0.2 8, a, ax, 0.4 ... 50, b, ac , 0.32 ... .. . each line contains an order to a different supplyer. val% means percentage of the participarion to deliver one product, so all the lines with "a, ag" have a sum of 100% = 1, same for "a, ax" and so on. query: now, the sheet sould be grouped by id a and id b (product codes) - no problem. But: in the forth row (val%) I want to have the number of lines needed, until the sum of minimal 0.8 is reached, what means: id a, id b, lines_needed ----------------------- a, ac, 4 a, ag, ... ... "4" because of 0.28 + 0.26 + 0.205 + 0.16, so it counts at first the biggest numbers. Want to confuse someone and not get answers? Do what you did. Use 0.26 and 0.16, figures that aren't used in your demo, to confuse people. Also talk about "ag" that isn't used anywhere in your example. Be sure nobody knows what "line" is...is it a calculated field or a field and does it have any relelevence except obfuscation? Then throw in some mutterings 100% but talk about .8. Then come up with a magic number of 4. Voila! Instant WTF. Nov 13 '05 #2

 P: n/a Matthias Haffke wrote: Ok, this is a tricky question for the pro's: My access sheet: line, id a, id b, val% ---------------- 1, a, ac, 0.04 2, a, ac, 0.28 3, a, ac, 0.015 4, a, ac, 0.205 5, a, ac, 0.18 6, a, ac, 0.02 7, a, ac, 0.2 8, a, ax, 0.4 ... 50, b, ac , 0.32 ... .. . each line contains an order to a different supplyer. val% means percentage of the participarion to deliver one product, so all the lines with "a, ag" have a sum of 100% = 1, same for "a, ax" and so on. query: now, the sheet sould be grouped by id a and id b (product codes) - no problem. But: in the forth row (val%) I want to have the number of lines needed, until the sum of minimal 0.8 is reached, what means: id a, id b, lines_needed ----------------------- a, ac, 4 a, ag, ... ... "4" because of 0.28 + 0.26 + 0.205 + 0.16, so it counts at first the biggest numbers. The sense is, that I will count in our database, how many suppliers are needed for the delivery of 80 percent of each of our products. Thank you very much. Matthias I think I see what you are driving at. You would be best off if you used a function. In your query, create a column with something like LineCount : GetLineCount([ida], [idb], .8) I'm passing .8. If this number changes, you can change the value in the query and the function will calc correctly. Then in the modules create a function called GetCount(). Aircode is: Public FUnction GetCount(strIDA As string, strIDA2 As String, _ dblDec As Double) As Integer Dim strSQL As STring Dim rst As Recordset Dim dblVal As Double 'select records for the ids strSQL = "Select number from ... " & _ "Where ida = strIDA and idb = strIDB" "order by number decending" set rst = currentdb.openrecordset(strSQL...) rst.movefirst Do while not rst.EOF 'add the number from the select dblVal = dblVal + Number 'increment line count GetCount = GetCount + 1 'if greater/equal, .8 line count reached If dblVal >= dblDec Then exit do endif rst.MoveNext loop End Function Nov 13 '05 #3

 P: n/a Salad wrote I think I see what you are driving at. You would be best off if you used a function. In your query, create a column with something like LineCount : GetLineCount([ida], [idb], .8) I'm passing .8. If this number changes, you can change the value in the query and the function will calc correctly. Then in the modules create a function called GetCount(). Aircode is: Public FUnction GetCount(strIDA As string, strIDA2 As String, _ dblDec As Double) As Integer Dim strSQL As STring Dim rst As Recordset Dim dblVal As Double 'select records for the ids strSQL = "Select number from ... " & _ "Where ida = strIDA and idb = strIDB" "order by number decending" set rst = currentdb.openrecordset(strSQL...) rst.movefirst Do while not rst.EOF 'add the number from the select dblVal = dblVal + Number 'increment line count GetCount = GetCount + 1 'if greater/equal, .8 line count reached If dblVal >= dblDec Then exit do endif rst.MoveNext loop End Function Thank you very much for the answer. I know, my explication was the worst I've probably ever made, but it was early and I was tired. Nevertheless you got it and I solved it. Thank you. The only thing is, that Access is extremely slow, it takes about one second to generate five results in the cells and ages for the whole table. Do you have a solution? For people who want to use this code later on here the corrected and working aircode: Public Function fncGetCountLief(strIDA As String, strIDB As String, _ dblDec As Double) As Integer Dim strSQL As String Dim rst As Recordset Dim dblVal As Double strSQL = "Select prozlief from [req Lief] " & _ "Where Hauptgruppe='" & strIDA & "' and Warengrp='" & _ strIDB & "' ORDER BY prozlief DESC" Set rst = CurrentDb.OpenRecordset(strSQL) rst.MoveFirst Do While Not rst.EOF dblVal = dblVal + rst.Fields!prozlief fncGetCountLief = fncGetCountLief + 1 If dblVal >= dblDec Then Exit Do End If rst.MoveNext Loop End Function Matthias Nov 13 '05 #4

### This discussion thread is closed

Replies have been disabled for this discussion.