Query calculation question?

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello,

I am attempting to run a query that totals a field in the
table as the query executes, then stops selecting records
once that total is reached. Therefore, the query is not a
SUM, but more a calculation.

There is a single field that has a currency amount in the
table, cAmount.

I wish the user to enter a parameter variable of
varTotalCandyBudget.

Then I wish to print as many records as I can until the
varTotalCandyBudget is reach (or exhausted).

An analogy would be a table containing candy. In the
candy table is a field cAmount, which represents how much
a piece of candy costs.

A child has a five dollar bill. He is only interested in
how many pieces of different candy he can get with his
five dollars. Our program would prompt the user for "How
much money do you have to buy candy with?"

The output would represent a number of records in which
the cAmount field, totaled of all records in the record
set, was less than five dollars. So say each piece of
candle was less than fifty cents. Our query would print
the first x records in the table until the five dollars
was spent. This is calculated by subtracting each piece
of candle, or record cAmount, from the five dollars. I
also have the cAmount set to ascending order in the query
to ensure the user can get the most candy for his 5
dollars starting with the less expensive candy first.

The pseudo code is something like
Select tblCandy!candyName
From tblCandy
Where tblCandy!cAmount < varTotalCandyBudget -=
(varTotalCandyBudget - tblCandy!cAmount)

Can I do this in Access? Basically, the
varTotalCandyBudget amount would be subtracted the amount
of cAmount when on each record until the amount hits zero,
or is less than any of the remaining candies.

I'm new at Access and am not sure if I am doing this
correctly. But I am sure about the need to enter an
amount, and print the rows that fall under the
accumulative amount of the number entered.

Thanks in advance for your time, as I am totally stuck!
Mike
 
PARAMETERS [Total Budget] Currency;
Select tblCandy.candyName
From tblCandy
Where [Total Budget] <
(SELECT Sum(cAmount)
FROM tblCandy As T2
WHERE T2.candyName <= tblCandy.candyName)
ORDER BY candyName;

... or you could order by ascending price to try to buy as much candy as
possible:

PARAMETERS [Total Budget] Currency;
Select tblCandy.candyName
From tblCandy
Where [Total Budget] <
(SELECT Sum(cAmount)
FROM tblCandy As T2
WHERE T2.cAmount <= tblCandy.cAmount)
ORDER BY candyName;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Mike wrote:

The pseudo code is something like
Select tblCandy!candyName
From tblCandy
Where tblCandy!cAmount < varTotalCandyBudget -=
(varTotalCandyBudget - tblCandy!cAmount)

Can I do this in Access? Basically, the
varTotalCandyBudget amount would be subtracted the amount
of cAmount when on each record until the amount hits zero,
or is less than any of the remaining candies.
Click to expand...
< snip >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't believe it can be done in a query; you'll have to iterate thru a
recordset to do this.

== air code (untested) ==

' The idea is to get the CandyID of all the candies
' that will fit inside the budget & save those IDs to an array
' You could also save the IDs in a temp table.

const SQL_CANDY = "SELECT CandyID, cAmount FROM tblCandy " & _
"ORDER BY cAmount"

const TOTAL_BUDGET As Currency = 5.00

' DAO set up - see below for ADO set up
'
dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset(SQL_CANDY)
'
' End DAO set up

' ADO set up - If you use this comment out the DAO
' set up & uncomment this section. Assumes an .adp project
'
' dim cn as adodb.connection
' dim rs as adodb.recordset
'
' set cn = currentproject.connection
' set rs = cn.execute(SQL_CANDY, , adCmdText)
'
' End ADO set up

dim curSpent As Currency ' running total
dim candyIDs() as Long ' where we save the IDs
dim i as long ' array index

with rs
do while not .eof
curSpent = curSpent + !cAmount
If curSpent >= TOTAL_BUDGET Then
exit do
else
i = i + 1
ReDim Preserve candyIDs(i)
candyIDs(i) = !CandyID
End If
loop
end with

' Do something with the CandyIDs array
' ..... something ......

' Clean up
rs.close
cn.close

== end air code ==

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHL8DIechKqOuFEgEQKrVwCgiMJlctLwZQUZ0DAQZgxuklDKHPcAnRBa
q56+X/YO8IdTNQO1yDbWri91
=Q3F+
-----END PGP SIGNATURE-----
 
So, you're saying you can't get my "pseudo code" query example to work?
Maybe it would help if you told us the real table structure and what it is
you're trying to accomplish in more detail.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Mike Pallos said:
I am coming to the same conclusion. I've been working on creating a
Click to expand...
record set all morning. I think I need to do a fetch..next in the middle of
a while loop. I'm still fumbling through. Thanks for you code snip. It
definitely helps.
 
Mike said:
I am coming to the same conclusion. I've been working on creating a record set all morning. I think I need to do a fetch..next in the middle of a while loop. I'm still fumbling through. Thanks for you code snip. It definitely helps.
Click to expand...

Yeah, I forgot the .MoveNext just be fore the Loop statement.
 
Hello John

I am working on a program for my dissertation and do not wish to let the “cat out of the bag†yet. The candy analogy works nicely representing the logic I need

The candy table contains many kinds of candies at all different prices. The user has a set budget and wishes to purchase as many candy as possible. However, the point I may have failed to focus on before is that, the user only wants one piece of each type of candy available that fit within his budget, i.e., how many different candies could one obtain for a certain budget?

To get the most amount of candy, the logic needs to start at with the least expensive piece, and keep purchasing additional pieces of different types of candy, until the budget is exhausted

I hope that helps. I am from old school. Unix C back in the late 80s doing embedded SQL. So I am more familiar with programs, than complex SQL statements – although I would much prefer to pull this off in SQL, making the logic more portable, and less tied to VB. Being an architect, I am rusty to programming – now a days I just talk about architecture and draw pictures mostly (Powerpointware!

Thanks for everything. The coding examples from your Access 2000 book are wonderful (I am using 2000). I am able to learn quite well from reading the book, and now working with your examples

I appreciate your time. I too hope to publish a book some day (let alone three!!!!)

Michae
 
Ah, well. I tried my ascending price solution - which I thought was
correct - in Northwind. However, I had the comparison backwards. This
almost works in Northwind:

PARAMETERS Budget Currency;
SELECT Products.ProductID, Products.ProductName, Products.UnitPrice,
[Budget] AS EnteredBudget, (Select Sum(UnitPrice) From Products As P2 Where
P2.UnitPrice <= Products.UnitPrice) AS TotSpent
FROM Products
WHERE [Budget] >= (Select Sum(UnitPrice) From Products As P2 Where
P2.UnitPrice <= Products.UnitPrice)
ORDER BY Products.UnitPrice;

The technique is to sum the price of all products whose price is less than
or equal to the price in the current row. As long as that sum is less than
or equal to the budget entered, the row is selected. This is not completely
accurate, however, when multiple products have the same price, and adding
just one or two of them would still keep you under the budget.

However, if your table has a unique identifier (like the ProductID field in
Northwind Products), you can get an exact answer, including duplicate
prices, like this:

PARAMETERS Budget Currency;
SELECT Products.ProductID, Products.ProductName, Products.UnitPrice,
[Budget] AS EnteredBudget, (Select Sum(UnitPrice) From Products As P2 Where
(P2.UnitPrice < Products.UnitPrice) Or ((P2.UnitPrice = Products.UnitPrice)
And (P2.ProductID <= Products.ProductID))) AS TotSpent
FROM Products
WHERE [Budget]>=(Select Sum(UnitPrice) From Products As P2 Where
(P2.UnitPrice < Products.UnitPrice) Or ((P2.UnitPrice = Products.UnitPrice)
And (P2.ProductID <= Products.ProductID)))
ORDER BY Products.UnitPrice;

Enter a budget of $225, and you'll get two of the three products listed with
a price of $14.

Let me guess - you're trying to find a slick way to get the most items for a
fixed "upgrades" budget. <s>
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Mike Pallos said:
Hello John,

I am working on a program for my dissertation and do not wish to let the
Click to expand...
"cat out of the bag" yet. The candy analogy works nicely representing the
logic I need.
The candy table contains many kinds of candies at all different prices.
Click to expand...
The user has a set budget and wishes to purchase as many candy as possible.
However, the point I may have failed to focus on before is that, the user
only wants one piece of each type of candy available that fit within his
budget, i.e., how many different candies could one obtain for a certain
budget?
To get the most amount of candy, the logic needs to start at with the
Click to expand...
least expensive piece, and keep purchasing additional pieces of different
types of candy, until the budget is exhausted.
I hope that helps. I am from old school. Unix C back in the late 80s
Click to expand...
doing embedded SQL. So I am more familiar with programs, than complex SQL
statements - although I would much prefer to pull this off in SQL, making
the logic more portable, and less tied to VB. Being an architect, I am
rusty to programming - now a days I just talk about architecture and draw
pictures mostly (Powerpointware!)
Thanks for everything. The coding examples from your Access 2000 book are
Click to expand...
wonderful (I am using 2000). I am able to learn quite well from reading the
book, and now working with your examples.
 
Back
Top