I have a report that looks like thi 1

[allong]

I have a report that looks like this:

Date Client Name Rates Hours

Mon, May 26 John Doe 19 5
Tue, May 27 John Doe 17 10
Wed, May 28 John Doe 19 5
Thr, May 29 John Doe 17 10
Fri, May 30 John Doe 19 5
Mon, May 26 John Doe 18.50 5

I would like for my report to look like this:

John Doe 19 15
17 20
18.50 5

I would like to combine the Rates and the hours if they are the same. Printing only one line per rate. I would also like to print the name only once. How can I accomplish this task?

 

[scriverb]

Try this:
1. You can use the Group By Aggregate function in your query supplying records for the report. By Grouping by all four fields you will get one row for each of the unique combinations. Use this SQL for your query afte you change the table name.
Select A.*
FROM tblYourTableName as A
GROUP BY A.Date, A.[Client Name], A.Rates, A.Hours
ORDER BY A.[Client Name];

2. In the report you can set the control property Hide Duplicates to Yes for the Date and Name controls. this will keep them from displaying after the first time.

Let me know if I can provide more assistance in this matter.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[allong]

Scriver, I tried your suggestion. The only thing is it does not total each rate. It gives a list that looks like this:
John Doe 19 8
19 8
19 8
20 8
I would like for it to look like this:
John Doe 19 24
20 8
Is this possible?

Thanks for your help so far.

 

[mpastore]

It's probably more like:

Select A.[Client Name], A.Rates, SUM(A.Hours) as TOT_hours
FROM tblYourTableName as A
GROUP BY A.[Client Name], A.Rates
ORDER BY A.[Client Name];


Mike Pastore

Hats off to (Roy) Harper

 

[scriverb]

Select A.[Client Name], A.[Rates], Sum(A.Hours)
FROM tblYourTableName as A
GROUP BY A.[Client Name], A.[Rates]
ORDER BY A.[Client Name], A.[Rates];

This should do it.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[CosmoKramer]

Try this:

SELECT TableName.[ClientName], TableName.[Rate], Sum(TableName.Hours) AS SumOfHours
FROM TableName
GROUP BY TableName.ClientName, TableName.Rate;

 

[allong]

CosmoKramer, everthing is coming together. There is one problem. If the rate have different hours, they do not add together. They only add the hours if the hours are the same. This is an example of what input data:

Rate Hours
John Doe 19 5
19 10
19 5

I get this:

John Doe 19 10
19 10

I want to see this:

John Doe 19 20

Thanks again.

 

[CosmoKramer]

I just tested "our" version again, this time with the exact 3 records you specified and I got the desired results....

Check the grouping again, make sure you are only grouping by ClientName and Rate......

 

[allong]

CosmoKramer, you were right on. My problem is that I need to connect the Client with the Employee. When I try to do that the totals change. Is there a way to connect them without changing my totals? Something to look like this.

Jane Doe #21111 (employee)

John Doe 19 40

 

[CosmoKramer]

What do you mean "connect the Client with the Employee?

Where is Employee coming from, and what does the query look like now??

 

[allong]

CosmoKramer you are a God send. I went back and looked at the SQL. typed something in wrong. Your code works perfectly. Thank you very much and everyone else for their help. You get a star for this one CosmoKramer. I was pulling my hair out. Thanks again. Take care and have a wonderful day.