Previous Month Date Function

[Joe]

Hello,

I'm trying to get the date on a pmt from the previous month within a query.
For example: My data date = 12/31/07 my Date Pmt Last was on 12/24/08 and now
I need a field named Last Pmt Received which would be the Date Pmt Last for
November. Any ideas how this can be done?

Data Date Date Pmt Last Last Pmt Received
12/31/08 12/24/08 11/15/08
11/30/08 11/15/08 10/01/08
10/31/08 10/01/08 09/02/08
09/30/08 09/02/08

Thank You

Joe

 

[a a r o n . k e m p f]

SQL Server has the capability to do things like this in MDX

Jet doesnt' have previous month, previous quarter, previous year
functionality

 

[Gina Whipp]

Joe,

Please hang on for a REAL answer... I have to get on a conference call but
I'll check back later to see if you got helped. Aaron only has one answer
no matter if you want bacon OR eggs!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[BruceM]

Where did Last Pmt Received come from? If this is all in one table you may
need to rethink the design so that each payment record is in its own record,
but first some more description of the database's purpose and structure may
help.

 

[Joe]

The last payment Received is the Date Pmt Last from the previous month. For
example: The Last Payment Received for December 08 would be the Date Pmt Last
for November. This is a one time only request where we have to determine the
number of days from the Date payment last. I try doing it as a subquery but
my experience is limited. What I did was I exported the data (61,000)
records) to excel. Sort it by Account, data_date, Date Pmt Last. Then I did
the following formula in excel.

IF(and(a2=a3,b2>b3),C3,"Diff Acct")... That worked perfectly now I have to
input the data back into access on a make table and link it to my original
table...

However, I still would like to know if this is possible thru access. I know
there's got to be a way using a subquery...

Thanks

Joe

 

[John W. Vinson]

SQL Server has the capability to do things like this in MDX

Jet doesnt' have previous month, previous quarter, previous year
functionality

.... other than the rich and capable series of date functions such as DateAdd,
DateDiff, DateSerial and so on.