Date 1 week apart

[elfranzen]

I am looking to build a report that pulls data from one table but I need to compare data that is I week apart. the tables logs this data every 15 mins in to 10 seperate groups. I need to match the time but be 1 week off. so if I am pulling field 1 I need to pull this field twice once at 12/1/12 at 5:00 am all ten sub groups and the same sub groups at 11/24/12 at 5:00am the date and time is logged in one field and this can not be changed. it is a ODBC connect to a database that can not be modified at all. I know there has to be a way to do this but I am :banghead:trying to figure it out. let me know if you can help. Thanks

 

[plog]

You need a subquery to prepare the data to be matched. Create a query based on your table and bring in all the fields you need to use (either to match or display on). Then create a new field using the DateAdd() function (http://www.techonthenet.com/access/functions/date/dateadd.php) and your Date/Time field that needs to be matched. Let's call the Date/Time field in your table [DateField]. This is what you would need to add to that query:

DateField_1: DateAdd("d", 7, [DateField])

If you are matching it to future data, use 7, if you are matching past date use -7. That is your sub-query. Now you create the main query by bringing in 2 instances of that sub-query, linking on all the fields you need to match (excluding the date fields), and then matching the [DateField] in one instance of the subquery to the [DateField_1] in the other instances of the sub-query. Then bring down all the fields you need in the query.

 

[elfranzen]

Got it Thanks so Much works great.