Return the value of one field in a query based on other query fields and calculated c

[bengiomanni]

I have a query with 4 fields - Account #, FiscalYear, GiftReceivedDt, and Total. This is a list of donations that people have given to the college where I work. Each row has the account # of the giver, the fiscal year in which it was given, the exact date it was entered, and the amount given. I need to know on which date the giver's total donations = $1000 for each fiscal year. Then I want to store this data somewhere - not sure where yet; for now I would be happy to just have it stored in a separate query with the account # and fiscal year. Then I could combine that query with something else as needed.


I am pretty new to VBA. I think I need to use some kind of loop statement with a recordset, but I am having a hard time getting started with this one. Any help would be greatly appreciated!

 

[plog]

Then I want to store this data somewhere - not sure where yet; for now I would be happy to just have it stored in a separate query with the account # and fiscal year

First, 'store' implies using a table. What you would do is use a query whenever you needed the data. Second, if I understand this correctly, sounds like it can be solved using just queries and no VBA.

I would need specifics about your query--its name and the field type of the 4 fields you listed to give you specific instructions. However, you can search this forum for how to create a running total query. Build one of those based on your query, then in turn use that running total query as the basis of another query where you find out the minimum GiftReceivedDt for each Account # and Fiscal year where that running total went over or equaled $1000.

 

[bengiomanni]

Thanks for the quick reply!

I may want to put it in a table eventually, but for now you're right, the query will calculate it every time and that's fine.

The Account # is an Integer, the Fiscal Year is a 4-digit year that was extracted from the GiftReceivedDt Date field. Total is a currency field.

I didn't even think about running total query. The question I have is how do I make the running total start over at 0 for each new fiscal year?

 

[plog]

I may want to put it in a table eventually

Want it all you want, but that's not how it should operate.

To make the running total start over at 0 for each fiscal year, you would add the fiscal year to the criteria section of the DSum() in your runnint total query.

 

[bengiomanni]

Still working on this. I will get back to you.

 

[bengiomanni]

Ok. I've got it working. Thank you. Here's what I ended up with.

SELECT dbo_FC_FundLedgerUnion_View.AccountID, dbo_FC_FundLedgerUnion_View.FiscalYear, dbo_FC_FundLedgerUnion_View.GiftReceivedDt, dbo_FC_FundLedgerUnion_View.Total, DSum("Total","dbo_FC_FundLedgerUnion_View","[FiscalYear] = " & [FiscalYear] & " AND [FundLedgerUID] <= " & [FundLedgerUID] & " AND [AccountID] = " & [AccountID] & "") AS FiscalBalance
FROM dbo_FC_FundLedgerUnion_View
ORDER BY dbo_FC_FundLedgerUnion_View.AccountID, dbo_FC_FundLedgerUnion_View.FiscalYear, dbo_FC_FundLedgerUnion_View.GiftReceivedDt;


Now I have a new problem. I am reusing this query in another query, trying to filter for FiscalBalance that is >= 1000, but when I put the criteria in the line, Access is putting quotes around 1000 as though this were a text field. How could Access think that a calcuated field with numbers is a text field?!!

 

[plog]

Multiply it by 1:

DSum("Total","dbo_FC_FundLedgerUnion_View","[FiscalYear] = " & [FiscalYear] & " AND [FundLedgerUID] <= " & [FundLedgerUID] & " AND [AccountID] = " & [AccountID] & "")*1 AS FiscalBalance

 

[bengiomanni]

Ok. Thanks for that tip. Now I have a new question. I modified that query and named it qryFundLedgerRunningTotals. But now I want a new query that will give me the GiftReceivedDt for each Fiscal Year where the Fiscal Balance reached $1000 or more. Here is what I have, but Access is taking so long to run it that nothing happens, so I'm wondering if there is a better way to go about this.

SELECT qryFiscalRunningTotals.AccountID, qryFiscalRunningTotals.FiscalYear, Min(qryFiscalRunningTotals.GiftReceivedDt) AS MinOfGiftReceivedDt, qryFiscalRunningTotals.FiscalBalance
FROM qryFiscalRunningTotals
WHERE (((qryFiscalRunningTotals.FiscalBalance)>=1000))
GROUP BY qryFiscalRunningTotals.AccountID, qryFiscalRunningTotals.FiscalYear, qryFiscalRunningTotals.FiscalBalance
ORDER BY qryFiscalRunningTotals.AccountID, qryFiscalRunningTotals.FiscalYear;

 

[plog]

I don't think so. Running sums usually take a while to run because they have to look back into the table at all the records for each row. And that's just your sub-query, the main one uses that running sum to get the data you actually want.

I don't know if it will be more efficient, but you might do it all in the sub-query. You would still use the running sum, but you would make it a criteria--limit it to just records where the sum is over 1000 and then get the Minimum date in that query.

 

[bengiomanni]

I thought I would try dumping the data from my totals query into a table to see if I could run a new query faster off of that to find the dates where the total is $1000, but when I ran the query below, it took over 10 minutes and nothing happened.

SELECT dbo_FC_FundLedgerUnion_View.AccountID, dbo_FC_FundLedgerUnion_View.FiscalYear, dbo_FC_FundLedgerUnion_View.GiftReceivedDt, dbo_FC_FundLedgerUnion_View.Total, (DSum("Total","dbo_FC_FundLedgerUnion_View","[FiscalYear] = " & [FiscalYear] & " AND [FundLedgerUID] >= " & [FundLedgerUID] & " AND [AccountID] = " & [AccountID] & ""))*1 AS FiscalBalance INTO tblFiscalRunningTotals
FROM dbo_FC_FundLedgerUnion_View
ORDER BY dbo_FC_FundLedgerUnion_View.AccountID, dbo_FC_FundLedgerUnion_View.FiscalYear, dbo_FC_FundLedgerUnion_View.GiftReceivedDt;

I have tried filtering the totals query itself. Again, it takes 5 minutes. I usually stop it because this long of a run time won't work.

I don't know if this might affect it, but this totals query is run off a union query that I have linked to from Access on our SQL server. Would that have something to do with this? That query opens very quickly itself, so I don't know.