Total Record Count per Individual

So did it work for you?

I did not see you post an actual question or problem you had.

I will point out that rather then loop through all your records you can get a total immediately from just using an SQL query.

http://www.w3schools.com/sql/func_count_ast.asp

Hi Jeff,

I got the total records count for ALL individual show up correctly. What I want to do is to get a total count for EACH individual. Please see below and thanks for your help.

Current:
CroCrew
CroCrew
Jim
Jim

Total Records: 4

Want it to be:

CroCrew
....
....
Total = 2

Jim
....
....
Total = 2

Hi Hotflash,

Jeff is right about getting the sql query to do the work for you. In order to get a count of each individual you can use the following simple query:

SELECT Individual, COUNT(CompleteDate) AS RecordCount FROM MyTable Group By Individual

If you want to put a condition in here then you can do so like this:

SELECT Individual, COUNT(CompleteDate) AS RecordCount FROM MyTable WHERE CompleteDate IS NOT NULL Group By Individual

Use this in your sql string then cycle through each of the records as you would before:



Let us know how you get on,

Dr B

PS This sounds silly I know but you should keep all your html as lower case - although it won't affect you at the moment if you ever start using .NET you'll find your html won't validate because it's in uppercase. Just thought I'd let you know ;-)

Quote: Originally Posted by hotflash Hi Jeff, I got the total records count for ALL individual show up correctly. What I want to do is to get a total count for EACH individual. Please see below and thanks for your help. Current: CroCrew CroCrew Jim Jim Total Records: 4 Want it to be: CroCrew .... .... Total = 2 Jim .... .... Total = 2

Hi hotfalsh
Try this sql query in select statement


thanks

Hi idsanjeev and DrBunchman,

Your recommendations sound EXTREMELY GOOD however, I am having problem to try to integrate them into the actual code that I used because I have case statements, etc for different type of search as well.

Is there a way, I can send you the 2 files that I used (1 search option, 1 display search result) for your recommendations? Please advise or send me a PM message. Thanks once again for your outstanding support.

Quote: Originally Posted by hotflash Hi idsanjeev and DrBunchman, Your recommendations sound EXTREMELY GOOD however, I am having problem to try to integrate them into the actual code that I used because I have case statements, etc for different type of search as well. Is there a way, I can send you the 2 files that I used (1 search option, 1 display search result) for your recommendations? Please advise or send me a PM message. Thanks once again for your outstanding support.

Hi Hotflash, have you managed to fix this yet or has idsanjeev helped you out?

Hi Hotflash
you can simply attached your file in post or pm

Hi Sanjeev,

Below are the files that I used for this search and display reports accordingly.
The MS Access fields are defined as follows:

RecordID: AutoNumber and Primary Key
CompleteDate: Date/Time
ProjectType: Text
Network: Text
Individual: Text (First and Last Name)

Thanks once again for your outstanding support.

Hi hotflash
i think you have to change your code for count individually.
But Dr. B can help you out if is possible to count individually with your code.
i havn't any idea but i try to think if is possible.
regards
jha

Thanks for your outstanding support and let me know if you have any luck or not.

Meanwhile, Dr B and CroCrew, any comments? Thanks.

Hi Hotflash,

Sorry for the late reply - I've been on holiday - hopefully you've got it working by now but if not....

Do you mean you want to use a different column in your count depending on which search type the user selects?

This shouldn't cause you any problems once you get started. Just remember that any columns that you Select alongside your Count need to be included in your Group By clause. For example, in the statement below I've selected Col1 and Col2 so I've had to include them in my Group By:

SELECT Col1, Col2, COUNT(Col3) FROM Table1 WHERE Col1 = 'A' GROUP BY Col1, Col2

Keep it simple - build a complete sql statement for each value of sType in your case statement. You can start to make it more efficient once you've got it working.

Looking at your code I can't see you attempting to implement this anywhere - give it a try yourself then come back to us if you have any problems.

Let us know how you get on,

Dr B

Hi Dr B (The Expert),

Thanks for your replying and hope you had a good holiday. I have been extremely busy on my daily projects as well. This is something very new to me and just want to do it for experience. Hope you can help me know. I know you can because you are the EXPERT on this stuff. Just want to make sure we are on the same sheet of music here.

Let's say there are 3 people in the team: myself (hotflash), dr b and jha. Each of us here completed 2 projects such as "server install" (this is a type of project or Project Type as listed in the case statement). The current working code, if you select By Project Type, type in "Server Install" in the search box and put in the From and To Dates, it will display the following and it is sorted by Individual.

Individual Project Type
Hotflash Server Install
Hotflash Server Install
Dr B Server Install
Dr B Server Install
Jha Server Install
Jha Server Install

Total Records: 6

Again, the code that I provided you WORK as listed above!

What I want but not knowing what to do is:

Individual Project Type
Hotflash Server Install
Hotflash Server Install

Total Records: 2

Dr B Server Install
Dr B Server Install

Total Records: 2

Jha Server Install
Jha Server Install

Total Records: 2

Grand Total: 6

I have sent Jha the codes for him to look over as well. I think it will be easy for me to send you the codes so you can see the output. I can't seem to integrate what you provided that's why I replied to this forum again to make sure I am not confusing you. As always, you are the best of all best and thanks for your outstanding support.

Hi hotflash, have you got this sorted out yet? If not let me know and I'll take a look at it for you.

Dr B

Hi Dr. B,

Haven't tried again lately. Honestly, I am new to the ASP world and this portion is way over my head and would appreciate if you can take a look for me. Please let me know if you can help. Thanks once again for your outstanding support.