By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,685 Members | 1,902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,685 IT Pros & Developers. It's quick & easy.

SL Sum question

P: 8
Hello All,

I pretty new to access and kinda stumbling my way through making a database
I made a SQL query to assign a numerical values to each role.

For example:

TNS role - WOPS = 28
App Role - INQ = 1
App Role - Invest = 27

If you add up both of the app roles it will equal 28. The issue that I am having is making the SQL expression to look at the User Name (constant) column and for the same users add up all of the numbers in the Role Number column.

For example:

User UserName Role RoleNumber
JSmith John Smith INQ 1
JSmitty John Smith Invest 27

If we add up all of the numbers in the Role Number column it comes up to 28 and will match the TNS role number for validation.

I am looking to make a SQL query that will look and see if the Username name is the same then it will add up all of the Role Numbers for that user and export to a different column.

Thanks for the help!
Feb 13 '08 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
Hello All,

I pretty new to access and kinda stumbling my way through making a database
I made a SQL query to assign a numerical values to each role.

For example:

TNS role - WOPS = 28
App Role - INQ = 1
App Role - Invest = 27

If you add up both of the app roles it will equal 28. The issue that I am having is making the SQL expression to look at the User Name (constant) column and for the same users add up all of the numbers in the Role Number column.

For example:

User UserName Role RoleNumber
JSmith John Smith INQ 1
JSmitty John Smith Invest 27

If we add up all of the numbers in the Role Number column it comes up to 28 and will match the TNS role number for validation.

I am looking to make a SQL query that will look and see if the Username name is the same then it will add up all of the Role Numbers for that user and export to a different column.

Thanks for the help!
To get sum of role number per Username

Expand|Select|Wrap|Line Numbers
  1. select Username, sum(RpleNumber) as sum_role from usertable group by username
  2.  

Now, let's say you have a table (roletable)
rolename - role - rolenumber
TNS role - WOPS - 28
App Role - INQ - 1
App Role - Invest - 27

You can do a:

Expand|Select|Wrap|Line Numbers
  1. select username, rolename, sum(roletable.rolename)
  2. from usertable inner join roletable on usertable.role = roletable.role
  3. group by username, rolename
  4.  
Assumptions:
1. The role in usertable are all in roletable, if not, do a left join instead.
2. The rolenumber in usertable is always equal with the rolenumber in roletable. If it is, you can remove the rolenumber in usertable and just keep the table relationship.

-- CK
Feb 13 '08 #2

P: 8
To get sum of role number per Username

Expand|Select|Wrap|Line Numbers
  1. select Username, sum(RpleNumber) as sum_role from usertable group by username
  2.  

Now, let's say you have a table (roletable)
rolename - role - rolenumber
TNS role - WOPS - 28
App Role - INQ - 1
App Role - Invest - 27

You can do a:

Expand|Select|Wrap|Line Numbers
  1. select username, rolename, sum(roletable.rolename)
  2. from usertable inner join roletable on usertable.role = roletable.role
  3. group by username, rolename
  4.  
Assumptions:
1. The role in usertable are all in roletable, if not, do a left join instead.
2. The rolenumber in usertable is always equal with the rolenumber in roletable. If it is, you can remove the rolenumber in usertable and just keep the table relationship.

-- CK
The Table that I need to Query on is call TBL_AppUsers and in that table are fields called:

User Name, Role and RoleNumber

Basically, if the User name is the same I want to get the sum of all of the RoleNumber fields.

So if:

John Smith INQ 1
John Smith INVEST 27

The output into another Field will be 28 for John Smith.

Remember that I am a real Newb with Access so be gentle .

Thanks
Feb 13 '08 #3

ck9663
Expert 2.5K+
P: 2,878
The Table that I need to Query on is call TBL_AppUsers and in that table are fields called:

User Name, Role and RoleNumber

Basically, if the User name is the same I want to get the sum of all of the RoleNumber fields.

So if:

John Smith INQ 1
John Smith INVEST 27

The output into another Field will be 28 for John Smith.

Remember that I am a real Newb with Access so be gentle .

Thanks
So this should be fine:

Expand|Select|Wrap|Line Numbers
  1. select [User Name], sum(RoleNumber) as sumrolenumber from TBL_AppUsers group by [User Name]
Have you tried posting it on the Access forum?

-- CK
Feb 13 '08 #4

P: 8
Thanks the code worked great!

If I wanted to make this a Maker table query where the Sum would output to a field called "SumRoleNumb" so I can keep as few tables as possible.

I attempted to do this but when I entered the other fields of my table the sums did not work. (This might be due to the multiple instances of the same user name.)

Is it possible to show only 12 instance of each user and have a total of the role numbers?

Thanks
Feb 14 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Thanks the code worked great!

If I wanted to make this a Maker table query where the Sum would output to a field called "SumRoleNumb" so I can keep as few tables as possible.

I attempted to do this but when I entered the other fields of my table the sums did not work. (This might be due to the multiple instances of the same user name.)

Is it possible to show only 12 instance of each user and have a total of the role numbers?

Thanks
If those 12 have common characteristics, yes. Is it correct to have all those multiple instance? Maybe it deserve a second look. You might not be solving the real problem.

-- CK
Feb 14 '08 #6

P: 8
If those 12 have common characteristics, yes. Is it correct to have all those multiple instance? Maybe it deserve a second look. You might not be solving the real problem.

-- CK
It must have been way too early when i posted the above.

I basically am looking for my new table to look like this.

ID UserName Role SumRoleNumber
Jsmith JohnSmith INQ 28
Jsmitty JohnSmith Invest 28
JDoe John Doe INQ 29
JDoee John Doe PROCBODEOD 29

Where the SumRoleNumber field is the sum of the RoleNumber field from the Query.

Thanks for the patience.
Feb 14 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.