Displaying Total Results

[Chip via AccessMonster.com]

Hi,

I need to find an expression that will display the total times a line of text
appears in a field.
I would like to display the results in a form via a textbox.

For example, the table is called “tblCustomerInfo†and the field is called
“Cityâ€. I might want to display very time “California†appears in the “Cityâ€
column and display this info in a textbox.

Any help is appreciated.

Thanks

 

['69 Camaro]

Hi, Chip.

I need to find an expression that will display the total times a line of
text
appears in a field.
I would like to display the results in a form via a textbox.

Set the text box's Control Source Property to use the DCount() method. For
example, if "California" is one word among one or more words in the "City"
column, try:

=DCount("*","tblCustomerInfo","City LIKE '*California*'")

However if "California" is the only word that may or may not display in the
"City" column, try:

=DCount("*","tblCustomerInfo","City = 'California'")

This counts how many rows contain "California" within the "City" column, not
the total number of times "California" appears in that column.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

If you are putting the State name into a City field to distinguish between
cities of the same name in different states then that is not a good design.
In a relational database each city should be represented by a row in a Cities
table with a unique primary key column such as CityID, e.g. an autonumber,
and a text City column. The Cities table should have a State column as a
foreign key referencing the primary key of a States table. As State names
are unique you don't need to use a 'surrogate' numeric StateID primary key
here, though you can if you wish. Two cities of the same name in different
states would be represented by two rows in the Cities table with different
values in the State column. By using normalized tables like this redundancy
is eliminated and the risk of update anomalies arising from the use of
non-normalized tables reduced.

The tblCustomerInfo table would have a numeric CityID column which
references the primary key of Cities. You do not need to store the State in
a separate column in tblCustomerInfo, and moreover should not do so as it
introduces redundancy, as the State is known from the value of CityID. To
count the occurrences of customers in California, you could use an expression
which combined the DCount and DLookup functions, but a better approach would
be to join tblCustomerInfo to Cities in a query then use an expression like
the following:

=DCount("*","YourQuery","City = ""California""")

This assumes you've used State as a 'natural' key rather than a numeric
StateID as a 'surrogate' key. If you use the latter the query would need to
join tblCustomerInfo , Cities and States so that you can refer to the state
by name in the DCount function call as above.

You'll find a demo of various ways of handling this type of geographical
data by using correlated combo boxes at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


It uses the local administrative areas of Civil Parish, District and County
in my neck of the woods, but the principle is the same.

Ken Sheridan
Stafford, England

 

[Chip via AccessMonster.com]

Everything works great. Big thanks to the both of you.

One more thing is there an expression that can be added to the =DCount
function that will only show the results between two dates? I already have a
field that records the Date.

If you are putting the State name into a City field to distinguish between
cities of the same name in different states then that is not a good design.
In a relational database each city should be represented by a row in a Cities
table with a unique primary key column such as CityID, e.g. an autonumber,
and a text City column. The Cities table should have a State column as a
foreign key referencing the primary key of a States table. As State names
are unique you don't need to use a 'surrogate' numeric StateID primary key
here, though you can if you wish. Two cities of the same name in different
states would be represented by two rows in the Cities table with different
values in the State column. By using normalized tables like this redundancy
is eliminated and the risk of update anomalies arising from the use of
non-normalized tables reduced.

The tblCustomerInfo table would have a numeric CityID column which
references the primary key of Cities. You do not need to store the State in
a separate column in tblCustomerInfo, and moreover should not do so as it
introduces redundancy, as the State is known from the value of CityID. To
count the occurrences of customers in California, you could use an expression
which combined the DCount and DLookup functions, but a better approach would
be to join tblCustomerInfo to Cities in a query then use an expression like
the following:

=DCount("*","YourQuery","City = ""California""")

This assumes you've used State as a 'natural' key rather than a numeric
StateID as a 'surrogate' key. If you use the latter the query would need to
join tblCustomerInfo , Cities and States so that you can refer to the state
by name in the DCount function call as above.

You'll find a demo of various ways of handling this type of geographical
data by using correlated combo boxes at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps

It uses the local administrative areas of Civil Parish, District and County
in my neck of the woods, but the principle is the same.

Ken Sheridan
Stafford, England

Hi,

[quoted text clipped - 9 lines]

Thanks

 

['69 Camaro]

Hi, Chip.

One more thing is there an expression that can be added to the =DCount
function that will only show the results between two dates?

Yes. Add the following syntax for the date criteria where OrderDate is the
name of the column containing the date (watch out for word wrap, as this is
all one line):

=DCount("*","tblCustomerInfo","City = 'California' AND (OrderDate BETWEEN
#1/1/2005# AND #1/1/2007#)")

Do not use the reserved word Date as the name of your column, or you'll be
sorry when VBA code or an expression suddenly saves today's date in the
column, overwriting the original date.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

You need to be a little careful defining a date range as in Access there is
no such thing as a date value per se, only a date/time value. When you enter
a date the value is really midnight at the start of the day, not the day as a
whole. Sometimes a non-zero time of day can creep into a date without you
realizing; a common culprit is the use of the Now() function as a default
value where the Date() function should be used. A BETWEEN….AND operation
will miss any records on the last day of the range if a value on that day has
a non-zero time of day as it will return rows on or after any time on the
first day, but only where the date/time value is on or before midnight at the
start of the final day. You can cater for this possibility, however, by
defining the range slightly differently:

"[OrderDate] >= #08/01/2007# And [OrderDate] < #08/31/2007# + 1"

Usually you would not use literal dates but variables, to which a value had
been assigned by the user or in code. A thing to remember here is that
literal dates need to be in US format or an otherwise internationally
unambiguous format, so to internationalize an application you need to format
the values entered as the parameters for the date range. Say the dates were
entered by the user in text boxes on a form:

"[OrderDate] >= #" & Format([txtStartDate],"mm/dd/yyyy") & "# And
[OrderDate] < #" & Format([txtEndDate],"mm/dd/yyyy") & "# + 1"

Of course if you've taken steps at the table definition level to disallow
any values with a non-zero time of day in the OrderDate column then there's
no reason why a BETWEEN…AND operation should not be used.

Ken Sheridan
Stafford, England

Everything works great. Big thanks to the both of you.

One more thing is there an expression that can be added to the =DCount
function that will only show the results between two dates? I already have a
field that records the Date.

If you are putting the State name into a City field to distinguish between
cities of the same name in different states then that is not a good design.
In a relational database each city should be represented by a row in a Cities
table with a unique primary key column such as CityID, e.g. an autonumber,
and a text City column. The Cities table should have a State column as a
foreign key referencing the primary key of a States table. As State names
are unique you don't need to use a 'surrogate' numeric StateID primary key
here, though you can if you wish. Two cities of the same name in different
states would be represented by two rows in the Cities table with different
values in the State column. By using normalized tables like this redundancy
is eliminated and the risk of update anomalies arising from the use of
non-normalized tables reduced.

The tblCustomerInfo table would have a numeric CityID column which
references the primary key of Cities. You do not need to store the State in
a separate column in tblCustomerInfo, and moreover should not do so as it
introduces redundancy, as the State is known from the value of CityID. To
count the occurrences of customers in California, you could use an expression
which combined the DCount and DLookup functions, but a better approach would
be to join tblCustomerInfo to Cities in a query then use an expression like
the following:

=DCount("*","YourQuery","City = ""California""")

This assumes you've used State as a 'natural' key rather than a numeric
StateID as a 'surrogate' key. If you use the latter the query would need to
join tblCustomerInfo , Cities and States so that you can refer to the state
by name in the DCount function call as above.

You'll find a demo of various ways of handling this type of geographical
data by using correlated combo boxes at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps

It uses the local administrative areas of Civil Parish, District and County
in my neck of the woods, but the principle is the same.

Ken Sheridan
Stafford, England

Hi,

[quoted text clipped - 9 lines]

Thanks