Keep Count of Query Output Records

[Guest]

Hello, I've got a query that is searching through the body of an email
message. I have it getting the target text by using the mid function. Is
there any way i can have it keep track of how many times a specific text is
retreived? i.e. the query outputs a username (john_doe) and it shows up
multiple times. Is there a way to keep count and display the number of times
it shows up?

 

[JohnFol]

You will have to do a loop using INSTR

 

[Marshall Barton]

If the question is how many times the name appears in each
target, it can be calculated without a code loop:

(Len(target) - Replace(target, thetext, "")) / Len(thetext)

However, I am not convinced that that's the question.

 

[Marshall Barton]

Hello, I've got a query that is searching through the body of an email
message. I have it getting the target text by using the mid function. Is
there any way i can have it keep track of how many times a specific text is
retreived? i.e. the query outputs a username (john_doe) and it shows up
multiple times. Is there a way to keep count and display the number of times
it shows up?

Are you asking how many emails contain the target text (as
opposed to how many times the target text appears in a
single email)?

If you want to count the number of records that contain the
target text, then use a group by query (Totals query).
Possible something like this:

SELECT TargetText, Count(*) As CountOfText
FROM thetable
WHERE email Like "*" & TargetText & "*"

 

[Guest]

Thanks for the reply.
Yes, i'll try to clarify further. I have a query that is calculating and
getting the text i am searching for by location. (i.e. i don't know exactly
what the text will be) I want to keep count of how many times a certain text
is found in the general query, not in each email message.

Here is the expression i am using in the query to get the text. (i found it
on this messageboard and modified it a little.)

LTrim(Mid(Left([Body],InStr([Body],"logged on to")-1),InStr([Body],"User
ENTERPRISE\")+16))

So basically i am trying to take what the code above outputs and keep count
of how many times a certain output is found for the whole query.

I tried using that in the example code you replied with, but it didn't work
out. i get an #error for the output of the query.

Any more help would be greatly appreciated.
Thanks!

 

[Marshall Barton]

Yes, i'll try to clarify further. I have a query that is calculating and
getting the text i am searching for by location. (i.e. i don't know exactly
what the text will be) I want to keep count of how many times a certain text
is found in the general query, not in each email message.

Here is the expression i am using in the query to get the text. (i found it
on this messageboard and modified it a little.)

LTrim(Mid(Left([Body],InStr([Body],"logged on to")-1),InStr([Body],"User
ENTERPRISE\")+16))

So basically i am trying to take what the code above outputs and keep count
of how many times a certain output is found for the whole query.

Still need to know how "certain text" comes into it. Is it
just a criteria?

I/m not clear about what "certain output" means either.

Maybe a few, simple sample records and an example search
item along with the desired result would help me get a grip
on what you want.

 

[Guest]

Sorry, i'm not explaining it well enough.

The email message contains a whole bunch of text, but all i am concerned
about it is a username. The query i wrote returns a username for each mail
message.
Ex. ABENT
ABWOE
ABENT

I want to be able to display each username and how many times it shows up.
so for the example above, i'd like to display :
ABENT 2
ABWOE 1

Hopefully that clears some of it up. Thanks for your patience.

Yes, i'll try to clarify further. I have a query that is calculating and
getting the text i am searching for by location. (i.e. i don't know exactly
what the text will be) I want to keep count of how many times a certain text
is found in the general query, not in each email message.

Here is the expression i am using in the query to get the text. (i found it
on this messageboard and modified it a little.)

LTrim(Mid(Left([Body],InStr([Body],"logged on to")-1),InStr([Body],"User
ENTERPRISE\")+16))

So basically i am trying to take what the code above outputs and keep count
of how many times a certain output is found for the whole query.

Still need to know how "certain text" comes into it. Is it
just a criteria?

I/m not clear about what "certain output" means either.

Maybe a few, simple sample records and an example search
item along with the desired result would help me get a grip
on what you want.

 

[Marshall Barton]

Ok, I think that help's a little. But, since queries
operate on tables and their fields, I need to know what
tables are involved and the pertinate fields in each table.

It looks like there may be two tables, one with a list of
users, and another with email messages.

Posting a Copy of your existing query's SQL view might also
help clarify what you're trying to do.
--
Marsh
MVP [MS Access]

The email message contains a whole bunch of text, but all i am concerned
about it is a username. The query i wrote returns a username for each mail
message.
Ex. ABENT
ABWOE
ABENT

I want to be able to display each username and how many times it shows up.
so for the example above, i'd like to display :
ABENT 2
ABWOE 1

Yes, i'll try to clarify further. I have a query that is calculating and
getting the text i am searching for by location. (i.e. i don't know exactly
what the text will be) I want to keep count of how many times a certain text
is found in the general query, not in each email message.

Here is the expression i am using in the query to get the text. (i found it
on this messageboard and modified it a little.)

LTrim(Mid(Left([Body],InStr([Body],"logged on to")-1),InStr([Body],"User
ENTERPRISE\")+16))

So basically i am trying to take what the code above outputs and keep count
of how many times a certain output is found for the whole query.

Still need to know how "certain text" comes into it. Is it
just a criteria?

I/m not clear about what "certain output" means either.

Maybe a few, simple sample records and an example search
item along with the desired result would help me get a grip
on what you want.

 

[Guest]

I have one table that was created by exporting mail messages from outlook
into an .mdb file. So it has the following field: (Subject, Body, FromName,
FromAddress, FromType, ToName, ToAddress, etc.)

Here is an example of one message.
-------------------------------------------
From: Service
Sent: Thursday, September 08, 2005 3:57:08 PM
To: Network
Subject: Security Issue Alert on PC

Severity: Security Issue
Status: New
Source: MS Mailbox Store
Name: Successful Mailbox logons
Description: Windows 2000 User ENTERPRISE\ABCDE logged on to
(e-mail address removed) mailbox, and is not the primary account on this mailbox.

From that table, i created this query.

SELECT [9-13].Body, LTrim(Mid(Left([Body],InStr([Body],"logged on
to")-2),InStr([Body],"User ENTERPRISE\")+16)) AS Expr1
FROM [9-13]
WHERE ((([9-13].Body) Like "*" & [Enter Name] & "*"))
ORDER BY LTrim(Mid(Left([Body],InStr([Body],"logged on
to")-2),InStr([Body],"User ENTERPRISE\")+16));

This query allows the user to enter a name to search for (John.Doe) and then
uses the function in the query to get the username ABCDE by searching for the
text in between "logged on to" and "ENTERPRISE\". So the usernames that i
get are the results of a query and are not stored in a field. Is there still
a way i can a keep count of those usernames?

Ok, I think that help's a little. But, since queries
operate on tables and their fields, I need to know what
tables are involved and the pertinate fields in each table.

It looks like there may be two tables, one with a list of
users, and another with email messages.

Posting a Copy of your existing query's SQL view might also
help clarify what you're trying to do.
--
Marsh
MVP [MS Access]

The email message contains a whole bunch of text, but all i am concerned
about it is a username. The query i wrote returns a username for each mail
message.
Ex. ABENT
ABWOE
ABENT

I want to be able to display each username and how many times it shows up.
so for the example above, i'd like to display :
ABENT 2
ABWOE 1

bgcpen wrote:
Yes, i'll try to clarify further. I have a query that is calculating and
getting the text i am searching for by location. (i.e. i don't know exactly
what the text will be) I want to keep count of how many times a certain text
is found in the general query, not in each email message.

Here is the expression i am using in the query to get the text. (i found it
on this messageboard and modified it a little.)

LTrim(Mid(Left([Body],InStr([Body],"logged on to")-1),InStr([Body],"User
ENTERPRISE\")+16))

So basically i am trying to take what the code above outputs and keep count
of how many times a certain output is found for the whole query.

Still need to know how "certain text" comes into it. Is it
just a criteria?

I/m not clear about what "certain output" means either.

Maybe a few, simple sample records and an example search
item along with the desired result would help me get a grip
on what you want.

 

[Marshall Barton]

That query can only find the one user name that was entered
into the prompt input box so that's not much of a problem.

If you only want the count, then I'll go back to my original
response:

SELECT [Enter Name] As User, Count(*) As CountOfUser
FROM [9-13]
WHERE [9-13].Body Like "*" & [Enter Name] & "*"

If you want to see the bodies of all the messages that match
then you'll need two queries, one to display the bodies and
another to count the total bodies that matched.

If you're displaying the bodies in a form or report, then
you can use your existing query and use a text box with the
expression =Count(*) in the form/report footer section.
--
Marsh
MVP [MS Access]

I have one table that was created by exporting mail messages from outlook
into an .mdb file. So it has the following field: (Subject, Body, FromName,
FromAddress, FromType, ToName, ToAddress, etc.)

Here is an example of one message.
-------------------------------------------
From: Service
Sent: Thursday, September 08, 2005 3:57:08 PM
To: Network
Subject: Security Issue Alert on PC

Severity: Security Issue
Status: New
Source: MS Mailbox Store
Name: Successful Mailbox logons
Description: Windows 2000 User ENTERPRISE\ABCDE logged on to
(e-mail address removed) mailbox, and is not the primary account on this mailbox.

From that table, i created this query.

SELECT [9-13].Body, LTrim(Mid(Left([Body],InStr([Body],"logged on
to")-2),InStr([Body],"User ENTERPRISE\")+16)) AS Expr1
FROM [9-13]
WHERE ((([9-13].Body) Like "*" & [Enter Name] & "*"))
ORDER BY LTrim(Mid(Left([Body],InStr([Body],"logged on
to")-2),InStr([Body],"User ENTERPRISE\")+16));

This query allows the user to enter a name to search for (John.Doe) and then
uses the function in the query to get the username ABCDE by searching for the
text in between "logged on to" and "ENTERPRISE\". So the usernames that i
get are the results of a query and are not stored in a field. Is there still
a way i can a keep count of those usernames?

Ok, I think that help's a little. But, since queries
operate on tables and their fields, I need to know what
tables are involved and the pertinate fields in each table.

It looks like there may be two tables, one with a list of
users, and another with email messages.

Posting a Copy of your existing query's SQL view might also
help clarify what you're trying to do.

The email message contains a whole bunch of text, but all i am concerned
about it is a username. The query i wrote returns a username for each mail
message.
Ex. ABENT
ABWOE
ABENT

I want to be able to display each username and how many times it shows up.
so for the example above, i'd like to display :
ABENT 2
ABWOE 1


bgcpen wrote:
Yes, i'll try to clarify further. I have a query that is calculating and
getting the text i am searching for by location. (i.e. i don't know exactly
what the text will be) I want to keep count of how many times a certain text
is found in the general query, not in each email message.

Here is the expression i am using in the query to get the text. (i found it
on this messageboard and modified it a little.)

LTrim(Mid(Left([Body],InStr([Body],"logged on to")-1),InStr([Body],"User
ENTERPRISE\")+16))

So basically i am trying to take what the code above outputs and keep count
of how many times a certain output is found for the whole query.


:
Still need to know how "certain text" comes into it. Is it
just a criteria?

I/m not clear about what "certain output" means either.

Maybe a few, simple sample records and an example search
item along with the desired result would help me get a grip
on what you want.

 

[Guest]

Marshall,

Thanks so much for your help and patience. I was able to use what i had done
before. So this functionality isn't needed any more. But thanks again!!!

That query can only find the one user name that was entered
into the prompt input box so that's not much of a problem.

If you only want the count, then I'll go back to my original
response:

SELECT [Enter Name] As User, Count(*) As CountOfUser
FROM [9-13]
WHERE [9-13].Body Like "*" & [Enter Name] & "*"

If you want to see the bodies of all the messages that match
then you'll need two queries, one to display the bodies and
another to count the total bodies that matched.

If you're displaying the bodies in a form or report, then
you can use your existing query and use a text box with the
expression =Count(*) in the form/report footer section.
--
Marsh
MVP [MS Access]

I have one table that was created by exporting mail messages from outlook
into an .mdb file. So it has the following field: (Subject, Body, FromName,
FromAddress, FromType, ToName, ToAddress, etc.)

Here is an example of one message.
-------------------------------------------
From: Service
Sent: Thursday, September 08, 2005 3:57:08 PM
To: Network
Subject: Security Issue Alert on PC

Severity: Security Issue
Status: New
Source: MS Mailbox Store
Name: Successful Mailbox logons
Description: Windows 2000 User ENTERPRISE\ABCDE logged on to
(e-mail address removed) mailbox, and is not the primary account on this mailbox.

From that table, i created this query.

SELECT [9-13].Body, LTrim(Mid(Left([Body],InStr([Body],"logged on
to")-2),InStr([Body],"User ENTERPRISE\")+16)) AS Expr1
FROM [9-13]
WHERE ((([9-13].Body) Like "*" & [Enter Name] & "*"))
ORDER BY LTrim(Mid(Left([Body],InStr([Body],"logged on
to")-2),InStr([Body],"User ENTERPRISE\")+16));

This query allows the user to enter a name to search for (John.Doe) and then
uses the function in the query to get the username ABCDE by searching for the
text in between "logged on to" and "ENTERPRISE\". So the usernames that i
get are the results of a query and are not stored in a field. Is there still
a way i can a keep count of those usernames?

Ok, I think that help's a little. But, since queries
operate on tables and their fields, I need to know what
tables are involved and the pertinate fields in each table.

It looks like there may be two tables, one with a list of
users, and another with email messages.

Posting a Copy of your existing query's SQL view might also
help clarify what you're trying to do.

bgcpen wrote:
The email message contains a whole bunch of text, but all i am concerned
about it is a username. The query i wrote returns a username for each mail
message.
Ex. ABENT
ABWOE
ABENT

I want to be able to display each username and how many times it shows up.
so for the example above, i'd like to display :
ABENT 2
ABWOE 1


bgcpen wrote:
Yes, i'll try to clarify further. I have a query that is calculating and
getting the text i am searching for by location. (i.e. i don't know exactly
what the text will be) I want to keep count of how many times a certain text
is found in the general query, not in each email message.

Here is the expression i am using in the query to get the text. (i found it
on this messageboard and modified it a little.)

LTrim(Mid(Left([Body],InStr([Body],"logged on to")-1),InStr([Body],"User
ENTERPRISE\")+16))

So basically i am trying to take what the code above outputs and keep count
of how many times a certain output is found for the whole query.


:
Still need to know how "certain text" comes into it. Is it
just a criteria?

I/m not clear about what "certain output" means either.

Maybe a few, simple sample records and an example search
item along with the desired result would help me get a grip
on what you want.