SL Sum question

[Tlou5831]

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!

[ck9663]

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

select Username, sum(RpleNumber) as sum_role from usertable group by username

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:

select username, rolename, sum(roletable.rolename)
from usertable inner join roletable on usertable.role = roletable.role
group by username, rolename

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

[Tlou5831]

To get sum of role number per Username

select Username, sum(RpleNumber) as sum_role from usertable group by username

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:

select username, rolename, sum(roletable.rolename)
from usertable inner join roletable on usertable.role = roletable.role
group by username, rolename

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

[ck9663]

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:

select [User Name], sum(RoleNumber) as sumrolenumber from TBL_AppUsers group by [User Name]

Have you tried posting it on the Access forum?

-- CK

[Tlou5831]

Thanks the code worked great!

If I wanted to make this a Maker table query where the Sum would output to a field called "SumRoleNum b" 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

[ck9663]

Thanks the code worked great!

If I wanted to make this a Maker table query where the Sum would output to a field called "SumRoleNum b" 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

[Tlou5831]

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.