Compare two date into the same table

[Alex_Firenze]

Hi all,
Please, help me to find the right query....
I would like to compare, within the same table, the figures related to
a reference date and the previous one...

Example, My table:
referencedate;location;class;npeople
01/01/2005;london;math;100
01/01/2005;london;finance;80
01/01/2005;london;statistics;50
01/01/2005;brighton;statistics;15
01/02/2005;london;math;90
01/02/2005;london;finance;85
01/02/2005;london;statistics;40
01/02/2005;brighton;statistics;12
01/02/2005;brighton;math;15

At the moment I am not able to manage the new entrances.. Indeed my
result is:

pres.referencedate; pres_1.referencedate;location;class;diff_people
01/02/2005;01/01/2005;london;math;-10
01/02/2005;01/01/2005;london;finance;5
01/02/2005;01/01/2005;london;statistics;-10
01/02/2005;01/01/2005;brighton;statistics;-3

But I would expect also the line:
01/02/2005;01/01/2005;brighton;math;15

How can I take into account new Classes or Locations that are appear in
the reference date but not in the previous??

My query:

SELECT pres.referencedate, pres_1.referencedate, pres.location,
pres.class, [pres].[npeople]-[pres_1].[npeople] AS diff_people
FROM pres LEFT JOIN pres AS pres_1 ON (pres.class = pres_1.class) AND
(pres.location = pres_1.location)
WHERE (((pres.referencedate)=#2/1/2005#) AND
((pres_1.referencedate)=#1/1/2005#));

Thank you in advance

Alex

 

[Michel Walsh]

Hi


SELECT pres.referencedate,
pres_1.referencedate,
pres.location,
pres.class,
pres.npeople-Nz(pres_1.npeople, 0) AS diff_people
FROM pres LEFT JOIN pres AS pres_1
ON (pres.class = pres_1.class)
AND
(pres.location = pres_1.location)
WHERE pres.referencedate=#2/1/2005#
AND
Nz(pres_1.referencedate,#1/1/2005# )=#1/1/2005#;





When you use an outer join, and have the unpreserved table, here pres_1, in
the WHERE clause, keep in mind that due to the nature of the outer join,
that table CAN SUPPLY NULL values. Remove the WHERE clause to convince
yourself. If you write the WHERE clause without handling these NULL, you
kill the outer join effect, since the WHERE is likely to remove these
records with a NULL.




Hoping it may help,
Vanderghast, Access MVP

 

[Alex_Firenze]

Hi Michael,
I think you are right. It is something related to the fact that in my
query I didn' take into account Null values in the join. (by the way,
now I know the NZ function)

However, the query you advice me is not working...
If I remove the WHERE condition on the second date and I display the NZ
result:

SELECT
pres_1.referencedate,
pres.location,
pres.class,
pres.npeople,
Val(Nz([pres_1].[npeople],0)) AS nz_people,
CDate(Nz([pres_1].[referencedate],#1/1/2005#)) AS nzdate
FROM
pres LEFT JOIN pres AS pres_1 ON (pres.class = pres_1.class) AND
(pres.location = pres_1.location)
WHERE (((pres.referencedate)=#2/1/2005#));

I get this result:

referencedate location class npeople nz_people nzdate
01/02/2005 london math 90 90 1/2/2005
01/01/2005 london math 90 100 1/1/2005
01/02/2005 london finance 85 85 1/2/2005
01/01/2005 london finance 85 80 1/1/2005
01/02/2005 london statistics 40 40 1/2/2005
01/01/2005 london statistics 40 50 1/1/2005
01/02/2005 brighton statistics 12 12 1/2/2005
01/01/2005 brighton statistics 12 15 1/1/2005
01/02/2005 brighton math 15 15 1/2/2005

If i insert now the condition nzdate=#01/01/2005# I get the same stuff
as before... Brighton-Math-15 desappear...
So, it seems that it is not possible to do it in a "clean
way"...Probably I should first build a temporary table with the result
at 01/01/2005 and then run the query with an outer join...

Any idea?

 

[Michel Walsh]

Hi,


Can you check the WHERE clause when you insert the condition (to a computed
alias). It should read



.... WHERE .... CDate(Nz([pres_1].[referencedate],#1/1/2005#)) = #1/1/2005#
.....



I suspect it has somehow been written like:


.... WHERE ... [pres_1].[referencedate] = #1/1/2005# ...



which makes your elusive record to not be kept in the result.


Hoping it may help,
Vanderghast, Access MVP