Query Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to design a query to tell me what reports I have not received for
the week.

I have a table that holds all the reports that are received in the
department. I then have a table that people input the information in when
the report is actually received. I need to be able to pull a date range to
show what reports I have not received for the week. I tried the mismatch
query and I'm not getting any information. Here is what I have so far:

SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN tblIDFraudWorkLogged ON
tblManualWrkListIDFraud.ID = tblIDFraudWorkLogged.ID
WHERE (((tblIDFraudWorkLogged.ID) Is Null));


I also have a date query so that I can pull the date range but am unsure how
to join in into the query above. The date query I have is below:

SELECT tblIDFraudWorkLogged.[REPORT NAME], tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date: mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));


Well hopefully I didn't confuse anyone too much.
 
You need to use tblIDFraudWorkLogged in an unmatched query with your date
range query. You do need to add the ID field to the query so you can use it
in a join in the second report.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date: mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));

Query Two: Identify those reports that have not been received
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null));
 
Is there a way to set up a query that says if it is a daily report then we
should receive it 5 times that week and if it is a report that is to be
received 3 times a week. I think the way that it is set up now if I put in a
date range and the report was received at least once then it won't show as
not received even though we should have received it everyday.

John Spencer said:
You need to use tblIDFraudWorkLogged in an unmatched query with your date
range query. You do need to add the ID field to the query so you can use it
in a join in the second report.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date: mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));

Query Two: Identify those reports that have not been received
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null));


WMorsberger said:
I am trying to design a query to tell me what reports I have not received
for
the week.

I have a table that holds all the reports that are received in the
department. I then have a table that people input the information in when
the report is actually received. I need to be able to pull a date range
to
show what reports I have not received for the week. I tried the mismatch
query and I'm not getting any information. Here is what I have so far:

SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN tblIDFraudWorkLogged ON
tblManualWrkListIDFraud.ID = tblIDFraudWorkLogged.ID
WHERE (((tblIDFraudWorkLogged.ID) Is Null));


I also have a date query so that I can pull the date range but am unsure
how
to join in into the query above. The date query I have is below:

SELECT tblIDFraudWorkLogged.[REPORT NAME], tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date: mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));


Well hopefully I didn't confuse anyone too much.
Click to expand...
Click to expand...
 
There should be a way, but it will depend on your data structure.

Do you have Number field in your table tblManualWrkListFraud that tells you
how often a report is due each week?

Or do you have a ReportsDue table that has a record for each day of the week
that a report is due.
ReportID WeekdayDue
122 2
122 4
122 6
133 6

So ReportId 122 is due on Mon, Wed, and Fri and report 133 is due only on
Friday.

Solutions can be worked out for all of these, but they are not simple to
implement. The simplest might be to change your current query one to get a
count of the reports returned.

Assumption:
tblManualWrkListIDFraud.FREQUENCY is a number field that tells you the
number of reports to be expected in any one week. Of course, if your count
is for a week and you put in a date range for a month, then this obviously
doesn't work.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date: mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID

QueryTwo:
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null)) OR qReportsReceived.ReportCount
<tblManualWrkListIDFraud.FREQUENCY


WMorsberger said:
Is there a way to set up a query that says if it is a daily report then we
should receive it 5 times that week and if it is a report that is to be
received 3 times a week. I think the way that it is set up now if I put
in a
date range and the report was received at least once then it won't show as
not received even though we should have received it everyday.

John Spencer said:
You need to use tblIDFraudWorkLogged in an unmatched query with your date
range query. You do need to add the ID field to the query so you can use
it
in a join in the second report.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));

Query Two: Identify those reports that have not been received
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null));


WMorsberger said:
I am trying to design a query to tell me what reports I have not
received
for
the week.

I have a table that holds all the reports that are received in the
department. I then have a table that people input the information in
when
the report is actually received. I need to be able to pull a date
range
to
show what reports I have not received for the week. I tried the
mismatch
query and I'm not getting any information. Here is what I have so far:

SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN tblIDFraudWorkLogged ON
tblManualWrkListIDFraud.ID = tblIDFraudWorkLogged.ID
WHERE (((tblIDFraudWorkLogged.ID) Is Null));


I also have a date query so that I can pull the date range but am
unsure
how
to join in into the query above. The date query I have is below:

SELECT tblIDFraudWorkLogged.[REPORT NAME], tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));


Well hopefully I didn't confuse anyone too much.
Click to expand...
Click to expand...
Click to expand...
 
My Frequency is set up just to say Mon-Fri, Daily, irregular and so on.
Since I'm pretty new at this database stuff I set up different tables by
department with each report in the table with the frequency that it should be
received. So I'm not sure how to approach it.


John Spencer said:
There should be a way, but it will depend on your data structure.

Do you have Number field in your table tblManualWrkListFraud that tells you
how often a report is due each week?

Or do you have a ReportsDue table that has a record for each day of the week
that a report is due.
ReportID WeekdayDue
122 2
122 4
122 6
133 6

So ReportId 122 is due on Mon, Wed, and Fri and report 133 is due only on
Friday.

Solutions can be worked out for all of these, but they are not simple to
implement. The simplest might be to change your current query one to get a
count of the reports returned.

Assumption:
tblManualWrkListIDFraud.FREQUENCY is a number field that tells you the
number of reports to be expected in any one week. Of course, if your count
is for a week and you put in a date range for a month, then this obviously
doesn't work.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date: mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID

QueryTwo:
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null)) OR qReportsReceived.ReportCount
<tblManualWrkListIDFraud.FREQUENCY


WMorsberger said:
Is there a way to set up a query that says if it is a daily report then we
should receive it 5 times that week and if it is a report that is to be
received 3 times a week. I think the way that it is set up now if I put
in a
date range and the report was received at least once then it won't show as
not received even though we should have received it everyday.

John Spencer said:
You need to use tblIDFraudWorkLogged in an unmatched query with your date
range query. You do need to add the ID field to the query so you can use
it
in a join in the second report.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));

Query Two: Identify those reports that have not been received
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null));


I am trying to design a query to tell me what reports I have not
received
for
the week.

I have a table that holds all the reports that are received in the
department. I then have a table that people input the information in
when
the report is actually received. I need to be able to pull a date
range
to
show what reports I have not received for the week. I tried the
mismatch
query and I'm not getting any information. Here is what I have so far:

SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN tblIDFraudWorkLogged ON
tblManualWrkListIDFraud.ID = tblIDFraudWorkLogged.ID
WHERE (((tblIDFraudWorkLogged.ID) Is Null));


I also have a date query so that I can pull the date range but am
unsure
how
to join in into the query above. The date query I have is below:

SELECT tblIDFraudWorkLogged.[REPORT NAME], tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));


Well hopefully I didn't confuse anyone too much.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, at this point I don't see how I can help you further.

WMorsberger said:
My Frequency is set up just to say Mon-Fri, Daily, irregular and so on.
Since I'm pretty new at this database stuff I set up different tables by
department with each report in the table with the frequency that it should
be
received. So I'm not sure how to approach it.


John Spencer said:
There should be a way, but it will depend on your data structure.

Do you have Number field in your table tblManualWrkListFraud that tells
you
how often a report is due each week?

Or do you have a ReportsDue table that has a record for each day of the
week
that a report is due.
ReportID WeekdayDue
122 2
122 4
122 6
133 6

So ReportId 122 is due on Mon, Wed, and Fri and report 133 is due only on
Friday.

Solutions can be worked out for all of these, but they are not simple to
implement. The simplest might be to change your current query one to get
a
count of the reports returned.

Assumption:
tblManualWrkListIDFraud.FREQUENCY is a number field that tells you the
number of reports to be expected in any one week. Of course, if your
count
is for a week and you put in a date range for a month, then this
obviously
doesn't work.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID

QueryTwo:
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null)) OR qReportsReceived.ReportCount
<tblManualWrkListIDFraud.FREQUENCY


WMorsberger said:
Is there a way to set up a query that says if it is a daily report then
we
should receive it 5 times that week and if it is a report that is to be
received 3 times a week. I think the way that it is set up now if I
put
in a
date range and the report was received at least once then it won't show
as
not received even though we should have received it everyday.

:

You need to use tblIDFraudWorkLogged in an unmatched query with your
date
range query. You do need to add the ID field to the query so you can
use
it
in a join in the second report.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));

Query Two: Identify those reports that have not been received
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null));


I am trying to design a query to tell me what reports I have not
received
for
the week.

I have a table that holds all the reports that are received in the
department. I then have a table that people input the information
in
when
the report is actually received. I need to be able to pull a date
range
to
show what reports I have not received for the week. I tried the
mismatch
query and I'm not getting any information. Here is what I have so
far:

SELECT tblManualWrkListIDFraud.ID,
tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN tblIDFraudWorkLogged ON
tblManualWrkListIDFraud.ID = tblIDFraudWorkLogged.ID
WHERE (((tblIDFraudWorkLogged.ID) Is Null));


I also have a date query so that I can pull the date range but am
unsure
how
to join in into the query above. The date query I have is below:

SELECT tblIDFraudWorkLogged.[REPORT NAME], tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));


Well hopefully I didn't confuse anyone too much.
Click to expand...
Click to expand...
Click to expand...
 
Are you able to help me with what you already gave me. I have adjusted my
tables and when I apply the first query that you have I get the following
error message:

You tried to execute a query that does not include the specified expression
'Report Name' as part of an aggregate function.

Here is what I have:

SELECT tblFraudWorkLogged.ID , tblIDFraudWorkLogged.[REPORT NAME] ,
Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date: mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID


John Spencer said:
Sorry, at this point I don't see how I can help you further.

WMorsberger said:
My Frequency is set up just to say Mon-Fri, Daily, irregular and so on.
Since I'm pretty new at this database stuff I set up different tables by
department with each report in the table with the frequency that it should
be
received. So I'm not sure how to approach it.


John Spencer said:
There should be a way, but it will depend on your data structure.

Do you have Number field in your table tblManualWrkListFraud that tells
you
how often a report is due each week?

Or do you have a ReportsDue table that has a record for each day of the
week
that a report is due.
ReportID WeekdayDue
122 2
122 4
122 6
133 6

So ReportId 122 is due on Mon, Wed, and Fri and report 133 is due only on
Friday.

Solutions can be worked out for all of these, but they are not simple to
implement. The simplest might be to change your current query one to get
a
count of the reports returned.

Assumption:
tblManualWrkListIDFraud.FREQUENCY is a number field that tells you the
number of reports to be expected in any one week. Of course, if your
count
is for a week and you put in a date range for a month, then this
obviously
doesn't work.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID

QueryTwo:
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null)) OR qReportsReceived.ReportCount
<tblManualWrkListIDFraud.FREQUENCY


Is there a way to set up a query that says if it is a daily report then
we
should receive it 5 times that week and if it is a report that is to be
received 3 times a week. I think the way that it is set up now if I
put
in a
date range and the report was received at least once then it won't show
as
not received even though we should have received it everyday.

:

You need to use tblIDFraudWorkLogged in an unmatched query with your
date
range query. You do need to add the ID field to the query so you can
use
it
in a join in the second report.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));

Query Two: Identify those reports that have not been received
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null));


I am trying to design a query to tell me what reports I have not
received
for
the week.

I have a table that holds all the reports that are received in the
department. I then have a table that people input the information
in
when
the report is actually received. I need to be able to pull a date
range
to
show what reports I have not received for the week. I tried the
mismatch
query and I'm not getting any information. Here is what I have so
far:

SELECT tblManualWrkListIDFraud.ID,
tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN tblIDFraudWorkLogged ON
tblManualWrkListIDFraud.ID = tblIDFraudWorkLogged.ID
WHERE (((tblIDFraudWorkLogged.ID) Is Null));


I also have a date query so that I can pull the date range but am
unsure
how
to join in into the query above. The date query I have is below:

SELECT tblIDFraudWorkLogged.[REPORT NAME], tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));


Well hopefully I didn't confuse anyone too much.
Click to expand...
Click to expand...
Click to expand...
 
When you do an aggregate query, you need to Group By any fields in the
SELECT clause that you haven't applied an aggregate function to (Avg, Count,
Sum, etc). So, to make the query work, add tblIDFraudWorkLogged.[REPORT
NAME] to the Group By clause.

SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date: mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID, tblIDFraudWorkLogged.[REPORT NAME]

WMorsberger said:
Are you able to help me with what you already gave me. I have adjusted my
tables and when I apply the first query that you have I get the following
error message:

You tried to execute a query that does not include the specified
expression
'Report Name' as part of an aggregate function.

Here is what I have:

SELECT tblFraudWorkLogged.ID , tblIDFraudWorkLogged.[REPORT NAME] ,
Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID


John Spencer said:
Sorry, at this point I don't see how I can help you further.

WMorsberger said:
My Frequency is set up just to say Mon-Fri, Daily, irregular and so on.
Since I'm pretty new at this database stuff I set up different tables
by
department with each report in the table with the frequency that it
should
be
received. So I'm not sure how to approach it.


:

There should be a way, but it will depend on your data structure.

Do you have Number field in your table tblManualWrkListFraud that
tells
you
how often a report is due each week?

Or do you have a ReportsDue table that has a record for each day of
the
week
that a report is due.
ReportID WeekdayDue
122 2
122 4
122 6
133 6

So ReportId 122 is due on Mon, Wed, and Fri and report 133 is due only
on
Friday.

Solutions can be worked out for all of these, but they are not simple
to
implement. The simplest might be to change your current query one to
get
a
count of the reports returned.

Assumption:
tblManualWrkListIDFraud.FREQUENCY is a number field that tells you the
number of reports to be expected in any one week. Of course, if your
count
is for a week and you put in a date range for a month, then this
obviously
doesn't work.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID

QueryTwo:
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null)) OR
qReportsReceived.ReportCount
<tblManualWrkListIDFraud.FREQUENCY


Is there a way to set up a query that says if it is a daily report
then
we
should receive it 5 times that week and if it is a report that is to
be
received 3 times a week. I think the way that it is set up now if I
put
in a
date range and the report was received at least once then it won't
show
as
not received even though we should have received it everyday.

:

You need to use tblIDFraudWorkLogged in an unmatched query with
your
date
range query. You do need to add the ID field to the query so you
can
use
it
in a join in the second report.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));

Query Two: Identify those reports that have not been received
SELECT tblManualWrkListIDFraud.ID,
tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null));


message
I am trying to design a query to tell me what reports I have not
received
for
the week.

I have a table that holds all the reports that are received in
the
department. I then have a table that people input the
information
in
when
the report is actually received. I need to be able to pull a
date
range
to
show what reports I have not received for the week. I tried the
mismatch
query and I'm not getting any information. Here is what I have
so
far:

SELECT tblManualWrkListIDFraud.ID,
tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN tblIDFraudWorkLogged ON
tblManualWrkListIDFraud.ID = tblIDFraudWorkLogged.ID
WHERE (((tblIDFraudWorkLogged.ID) Is Null));


I also have a date query so that I can pull the date range but am
unsure
how
to join in into the query above. The date query I have is below:

SELECT tblIDFraudWorkLogged.[REPORT NAME],
tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));


Well hopefully I didn't confuse anyone too much.
Click to expand...
Click to expand...
Click to expand...
 
I got the first query to work fine. Now when I do the second query the only
thing it seems to show me is the the worklist table. It doesn't give me
anyother information. Here is what i have for the 2nd query.

SELECT tblManualWrkListBusCards.ID, tblManualWrkListBusCards.REPORTNAME,
tblManualWrkListBusCards.[RISK LEVEL], tblManualWrkListBusCards.DAYSRECD
FROM tblManualWrkListBusCards LEFT JOIN BusCardsTestQuery
ON tblManualWrkListBusCards.ID = BusCardsTestQuery.ID
WHERE (((BusCardsTestQuery.ID) Is Null)) OR BusCardsTestQuery.ReportCount
<tblManualWrkListBusCards.DAYSRECD


John Spencer said:
Sorry, at this point I don't see how I can help you further.

WMorsberger said:
My Frequency is set up just to say Mon-Fri, Daily, irregular and so on.
Since I'm pretty new at this database stuff I set up different tables by
department with each report in the table with the frequency that it should
be
received. So I'm not sure how to approach it.


John Spencer said:
There should be a way, but it will depend on your data structure.

Do you have Number field in your table tblManualWrkListFraud that tells
you
how often a report is due each week?

Or do you have a ReportsDue table that has a record for each day of the
week
that a report is due.
ReportID WeekdayDue
122 2
122 4
122 6
133 6

So ReportId 122 is due on Mon, Wed, and Fri and report 133 is due only on
Friday.

Solutions can be worked out for all of these, but they are not simple to
implement. The simplest might be to change your current query one to get
a
count of the reports returned.

Assumption:
tblManualWrkListIDFraud.FREQUENCY is a number field that tells you the
number of reports to be expected in any one week. Of course, if your
count
is for a week and you put in a date range for a month, then this
obviously
doesn't work.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID

QueryTwo:
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL], tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null)) OR qReportsReceived.ReportCount
<tblManualWrkListIDFraud.FREQUENCY


Is there a way to set up a query that says if it is a daily report then
we
should receive it 5 times that week and if it is a report that is to be
received 3 times a week. I think the way that it is set up now if I
put
in a
date range and the report was received at least once then it won't show
as
not received even though we should have received it everyday.

:

You need to use tblIDFraudWorkLogged in an unmatched query with your
date
range query. You do need to add the ID field to the query so you can
use
it
in a join in the second report.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));

Query Two: Identify those reports that have not been received
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null));


I am trying to design a query to tell me what reports I have not
received
for
the week.

I have a table that holds all the reports that are received in the
department. I then have a table that people input the information
in
when
the report is actually received. I need to be able to pull a date
range
to
show what reports I have not received for the week. I tried the
mismatch
query and I'm not getting any information. Here is what I have so
far:

SELECT tblManualWrkListIDFraud.ID,
tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN tblIDFraudWorkLogged ON
tblManualWrkListIDFraud.ID = tblIDFraudWorkLogged.ID
WHERE (((tblIDFraudWorkLogged.ID) Is Null));


I also have a date query so that I can pull the date range but am
unsure
how
to join in into the query above. The date query I have is below:

SELECT tblIDFraudWorkLogged.[REPORT NAME], tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));


Well hopefully I didn't confuse anyone too much.
Click to expand...
Click to expand...
Click to expand...
 
That is what I thought you wanted - show a list of reports that have not
been received.

Try dropping the where clause and see what you get back. I think the
comparison operator should be Greater Than rather than LESS Than.

WHERE BusCardsTestQuery.Id is NULL OR ReportCount > DaysRecd

WMorsberger said:
I got the first query to work fine. Now when I do the second query the
only
thing it seems to show me is the the worklist table. It doesn't give me
anyother information. Here is what i have for the 2nd query.

SELECT tblManualWrkListBusCards.ID, tblManualWrkListBusCards.REPORTNAME,
tblManualWrkListBusCards.[RISK LEVEL], tblManualWrkListBusCards.DAYSRECD
FROM tblManualWrkListBusCards LEFT JOIN BusCardsTestQuery
ON tblManualWrkListBusCards.ID = BusCardsTestQuery.ID
WHERE (((BusCardsTestQuery.ID) Is Null)) OR BusCardsTestQuery.ReportCount
<tblManualWrkListBusCards.DAYSRECD


John Spencer said:
Sorry, at this point I don't see how I can help you further.

WMorsberger said:
My Frequency is set up just to say Mon-Fri, Daily, irregular and so on.
Since I'm pretty new at this database stuff I set up different tables
by
department with each report in the table with the frequency that it
should
be
received. So I'm not sure how to approach it.


:

There should be a way, but it will depend on your data structure.

Do you have Number field in your table tblManualWrkListFraud that
tells
you
how often a report is due each week?

Or do you have a ReportsDue table that has a record for each day of
the
week
that a report is due.
ReportID WeekdayDue
122 2
122 4
122 6
133 6

So ReportId 122 is due on Mon, Wed, and Fri and report 133 is due only
on
Friday.

Solutions can be worked out for all of these, but they are not simple
to
implement. The simplest might be to change your current query one to
get
a
count of the reports returned.

Assumption:
tblManualWrkListIDFraud.FREQUENCY is a number field that tells you the
number of reports to be expected in any one week. Of course, if your
count
is for a week and you put in a date range for a month, then this
obviously
doesn't work.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, Count([Report Name]) as ReportCount
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]))
GROUP BY tblFraudworkLogged.ID

QueryTwo:
SELECT tblManualWrkListIDFraud.ID, tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null)) OR
qReportsReceived.ReportCount
<tblManualWrkListIDFraud.FREQUENCY


Is there a way to set up a query that says if it is a daily report
then
we
should receive it 5 times that week and if it is a report that is to
be
received 3 times a week. I think the way that it is set up now if I
put
in a
date range and the report was received at least once then it won't
show
as
not received even though we should have received it everyday.

:

You need to use tblIDFraudWorkLogged in an unmatched query with
your
date
range query. You do need to add the ID field to the query so you
can
use
it
in a join in the second report.

QueryOne. qReportsReceived (your date query)
SELECT tblFraudWorkLogged.ID
, tblIDFraudWorkLogged.[REPORT NAME]
, tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));

Query Two: Identify those reports that have not been received
SELECT tblManualWrkListIDFraud.ID,
tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN qReportsReceived
ON tblManualWrkListIDFraud.ID = qReportsReceived .ID
WHERE (((qReportsReceived .ID) Is Null));


message
I am trying to design a query to tell me what reports I have not
received
for
the week.

I have a table that holds all the reports that are received in
the
department. I then have a table that people input the
information
in
when
the report is actually received. I need to be able to pull a
date
range
to
show what reports I have not received for the week. I tried the
mismatch
query and I'm not getting any information. Here is what I have
so
far:

SELECT tblManualWrkListIDFraud.ID,
tblManualWrkListIDFraud.REPORTNAME,
tblManualWrkListIDFraud.[RISK LEVEL],
tblManualWrkListIDFraud.FREQUENCY
FROM tblManualWrkListIDFraud LEFT JOIN tblIDFraudWorkLogged ON
tblManualWrkListIDFraud.ID = tblIDFraudWorkLogged.ID
WHERE (((tblIDFraudWorkLogged.ID) Is Null));


I also have a date query so that I can pull the date range but am
unsure
how
to join in into the query above. The date query I have is below:

SELECT tblIDFraudWorkLogged.[REPORT NAME],
tblIDFraudWorkLogged.Date
FROM tblIDFraudWorkLogged
WHERE (((tblIDFraudWorkLogged.Date) Between [Enter Start Date:
mm/dd/yyyy]
And [Enter Ending Date: mm/dd/yyyy]));


Well hopefully I didn't confuse anyone too much.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top