End of the month totals query

[Karl419]

Hello!



I am not very good with Access but have had to start using it. I am trying to run a query but am having trouble.



I wish to run a query that will provide a total amount at the end of each month. It took me a while, but I sort of got it achieved through the above design. However, if there isn't a transaction necessarily on the last day of a month, the query will come up empty/blank.

In other words, if I wish to run a query to provide me with the total balance at September 30, 2019, but the last transaction of that month just happened to occur on September 27, it won't provide me with that information.

Is there a way to correct this? A way to design a query to provide me the balance at the last day of the month, and if there isn't a posted transaction on the last day of the month, then the total at the next most recent date?

Thank you for your time and assistance. Much appreciated.

-Karl.

[Micron]

First, I'd advise that you not use reserved words for any Access object (e.g.Date) because the habit can raise errors and/or introduce erroneous results.
See http://allenbrowne.com/AppIssueBadWord.html

You might have to provide some data and the results you want from it as guidance to get focused answers. I interpret your goal as something that requires more than just saying use BETWEEN or math operators (<=, >=). I think you're asking for 1 transaction record in the next month if there isn't one on the last day of any given month. I'm guessing you're going to need code because of the numerous variations in the number of days in a given month, but I have been surprised by query solutions offered here before.

Maybe if I was an accountant I wouldn't ask, but why would anyone want to include data in a month result when it didn't happen in a given month, only to include it again in the results for the next month's run?

[Karl419]

Hello,

Thank you for your reply.

My apologies for the confusion. The transactions are only being accounted for in the month in which they occured. They are not being double accounted.

I will try to be more specific, by using an example:

I have a table that is full of a list of transactions for years, but I only want access to provide me with the the total of the transactions up to a certain date. For example, I want access to provide me with a result/query for the total of transactions UP TO September 30, 2019. However, when I tried to run the query using my above query design, it provided a $0.00 answer because the last transaction for September, in this case, occurred on September 27.

So, I am wondering if there would be a way for Access to provide me with the total amount of transactions at the end of the month, BUT if there isn't a transaction on the last day of the month, then just the most recent date before the end of the month.

Is that a little more clear?

1) September 2, 2019: $171.82 sale
2) September 5, 2019: ($51.22) purchase
3) September 19, 2019: ($70.95) purchase
4) September 27, 2019: $506.21 sale

If I were to run my above query with just that data above, for example, it would provide me with a total of $0.00 since there was no transaction on September 30, 2019 on which to provide a total, when in reality the total at the end of the month is $555.86.

[RayMilhon]

The Criteria you have provides you only with transactions on that particular Date.

If you put <= in front of your criteria you'll get all dates Less than or equal to that date If your data includes multiple months you might want to make it Between "[startdate] and [enddate]" putting in what's between the "" will have the query prompt you for a start date and an enddate.

[Micron]

I think I got it now. Use the operators I mentioned. If the dates have no time value (e.g. you used Date() function to insert a date rather than using Now() )
then you one one of the following, depending on the desired range (e.g. January 1, 2019 and September 30, 2019)

BETWEEN #01/01/2019# AND #09/30/2019#
>= #01/01/2019# AND <= #09/30/2019#

or to have a parameter prompt

BETWEEN [Enter Start Date] AND [Enter End Date]
>= [Enter Start Date] AND <= [Enter End Date]

You can also use form fields for date values. That and the parameter prompts have some drawbacks, such as the fact that you can pass anything or nothing and the query will still run.

EDIT -

"[startdate] and [enddate]"

the quotes will raise an error...

[Karl419]

Thank you, both, for your great responses.

I think I got it working now, but I had to change the "Total" to "Where". It didn't work for "Group by", as it simply gave me the total of every transaction UP TO the specified date, whereas I am looking for simply the running total at the end of the period.




Thank you, again.

[Micron]

I don't get why you're still using DateSerial function. If you have a date, you don't need that as it's only returning a date anyway. Why not just >=#09/30/2019# ?

[Karl419]

I appear to have a related problem now. When I run the same query, but trying to pull data from multiple tables, the query results in erroneous data.





I'm not sure what the problem is.

Basically, I have multiple tables that all have different transactions. I would like the query to run so that it will, for example, pull the balance at September 30, 2019 for all the tables. The $610K result and the $619K result are completely incorrect for each of those individual tables.

The result for September 30, 2019 for these two tables, for example, should be around $1600 and $150. I don't understand where those large results are coming from.

Any ideas?

[Micron]

You probably have created a Cartesian Product by not joining the tables. If that is the case, I suggest you do some research on how to create queries as joining tables on related fields is basic knowledge. If you cannot join tables correctly, you might need a UNION query. Having said that, there are so many query types and configurations:
- select
- totals
- update
- append
- union
- stacked
- nested
- find duplicates (or find values missing from one table that are in another).
There are also predicates for TOP n values, DISTINCT and DISTINCT ROW and so on.

[davegri]

If you need to calculate the sums from tables that are independent of each other, you could use Access' DSum function.