Solved

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

Posted on 2016-10-27
2
44 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
[X]
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
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

726 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