Group on data from two fields

[Catalina]

I need to group a report on a value that may exist in 2 different fields in the table.

There are two pay method fields (Paymethod1 and PayMethod2), and they contain values such as: Cash, Debit, Credit etc.
This as a result of clients paying a bill using 2 different methods.
Obviously, if they use only 1 method the Paymethod2 field will be empty.

Now I need to group on Cash, Debit, Credit etc. whether it exists in PayMethod1 or PayMethod2.

Do I need to make 2 separate queries for each field and then join them or is there another way?

All ideas will be greatly appreciated.

Catalina

 

[Catalina]

Or maybe I should get rid of both fields and use a separate table for payments.
Any thoughts on that?

Catalina

 

[Beetle]

Or maybe I should get rid of both fields and use a separate table for payments.

I agree. More normalized and provides the flexibility needed if anyone ever has more than two payment methods.

 

[Catalina]

Thanks Beetle,

Suppose I just want to keep it as it is, any idea how I would manage the grouping?

Catalina

 

[Beetle]

You could combine the two columns into one with a Union query. You can't do this with the query builder, you have to actually write the SQL yourself (open the query builder and switch to SQL view). The SQL would look something like;

SELECT Payment1 FROM YourTable UNION ALL SELECT Payment2 FROM YourTable;

You could then use this query in your Totals query and group by that combined column. Whether or not this will help you solve your problem, I don't know. It somewhat depends on what else you need returned by your Totals query and relationships involved.

 

[Catalina]

Thanks Beetle,

I'm experimenting with it and it seems it is going to work.

I am re-working an application and a new structure also requires
a different approach, need to take a fresh look at it.

Catalina