Sbracting previous month data from current month

[jband]

I been trying to get a query written that will pull the previous months total for a facility so i can subtract it from the current month to get a change from the previous month to current month.

Here is my current query that I am stuck on

SELECT [CashFlow Link].LOCATION, [CashFlow Link].RCNAME, [CashFlow Link].Date1, [CashFlow Link].VISITS, [CashFlow Link].TOTCHG, [CashFlow Link].PAYMNT, [CashFlow Link].TOTCRD, [CashFlow Link].TOTAR, [CashFlow Link].PENCRD, [CashFlow Link].TOTDEB, (select PENCRD
from [CashFlow Link] as a
where a.RCNAME = [CashFlow Link].RCNAME
and [CashFlow Link].CLTFAC <> ''
and a.Date1 < [CashFlow Link].Date1) AS Previous
FROM [CashFlow Link]
WHERE ((([CashFlow Link].CLTFAC)<>''))
ORDER BY [CashFlow Link].Date1, [CashFlow Link].RCNAME;

Any help would be awesome

 

[CJ_London]

Using subqueries in this context, the subquery must return a single value. As written it could return more than one value (even if there is only one).

So change it to
select First(PENCRD)
or
select Max(PENCRD)
or
Select sum(PENCRD)
or
Select Top 1 PENCRD

depending on your requirement

 

[jband]

Thank you for your suggestions CJ but neither of them worked, I got duplicate numbers. I am not sure if a sub query is the way to go, so i am up for any other suggestions. I am unfamiliar with Access so I apologize.

 

[CJ_London]

not sure what you mean by getting duplicate numbers - assuming you are using 'first' et al then by definition, it can only return one number - this implies the problem is with the rest of the query.

As an alternative to the subquery you can use

dlookup("PENCRD","[CashFlow Link]","RCNAME = " & RCNAME & " and CLTFAC <> '' and Date1 < " & [Date1])

Just out of curiosity, does your table only contain the prior month? if not then Date1 < " & [Date1] will produce a result for each month, this may be where your duplicates are coming from

 

[jband]

It displays only upto the previous month. The dlookup gave repeated numbers

Here is what it gave
CashFlow
Previous
130.7
130.7
130.7
130.7
130.7
130.7

 

[CJ_London]

OK if you take out the subquery, do you still get repeating rows?

Again Dlookup can only return one value, so the repeating is occuring somewhere else in your query so either your WHERE statement for the main query is not specific enough, or you need to use GROUP BY or SELECT DISTINCT.

 

[jband]

I noticed that my dlookup left off & [Date1]) for some reason. I added it in and now I am getting a Syntax error(missing Operator) in query expression [RCNAME] = Bluefield and CLTFAC <> '' and Date1 < 11/30/2012

 

[CJ_London]

It would help if you put the full syntax but it looks like you should be using

Date1 < #11/30/2012#

 

[CJ_London]

Also this is wrong as well (assuming bluefield is a name)

[RCNAME] = Bluefield

should be

[RCNAME] = 'Bluefield'

 

[jband]

This is the statement I am using.

dlookup("PENCRD","[CashFlow Link]","RCNAME = '" & RCNAME & "' and [Date1] < " & [Date1])

 

[CJ_London]

try

dlookup("PENCRD","[CashFlow Link]","RCNAME = '" & RCNAME & "' and [Date1] < #" & [Date1] & "#")

 

[jband]

Same synatx error but now the date looks like #11/30/2012#

 

[CJ_London]

can you post a screenshot of the syntax error and your full query please

 

[jband]

I had to attach the error as a pic.



SELECT DISTINCT [CashFlow Link].LOCATION, [CashFlow Link].RCNAME, [CashFlow Link].Date1, [CashFlow Link].VISITS, [CashFlow Link].TOTCHG, [CashFlow Link].PAYMNT, [CashFlow Link].TOTCRD, [CashFlow Link].TOTAR, [CashFlow Link].PENCRD, [CashFlow Link].TOTDEB, dlookup("PENCRD","[CashFlow Link]","RCNAME = '" & RCNAME & "' and [Date1] < #" & [Date1] & "#")
AS Previous
FROM [CashFlow Link]
WHERE ((([CashFlow Link].CLTFAC)<>''))
and [CashFlow Link].Date1 >=DateAdd("m",-6,Date())
ORDER BY [CashFlow Link].Date1, [CashFlow Link].RCNAME;

 

[CJ_London]

the error is in your data St Mary's MeD.... which has a quotation mark in it.

To solve this, replace

RCNAME = '" & RCNAME .....

with

RCNAME = '" & Replace(RCNAME,"'","''") ....

i.e. replace a single quotation mark with two single quotation marks

 

[jband]

It is now back to duplicates for each facility

 

[CJ_London]

I'm out of idea's, Dlookup can only produce a single value so I repeat, the error has to be elsewhere in your query.

Is Cashflow Link a query or a table? In either event, if you run your select query without the dlookup - do you get the right number of records or do you styill get duplicates?

 

[jband]

It is a table. I checked for duplicates and I could not find any. Thank you for your help though. I will see what I can do from here