VBA Summing data based on user criteria

[Igawa29]

So I have a dilemma I am trying to work through. I have 1 access table with 13 fields (Name, Jan Proj, Feb Proj, Mar Proj, Apr Proj, May Proj, Jun Proj, Jul Proj, Aug Proj, Sep Proj, Oct Proj, Nov Proj, Dec Proj).

The names can contain duplicates and the Month Proj contains either a 0 or a positive number.

I was thinking about creating a form which allows a user to select a distinct list of user names (keeping a * in the drop down for all), then allowing the user to select the month they wish to run through for data.

So for example if the user Selects Dave Righetti along with August for the month, I want access to run a query to give populate a summary result:

[Jan Proj]+[Feb Proj]+[Mar Proj]+[Apr Proj]+[May Proj]+[Jun Proj]+[Jul Proj]+[Aug Proj] = August Projected


I want this to automatically Export into an Excel file (Which I believe I have code for). I am looking for syntax too, since I will have to do other similar calculations like this for other fields and tables as well (Though if I am combining tables I might do a simple query first, then do my calculation)

Any help on this would be great. I know it has to be something like

If (User Input) = August
then [Jan Proj]+[Feb Proj]+[Mar Proj]+[Apr Proj]+[May Proj]+[Jun Proj]+[Jul Proj]+[Aug Proj]

Else If (User Input) = September

.....etc etc etc etc

 

[SkipVought]

Hi,

Your table design leaves a lot to be desired. Klunky.

1. Your table is not normalized: a definite no no.

http://sbuweb.tcu.edu/bjones/20263/access/ac101_fundamentalsdb_design.pdf

2. Using text for dates is really klunky.
3. What about year? Are you always just interested in the current year alone?
4. What happens to this table next January? Will historical data be deleted?

Some other questions:

1. "The names can contain duplicates" Does that mean there could be two or more rows with Dave Righetti? So how would the Month Projected be calculated?
2. Will your Month Projected ALWAYS be a YTD calculation?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Igawa29]

Hey Skip,
Thanks for your response, and unfortunately I do not control the data being fed into the table, I just need to figure out how to work with what I am given, which is why I came over to these forums.

Year will not matter, since the user will export the results in December. Plus the user will be adding records throughout the year to populate the month data.

So yes the names can contain duplicates, I might add another field called JOB ID, which won't contain unique values but combined with the name should create unique values through that combination.

Also Month Projected will always be a YTD calculation, that's why I was thinking an IF THEN ELSE IF statement would work based on the user selection of month.


Appreciate the help on this, I hope I made things more clear.

~Matt

 

[SkipVought]

I'm not talking about data FED into the table. I'm referring to tha STRUCTURE of the table.

With a structure like

Name, MonthNum, ProjValue

you could perform a simple query to sum the ProjValue from 1/1/2016 to whatever MonthNum is selected for any Name...

Select Sum(ProjValue)
From YourTable
Where Name = SomeSelectedName
  And MonthNum <= SelectedMonthNum

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Igawa29]

Hey thanks Skip, I will try to restructure the table that way.

I might have a few other questions along the line, but I am going to work on this for a little while. Thank you

 

[Igawa29]

I was just curious, I was trying something else to play with. Why wouldn't this work as a query? Its telling me I am missing an operator:

SELECT Sheet1.Name,
SUM(CASE WHEN FORMS!Main!Month = 'January'
THEN Sheet1.Jan
END) as January
FROM Sheet1;

 

[SkipVought]

So you have a table named Sheet1? Hmmmmmmmm. Excel as source?

Well to your question. Case is not a correct syntax for Access: IIF() is...

SELECT Sheet1.Name,
SUM(IIF( FORMS!Main!Month = 'January'
, Sheet1.Jan
,0) as January
FROM Sheet1
Group By Sheet1.Name

But this code is gonna get ugly, which is what happens when your table structure is not normalized.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Igawa29]

Yeah, its been an interesting project so far. I just have it named Sheet1 for testing, which will be changed once the code works for me.

I do agree this code will start getting ugly, I was trying to come up with normalization solutions for the tables themselves.

 

[SkipVought]

To easily normalize your table in Excel...
FAQ68-5287

Alternatively Here's some SQL to start with in Access...

Select Name, 1 As ProjMon, [Jan Proj] As ProjVal
From Table1
Where [Jan Proj] > 0
UNION ALL 
Select Name, 2, [Feb Proj]
From Table1
Where [Feb Proj] > 0
UNION ALL 
.......etc.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Having excel 2010+ version, you can simply unpivot data in excel before importing it to access, only mouse actions with power query add-in:

https://support.office.com/en-ie/ar...er-Query-0f7bad4b-9ea1-49c1-9d95-f588221c7098

combo