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

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/




"Jeff" <(E-Mail Removed)> wrote in message
news:A73665E5-5DF2-43E3-8C0B-(E-Mail Removed)...
> 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

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 wrote:
> 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.
>

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


"Ken Snell [MVP]" wrote:

> 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/
>
>
>
>
> "Jeff" <(E-Mail Removed)> wrote in message
> news:A73665E5-5DF2-43E3-8C0B-(E-Mail Removed)...
> > 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


"John Spencer" wrote:

> 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 wrote:
> > 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" <(E-Mail Removed)> wrote in message
news:A9D738B4-7E33-4F28-B941-(E-Mail Removed)...
> 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/

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 wrote:
> 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.
>

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


"John Spencer" wrote:

> 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 wrote:
> > 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.
> >
>

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


"John Spencer" wrote:

> 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 wrote:
> > 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.
> >
>

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


"John Spencer" wrote:

> 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 wrote:
> > 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.
> >
>