DSum Function with Multiple Criteria in Microsoft Access to Calculate Previous 30 Days Sales Totals
HTML-код
- Опубликовано: 21 окт 2024
- In this Microsoft Access tutorial, I will show you how to calculate the previous 30 days of paid and unpaid orders using the DSum function. We'll learn how to use it with multiple criteria as well.
LEARN MORE:
599cd.com/DSum
PRE-REQUISITES:
DLookup: 599cd.com/DLookup
RECOMMENDED COURSES:
Access Expert 11: 599cd.com/ACX11
LINKS:
Date Criteria: 599cd.com/Date...
Concatenation: 599cd.com/Concat
Dashboards: 599cd.com/Dash...
NZ Function: 599cd.com/nz
Aggregate Query: 599cd.com/Aggr...
USAGE:
DSum("OrderTotal","OrderT","OrderDate XXX= Date()-30")
DSum("OrderTotal","OrderT","OrderDate XXX= #" & Date()-30 & "#")
DSum("OrderTotal","OrderT","OrderDate XXX= #" & Date()-30 & "# AND IsPaid=TRUE")
NOTE: I had to put XXX above to represent the less-than symbol because RUclips doesn't allow them in their description fields.
BECOME A MEMBER:
RUclips: / @599cd
or My Site: 599cd.com/THMe...
ADDITIONAL RESOURCES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/Tech...
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.o...
products.offic...
microsoft.com/...
KEYWORDS
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, DSum Function, DSum(), ms access dsum multiple criteria, dsum function ms access, ms access vba sum function
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
The most generous guru for Access DB. Thank you so much Richard. Excellent works.
My pleasure!
Wirh close to 3 decades of knowledge and training. 🤔👍
Rost is the Oracle of ms access database, I have learn so much from him. Double respect to you sir,
Pls Sir how can we Link ms access to dataverse and azure. We humble beg you Master teach us
Dear My Instructor, This Was Awesome , Thank You
Welcome
Good tutorial. Hopefully just a quick question. I have 4 tables tblProj, tblWO, tblAMES and tblEMES. tblProj is related to tblWO by an ID Number. tblWO (field WOID)is related to tblAMES (field WOOrder) and tblEMES (field WOOrder). I am trying to create a query/report that sums all tblAMES values and all tblEMES values based on the WOID/WOOrder fields. I assume that requires DSum but I continue to receive #Error results.
SumofAMES: DSum("AMESCst","tblAMES","WOOrder=" & WOID)
SumofEMES: DSum("EMESCst","tblEMES","WOOrder=" & WOID)
Brother I Need Help!
i wanted to find Total Stay Payment. Dose this code correct or not? Ex: Customer check in on Date 20-04-2022 and Check Out on 25-04-2022. For Staying 1 day cost $20
Please correct me.
SELECT Customer, Sum(DateCheckOut - DateCheckIn) as Total_Stay
FROM Where TotalDay, Sum(CostPrice * Stay) as Total_Pay
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT
The #" & Date() & "# is not working for me does not return the value unless i just put Date() in my criteria for DSUM("QTY","TableT", "[DateCompleted] = #" & Date() & "#")
Excellent tutorials, however I use D functions in limited fashion and when I need to I source them a different way. I typically do not perform Dsums or DLookups or any of the others directly against a table. The reason being is that the D functions are resource hogs in large record sets. I will source them to query specifically for these purposes that have the record set type property set to snapshot. This increases the performance especially in large data sets. I will generally only use them in single form screens.
They're SUPER resource hogs! I always say use them sparingly... single forms, one record at a time.