MOP column to different column of values (1 Viewer)

[moi]

Hi all,

I have a query with MOP (mode of payment) column, like.

Cash 500
Cash 300
GCash 400
Bank 300
Bank 600
Cash 300
Bank 300
GCash 200

Now, this one i could not figure out how to make a total for each mode (cash, gcash, bank) in separate column.

 

[plog]

I don't fully understand what you have, nor what you want.

Do you have a query with just one column? And in that one column you have a value that is like "Cash 500"? Or is "Cash" in one column and 500 in another column?

It should be 2 columns--one for the string value ("Cash") and one for the numeric value (500). In which case it is a simple query query that would look something like this:

SELECT PaymentMode, SUM(PaymentAmount) AS TotalAmount
FROM YourQueryNameHere
GROUP BY PaymentMode

If it is just 1 column, then you have bigger issues to resolve than this query--namely setting up your tables and fields properly.

 

[moi]

I don't fully understand what you have, nor what you want.

Do you have a query with just one column? And in that one column you have a value that is like "Cash 500"? Or is "Cash" in one column and 500 in another column?

It should be 2 columns--one for the string value ("Cash") and one for the numeric value (500). In which case it is a simple query query that would look something like this:

SELECT PaymentMode, SUM(PaymentAmount) AS TotalAmount
FROM YourQueryNameHere
GROUP BY PaymentMode

If it is just 1 column, then you have bigger issues to resolve than this query--namely setting up your tables and fields properly.

hi,
here are some columns of my query..

 

[moi]

I don't fully understand what you have, nor what you want.

Do you have a query with just one column? And in that one column you have a value that is like "Cash 500"? Or is "Cash" in one column and 500 in another column?

It should be 2 columns--one for the string value ("Cash") and one for the numeric value (500). In which case it is a simple query query that would look something like this:

SELECT PaymentMode, SUM(PaymentAmount) AS TotalAmount
FROM YourQueryNameHere
GROUP BY PaymentMode

If it is just 1 column, then you have bigger issues to resolve than this query--namely setting up your tables and fields properly.

Sorry my bad, i have 2 columns, (mop and amountpaid)..

 

[moi]

I don't fully understand what you have, nor what you want.

Do you have a query with just one column? And in that one column you have a value that is like "Cash 500"? Or is "Cash" in one column and 500 in another column?

It should be 2 columns--one for the string value ("Cash") and one for the numeric value (500). In which case it is a simple query query that would look something like this:

SELECT PaymentMode, SUM(PaymentAmount) AS TotalAmount
FROM YourQueryNameHere
GROUP BY PaymentMode

If it is just 1 column, then you have bigger issues to resolve than this query--namely setting up your tables and fields properly.

Should i put that in a query column, pls bear with im a newbie and trying to lern.

 

[moi]

Should i put that in a query column, pls bear with im a newbie and trying to lern.

I got a msg "the syntax of the subquery in this expression is incorrect..

 

[plog]

You shoudl create a new query, in Design View switch to SQL view, take the code I wrote and paste it in. Then change the fields in the SELECT and GROUP BY clauses to the right ones and change the FROM clause to the right query name (the name of the one you showed me data from).

 

[Pat Hartman]

Select MOP, Sum(AmountPaid) as SumAmt From YourTable
Group By MOP
Order by MOP;

If you want the sum by date, include date and add a group by for the date. If you want to sum by month, then use Format()

Select MOP, Sum(AmountPaid) as SumAmt, Format(PaidDT, "yyyy-mm") as yyyymm From YourTable
Group By MOP, Format(PaidDT, "yyyy-mm")
Order by MOP Format(PaidDT, "yyyy-mm");

 

[cheekybuddha]

I have a query with MOP (mode of payment) column, like.

Now, this one i could not figure out how to make a total for each mode (cash, gcash, bank) in separate column.

SELECT
  SUM(IIf(MOP = 'CASH', AmountPaid, 0)) AS Cash,
  SUM(IIf(MOP = 'GCASH', AmountPaid, 0)) AS GCash,
  SUM(IIf(MOP = 'BANK', AmountPaid, 0)) AS Bank,
  SUM(IIf(MOP = 'CHECK', AmountPaid, 0)) AS [Check],
  SUM(IIf(Len(MOP & '') = 0, AmountPaid, 0)) AS Blank
FROM YourQuery
;

 

[Pat Hartman]

I try not to write queries that need to be changed when data changes. Just sayin'. I'd do a crosstab if that's what I really wanted.

 

[cheekybuddha]

Now, this one i could not figure out how to make a total for each mode (cash, gcash, bank) in separate column.

Crosstab would certainly be a solution.

An example using the data here would help everyone even more.

 

[moi]

Crosstab would certainly be a solution.

An example using the data here would help everyone even more.

hi,

i am not familiar with crosstab yet.. can you connect me to a demo link pls..

 

[moi]

SELECT
  SUM(IIf(MOP = 'CASH', AmountPaid, 0)) AS Cash,
  SUM(IIf(MOP = 'GCASH', AmountPaid, 0)) AS GCash,
  SUM(IIf(MOP = 'BANK', AmountPaid, 0)) AS Bank,
  SUM(IIf(MOP = 'CHECK', AmountPaid, 0)) AS [Check],
  SUM(IIf(Len(MOP & '') = 0, AmountPaid, 0)) AS Blank
FROM YourQuery
;

Many thanks cheekybuddha

 

[Gasman]

hi,

i am not familiar with crosstab yet.. can you connect me to a demo link pls..

Here, I have Google on this laptop.

access crosstab query - Google Search

 

[Pat Hartman]