Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How can I use the Ms Access function DSUM in a manner similar to SUMIFS? Example Provided

Posted on 2016-10-27
2
40 Views
Last Modified: 2016-10-27
If you look at the posted Excel example you will find a SUMIFS function that is deriving last week's number by using the store as the first criteria and the weekstartdate-7 as the second criteria. Is there a method where I can achieve the same results using the data in  columns A-C to accomplish what the function is doing in D?
Example.xls
0
Comment
Question by:Ryan Simmons
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41863242
better is to use a Query with subQuery, import your excel file to access and call it myTable

SELECT myTable.[Store Number], myTable.WeekStartDate, myTable.WFMR_DEMAND, CDbl(Nz((select Min([WFMR_DEMAND]) From myTable As A where A.[Store Number]=myTable.[Store Number] And DateDiff("d",A.[WeekStartDate],myTable.[WeekStartDate])=7),0)) AS Expr1
FROM myTable;
0
 

Author Closing Comment

by:Ryan Simmons
ID: 41863299
Good point. Ty!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question