Moving Sumif formulas into Access

[Magnolia1]

Hello Everyone,
I am reaching out to you for your help. I am moving a process that was built in Excel into Access and came across with an issue where I am unable to replicate a SUMIFS formula to a work around in Access. I know that there is no such thing as SUMIFS in Access, but there must be a way to get it work. I tried grouping and Crosstab but the issue is that the result that I received was not the result that I was looking for. The grouping did well in scenarios where start date and end date of the event fell into the same months, however, in scenarios where I have a start date for example in 02/01/16 and end date 5/31/16, the result gave me one lump sum that was placed into Sale End Date bucket. The grouping process does not break out the lump sum into per monthly amounts. For Example: Column Y represents Stock Amount, Column X represents Group ID, Column V represents Sale Start Date and column W represents Sale End Date. Since the sale for the Group 1, Stock Amount 32.8 took place for period from 2/1/16 to 5/31/16 I am looking for the following result:
Group ID; Total; February; March; April; May


1; 131.20; 32.8; 32.8; 32.8; 32.8

The formula that I need to replicate is as followsSUMIFS(MM!$Y:$Y,MM!$X:$X,RIGHT($B4,2),MM!$V:$V,"<= "&D$2,MM!$W:$W,">="&D$2).
The logic behind it as follows:
It's basically saying sum the value in column y if the value in column X MM = the value in right(B4,2) MM Stats, AND if the value in column V MM is less than or equal to the value in D2 MM Stats AND if the value in Column W MM is Greater than or equal to the value in D2 MM Stats.

Can someone please help me to resolve this issue? Either formulas or VBA code will work for me.

Thanks, Magnolia1.

[orange]

Have you done some "google-ing"??
Here is one link:
http://www.access-programmers.co.uk/...d.php?t=178746

[Magnolia1]

Hello Orange,
Yes, I googled this before to post my question. Also, I tried DSum in 2 different ways: DSum by itself and with IIf in it and may be I am doing something wrong, but I came up with the same result as I indicated above. The formula did now work for me as I wanted.

I was thinking here... I got another data set that I can use, it contains Total Stock Amount, it is not broken down by month that I had previously listed. In case where Sale Start Date (2/1/16) and Sale End Date (5/31/16), I could build a formula to get total number of months affected, in this scenario it will be 4 months. Also I could break out the Total Stock Amount (131.20) into per month amount which will be 32.8 per month. I could isolate the individuals who's Sale End Date is greater then 1 months. I would need to add the records to every member in this case. For example: Group ID: 1 , had 4 month. I would add 3 additional records to Group ID 1 and since I divided Total Stock Amount into per monthly amounts, I could add Stock Amount for new records and update the Stock Amount for the record that had the Total Amount listed. Also I would add Sale Start and Sale End Date to every new record, then I could use grouping to group the broken by month payments.
The issue is that I am very new to SQL and VBA and I do not have enough knowledge to build the code to add additional records to the isolated table and add/update Stock Amount and Sale Start/End Dates.
Could someone help me with it?
Thank you,

[orange]

I am not an excel person.
You have provided info that others may use to mock something up for Access.
Have you tried creating a flowchart or diagram that shows the logic of the issue involved? I'm suggesting you ideentify the logic independent of Access vba or SQL.

Along the line:
Start

If XXX > YYY then....
do this
else... do that
....


This clarifies it in the communications and in your approach. It is difficult for anyone not familiar with your specific issue, excel, vba and sql to get into the middle of the issue logically.

Good luck.

[Magnolia1]

Thank you Orange for you input. I appreciate it.

[orange]

Cross posted http://www.access-programmers.co.uk/...d.php?t=287422

[Magnolia1]

Hello Orange, Thank you. I found the answer that I was looking for.
This question was answered few hours ago.

[orange]

Perhaps you could point readers to the solution. You're not the first to ask this question.
Glad you have it solved.