Creating a historical report

J

J

I need to create a report that reports history based on a 'Date Completed'.
So I created a query off of my table that calculates 30 days less that the
'Date Completed'.

Using an [Enter Completed Date] in my query as 09/30/08 the result in my
'Date_Completed30' field is correct, indicating 08/31/08.

Now I want a report that will give me all the records between the
'Date_Completed' and the 'Date_Completed30'.

Since my initial attempts resulted in a 'circular' error, I went back to my
first query and created a field called 'Date_Completed2' which equals my
'Date_Completed'.

Now I have 3 date fields, 'Date_Completed', 'Date_Completed2', and
'Date_Completed30', and they are populating correctly.

My next attempt was to create a 2nd query to give me the records between the
'Date_Completed2' and 'Date_Completed30' fields. I've tried 'between'
statements, 'IIF' statements but for some reason the only records I end up
with are for the 'Date_Completed'. So I have the correct # of records for
those completed on 09/30/08, but can't get those that land between 09/30/08
and 08/31/08.

Here's my SQL statement:

SELECT tbl_Incident_Table_MT.Incident_IDNumb,
qry_ITIncidentHistory_1.Date_Completed,
qry_ITIncidentHistory_1.Date_Completed2,
qry_ITIncidentHistory_1.Date_Completed30
FROM tbl_Incident_Table_MT INNER JOIN qry_ITIncidentHistory_1 ON
tbl_Incident_Table_MT.Incident_IDNumb =
qry_ITIncidentHistory_1.Incident_IDNumb
WHERE (((qry_ITIncidentHistory_1.Date_Completed) Between
[qry_ITIncidentHistory_1]![Date_Completed2] And
[qry_ITIncidentHistory_1]![Date_Completed30]))
GROUP BY tbl_Incident_Table_MT.Incident_IDNumb,
qry_ITIncidentHistory_1.Date_Completed,
qry_ITIncidentHistory_1.Date_Completed2,
qry_ITIncidentHistory_1.Date_Completed30;

Any help would be appreciated.
 
C

Clifford Bass

Hi J,

I think you are making it too complicated. Why are you doing a
summary query when you are not doing any summing, counting or other aggregate
functions. You have a tbl_Incident_Table_MT table. You should not need to
create the Date_Completed2 or Date_Completed30 fields in that table. What
table does the query qry_ITIncidentHistory_1 draw from? The same one or
another one? What is the SQL for that query?

Clifford Bass
 
J

J

Hey Clifford,

My qry_ITIncidentHistory_1 pulls from the main tbl_Incident_Table_MT but I'm
using it to calculate the 30 day criteria based on the [Enter Date Completed]
function.

Once that date is calculated, I need each record between those 2 dates.
--
J


Clifford Bass said:
Hi J,

I think you are making it too complicated. Why are you doing a
summary query when you are not doing any summing, counting or other aggregate
functions. You have a tbl_Incident_Table_MT table. You should not need to
create the Date_Completed2 or Date_Completed30 fields in that table. What
table does the query qry_ITIncidentHistory_1 draw from? The same one or
another one? What is the SQL for that query?

Clifford Bass

J said:
I need to create a report that reports history based on a 'Date Completed'.
So I created a query off of my table that calculates 30 days less that the
'Date Completed'.

Using an [Enter Completed Date] in my query as 09/30/08 the result in my
'Date_Completed30' field is correct, indicating 08/31/08.

Now I want a report that will give me all the records between the
'Date_Completed' and the 'Date_Completed30'.

Since my initial attempts resulted in a 'circular' error, I went back to my
first query and created a field called 'Date_Completed2' which equals my
'Date_Completed'.

Now I have 3 date fields, 'Date_Completed', 'Date_Completed2', and
'Date_Completed30', and they are populating correctly.

My next attempt was to create a 2nd query to give me the records between the
'Date_Completed2' and 'Date_Completed30' fields. I've tried 'between'
statements, 'IIF' statements but for some reason the only records I end up
with are for the 'Date_Completed'. So I have the correct # of records for
those completed on 09/30/08, but can't get those that land between 09/30/08
and 08/31/08.

Here's my SQL statement:

SELECT tbl_Incident_Table_MT.Incident_IDNumb,
qry_ITIncidentHistory_1.Date_Completed,
qry_ITIncidentHistory_1.Date_Completed2,
qry_ITIncidentHistory_1.Date_Completed30
FROM tbl_Incident_Table_MT INNER JOIN qry_ITIncidentHistory_1 ON
tbl_Incident_Table_MT.Incident_IDNumb =
qry_ITIncidentHistory_1.Incident_IDNumb
WHERE (((qry_ITIncidentHistory_1.Date_Completed) Between
[qry_ITIncidentHistory_1]![Date_Completed2] And
[qry_ITIncidentHistory_1]![Date_Completed30]))
GROUP BY tbl_Incident_Table_MT.Incident_IDNumb,
qry_ITIncidentHistory_1.Date_Completed,
qry_ITIncidentHistory_1.Date_Completed2,
qry_ITIncidentHistory_1.Date_Completed30;

Any help would be appreciated.
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi J,

So, could you not just do something like this:

SELECT Incident_IDNumb, Date_Completed
FROM tbl_Incident_Table_MT
WHERE Date_Completed Between DateAdd("d",-30,[Enter Date Completed]) and
[Enter Date Completed]
ORDER BY Date_Completed;

Clifford Bass

J said:
Hey Clifford,

My qry_ITIncidentHistory_1 pulls from the main tbl_Incident_Table_MT but I'm
using it to calculate the 30 day criteria based on the [Enter Date Completed]
function.

Once that date is calculated, I need each record between those 2 dates.
--
J


Clifford Bass said:
Hi J,

I think you are making it too complicated. Why are you doing a
summary query when you are not doing any summing, counting or other aggregate
functions. You have a tbl_Incident_Table_MT table. You should not need to
create the Date_Completed2 or Date_Completed30 fields in that table. What
table does the query qry_ITIncidentHistory_1 draw from? The same one or
another one? What is the SQL for that query?

Clifford Bass

J said:
I need to create a report that reports history based on a 'Date Completed'.
So I created a query off of my table that calculates 30 days less that the
'Date Completed'.

Using an [Enter Completed Date] in my query as 09/30/08 the result in my
'Date_Completed30' field is correct, indicating 08/31/08.

Now I want a report that will give me all the records between the
'Date_Completed' and the 'Date_Completed30'.

Since my initial attempts resulted in a 'circular' error, I went back to my
first query and created a field called 'Date_Completed2' which equals my
'Date_Completed'.

Now I have 3 date fields, 'Date_Completed', 'Date_Completed2', and
'Date_Completed30', and they are populating correctly.

My next attempt was to create a 2nd query to give me the records between the
'Date_Completed2' and 'Date_Completed30' fields. I've tried 'between'
statements, 'IIF' statements but for some reason the only records I end up
with are for the 'Date_Completed'. So I have the correct # of records for
those completed on 09/30/08, but can't get those that land between 09/30/08
and 08/31/08.

Here's my SQL statement:

SELECT tbl_Incident_Table_MT.Incident_IDNumb,
qry_ITIncidentHistory_1.Date_Completed,
qry_ITIncidentHistory_1.Date_Completed2,
qry_ITIncidentHistory_1.Date_Completed30
FROM tbl_Incident_Table_MT INNER JOIN qry_ITIncidentHistory_1 ON
tbl_Incident_Table_MT.Incident_IDNumb =
qry_ITIncidentHistory_1.Incident_IDNumb
WHERE (((qry_ITIncidentHistory_1.Date_Completed) Between
[qry_ITIncidentHistory_1]![Date_Completed2] And
[qry_ITIncidentHistory_1]![Date_Completed30]))
GROUP BY tbl_Incident_Table_MT.Incident_IDNumb,
qry_ITIncidentHistory_1.Date_Completed,
qry_ITIncidentHistory_1.Date_Completed2,
qry_ITIncidentHistory_1.Date_Completed30;

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
J

J

Clifford

Yahoo, that worked. When I get some time I'll review and assess why I made
it so complicated. I'm a novice so...

Thanks for your assistance. HAVE A GREAT DAY!
--
J


Clifford Bass said:
Hi J,

So, could you not just do something like this:

SELECT Incident_IDNumb, Date_Completed
FROM tbl_Incident_Table_MT
WHERE Date_Completed Between DateAdd("d",-30,[Enter Date Completed]) and
[Enter Date Completed]
ORDER BY Date_Completed;

Clifford Bass

J said:
Hey Clifford,

My qry_ITIncidentHistory_1 pulls from the main tbl_Incident_Table_MT but I'm
using it to calculate the 30 day criteria based on the [Enter Date Completed]
function.

Once that date is calculated, I need each record between those 2 dates.
--
J


Clifford Bass said:
Hi J,

I think you are making it too complicated. Why are you doing a
summary query when you are not doing any summing, counting or other aggregate
functions. You have a tbl_Incident_Table_MT table. You should not need to
create the Date_Completed2 or Date_Completed30 fields in that table. What
table does the query qry_ITIncidentHistory_1 draw from? The same one or
another one? What is the SQL for that query?

Clifford Bass

:

I need to create a report that reports history based on a 'Date Completed'.
So I created a query off of my table that calculates 30 days less that the
'Date Completed'.

Using an [Enter Completed Date] in my query as 09/30/08 the result in my
'Date_Completed30' field is correct, indicating 08/31/08.

Now I want a report that will give me all the records between the
'Date_Completed' and the 'Date_Completed30'.

Since my initial attempts resulted in a 'circular' error, I went back to my
first query and created a field called 'Date_Completed2' which equals my
'Date_Completed'.

Now I have 3 date fields, 'Date_Completed', 'Date_Completed2', and
'Date_Completed30', and they are populating correctly.

My next attempt was to create a 2nd query to give me the records between the
'Date_Completed2' and 'Date_Completed30' fields. I've tried 'between'
statements, 'IIF' statements but for some reason the only records I end up
with are for the 'Date_Completed'. So I have the correct # of records for
those completed on 09/30/08, but can't get those that land between 09/30/08
and 08/31/08.

Here's my SQL statement:

SELECT tbl_Incident_Table_MT.Incident_IDNumb,
qry_ITIncidentHistory_1.Date_Completed,
qry_ITIncidentHistory_1.Date_Completed2,
qry_ITIncidentHistory_1.Date_Completed30
FROM tbl_Incident_Table_MT INNER JOIN qry_ITIncidentHistory_1 ON
tbl_Incident_Table_MT.Incident_IDNumb =
qry_ITIncidentHistory_1.Incident_IDNumb
WHERE (((qry_ITIncidentHistory_1.Date_Completed) Between
[qry_ITIncidentHistory_1]![Date_Completed2] And
[qry_ITIncidentHistory_1]![Date_Completed30]))
GROUP BY tbl_Incident_Table_MT.Incident_IDNumb,
qry_ITIncidentHistory_1.Date_Completed,
qry_ITIncidentHistory_1.Date_Completed2,
qry_ITIncidentHistory_1.Date_Completed30;

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

group by yy/mm from a date variable 1
How to lock records until specific conditions are met? 1
Lost in time! 5
field with date formatted as string 6
very slow report with dcount() 6
Weigh tasks 0
Macro to extract few values from a cell 4
Creating a Search using a combo box and then creating a report. 4

Top