Attempting the Impossible? Totaling names.

[Remdul]

Is it possible to total how many times someone is mentioned in an access datasheet? For example, A datasheet containing the list of all the techs that have helped users, and specific users they have helped.

Joe helped Jane
Frank helped Billy
Joe helped Bob
Joe helped Craig
Frank helped Alex

Joe = 3
Frank = 2

I want to be able to total how many times one name is mentioned in a database worksheet. Is this possible in Microsoft Access 2002? Thank you,

Bryan Elliott,
Asst. Head of Technology.

 

[rjoubert]

SELECT Count(YourTable.YourField) AS CountOfData
FROM YourTable
WHERE (((YourTable.YourField) Like "*Joe*"));

 

[PHV]

You may try something like this (SQL code):
SELECT Left([yourField], InStr([yourField], ' ')-1) AS Tech, Count(*) As CountOfHelp
FROM yourTable
GROUP BY Left([yourField], InStr([yourField], ' ')-1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remdul]

Thank you for your quick replies! I will try these immediately. I appreciate it very much!

Bryan Elliott

 

[JoeAtWork]

If you are not too committed yet to the current structure of your table, I would suggest replacing your "comment" style field with something more precisely defined.

For example, a PersonHelped field and Helper field. Even better would be creating a separate Employees table with EmployeeID, FirstName, LastName fields, and in your original table create PersonHelpedID and HelperID fields that relate to the EmployeeID field.

If you don't do that, as soon as your company hires another "Joe" or "Frank" the above queries will become meaningless.

 

[MajP]

I Echo Joeatwork. Fix your table structure.

tblEmployees
employeeID
strFirstName
strLastName
other Fields

joinTblHelperHelped
helperEmployeeID
helpeeEmployeeID

Proper table design makes life easy. Now summing up who helped who is simple. You can sort and group to make reports.

 

[Remdul]

Actually, that was merely an example. What I am currently doing is building a Database for the Animal Control Division of my local Police Department. I have an extensive plethra of fields, including name, address, ID Tag, Rabies Tag, Type, Breed, Description, Citations, etc... It's a fairly complex database.

I have ran into another problem though, one that has me slightly confused. This is the first time my IT Department has really used Access to build a database, so there's a substantial bit of learning involved. What I ended up doing is running the SQL that rjoubert and PHV generously provided. Unfortunately I cannot combine fields I am attempting to search under one query, due to the fact that AND & OR seperating the search fields causes the field to search for both of the names, not just one.

I have it set up now to where I have 5 queries with the same code, slightly different. One field queries "Cat", another "Dog". "Kit" "Pup" and "Other". Now, I get it to run to show me that I have 42 dogs in my database, which is the exact information I wanted. The only problem is now I want it in a report.

"You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and form a query based onm that table. If so, try choosing fields from one the table or only the query." I receive this error when attempting to put them together under one report. Can I not combine multiple queries uner one report to run all the information together? Thank you,

Bryan Elliott

 

[MajP]

If you need to show information from 2 seperate recordsets look at using a subreport. If you are looking at return a specific value from multiple tables or queries you can use a Dlookup or other D function in an unbound control.

 

[Remdul]

So on the report, I would have it Dlookup the query? I'll try that. Not entirely sure what you mean by using the subreports to present the data though. I am attempting to bring 5 queries into one report, and it's presenting me with an error saying that it cannot connect. Thank you,

Bryan Elliott

 

[finsalgal]

Hi Can you tell me how to calculate between two dates so it does not take into account weekends. Ie I manufacture belts and I am trying to establish how many working days in took at the minute I put in the start date and end date and it will say 5 days but it only took 3 days because it calculated the weekend.

Many thanks

 

[SkipVought]

finsalgal,

Please start a new thread with your question.

Skip,
[sub]
[red][/red]
[/sub]