sum of a certain field in a query

[hunterfan48]

Please take a look at my screen shot...it will explain it much better than I could ever try in words. LOL

Nonetheless, from the screen shot as you see, I would like to take [Transaction Type] from my 'tblTransactions' and find the sum of the total transactions equaling that certain transaction type.

For example, in my field [Transaction Type] there is a value titled 'Card Sale.' This is a record in my table (tblTransactionType). I would like to have this query calculate the sum of the net price of all records that have 'card sale' selected in the field 'transaction type'.

Please advise...thanks!
Brady

 

[Brianwarnock]

I mustn't be understanding the problem as it seems too simple

just group by transactiontype and sum the netprice field which ever that is, of course you cannot include other fields in the query unless they are also part of the aggregate function, which they probably aren't.

Brian

 

[hunterfan48]

I don't have any option to do that, at least from what I can see.

So starting at the top as shown in my screen shot, what would I do next? If I add a certain field, where is the group by option?

Edit:: Found it LOL

So now I have the field [Transaction Type] from my table so when I query it shows me the 9 different types of transactions I have. Now I would like to have a sum of the total dollar amount of the transactions that are marked with each specific [transaction type].

For example, in my tblTransactions there are 45 different records that match 'card sale.' In this table, there is a field for price. I want to add up the total (aka sum) of all the records and the corresponding price value for that record to find out how many dollars worth of total sales I've had. Is this possible?

 

[Brianwarnock]

I'm not following this.

You have done a Groupby of TransactionType with a criteria of "card sales" and a sum of Price, now what?

On reading your post again are you asking to sum each transactionType and also have an overall Sum? This cannot be done in 1 query do it in a Report with the Grand total in the report footer.

Brian

 

[hunterfan48]

I want to find the sum of the total price for each record that has the criteria in transaction type that matches 'card sale.' Or 'card purchase,' or 'memorabilia sale' etc. etc.

Soooo in my table again, I have a record that is a 'card sale' and it has a price of $300. Again, I have another record that is a 'card sale' and it has a price of $25. I want a query that will sum up the total price of each card sale.

So then this query based on these two records would show a sum of $325 for card sales.

Can this be done in a simple query like this?? Did I explain it better??

Let me know

Thanks
Brady

 

[Brianwarnock]

That is a simple Totals query Groupby transaction type and sum price, with no criteria it will sum each of all of the transaction types or criteria can restrict the selection. What have you tried, post the SQL.

Brian

 

[hunterfan48]

Do I need to do a crosstab query then?

I did try one and here's the sql I came up with...I'm checking right now to see if it calculated the amounts correctly.

TRANSFORM Sum(qryTransactionsNetPrice.Price) AS SumOfPrice
SELECT qryTransactionsNetPrice.TransactionType, Sum(qryTransactionsNetPrice.Price) AS [Total Of Price]
FROM qryTransactionsNetPrice
GROUP BY qryTransactionsNetPrice.TransactionType
PIVOT qryTransactionsNetPrice.Summary;

 

[Brianwarnock]

I mustn't be understanding the problem as it seems too simple

Brian

I said this back at post #2 and I guess I should have stayed out of it because I'm lost

Brian

 

[cookiegary01]

Hi,
Try this... (I think I understand):
In the Query delete your Query on the right [QyCardTransactions] this is probabaly overdone.
Then make make your Quer7 a "Totals query" and Groupby [Type] and Sum [Price].
Run the Query

 

[Brianwarnock]

Hi,
Try this... (I think I understand):
In the Query delete your Query on the right [QyCardTransactions] this is probabaly overdone.
Then make make your Quer7 a "Totals query" and Groupby [Type] and Sum [Price].
Run the Query

Isn't that what I said in posts 2,4 and 6

Brian

 

[cookiegary01]

It's exactly what you said Brian,

I just gave it another approach.
I'm trying to explain it in "the requestor's level of understanding".

 

[Brianwarnock]

Fair enough let's hope that you have more success than I did

Brian

 

[hunterfan48]

I get the feeling my ignorance is being pointed out??? LOL isn't this is what a forum is for tho?? To help others??

I ended up doing a crosstab query...should I have done that or not??
It gave me the results I wanted...calculated everything out perfectly except I've got a bunch of columns to the right of it which make no sense to me. What do those mean? I created a report off of this crosstab query and then deleted those extra columns...I would like to know what they mean tho...

THANKS TO COOKIE FOR EXPLAINING IT A LIL FURTHER! However, you were wrong in that I didn't need that query (but that's not your fault...jus sayin) The reason I need that query is because I want to find the sum of not 'price' but the 'summary' which is found in this query that you told me to delete.

After reading your post, I was able to figure it out. Thanks to the both of you...

 

[Brianwarnock]

I mustn't be understanding the problem as it seems too simple

just group by transactiontype and sum the netprice field which ever that is, of course you cannot include other fields in the query unless they are also part of the aggregate function, which they probably aren't.

Brian

The above is my first post, if we look at query7 now that we know that the field you wish to sum is summary just click on the totals icon, top row, this opens up the Totals row , change the drop down in the summary column to Sum and run.
I actually think that you could run this just on the query as it contains all the fields that you need.

Brian

 

[hunterfan48]

You are correct and now I understand...

I forgot about the totals for the query...that's what threw me off.

 

[Brianwarnock]

It is often the simple things that throw us, when we can't see the wood for the trees.

Brian