Count and display entries

  • Thread starter Thread starter Charles L. Phillips
  • Start date Start date
C

Charles L. Phillips

Hello,

I've created a data entry form with the following fields:

1. Address

2. City

3. State

4. Zip Code

5. Number of Entries

I want the form to count and display the number of entries of each individual entry, and display the number in the "Number of Entries" field.

Can/will ANYBODY point me to an example/sample of this nature???



Thank you,


--
Thank you,


Charles L. Phillips



DISCLAIMER: This e-mail and any attachments are intended only for the individual or company to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure or unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in error, please return the material received to the sender and delete all copies from your system. The information provided in this and ALL messages sent is provided as-is. No warranty is provided or implied. Use at your own risk. All content on these pages has been gathered and compiled by Charles L. Phillips.
 
Hello,

I've created a data entry form with the following fields:

1. Address

2. City

3. State

4. Zip Code

5. Number of Entries

I want the form to count and display the number of entries of each individual entry, and display the number in the "Number of Entries" field.

Can/will ANYBODY point me to an example/sample of this nature???



Thank you,
Click to expand...

First off... you're starting in the middle. Data is not stored in forms, it's
stored in Tables; a Form is *just a window*, a tool to let you edit data in
tables.

Secondly, you should absolutely NOT store "Number of Entries" anywhere. That
information will change every time you add or delete a record. It can be (and
will be) calculated and displayed automatically.

It's not clear what you mean by "number of entries of each individual entry" -
an entry is one thing; what's the number of entries of one entry???

You might want to see some of these sites for some introduction to how Access
can be most productively used:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
Hello,
"Thank You" for your timely response.
I know you do not store data in forms, and I know forms are used to display
data.
I know the data stored in tables can be edited or deleted, but what I'm
asking is what I want to see displayed in form view.
If I entered ABC-123, I want to see how many times I entered ABC-123, and
have the number of entries displayed on the form.
"Thank You" for the reference to the websites.
I want to know how my request can be processed...


Thank you,


Charles L. Phillips


DISCLAIMER: This e-mail and any attachments are intended only for the
individual or company to which it is addressed and may contain information
which is privileged, confidential and prohibited from disclosure or
unauthorized use under applicable law. If you are not the intended
recipient of this e-mail, you are hereby notified that any use,
dissemination, or copying of this e-mail or the information contained in
this e-mail is strictly prohibited by the sender. If you have received this
transmission in error, please return the material received to the sender and
delete all copies from your system. The information provided in this and ALL
messages sent is provided as-is. No warranty is provided or implied. Use at
your own risk. All content on these pages has been gathered and compiled by
Charles L. Phillips.
 
Hello,
"Thank You" for your timely response.
I know you do not store data in forms, and I know forms are used to display
data.
I know the data stored in tables can be edited or deleted, but what I'm
asking is what I want to see displayed in form view.
If I entered ABC-123, I want to see how many times I entered ABC-123, and
have the number of entries displayed on the form.
"Thank You" for the reference to the websites.
I want to know how my request can be processed...
Click to expand...

I'm still confused. ABC-123 does not appear to be an address, or a city, or a
state, or any of the other fields you depict.

Do you want five different counts, one for each field on the form?
Or is there one field on the form which you're particularly interested in
counting?

The solution will be to have a textbox with a Control Source resembling

=DCount("*", "[TableName]", "[FieldName] = '" & [ControlName] & "'")

YOu would probably need to requery this textbox in the afterupdate event of
whichever control contains the field you want counted.
 
Hello,
PLEASE, accept my apology for giving a BAD example.
If I entered the address:

218 N. Oak St.
Los Angeles, CA 90012

5 times in the database, I'd like to see how many times I've entered the
address in the database.
I'd like to see that information displayed in/on the form.
I know I can produce that information in a report, I'd like to see the
information displayed in a form view.




Thank you,


Charles L. Phillips


DISCLAIMER: This e-mail and any attachments are intended only for the
individual or company to which it is addressed and may contain information
which is privileged, confidential and prohibited from disclosure or
unauthorized use under applicable law. If you are not the intended
recipient of this e-mail, you are hereby notified that any use,
dissemination, or copying of this e-mail or the information contained in
this e-mail is strictly prohibited by the sender. If you have received this
transmission in error, please return the material received to the sender and
delete all copies from your system. The information provided in this and ALL
messages sent is provided as-is. No warranty is provided or implied. Use at
your own risk. All content on these pages has been gathered and compiled by
Charles L. Phillips.
John W. Vinson said:
Hello,
"Thank You" for your timely response.
I know you do not store data in forms, and I know forms are used to
display
data.
I know the data stored in tables can be edited or deleted, but what I'm
asking is what I want to see displayed in form view.
If I entered ABC-123, I want to see how many times I entered ABC-123, and
have the number of entries displayed on the form.
"Thank You" for the reference to the websites.
I want to know how my request can be processed...
Click to expand...

I'm still confused. ABC-123 does not appear to be an address, or a city,
or a
state, or any of the other fields you depict.

Do you want five different counts, one for each field on the form?
Or is there one field on the form which you're particularly interested in
counting?

The solution will be to have a textbox with a Control Source resembling

=DCount("*", "[TableName]", "[FieldName] = '" & [ControlName] & "'")

YOu would probably need to requery this textbox in the afterupdate event
of
whichever control contains the field you want counted.
Click to expand...
 
Hello,
PLEASE, accept my apology for giving a BAD example.
If I entered the address:

218 N. Oak St.
Los Angeles, CA 90012

5 times in the database, I'd like to see how many times I've entered the
address in the database.
I'd like to see that information displayed in/on the form.
I know I can produce that information in a report, I'd like to see the
information displayed in a form view.
Click to expand...

What if you have

218 North Oak St., Los Angeles, CA 90012
218 N. Oak Street, LosAngeles, CA 90012
218 North Oak Street, Los Angeles, Cal, 90012

Should that count as three of the same address? It won't. They'll only count
as matches if they are EXACT, character for character, blank for blank
matches. That said... you can amplify my example using a DCount() function:

=DCount("*", "[tablename]", "[Address] = """ & [Address] & """ AND [City] =
""" & [City] & """ AND State= '" & [State] & "' AND Zip = '" & [Zip] & "'")

Alternatively, especially for the Report, you can create a Totals Query;
include the table's Primary Key (if it doesn't have one, ADD ONE, it's
essential) and the four fields; click the Greek Sigma icon to get the Totals
row. Leave the default Group By on the four address fields and change it to
Count on the primary key field.
 
Hello,
I have tried to make each entry the same, so I would not have the issue you
just pointed out.
But, I am glad you pointed that out. Now the code you presented should be
able to handle this...



Thank you,


Charles L. Phillips


DISCLAIMER: This e-mail and any attachments are intended only for the
individual or company to which it is addressed and may contain information
which is privileged, confidential and prohibited from disclosure or
unauthorized use under applicable law. If you are not the intended
recipient of this e-mail, you are hereby notified that any use,
dissemination, or copying of this e-mail or the information contained in
this e-mail is strictly prohibited by the sender. If you have received this
transmission in error, please return the material received to the sender and
delete all copies from your system. The information provided in this and ALL
messages sent is provided as-is. No warranty is provided or implied. Use at
your own risk. All content on these pages has been gathered and compiled by
Charles L. Phillips.
John W. Vinson said:
Hello,
PLEASE, accept my apology for giving a BAD example.
If I entered the address:

218 N. Oak St.
Los Angeles, CA 90012

5 times in the database, I'd like to see how many times I've entered the
address in the database.
I'd like to see that information displayed in/on the form.
I know I can produce that information in a report, I'd like to see the
information displayed in a form view.
Click to expand...

What if you have

218 North Oak St., Los Angeles, CA 90012
218 N. Oak Street, LosAngeles, CA 90012
218 North Oak Street, Los Angeles, Cal, 90012

Should that count as three of the same address? It won't. They'll only
count
as matches if they are EXACT, character for character, blank for blank
matches. That said... you can amplify my example using a DCount()
function:

=DCount("*", "[tablename]", "[Address] = """ & [Address] & """ AND [City]
=
""" & [City] & """ AND State= '" & [State] & "' AND Zip = '" & [Zip] &
"'")

Alternatively, especially for the Report, you can create a Totals Query;
include the table's Primary Key (if it doesn't have one, ADD ONE, it's
essential) and the four fields; click the Greek Sigma icon to get the
Totals
row. Leave the default Group By on the four address fields and change it
to
Count on the primary key field.
Click to expand...
 
Hello,
Do you have ANY suggestions on ANY VBA books or training ?????



Thank you,


Charles L. Phillips



DISCLAIMER: This e-mail and any attachments are intended only for the
individual or company to which it is addressed and may contain information
which is privileged, confidential and prohibited from disclosure or
unauthorized use under applicable law. If you are not the intended
recipient of this e-mail, you are hereby notified that any use,
dissemination, or copying of this e-mail or the information contained in
this e-mail is strictly prohibited by the sender. If you have received this
transmission in error, please return the material received to the sender and
delete all copies from your system. The information provided in this and ALL
messages sent is provided as-is. No warranty is provided or implied. Use at
your own risk. All content on these pages has been gathered and compiled by
Charles L. Phillips.
John W. Vinson said:
Hello,
PLEASE, accept my apology for giving a BAD example.
If I entered the address:

218 N. Oak St.
Los Angeles, CA 90012

5 times in the database, I'd like to see how many times I've entered the
address in the database.
I'd like to see that information displayed in/on the form.
I know I can produce that information in a report, I'd like to see the
information displayed in a form view.
Click to expand...

What if you have

218 North Oak St., Los Angeles, CA 90012
218 N. Oak Street, LosAngeles, CA 90012
218 North Oak Street, Los Angeles, Cal, 90012

Should that count as three of the same address? It won't. They'll only
count
as matches if they are EXACT, character for character, blank for blank
matches. That said... you can amplify my example using a DCount()
function:

=DCount("*", "[tablename]", "[Address] = """ & [Address] & """ AND [City]
=
""" & [City] & """ AND State= '" & [State] & "' AND Zip = '" & [Zip] &
"'")

Alternatively, especially for the Report, you can create a Totals Query;
include the table's Primary Key (if it doesn't have one, ADD ONE, it's
essential) and the four fields; click the Greek Sigma icon to get the
Totals
row. Leave the default Group By on the four address fields and change it
to
Count on the primary key field.
Click to expand...
 
Hello,
Do you have ANY suggestions on ANY VBA books or training ?????
Click to expand...

Here's some resources and pointers to more:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


Jeff's site and the Access Web have "books" pages with tons of references. I'd
recommend anything by John Viescas, and you should try to get a copy of the
Access 2002 Developer's Handbook by Getz et al.; two big volumes, not cheap,
but worth its weight PLUS its cost many times over.
 
Back
Top