need help to compare records from the same table

[sumhungl0]

i have an access database that I import maintenance jobs/actions, that I get from a web report as a csv excel file. these maintenance jobs are reoccurring items like oil changes and things like that. each of them have a calendar or hourly increment. so like an oil change is every 3 months and my report gives me "next due date" and "last accomplished date". i import the csv report into access about weekly with a macro but not always on the same day of the week. I have an added field in the table for csv import date so that I can decipher between the csv reports that i import. I need to query all jobs/actions that were accomplished last month so that I can come up with a report of the past months performance. I have a couple problems to overcome. some maintenance jobs happen more than once in a month. example: window wash every 15 days, csv report imported on 2/2/2015 shows last accomplished 1/26/2015 and next due 2/10/2015. csv report imported on 2/9/2015 has the same dates for this maintenance job. but now the csv report imported on 2/17/2015 shows that same job last accomplished 2/10/2015 and next due 2/25/2015. i have to be able to query for this job from multiple reports or i will not get all maintenance actions for the month. so i cannot just query the MAX import date. so how would i query for the most recent several reports? would that even work? in the end, i need to be able to pull up an access report that shows all jobs due in the previous month, this would need to be from all records with an import date of before the previous month so that i have a starting set of records. then i would need an import date of current month to find all the dates last accomplished within previous month so that i have an ending set of records. but items that happen more than once a month will be missed so i would need a mid-month import to get these jobs. does anyone have any ideas on how i can query for these records?

 

[sumhungl0]

maybe im not clear on what im asking? ill try again. I import a csv report into an access table about weekly. I download the report of maintenance items from the web and it contains basically the same items except for the dates or hours change. <o></o>

sample record: <o></o>
car1, window wash, every 15 days, due 26Feb15, last accomplished 11Feb15, imported 23Feb15

next week when I import the report it will add a recordfor the same maintenance item that will look like this:<o></o>
car1, window wash, every 15 days, due 13Mar15, last accomplished 26Feb15, imported 2Mar15

so how can i tie these two records together to get a summary of what happend at the end of the month? basicly i need to get the info of the whole transaction from two records in the table. when it was due, and when it got done. any ideas?
<o></o>

 

[alansidman]

You have not given us any field names, so it will be difficult to give you a specific SQL statement. However, you should run a query specifying that you want all data returned for Car1 with the last accomplished date between 1 Feb 2015 and 28 feb 2015.

 

[sumhungl0]

You have not given us any field names, so it will be difficult to give you a specific SQL statement. However, you should run a query specifying that you want all data returned for Car1 with the last accomplished date between 1 Feb 2015 and 28 feb 2015.

for the field names, I will use the above sample. but it really doesn't matter, the names have changed a few times.
sample record:
equipID, maintenanceItem, increment, logic, dueDate, last accomplished, importDate
car1, window wash, 15, days, 26Feb15, 11Feb15, 23Feb15

as far as the query to get all "last accomplished", yes, that is a great idea and I don't know why I didn't think of that. but it still leaves me with the problem of tying the [last accomplished] to the [dueDate] record. i cant even think of how i can get that to happen.

few questions.
to query for all "last accomplished" dates within the month, how would i set the query criteria for all dates for that month? because i cannot just manually type the query criteria each time. i wanna use a form for the user to select the month and then the query use the form for the month criteria. any thoughts on that?

next question.
how can i query this table to get the records to match up? using the sample i posted above....

sample record:
car1, window wash, every 15 days, due 26Feb15, last accomplished 11Feb15, imported 23Feb15

next week when I import the report it will add a record for the same maintenance item that will look like this:
car1, window wash, every 15 days, due 13Mar15, last accomplished 26Feb15, imported 2Mar15

so the result from this that i need to get is.... for the month of Feb, for car1's window wash.... i should get the following results.

car1, window wash, due 11Feb15, accomplished 11Feb15
car1, window wash, due 26Feb15, accomplished 26Feb15

points to add...
those results would be using dates from 3 records in the table. like this.... result1 would read dueDate from record1 in the table and read accomplished from record2. result2 would read dueDate from record2 and read accomplished from record3. does that help?

 

[alansidman]

to use a form to drive a query criteria, look at this tutorial.
Macromedia Flash (SWF) Movie Created by Camtasia Studio 2