By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

MS Access Query

P: n/a
Hi, can anyone help me how to do this query from a table in MS Access.
Table name: A
date | fclass | trx_type | trx_amount
5/2 1 a 57.02
5/2 1 b 60.35
5/3 5 t 41.50
5/4 2 p 49.60
5/5 9 c 55.90

I need to run a query so that it should look like this;

date (group by) | fclass (group by) | trx_amount (sum where trx_type =
a) | trx_amount (sum where trx_type = b) | trx_amount (sum where
trx_type = t) | trx_amount (sum where trx_type = c) | trx_amount (sum
where trx_type = t) | trx_amount (sum where trx_type = p)

Thanks for your help.

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
What you need is a Transform (Crosstab) query. Try the following SQL:

TRANSFORM Sum(A.trx_amount)
SELECT A.date, A.fclass
FROM A
GROUP BY A.date, A.fclass
PIVOT A.trx_type;
"suya" <su*******@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hi, can anyone help me how to do this query from a table in MS Access.
Table name: A
date | fclass | trx_type | trx_amount
5/2 1 a 57.02
5/2 1 b 60.35
5/3 5 t 41.50
5/4 2 p 49.60
5/5 9 c 55.90

I need to run a query so that it should look like this;

date (group by) | fclass (group by) | trx_amount (sum where trx_type =
a) | trx_amount (sum where trx_type = b) | trx_amount (sum where
trx_type = t) | trx_amount (sum where trx_type = c) | trx_amount (sum
where trx_type = t) | trx_amount (sum where trx_type = p)

Thanks for your help.

Nov 13 '05 #2

P: n/a
Thanks. Using a crosstab query gives the result that I wanted. Is there
a way to save this result in a new table? Something like using a 'make
tabele query'?

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.