Help with a calculation

[Cdray]

I have a spot in my table where dates are tied with numbers i.e.



Datefield: 31 Apr 15
Numberfield: 56000

Every month for each client the month end date is added and a number put it. So each date field and number field is of course tied to a ClientID basically (the master key).

This is all fine, but what I'm trying to do is get a table value calculation which is a subtraction of the month end date within the last 31 days, from the month end date in the last 62 days (basically the most recent month from the most recent before that).

Datefield: 31 Apr 15
Numberfield: 56000

Datefield: 31 May 15
Numberfield: 58000

I want it to subtract 56000 from 58000.

I know how to get dates, though it may not be the best way "(Between Date() And DateAdd("m",-1,Date())". But how do I tie that to the numberfield and subtract them?

This field I'm trying to add, it should auto populate as 2,000, based on the calculation.

Any ideas?

[June7]

So you need to pull data from another record of the same table? One method: http://allenbrowne.com/subquery-01.html#AnotherRecord

[Cdray]

I'm not sure how to use this? I don't understand how to correlate that with mine. All the data I have is CustomerID (Primary key), Datefield, and numberfield.

For each customer they have infinite datefields with 1 numberfield per date field, and I need to subtract the most recent numberfield from the 2nd most recent.

[orange]

There is no 31 April!!!!!!!!!!!!!!!!!!
Do you have a clear description in plain English of what you are trying to do?

Access had Date-related functions and can handle dates and differences in dates quite well.

[Cdray]

Hi, sorry for the confusion.

Today's June 25.

So for a client I have

Datefield: 31 May 15
Numberfield: 56000

These are on the same table, and the same record.

Then I have

Datefield: 30 Apr 15
Numberfield: 54000


I need it to automatically subtract the most recent number (56,000, tied to 31 May 15), from the number tied to the earlier date (54,000, tied to 30 Apr 15).

[June7]

Nested subquery is the way to pull previous record data to calc with in current record. Something like:

SELECT ClientID, Datefield, Numberfield, (SELECT TOP 1 Dupe.Numberfield FROM tablename AS Dupe WHERE Dupe.ClientID = tablename.ClientID AND Dupe.Datefield < tablename.Datefield ORDER BY Dupe.Datefield DESC, ClientID) AS PriorValue FROM tablename;

Now use that query as RecordSource for a report. Expression in textbox: =[Numberfield] - [PriorValue]

[Cdray]

SELECT ClientID, Datefield, Numberfield, (SELECT TOP 1 Dupe.Numberfield FROM tablename AS Dupe WHERE Dupe.ClientID = tablename.ClientID AND Dupe.Datefield < tablename.Datefield ORDER BY Dupe.Datefield DESC, ClientID) AS PriorValue FROM tablename;

Now use that query as RecordSource for a report. Expression in textbox: =[Numberfield] - [PriorValue]

Where would I place that first line? I've designed a whole database but in a very rudimentary way as my programming is definitely not great.

[June7]

That is a complete SQL statement to build a query object. Open query designer, switch to SQL View, copy/paste the statement, change table and field names as appropriate.

[Cdray]

I got a syntax error in the part with parenthesis. Not sure which is wrong, my table name has spaces in, does it need to underscore?

[June7]

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. If used, enclose name in [].

FROM [table name]