Does anyone want to tackel this one with me???

[WB786]

Here is what my problem: A report needs to be designed so that it prints out the page with Counselor name at the top and then has certain fields of data for client and at the bottom or at the end of report also prints out any refunds/chargebacks for this counselor and then totals the amount the client(s) paid and if any refunds then subtract that one from the subtotal. The math part I am good with. It is other part. How can I build a query that will know how to print only for that particular counselor with and witout refunds. Here is what the rpt will look like:


Counselor
Date Client Amount Comments



At the bottom of page:

Refunds/Chargebacks
Date of refund Client Name Refund Amount RefundType

Total Counselor Bonus: $

So the query should only print one counselor at a time and print them all though with both info. I have setup two queries with the rpt showing a subreport for the refunds section. But can't them to work togather.

Thanks in advance.

 

[MichaelRed]

use a subreport for the "footer" link the master/child fields on the "Counselor". Of course, for this to work you need to assure the "counselor"s are all identified uniquely AND all of the entries are "spelled" the same.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WB786]

Actually the counselor field is just a stored value from a counselor combo box. Is there no way to make this rpt work then? The counselor are not unique, just the ClientID is. Thanks.

 

[MichaelRed]

Well, you CAN make it work. It would, however, not be pratical to provide any advice w/o knowing a bit more. At a minimum, you need SOME method to relate the counselor to the clientID.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WB786]

I have two tables: ClientInfo and PayInfo. These two tables are linked. The counselor values are stored in the ClientInfo table. The form is built on ClientInfo and PayInfo tables. Both of the main tables have autonumbers to make them unique. Hope this helps. THanks.

 

[MichaelRed]

then create the query to include both tables with the counselor field. Just revert to the first suggestion.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WB786]

Hey Michael I tried couple of things and still can not get the rpt to work out. Do I need to create 2 queries or just one. The montly bonus are based on a date range and the refund can be for a client that paid couple of months ago but the money is getting subtracted from this month. Can we say commission sales here. In your first post you said : "use a subreport for the "footer" and link the master/child fields." I guess this is the part I am not to savy on. Can you detail it for me if it is not too much of a bother. Thanks again.

 

[MichaelRed]

Ms. A includes examples of subreports and master-child linkages in hte help files. I think you should be able to get 99% of this problem from there, and 1% from ye olde 'sweat equity'. At least take a look at the help topics and examples in these areas. If -after that- there are spome specific questions, respond w/ them.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over