INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Summing data based on user criteria

VBA Summing data based on user criteria

(OP)
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

RE: VBA Summing data based on user criteria

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_fu...
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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Summing data based on user criteria

(OP)
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

RE: VBA Summing data based on user criteria

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...

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Summing data based on user criteria

(OP)
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

RE: VBA Summing data based on user criteria

(OP)
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;

RE: VBA Summing data based on user criteria

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...

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Summing data based on user criteria

(OP)
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.

RE: VBA Summing data based on user criteria

To easily normalize your table in Excel...
FAQ68-5287: NORMALIZE Your Table using the PivotTable Wizard

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close