Query appends incorrect data

[Crystal via AccessMonster.com]

Hi,

This is hard to describe, but here goes.
I have several archive tables and queries. They are set up as follows:
On open: Date_Range appends to Archive_DR if ENDATE (ending date) is <Date()
Items appends to Archive_Items (Table inner joined with a
Select version of the above query)
Items deletes from Items where the above is true
Date_Range deletes where ENDATE <Date()

I also have my tables updating when the database loads. This occurs first,
then the archive immediately runs to grab any outdated information. The
update looks at my Items table to see when the Item was entered (can have the
same item multiple times). It takes the minimum date for each Item and uses
that as my start date. My end date is figured by adding 60 days to the start
date. The same items will not always be used from period to period, nor are
the periods the same. When the end date is reached, it is archived. A new
date should not be calculated until the item appears in the Items table again.


My problem is that sometimes this works and sometimes it doesn't. For
instance, one item has an archived date range of 2/1 thru 4/2. The current
or new date range is 3/31 thru 5/30. I checked the Items table, but that
item is not listed. All the occurences have been successfully deleted and
archived into Archive_Items. I've noticed that the new date range on these
incorrect items is always the last date the item appeared. I cannot find any
other pattern as to why this may be happening. I cannot have any dates that
overlap. Please help!

Thanks,
Crystal

 

[Guest]

How does the Date_range get set, initially, and what does Date_Range look
like before you run the archive/delete process? There is nothing in your
process (at least as described) that changes the date range, so I suspect
that the problem is created at an earlier stage in your process and only gets
"noticed" after you have done the archive/delete. You need to find how that
date range gets set inaccurately; everything else I think is just a "symptom"
of an error in that part of the process.

One clue to follow is the fact that the date is always the last day used.
Am I correct in reading your description that if the item is used again
within the 60 day "window" that you do not reset the date range? It appears
that on at least these items the date range was reset while it was still
inside the 60 day window, so it is quite possible that the expression or code
that calculates the date range is not accounting for this properly.

 

[dreamsoul620 via AccessMonster.com]

Thanks for your response.

I have a query when the database first opens to look at Items, find the min
date for each item and write that as my start date. The end date is then
startdate + 60 days. If the Item is already in the table, the date will not
append. As I said earlier, only one range per Item at a time. Where I get
confused, is the new date range that is being generated after
archiving/deleting does not correpsond to the Item table at all. The start
date on these new ranges is equal to the last archived date for that Item. I
have a macro that runs the archive queries. The Date append is first, then
Item append (since it's dependent on the current date ranges), Item delete,
then Date delete. If I'm thinking correctly, this should wipe out all
expired date ranges. Originally, I had the Create_Date query run immediately
after so the Date_Range table has new dates if needed. That works great for
about 80% of the items. I've checked this process step by step, but the
Items are deleted correctly before the Dates are deleted. This is all
correct before the Create_Date reruns. Should I not run the Create_Date
before and after archiving?

How does the Date_range get set, initially, and what does Date_Range look
like before you run the archive/delete process? There is nothing in your
process (at least as described) that changes the date range, so I suspect
that the problem is created at an earlier stage in your process and only gets
"noticed" after you have done the archive/delete. You need to find how that
date range gets set inaccurately; everything else I think is just a "symptom"
of an error in that part of the process.

One clue to follow is the fact that the date is always the last day used.
Am I correct in reading your description that if the item is used again
within the 60 day "window" that you do not reset the date range? It appears
that on at least these items the date range was reset while it was still
inside the 60 day window, so it is quite possible that the expression or code
that calculates the date range is not accounting for this properly.

Hi,

[quoted text clipped - 26 lines]

Thanks,
Crystal

 

[dreamsoul620 via AccessMonster.com]

Here are the SQL queries I'm using btw...

To Append the Date:
INSERT INTO ARCHIVE_DR ( EMPNUM, EMPNAME, STDATE, ENDATE )
SELECT DATE_RANGE.EMPNUM, DATE_RANGE.EMPNAME, DATE_RANGE.STDATE, DATE_RANGE.
ENDATE
FROM DATE_RANGE
WHERE (((DateDiff("d",[STDATE],Date()))>60));

To Delete the Date:
DELETE DISTINCTROW DATE_RANGE.*
FROM DATE_RANGE
WHERE (((DateDiff("d",[STDATE],Date()))>60));

To Append Items/Employee:
INSERT INTO ARCHIVE_POINTS ( EMPNAME, EMPNUM, [DATE], USTR1, FCLASS, LENT1,
MINUTES, POINTS, REASON )
SELECT EARLY_LEAVE_POINTS.EMPNAME, EARLY_LEAVE_POINTS.EMPNUM,
EARLY_LEAVE_POINTS.DATE, EARLY_LEAVE_POINTS.USTR1, EARLY_LEAVE_POINTS.FCLASS,
EARLY_LEAVE_POINTS.LENT1, EARLY_LEAVE_POINTS.MINUTES, EARLY_LEAVE_POINTS.
POINTS, EARLY_LEAVE_POINTS.REASON
FROM EARLY_LEAVE_POINTS INNER JOIN ARCHIVE_FIND_ENDATE ON EARLY_LEAVE_POINTS.
EMPNAME = ARCHIVE_FIND_ENDATE.EMPNAME;

To Delete Items/Employee:
DELETE DISTINCTROW EARLY_LEAVE_POINTS.*
FROM EARLY_LEAVE_POINTS INNER JOIN ARCHIVE_FIND_ENDATE ON EARLY_LEAVE_POINTS.
EMPNAME=ARCHIVE_FIND_ENDATE.EMPNAME;

Thanks for your response.

I have a query when the database first opens to look at Items, find the min
date for each item and write that as my start date. The end date is then
startdate + 60 days. If the Item is already in the table, the date will not
append. As I said earlier, only one range per Item at a time. Where I get
confused, is the new date range that is being generated after
archiving/deleting does not correpsond to the Item table at all. The start
date on these new ranges is equal to the last archived date for that Item. I
have a macro that runs the archive queries. The Date append is first, then
Item append (since it's dependent on the current date ranges), Item delete,
then Date delete. If I'm thinking correctly, this should wipe out all
expired date ranges. Originally, I had the Create_Date query run immediately
after so the Date_Range table has new dates if needed. That works great for
about 80% of the items. I've checked this process step by step, but the
Items are deleted correctly before the Dates are deleted. This is all
correct before the Create_Date reruns. Should I not run the Create_Date
before and after archiving?

How does the Date_range get set, initially, and what does Date_Range look
like before you run the archive/delete process? There is nothing in your

[quoted text clipped - 15 lines]

 

[Guest]

Just wanted to say I haven't given up here, just that I do this during breaks
at work and your problem will take some concentration and time to figure out,
so keep checking.

I do suspect that running Create_Date twice is somehow involved in this
problem - I think that though the steps themselves may be OK the sequence of
them throws off that date calculation.
--
- K Dales

Here are the SQL queries I'm using btw...

To Append the Date:
INSERT INTO ARCHIVE_DR ( EMPNUM, EMPNAME, STDATE, ENDATE )
SELECT DATE_RANGE.EMPNUM, DATE_RANGE.EMPNAME, DATE_RANGE.STDATE, DATE_RANGE.
ENDATE
FROM DATE_RANGE
WHERE (((DateDiff("d",[STDATE],Date()))>60));

To Delete the Date:
DELETE DISTINCTROW DATE_RANGE.*
FROM DATE_RANGE
WHERE (((DateDiff("d",[STDATE],Date()))>60));

To Append Items/Employee:
INSERT INTO ARCHIVE_POINTS ( EMPNAME, EMPNUM, [DATE], USTR1, FCLASS, LENT1,
MINUTES, POINTS, REASON )
SELECT EARLY_LEAVE_POINTS.EMPNAME, EARLY_LEAVE_POINTS.EMPNUM,
EARLY_LEAVE_POINTS.DATE, EARLY_LEAVE_POINTS.USTR1, EARLY_LEAVE_POINTS.FCLASS,
EARLY_LEAVE_POINTS.LENT1, EARLY_LEAVE_POINTS.MINUTES, EARLY_LEAVE_POINTS.
POINTS, EARLY_LEAVE_POINTS.REASON
FROM EARLY_LEAVE_POINTS INNER JOIN ARCHIVE_FIND_ENDATE ON EARLY_LEAVE_POINTS.
EMPNAME = ARCHIVE_FIND_ENDATE.EMPNAME;

To Delete Items/Employee:
DELETE DISTINCTROW EARLY_LEAVE_POINTS.*
FROM EARLY_LEAVE_POINTS INNER JOIN ARCHIVE_FIND_ENDATE ON EARLY_LEAVE_POINTS.
EMPNAME=ARCHIVE_FIND_ENDATE.EMPNAME;

Thanks for your response.

I have a query when the database first opens to look at Items, find the min
date for each item and write that as my start date. The end date is then
startdate + 60 days. If the Item is already in the table, the date will not
append. As I said earlier, only one range per Item at a time. Where I get
confused, is the new date range that is being generated after
archiving/deleting does not correpsond to the Item table at all. The start
date on these new ranges is equal to the last archived date for that Item. I
have a macro that runs the archive queries. The Date append is first, then
Item append (since it's dependent on the current date ranges), Item delete,
then Date delete. If I'm thinking correctly, this should wipe out all
expired date ranges. Originally, I had the Create_Date query run immediately
after so the Date_Range table has new dates if needed. That works great for
about 80% of the items. I've checked this process step by step, but the
Items are deleted correctly before the Dates are deleted. This is all
correct before the Create_Date reruns. Should I not run the Create_Date
before and after archiving?

How does the Date_range get set, initially, and what does Date_Range look
like before you run the archive/delete process? There is nothing in your

[quoted text clipped - 15 lines]

Thanks,
Crystal

 

[Guest]

One more question: You mention Create_Date; I assume this is your query or
process that creates an entry in the DATE_RANGE table - if you could post the
query or give details of how that works it would help, since this is
presumably where that start date is being calculated and I would like to see
how that is done.
--
- K Dales

Here are the SQL queries I'm using btw...

To Append the Date:
INSERT INTO ARCHIVE_DR ( EMPNUM, EMPNAME, STDATE, ENDATE )
SELECT DATE_RANGE.EMPNUM, DATE_RANGE.EMPNAME, DATE_RANGE.STDATE, DATE_RANGE.
ENDATE
FROM DATE_RANGE
WHERE (((DateDiff("d",[STDATE],Date()))>60));

To Delete the Date:
DELETE DISTINCTROW DATE_RANGE.*
FROM DATE_RANGE
WHERE (((DateDiff("d",[STDATE],Date()))>60));

To Append Items/Employee:
INSERT INTO ARCHIVE_POINTS ( EMPNAME, EMPNUM, [DATE], USTR1, FCLASS, LENT1,
MINUTES, POINTS, REASON )
SELECT EARLY_LEAVE_POINTS.EMPNAME, EARLY_LEAVE_POINTS.EMPNUM,
EARLY_LEAVE_POINTS.DATE, EARLY_LEAVE_POINTS.USTR1, EARLY_LEAVE_POINTS.FCLASS,
EARLY_LEAVE_POINTS.LENT1, EARLY_LEAVE_POINTS.MINUTES, EARLY_LEAVE_POINTS.
POINTS, EARLY_LEAVE_POINTS.REASON
FROM EARLY_LEAVE_POINTS INNER JOIN ARCHIVE_FIND_ENDATE ON EARLY_LEAVE_POINTS.
EMPNAME = ARCHIVE_FIND_ENDATE.EMPNAME;

To Delete Items/Employee:
DELETE DISTINCTROW EARLY_LEAVE_POINTS.*
FROM EARLY_LEAVE_POINTS INNER JOIN ARCHIVE_FIND_ENDATE ON EARLY_LEAVE_POINTS.
EMPNAME=ARCHIVE_FIND_ENDATE.EMPNAME;

Thanks for your response.

I have a query when the database first opens to look at Items, find the min
date for each item and write that as my start date. The end date is then
startdate + 60 days. If the Item is already in the table, the date will not
append. As I said earlier, only one range per Item at a time. Where I get
confused, is the new date range that is being generated after
archiving/deleting does not correpsond to the Item table at all. The start
date on these new ranges is equal to the last archived date for that Item. I
have a macro that runs the archive queries. The Date append is first, then
Item append (since it's dependent on the current date ranges), Item delete,
then Date delete. If I'm thinking correctly, this should wipe out all
expired date ranges. Originally, I had the Create_Date query run immediately
after so the Date_Range table has new dates if needed. That works great for
about 80% of the items. I've checked this process step by step, but the
Items are deleted correctly before the Dates are deleted. This is all
correct before the Create_Date reruns. Should I not run the Create_Date
before and after archiving?

How does the Date_range get set, initially, and what does Date_Range look
like before you run the archive/delete process? There is nothing in your

[quoted text clipped - 15 lines]

Thanks,
Crystal

 

[dreamsoul620 via AccessMonster.com]

Thanks. I apologize for not responding sooner. I've been on vacation for
the past week.

This query gets my min start date.

SELECT EARLY_LEAVE_POINTS.EMPNAME, EARLY_LEAVE_POINTS.EMPNUM, Min
(EARLY_LEAVE_POINTS.DATE) AS STDATE
FROM EARLY_LEAVE_POINTS
GROUP BY EARLY_LEAVE_POINTS.EMPNAME, EARLY_LEAVE_POINTS.EMPNUM;

This query creates the range based off the above query.

INSERT INTO DATE_RANGE ( EMPNAME, EMPNUM, STDATE, ENDATE )
SELECT DATES1.EMPNAME, DATES1.EMPNUM, DATES1.STDATE, DateAdd("d",60,[STDATE])
AS ENDATE
FROM DATES1
GROUP BY DATES1.EMPNAME, DATES1.EMPNUM, DATES1.STDATE;

One more question: You mention Create_Date; I assume this is your query or
process that creates an entry in the DATE_RANGE table - if you could post the
query or give details of how that works it would help, since this is
presumably where that start date is being calculated and I would like to see
how that is done.

Here are the SQL queries I'm using btw...

[quoted text clipped - 49 lines]