query: Count lines until value reached

[Matthias Haffke]

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

[Salad]

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.

[Salad]

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

[Matthias Haffke]

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