Incrementing a variable on a field in the current table

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hello,

I have a table with one of the fields "Cost".
I wish to accept an input parameter from the user
of "Total Budget"

I then wish to print all of the cost rows until the total
budget is reached.

I know if I were writing a program the logic would be some
like

Sort table by Date
VarCost = 0
Do while varCost + Cost < TotalBudget
Print row
Fetch Next Row
varCost = varCost + Cost
End

Can I do this logic using a query only perhaps using the
Expression Builder? If so how? I can't figure out how to
increment a variable on a field in the current table, then
stopping once a total figure is hit.

Thanks in advance for your time and guidance. I am new at
Access, put do have a programming background. I am hoping
to do this all in Access.

Thanks,
Michael
 
You might do this in two steps:

1. Create a query (say, named Query1) that finds the cumulative cost by
date for those rows where it is less than your "Total Budget" parameter.
The SQL might look something like this:

PARAMETERS [Total Budget] Currency;
SELECT
[Your Table].[Date],
First([Your Table].[Cost]) AS [Cost],
Sum([Self].[Cost]) AS [Cumulative Cost]
FROM
[Your Table],
[Your Table] AS [Self]
WHERE
[Self].[Date] <= [Your Table].[Date]
HAVING
Sum([Self].[Cost]) <= [Total Budget];

This assumes that values in your date field are unique.

2. Create a query that finds the latest date and corresponding cost and
cumulative cost from Query1. The SQL might look something like this:

SELECT TOP 1
[Query1].[Date],
[Query1].[Cost],
[Query1].[Cumulative Cost]
FROM
[Query1]
ORDER BY
[Query1].[Date] DESC
 
Back
Top