Currently I have a query that returns records like this:
ChargeCode Type Price
THC Credit 6200
OTH Debit 3000
THC Debit 5400
Then i need to generate a report for it. I do not want the report to show as follows
Debit ChargeCode Credit
5400 THC 6200
3000 OTH
How can I do it? I can not find any way to have Access allow us to customize how we output for each record. Or we have the main query that output the charge code, then the left col will do the query based on received charge code and type="debit", the right col will do the query based on received charge code and type="credit"?
I don't really understand your concern. If it's the order of the columns that bothers you, then the query...
TRANSFORM Sum(Charges.[Price]) AS SumOfPrice
SELECT Charges.[ChargeCode]
FROM Charges
GROUP BY Charges.[ChargeCode]
PIVOT Charges.[Type] In ("Debit","Credit");
...will produce the following result:
ChargeCode Debit Credit
---------- ----- ------
OTH 3000
THC 5400 6200
You can then create a report using that query as its Record Source, but when you create the Report you can specify the columns in whatever order you prefer, anyway.