Help with query!

[Wren17]

Hello Everyone,

I have a little problem with a query and was wondering if this could be done.

I have a field entitled Payment form.
In this field there can be 5 different things. 0 for Null, C for Card,
D for Cod, E for E-check, and W for Wbill.

I have another field entitled Sub Payment
Now sub payment is broken down into the different kinds of payments.

Now the problem that I am having is when I run my query the results yeild 5 different lines. I understand why it does this, but
I really need it to be broken into 5 different columns. so that everything goes to one line. I have an example below of what
It looks like after I run my query.

payment_form Sub Payment
0 35.00
C 35.00
D 35.00
E 35.00
W 35.00

I need it to look like this:
Null
35.00
next column
Card
35.00
and so forth
all on the same line



So I guess my question is. How can I create 5 different columns from one single column?

I was thinking that I might have to get more advanced and use some SQL on this, I could be wrong.

Any help would be appreciated.

Thanks,
Wren

 

[antomack]

I don't know of any particular way of getting your query to result in 5 columns, a crosstab would normally be used but you wouldn't have enough data with only 2 fields to do a crosstab.

If it is only to facilitate the display of the results and the number of Payment Forms is fixed then you could create a multi-column report.

Just need to put in the two fields one on top of the other in the detail and go into Page Set to change columns to 5 (or number of Payment Forms) and set the width to divide the available page space evenly between the columns. You will also need to ensure that the Column Layout is set to 'Across then Down'. The details will now appear in 5 columns, one for each record in the query.

The detail section of the report would be like this
Detail
Payment Form
Sub Payment

 

[Wren17]

Thanks.

I don't think I explained what I needed to well.

But I actually found a solution.

I just ran another query from my original make-table query.

and put an if statement in my Field Name.

COD Amount: Sum(IIf([payment_form] Like "D",[Sumofaih_sub_payment]))

This ultimately helped me accomplish my goal.

Matt