SQL Query

[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!

[Stewart Ross]

Hi. In answer to your question, you could use

Select [User Name], sum(Role Number]) as [Role Total] from [Your Table];

where Your Table is the actual name of your table.

However, I am sure that 'user name' is not an appropriate field to do this on - you can have two or more different users with the same name. Your tables do not appear to be normalised, a very basic step in database design, as unless you are joining to a User table to create your view you should have a single User table with a unique key field for each user, and not be repeating the user name on every line in your role table. Instead, use the unique User ID as a secondary key and group on this in your role total query.

Regards

Stewart