Another Grouping Question

[wizcow]

My query has 4 main fields.

1. Practitioner (sales person)
2. Therapist (sales person)
3. PaymentType (cash,debit,creditcard)
4. InvoiceAmount ($)

I want the report to sort by who made the sale. ( the Practitoner or the Therapist)

The report wizard lets me sort by either Practitioner or Therapist, not both.


Here is an example of what I would like to see;

Practioner1
Cash $10.00
Debit $ 8.00
CreditCard $20.00

Practioner2
Cash $ 5.00
Debit $ 3.00
CreditCard $50.00

Therapist1
Cash $ 4.00
Debit $ 6.00
CreditCard $12.00


I hope this makes a sense.
Tom

 

[neileg]

You need to get the Therapist and the Practitioner names into the same field.

There may be more elegant ways but this would do it. Create two queries:
Sales_person: Practitioner
PaymentType (cash,debit,creditcard)
InvoiceAmount ($)

Sales_person: Therapist
PaymentType (cash,debit,creditcard)
InvoiceAmount ($)

Then create a union query linking these two queries. Base your report on this.

 

[Rich]

You should only have one salesperson field in your table

 

[Fornatian]

Agree with Rich - I would use a lookup table (SalesPerson,Occupation) if you really need to distinguish between what occupation made the sale.

 

[wizcow]

Thanks for the reply.

I need both empoyees on the same invoice, as two poeple can work on the same patient.

I have an occupation field in the Employee table.

I have tried to incorperate this field into my query but it comes up blank.

?
Tom

 

[wizcow]

I discovered a way to accomplish my objective.

To quote Neileg, there are probably more eglegant methods, but this one works.

I put a sub report in the header, with the Therapist info and the rest of the report looks after the Practitioner info.

Thanks everyone

Tom