Display different records in a combox in a query under own heading

[neelsfer]

I have a table called Transactions
The Autonumber field is called TransactId
The main field = Transaction
If the TransactId = 1 = Sale
If Transactid = 2 = Purchase
In a query called qryData where i have the client and quantity and transaction fields, i would like to have columns called Sale and Purchase with the quantity displayed depending whether the TransactId =1 or 2

Client TransactId Quantity Sales Purchase
Peter 1 10 10 0
John 2 20 0 20

In the query

Sales:IIf([transactid]="1",[Quantity],"0")

Do i use the iff function.I get #Error. Any suggestions?

[Rabbit]

If what you are saying is correct and transactid is an autonumber, then that it's a numeric data type but you're treating it like a string.

[jimatqsi]

Just as Rabbit is saying ... change "1" to 1 and "0" to 0

Jim

[neelsfer]

Thx thx rabbit , i have been wrestling with this issue for a year. Yesterday while out riding my mountain bike i realized that i am doing things very inefficiently and the size of my database needs to be trimmed ASAP. i can now probably half the size of my database, as i use to append this data individually into about 15 separate fields in the table, in real time.

[neelsfer]

thx jimatqsi for your input also