DCount problem

[Jeff]

Hi,
I have a database (table) that stores the record of vaccine given to
patient. Some vaccine may be given once, some twice, some 3 times, and some 4
times. I use DCount to count the vaccines given at a specific period of time.
I have 65,000 records in my table and have 100 lines as follow:

lngVaccineA1 = DCount("[TradeNm]", "tbVaccine", "[TradeNm] = 'VaccineA' And
[VaccNth] = '1' And [InjectDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] & "'")

It works very slowly. Is there any other way to make it work faster? Thank
you.

 

[Ken Snell [MVP]]

I don't know if this will be faster, but here's an alternative way to do it:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Count([TradeNm]) " & _
"As TheCount FROM tbVaccine WHERE [TradeNm] = " & _
"'VaccineA' And [VaccNth] = '1' And [InjectDate] " & _
"Between '" & Me![StartDate] & "' And '" & Me![EndDate] & _
"'", dbOpenForwardOnly, dbReadOnly)
lngVaccineA1 = Nz(rst.Fields(0).Value, 0)
rst.Close
' repeat "Set rst", "lngVaccineA1", and "rst.Close" steps for
' each vaccine --
' then use these code lines
Set rst =Nothing
dbs.Close
Set dbs = Nothing


By the way, why are you using text strings for the InjectDate's parameters
in the WHERE clause? Isn't InjectDate a date datatype field?

 

[John Spencer]

Perhaps you just want an aggregate query.

SELECT TradeNm
, VaccNth
, Count(TradeNm) as Count
FROM tblVaccine
WHERE InjectDate Between [StartDate] and [EndDate]
GROUP BY TradeNm, VaccNth

Since you gave us no idea HOW you are using the information or WHERE you
are using the information, that is my best guess on what you might need
to solve your problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jeff]

Hi Ken,

Thank you for your quick response, I test your code for a single vaccine and
it works. I wonder if I should narrow the range of my tbVaccine first before
counting. That is, I should retrieve my tbVaccine with [InjectDate] Between
Me![StartDate] And Me![EndDate] first, then count the vaccine with this lot
of records. So Access doesn't need to go through tbVaccine for each Vaccine
and dose number. I don't know how to put this in code, any help will be
appreciated.

Yes, [Injectdate] should be a date datatype field. I used String datatype
when I wrote the code. I'll fix it, Thank you.

--
Jeff

I don't know if this will be faster, but here's an alternative way to do it:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Count([TradeNm]) " & _
"As TheCount FROM tbVaccine WHERE [TradeNm] = " & _
"'VaccineA' And [VaccNth] = '1' And [InjectDate] " & _
"Between '" & Me![StartDate] & "' And '" & Me![EndDate] & _
"'", dbOpenForwardOnly, dbReadOnly)
lngVaccineA1 = Nz(rst.Fields(0).Value, 0)
rst.Close
' repeat "Set rst", "lngVaccineA1", and "rst.Close" steps for
' each vaccine --
' then use these code lines
Set rst =Nothing
dbs.Close
Set dbs = Nothing


By the way, why are you using text strings for the InjectDate's parameters
in the WHERE clause? Isn't InjectDate a date datatype field?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi,
I have a database (table) that stores the record of vaccine given to
patient. Some vaccine may be given once, some twice, some 3 times, and
some 4
times. I use DCount to count the vaccines given at a specific period of
time.
I have 65,000 records in my table and have 100 lines as follow:

lngVaccineA1 = DCount("[TradeNm]", "tbVaccine", "[TradeNm] = 'VaccineA'
And
[VaccNth] = '1' And [InjectDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] & "'")

It works very slowly. Is there any other way to make it work faster? Thank
you.

 

[Jeff]

Hi John,
This is a part of my Vaccine Manager program. At the end of the day, week,
or month, we need to calculate to see how many doses of each vaccine was
given in dose 1, dose 2, dose 3, or dose 4. We just key in StartDate, EndDate
and click a button, a statistical table then appears with all the firgures on
it. I have built this page using DCount, and I am now seeking another way to
make it work faster.

--
Jeff

Perhaps you just want an aggregate query.

SELECT TradeNm
, VaccNth
, Count(TradeNm) as Count
FROM tblVaccine
WHERE InjectDate Between [StartDate] and [EndDate]
GROUP BY TradeNm, VaccNth

Since you gave us no idea HOW you are using the information or WHERE you
are using the information, that is my best guess on what you might need
to solve your problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi,
I have a database (table) that stores the record of vaccine given to
patient. Some vaccine may be given once, some twice, some 3 times, and some 4
times. I use DCount to count the vaccines given at a specific period of time.
I have 65,000 records in my table and have 100 lines as follow:

lngVaccineA1 = DCount("[TradeNm]", "tbVaccine", "[TradeNm] = 'VaccineA' And
[VaccNth] = '1' And [InjectDate] Between '" & Me![StartDate] & "' And '" &
Me![EndDate] & "'")

It works very slowly. Is there any other way to make it work faster? Thank
you.

 

[Ken Snell [MVP]]

Hi Ken,

Thank you for your quick response, I test your code for a single vaccine
and
it works. I wonder if I should narrow the range of my tbVaccine first
before
counting. That is, I should retrieve my tbVaccine with [InjectDate]
Between
Me![StartDate] And Me![EndDate] first, then count the vaccine with this
lot
of records.

This is what the query is doing now. In a totals query, the WHERE clause is
applied first in order to filter the dataset, then ACCESS does the counting.

So Access doesn't need to go through tbVaccine for each Vaccine
and dose number. I don't know how to put this in code, any help will be
appreciated.

Yes, [Injectdate] should be a date datatype field. I used String datatype
when I wrote the code. I'll fix it, Thank you.

Be sure that you have the StartDate and EndDate controls on the form
formatted as "short date" or some other date format; this will let ACCESS
know that you are providing a date value in the WHERE clause. Else, do this:

Set rst = dbs.OpenRecordset("SELECT Count([TradeNm]) " & _
"As TheCount FROM tbVaccine WHERE [TradeNm] = " & _
"'VaccineA' And [VaccNth] = '1' And [InjectDate] " & _
"Between " & Format(Me![StartDate], "\#mm\/dd\/yyyy\#") & _
" And '" & Format(Me![EndDate], "\#mm\/dd\/yyyy\#"), _
dbOpenForwardOnly, dbReadOnly)

 

[John Spencer]

Whoops, I forgot to mention that you could use a crosstab query to get a
solution that might appeal to you. The Crosstab that I have posted
below will return records like:

TradeNm V1 V2 V3 V4 V5 TotalCount
Tetanus 100 99 2 1 0 202
xxxxx 99 98 0 0 0 197


PARAMETERS [StartDate] as DateTime, [EndDate] as DateTime;
TRANSFORM CLng(Nz(Count(TradeNm),0)) as TheCount
SELECT TradeNm
, Count(TradeNm) as TotalCount
FROM tblVaccine
WHERE InjectDate Between [StartDate] and [EndDate]
GROUP BY TradeNm
PIVOT "V" & VaccNth in ("V1","V2","V3","V4","V5")


You can change the pivot clause to include as many different VaccNth
values as you wish. Just for safety's sake I add V5, but you can drop
that if you wish.

If you need instructions on how to build the crosstab query using the
design view, post back and I will try to give you step by step instructions.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jeff]

Hi John,

I need your help to build the crosstab query either using SQL view or design
view. I tried to build it myself by typing your codes with SQL view, but can
not save it due to some errors in it. I have corrected the first line as
PARAMETERS [StarDate] DateTime, [EndDate] DateTime; but still can not save
it. Thank you.

--
Jeff

Whoops, I forgot to mention that you could use a crosstab query to get a
solution that might appeal to you. The Crosstab that I have posted
below will return records like:

TradeNm V1 V2 V3 V4 V5 TotalCount
Tetanus 100 99 2 1 0 202
xxxxx 99 98 0 0 0 197


PARAMETERS [StartDate] as DateTime, [EndDate] as DateTime;
TRANSFORM CLng(Nz(Count(TradeNm),0)) as TheCount
SELECT TradeNm
, Count(TradeNm) as TotalCount
FROM tblVaccine
WHERE InjectDate Between [StartDate] and [EndDate]
GROUP BY TradeNm
PIVOT "V" & VaccNth in ("V1","V2","V3","V4","V5")


You can change the pivot clause to include as many different VaccNth
values as you wish. Just for safety's sake I add V5, but you can drop
that if you wish.

If you need instructions on how to build the crosstab query using the
design view, post back and I will try to give you step by step instructions.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John,
This is a part of my Vaccine Manager program. At the end of the day, week,
or month, we need to calculate to see how many doses of each vaccine was
given in dose 1, dose 2, dose 3, or dose 4. We just key in StartDate, EndDate
and click a button, a statistical table then appears with all the firgures on
it. I have built this page using DCount, and I am now seeking another way to
make it work faster.

 

[Jeff]

Hi John,

I need your help to build the crosstab query either using SQL view or design
view. I tried to build it myself by typing your codes with SQL view, but can
not save it due to some errors in it. I have corrected the first line as
PARAMETERS [StarDate] DateTime, [EndDate] DateTime; but still can not save
it. Thank you.

--
Jeff

Whoops, I forgot to mention that you could use a crosstab query to get a
solution that might appeal to you. The Crosstab that I have posted
below will return records like:

TradeNm V1 V2 V3 V4 V5 TotalCount
Tetanus 100 99 2 1 0 202
xxxxx 99 98 0 0 0 197


PARAMETERS [StartDate] as DateTime, [EndDate] as DateTime;
TRANSFORM CLng(Nz(Count(TradeNm),0)) as TheCount
SELECT TradeNm
, Count(TradeNm) as TotalCount
FROM tblVaccine
WHERE InjectDate Between [StartDate] and [EndDate]
GROUP BY TradeNm
PIVOT "V" & VaccNth in ("V1","V2","V3","V4","V5")


You can change the pivot clause to include as many different VaccNth
values as you wish. Just for safety's sake I add V5, but you can drop
that if you wish.

If you need instructions on how to build the crosstab query using the
design view, post back and I will try to give you step by step instructions.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John,
This is a part of my Vaccine Manager program. At the end of the day, week,
or month, we need to calculate to see how many doses of each vaccine was
given in dose 1, dose 2, dose 3, or dose 4. We just key in StartDate, EndDate
and click a button, a statistical table then appears with all the firgures on
it. I have built this page using DCount, and I am now seeking another way to
make it work faster.

 

[Jeff]

Hi John,

I finally get the crosstab query work. The speed seems much better. How can
I let the result display in the TextBox of the Form? Say, if I want to
display VaccineA V1 in Me!bxVaccAV1, how can I do it? I also need to make a
grand total. Thank you.

--
Jeff

Whoops, I forgot to mention that you could use a crosstab query to get a
solution that might appeal to you. The Crosstab that I have posted
below will return records like:

TradeNm V1 V2 V3 V4 V5 TotalCount
Tetanus 100 99 2 1 0 202
xxxxx 99 98 0 0 0 197


PARAMETERS [StartDate] as DateTime, [EndDate] as DateTime;
TRANSFORM CLng(Nz(Count(TradeNm),0)) as TheCount
SELECT TradeNm
, Count(TradeNm) as TotalCount
FROM tblVaccine
WHERE InjectDate Between [StartDate] and [EndDate]
GROUP BY TradeNm
PIVOT "V" & VaccNth in ("V1","V2","V3","V4","V5")


You can change the pivot clause to include as many different VaccNth
values as you wish. Just for safety's sake I add V5, but you can drop
that if you wish.

If you need instructions on how to build the crosstab query using the
design view, post back and I will try to give you step by step instructions.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John,
This is a part of my Vaccine Manager program. At the end of the day, week,
or month, we need to calculate to see how many doses of each vaccine was
given in dose 1, dose 2, dose 3, or dose 4. We just key in StartDate, EndDate
and click a button, a statistical table then appears with all the firgures on
it. I have built this page using DCount, and I am now seeking another way to
make it work faster.

 

[John Spencer MVP]

You will need to create a new form and use a continuous form.

Basically your form would have seven controls in the detail section and the
controls would be bound to the fields returned by the Crosstab query -
TradeNm, V1, V2, V3, V4, V5, TotalCount

As far as the Grand Total goes, you can either use a sub-form in the footer of
the form bound to a variation of the crosstab query. Or you can try binding
additional controls in the footer to expressions like:
=Sum(V1)

Roughly the crosstab for grand totals would look like this (if you decide to
use a sub-form)
PARAMETERS [StartDate] as DateTime, [EndDate] as DateTime;
TRANSFORM CLng(Nz(Count(TradeNm),0)) as TheCount
SELECT "GrandTotal" as LineName
, Count(TradeNm) as TotalCount
FROM tblVaccine
WHERE InjectDate Between [StartDate] and [EndDate]
GROUP BY "GrandTotal"
PIVOT "V" & VaccNth in ("V1","V2","V3","V4","V5")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff]

Hi Ken,

I put all the codes in and feel that the speed is not much different as
using DCount. Thank you very much for your help.

--
Jeff


"Ken Snell [MVP]" 來函：

Hi Ken,

Thank you for your quick response, I test your code for a single vaccine
and
it works. I wonder if I should narrow the range of my tbVaccine first
before
counting. That is, I should retrieve my tbVaccine with [InjectDate]
Between
Me![StartDate] And Me![EndDate] first, then count the vaccine with this
lot
of records.

This is what the query is doing now. In a totals query, the WHERE clause is
applied first in order to filter the dataset, then ACCESS does the counting.

So Access doesn't need to go through tbVaccine for each Vaccine
and dose number. I don't know how to put this in code, any help will be
appreciated.

Yes, [Injectdate] should be a date datatype field. I used String datatype
when I wrote the code. I'll fix it, Thank you.

Be sure that you have the StartDate and EndDate controls on the form
formatted as "short date" or some other date format; this will let ACCESS
know that you are providing a date value in the WHERE clause. Else, do this:

Set rst = dbs.OpenRecordset("SELECT Count([TradeNm]) " & _
"As TheCount FROM tbVaccine WHERE [TradeNm] = " & _
"'VaccineA' And [VaccNth] = '1' And [InjectDate] " & _
"Between " & Format(Me![StartDate], "\#mm\/dd\/yyyy\#") & _
" And '" & Format(Me![EndDate], "\#mm\/dd\/yyyy\#"), _
dbOpenForwardOnly, dbReadOnly)


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jeff]

Hi John,

Thank you very much for your help.

--
Jeff


"John Spencer MVP" 來函：

You will need to create a new form and use a continuous form.

Basically your form would have seven controls in the detail section and the
controls would be bound to the fields returned by the Crosstab query -
TradeNm, V1, V2, V3, V4, V5, TotalCount

As far as the Grand Total goes, you can either use a sub-form in the footer of
the form bound to a variation of the crosstab query. Or you can try binding
additional controls in the footer to expressions like:
=Sum(V1)

Roughly the crosstab for grand totals would look like this (if you decide to
use a sub-form)
PARAMETERS [StartDate] as DateTime, [EndDate] as DateTime;
TRANSFORM CLng(Nz(Count(TradeNm),0)) as TheCount
SELECT "GrandTotal" as LineName
, Count(TradeNm) as TotalCount
FROM tblVaccine
WHERE InjectDate Between [StartDate] and [EndDate]
GROUP BY "GrandTotal"
PIVOT "V" & VaccNth in ("V1","V2","V3","V4","V5")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

I finally get the crosstab query work. The speed seems much better. How can
I let the result display in the TextBox of the Form? Say, if I want to
display VaccineA V1 in Me!bxVaccAV1, how can I do it? I also need to make a
grand total. Thank you.