| View previous topic :: View next topic |
| Author |
Message |
allenc92
Newbie
Joined: 02 Sep 2011
Posts: 4
Location: Singapore
|
 Posted: Tue Sep 06, 2011 11:06 pm Post subject: Sorting of data into different fields |
 |
|
Hi guys, i need your help again :/ i would like to ask if there is by any chance, a way to calculate the amount according to the type, for example, user A has claimed $100 on travelling on 1/1/2011, and $500 on accomodation on 2/1/2011, and claimed another $200 on travelling on 3/1/2011. Can i sort the total amount out according to the type of claims the user made? Like $300 on travelling and $500 on accomodation. Thanks guys  |
|
| Back to top |
|
 |
mgroenescheij
Super User
Joined: 20 Apr 2011
Posts: 862
Location: Australia
|
| Posted: Thu Sep 08, 2011 12:20 am Post subject: |
|
|
Hi,
Depending if you have defined the Claim Type you can use the SUMPRODUCT() function.
Martin
Edit: Sorry I missed that the question was related to Base
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Last edited by mgroenescheij on Thu Sep 08, 2011 2:10 pm; edited 1 time in total |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2473
Location: 3rd Rock From The Sun
|
| Posted: Thu Sep 08, 2011 6:13 am Post subject: |
|
|
allenc92:
You said / asked:
| allenc92 wrote: | | Can i sort the total amount out according to the type of claims the user made? Like $300 on travelling and $500 on accomodation. |
Yes you can. This is called an Aggregate Query.
I will assume, based on your words, for each User and Category, you want a SUM for Amount.
Start by making your Query, and, include on the Field line, the User column, the Category column and the Amount column.
For the two columns, User and Category, on the Function line drop-down menu, choose Group . . . and . . . on the Amount column, choose Sum from the Function drop-down list.
Run your Query, and, it will return the Sum for each unique combination of User and Category.
Additionally, if you want, you could SORT ( order ) the results by User and Category, if you so desired.
I hope this helps, please be sure to let me / us know.
Note to Martin: This question was asked in the Base forum, not the Calc forum, so, Calc function SUMPRODUCT() does not apply. 
Sliderule
Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
allenc92
Newbie
Joined: 02 Sep 2011
Posts: 4
Location: Singapore
|
| Posted: Thu Sep 08, 2011 5:09 pm Post subject: |
|
|
| Somehow, i have an error, but i managed to bypass this, by making an additional 2 inputs, the user will have to key in which type of claims he/she is making. Thanks guys for your help ^^ |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
