Query/Date Question

[vladi16]

I posted this once but for some reason can not open it... trying agin:

I have a table, tbltests that has the following fields: testnumber and
rulename. It is a storage type table where I list all of the tests we
perform on our employees.
I have a form, fmTOs, that we use to input all the tests we perform on our
employees it includes the date and time the test was performed, the test
number and rule name that was performed, the employee etc. All of this input
goes to a table, tbltretests.

I have created a report, rptTestCount, from a right join query, qryTestCount
with the following fields:

testnumber.tbltest
rulename.tbltest
testnumber.tbltretests (Count)

The query & report come out as intended… I see all of the tests numbers and
their matching rule name… and I get the count of how many of each was
performed. What I can’t seem to figure out is how to add the testdate field
from tbletretests into the query to allow myself to be able to filter records
between start and end dates.

Ultimately, once I get the query situated, I want to open my report,
rptTestCount, from a form where I enter two dates and presto… the report
appears. I now know how to do the form; I just need help with the date issue.

Thanks, sorry to be so wordy with my description.

 

[Rob Parker]

You haven't posted the SQL for your query, but I suspect that it's a totals
query, since you've got a Count in your output.

You need to add the TestDate field (from tbltretests) into your query. By
default, it will have a Group By entry in the Total field, and that will
give you multiple rows (one for each date) in your output. Change the entry
in the Total field to Where (the Show box will become unchecked, preventing
the field appearing in the output), and in the criteria field put the
criteria you want - from your related previous post, I guess it will be
something like Between [Forms]![frmEmployeeFilter].[txtStartDate] And
[Forms]![frmEmployeeFilter].[txtEndDate]

HTH,

Rob

 

[Ken Sheridan]

One thing you should also do when using date/time parameters is
declare them as such. Otherwise a date entered as a parameter in
short date format might be interpreted as an arithmetical expression
rather than a date and give the wrong results. Also it takes account
of regional date format differences. The parameters can be declared
in design view (Query | Parameters on the menu bar) or in SQL view.
The resulting SQL would look something like this:

PARAMETERS
[Forms]![frmEmployeeFilter].[txtStartDate] DATETIME,
[Forms]![frmEmployeeFilter].[txtEndDate] DATETIME;
SELECT tbltest.testnumber, tbltest.rulename,
COUNT(*)
FROM tbltest INNER JOIN tbltretests
ON tbltest.testnumber = tbltretests.testnumber
WHERE testdate BETWEEN [Forms]![frmEmployeeFilter].[txtStartDate]
And [Forms]![frmEmployeeFilter].[txtEndDate]
GROUP BY tbltest.testnumber, tbltest.rulename;

On the form just include a command button which opens the report, or
perhaps two buttons, one to open it in print preview, the other to
print it.

Ken Sheridan
Stafford, England

 

[vladi16]

Rob,

Thanks, I made th changes you suggested, but now when I run the report the
tbltests.testnumber field only has test numbers if they are not null. In
other words, if I did not perform tests #s 106, 202, and/or 313 between the
two dates I chose then those particular test #s don't show up on the report.
My goal is to have them all show up with the total count of the ones I did
and zeros next to the ones that were not performed.

Below is the SQL I currently have which includes your suggestion:

SELECT tbltest.testnumber, tbltest.rulename, Count(tbltretests.testnumber)
AS CountOftestnumber
FROM tbltest LEFT JOIN tbltretests ON tbltest.testnumber =
tbltretests.testnumber
WHERE (((tbltretests.testdate) Between
[Forms]![fmFilterTestCount]![txtStartDate] And
[Forms]![fmFilterTestCount]![txtEndDate]))
GROUP BY tbltest.testnumber, tbltest.rulename
HAVING (((tbltest.testnumber) Not Like "6*"))
ORDER BY tbltest.testnumber;

Any thoughts? Thanks.

You haven't posted the SQL for your query, but I suspect that it's a totals
query, since you've got a Count in your output.

You need to add the TestDate field (from tbltretests) into your query. By
default, it will have a Group By entry in the Total field, and that will
give you multiple rows (one for each date) in your output. Change the entry
in the Total field to Where (the Show box will become unchecked, preventing
the field appearing in the output), and in the criteria field put the
criteria you want - from your related previous post, I guess it will be
something like Between [Forms]![frmEmployeeFilter].[txtStartDate] And
[Forms]![frmEmployeeFilter].[txtEndDate]

HTH,

Rob

I posted this once but for some reason can not open it... trying agin:

I have a table, tbltests that has the following fields: testnumber and
rulename. It is a storage type table where I list all of the tests
we perform on our employees.
I have a form, fmTOs, that we use to input all the tests we perform
on our employees it includes the date and time the test was
performed, the test number and rule name that was performed, the
employee etc. All of this input goes to a table, tbltretests.

I have created a report, rptTestCount, from a right join query,
qryTestCount with the following fields:

testnumber.tbltest
rulename.tbltest
testnumber.tbltretests (Count)

The query & report come out as intended. I see all of the tests
numbers and their matching rule name. and I get the count of how many
of each was performed. What I can't seem to figure out is how to add
the testdate field from tbletretests into the query to allow myself
to be able to filter records between start and end dates.

Ultimately, once I get the query situated, I want to open my report,
rptTestCount, from a form where I enter two dates and presto. the
report appears. I now know how to do the form; I just need help with
the date issue.

Thanks, sorry to be so wordy with my description.

 

[Rob Parker]

Include a criterion of Is Null for the testdate field. In the SQL view, it
will become:

SELECT tbltest.testnumber, tbltest.rulename,
Count(tbltretests.testnumber) AS CountOftestnumber
FROM tbltest LEFT JOIN tbltretests ON tbltest.testnumber =
tbltretests.testnumber
WHERE (((tbltretests.testdate) Between
[Forms]![fmFilterTestCount]![txtStartDate] And
[Forms]![fmFilterTestCount]![txtEndDate]))
OR tblTretests.testdate Is Null
GROUP BY tbltest.testnumber, tbltest.rulename
HAVING (((tbltest.testnumber) Not Like "6*"))
ORDER BY tbltest.testnumber;

If you set this up by adding the new criterion on the next criteria line in
the query design grid, it will have more bracketing around it ;-)

HTH,

Rob

Rob,

Thanks, I made th changes you suggested, but now when I run the
report the tbltests.testnumber field only has test numbers if they
are not null. In other words, if I did not perform tests #s 106,
202, and/or 313 between the two dates I chose then those particular
test #s don't show up on the report. My goal is to have them all show
up with the total count of the ones I did and zeros next to the ones
that were not performed.

Below is the SQL I currently have which includes your suggestion:

SELECT tbltest.testnumber, tbltest.rulename,
Count(tbltretests.testnumber) AS CountOftestnumber
FROM tbltest LEFT JOIN tbltretests ON tbltest.testnumber =
tbltretests.testnumber
WHERE (((tbltretests.testdate) Between
[Forms]![fmFilterTestCount]![txtStartDate] And
[Forms]![fmFilterTestCount]![txtEndDate]))
GROUP BY tbltest.testnumber, tbltest.rulename
HAVING (((tbltest.testnumber) Not Like "6*"))
ORDER BY tbltest.testnumber;

Any thoughts? Thanks.

You haven't posted the SQL for your query, but I suspect that it's a
totals query, since you've got a Count in your output.

You need to add the TestDate field (from tbltretests) into your
query. By default, it will have a Group By entry in the Total
field, and that will give you multiple rows (one for each date) in
your output. Change the entry in the Total field to Where (the Show
box will become unchecked, preventing the field appearing in the
output), and in the criteria field put the criteria you want - from
your related previous post, I guess it will be something like
Between [Forms]![frmEmployeeFilter].[txtStartDate] And
[Forms]![frmEmployeeFilter].[txtEndDate]

HTH,

Rob

I posted this once but for some reason can not open it... trying
agin:

I have a table, tbltests that has the following fields: testnumber
and rulename. It is a storage type table where I list all of the
tests we perform on our employees.
I have a form, fmTOs, that we use to input all the tests we perform
on our employees it includes the date and time the test was
performed, the test number and rule name that was performed, the
employee etc. All of this input goes to a table, tbltretests.

I have created a report, rptTestCount, from a right join query,
qryTestCount with the following fields:

testnumber.tbltest
rulename.tbltest
testnumber.tbltretests (Count)

The query & report come out as intended. I see all of the tests
numbers and their matching rule name. and I get the count of how
many of each was performed. What I can't seem to figure out is how
to add the testdate field from tbletretests into the query to allow
myself to be able to filter records between start and end dates.

Ultimately, once I get the query situated, I want to open my report,
rptTestCount, from a form where I enter two dates and presto. the
report appears. I now know how to do the form; I just need help
with the date issue.

Thanks, sorry to be so wordy with my description.

 

[vladi16]

Great!! Worked perfectly. Thanks Rob. I continue to learn something new
everytime I post. Have a safe New Year!

Include a criterion of Is Null for the testdate field. In the SQL view, it
will become:

SELECT tbltest.testnumber, tbltest.rulename,
Count(tbltretests.testnumber) AS CountOftestnumber
FROM tbltest LEFT JOIN tbltretests ON tbltest.testnumber =
tbltretests.testnumber
WHERE (((tbltretests.testdate) Between
[Forms]![fmFilterTestCount]![txtStartDate] And
[Forms]![fmFilterTestCount]![txtEndDate]))
OR tblTretests.testdate Is Null
GROUP BY tbltest.testnumber, tbltest.rulename
HAVING (((tbltest.testnumber) Not Like "6*"))
ORDER BY tbltest.testnumber;

If you set this up by adding the new criterion on the next criteria line in
the query design grid, it will have more bracketing around it ;-)

HTH,

Rob

Rob,

Thanks, I made th changes you suggested, but now when I run the
report the tbltests.testnumber field only has test numbers if they
are not null. In other words, if I did not perform tests #s 106,
202, and/or 313 between the two dates I chose then those particular
test #s don't show up on the report. My goal is to have them all show
up with the total count of the ones I did and zeros next to the ones
that were not performed.

Below is the SQL I currently have which includes your suggestion:

SELECT tbltest.testnumber, tbltest.rulename,
Count(tbltretests.testnumber) AS CountOftestnumber
FROM tbltest LEFT JOIN tbltretests ON tbltest.testnumber =
tbltretests.testnumber
WHERE (((tbltretests.testdate) Between
[Forms]![fmFilterTestCount]![txtStartDate] And
[Forms]![fmFilterTestCount]![txtEndDate]))
GROUP BY tbltest.testnumber, tbltest.rulename
HAVING (((tbltest.testnumber) Not Like "6*"))
ORDER BY tbltest.testnumber;

Any thoughts? Thanks.

You haven't posted the SQL for your query, but I suspect that it's a
totals query, since you've got a Count in your output.

You need to add the TestDate field (from tbltretests) into your
query. By default, it will have a Group By entry in the Total
field, and that will give you multiple rows (one for each date) in
your output. Change the entry in the Total field to Where (the Show
box will become unchecked, preventing the field appearing in the
output), and in the criteria field put the criteria you want - from
your related previous post, I guess it will be something like
Between [Forms]![frmEmployeeFilter].[txtStartDate] And
[Forms]![frmEmployeeFilter].[txtEndDate]

HTH,

Rob

vladi16 wrote:
I posted this once but for some reason can not open it... trying
agin:

I have a table, tbltests that has the following fields: testnumber
and rulename. It is a storage type table where I list all of the
tests we perform on our employees.
I have a form, fmTOs, that we use to input all the tests we perform
on our employees it includes the date and time the test was
performed, the test number and rule name that was performed, the
employee etc. All of this input goes to a table, tbltretests.

I have created a report, rptTestCount, from a right join query,
qryTestCount with the following fields:

testnumber.tbltest
rulename.tbltest
testnumber.tbltretests (Count)

The query & report come out as intended. I see all of the tests
numbers and their matching rule name. and I get the count of how
many of each was performed. What I can't seem to figure out is how
to add the testdate field from tbletretests into the query to allow
myself to be able to filter records between start and end dates.

Ultimately, once I get the query situated, I want to open my report,
rptTestCount, from a form where I enter two dates and presto. the
report appears. I now know how to do the form; I just need help
with the date issue.

Thanks, sorry to be so wordy with my description.