SQL Query

[Diane]

I have a database with the following columns:

GLACCT
TRANS
ACCT
LASTNAME
FC
STATUS
ADMIT date
DISCH date
TYPE
ADJ AMT
I/O
CODE
CHARGES

I would like to do a query where based on disch date, it would put the adj amt amount in a column.

For example, I have these discharged dates and adj amts:
DISCH TYPE ADJ AMT
02/05/99 TCU 534.66
06/11/02 MED -6,149.23
07/07/03 MED -141.6
02/05/99 TCU -534.66
01/17/01 MED -117.68
05/08/03 SCC -24.85
05/09/03 SCC -23.12

I would like to have three columns in my query based on disch date so my data I am trying to get returned would look like this:

DISCH ADJ AMT 2003 2002 2001
02/05/99 534.66 534.66
06/11/02 -6,149.23 -6,149.23
07/07/03 -141.6 -141.6 -141.6
02/05/99 -534.66 -534.66
01/17/01 -117.68 -117.68
05/08/03 -24.85 -24.85
05/09/03 -23.12 -23.12

I tried using the following criteria but I am not allowed to use disch date.
Disch>09/30/2002 for 2003 col
Disch>09/30/2001 for 2002 col
Disch<10/01/2001 for 2001 col

I Can't get the amts to line up in a nice col -- these amts s/b
in Col 2001
534.66
-534.66
-117.68

in Col 2002
-6,149.23
-141.6

in Col 2003
-141.6
-24.85
-23.12


Is there any way to accomplish what I am trying to do?

Thanks for any help














































































































































































































































[/url][/list][/code][/list]

 

[SydneyGeek]

You could try a new field called DISCH Year in the query. Something like:
DISCH Year:Iif(Year([DISCH])<=2001,2001,Year([DISCH]))
That should give you 2001, 2002, 2003 as appropriate. From there, build a crosstab query based on the first query.
1. Put the following into the Row header: [DISCH] and [ADJ]
2. Put into the Column Header: [DISCH Year]
3. Put into the Value: [AMT] and choose Sum as the calculation.

You may need to tweak it a bit but that should give most of what you want.

HTH

Denis