Previous Date in a query..help!

[Skotor]

Hello all,
I'm looking for a way to pull the previous date in a query. The table data is structured in the same way listed below minus the red text. The red dates below are my goal. I am using Access 2003. I've done some searching and tried a few things I found, but no luck so far. What would be the best way to go about doing that? Even if all you can do is point me in the right direction, it would be greatly appreciated.

Thanks!

[month] [Year] [Route] [Loc#] [SubLoc] [Actual Date] [Previous] [Sales]
07 2007 1 481 A 7/31/2007 (null) $776.50
07 2007 1 481 B 7/31/2007 (null) $604.50
08 2007 1 481 A 8/31/2007 7/31/2007 $322.25
08 2007 1 481 B 8/31/2007 7/31/2007 $333.00
09 2007 1 481 A 9/28/2007 8/31/2007 $111.50
09 2007 1 481 B 9/28/2007 8/31/2007 $170.25
10 2007 1 481 A 10/31/2007 9/28/2007 $232.50
10 2007 1 481 B 10/31/2007 9/28/2007 $225.00
11 2007 1 481 A 11/29/2007 10/31/2007 $253.50
11 2007 1 481 B 11/29/2007 10/31/2007 $277.00

 

[pbaldy]

Does this help?

http://www.baldyweb.com/PrevValue.htm

 

[Skotor]

I found that on a previous search and was unable to make it work for my query. Kept getting an error. I'll attempt it again and post what I used. Might can point out where I screwed up.

Thanks!

 

[Skotor]

I get a syntax error when I try to run what is listed below...

prev: (SELECT Actual Date FROM Sales Data AS Alias WHERE Actual Date = (Select MAX(Actual Date) FROM Sales Data AS Alias2 WHERE Alias2.Actual Date < Sales Data.Actual Date AND Alias2.Location # = Sales Data.Location #) AND Alias.Location # = Sales Data.Location #)

 

[pbaldy]

For starters, because of the inadvisable spaces and symbols, you need to bracket your table and field names:

[Actual Date]

If you're still stuck, can you post a sample db?

 

[Skotor]

Still stuck. Sample DB attached. Apparently I don't understand how to get the bracket syntax correctly as I'm getting a different error now.

Thanks for the help.

 

[pbaldy]

I only have Access 2000 here, if you can post one in that version. Otherwise I can look at it tonight from home where I have 2003 and 2007.

 

[Skotor]

See attached DB for 2000 copy.

 

[pbaldy]

Okay, I was able to get it to work with a couple of changes. First, I changed the location# field to Sub Loc ID, since the location# is always the same. I assume you wanted it for the sub field:

(SELECT [Actual Date] FROM [Sales Data] AS Alias WHERE [Actual Date] = (Select MAX([Actual Date]) FROM [Sales Data] AS Alias2 WHERE Alias2.[Actual Date] < [Sales Data].[Actual Date] AND Alias2.[Sub Loc ID] = [Sales Data].[Sub Loc ID]) AND Alias.[Sub Loc ID] = [Sales Data].[Sub Loc ID]) AS prev

I also had to tweak the data a little, as there instances of the same date being used for different months, so the subquery was finding multiple records (I assume you were getting the error about the subquery can return at most one record). I'm not the strongest with SQL, but the method assumes there will only be one date/sub loc id combination.

 

[Skotor]

Ahah, not exactly what I needed. But I think it pointed me in the proper direction to correct that error. I'll force the place holder dates to null in the source query. I do need it tied into the location # AND sub location though. I will be running this on anywhere from 20-200 locations at a time. All with different numbers of sub locations. But I think with the way it is working now I can tinker and get it working right for what I need.

Thanks! I'll post back if (when?) I get stuck again.