Re: Compare two date into the same table

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?

.

Relevant Pages

  • Re: List Box Display Anomaly
    ... Michael, since it works fine on one machine but not on the other, you are looking to pin down the difference. ... There were some bugs introduced by SP3 for Office 2003, including combo display problems: ... All of a sudden, the columns from one of the tables is not displaying in the list box, but if I run the query in the query window, all the columns return data. ... I delivered a new front-end to a client this morning and he alerted me the anomaly. ...
    (microsoft.public.access.forms)
  • RE: calculated member question -- why (null)?
    ... why would a calculated member with a constant value ... "Michael D." ... ROWS FROM [Adventure Works] ... (My actual query is still slightly ...
    (microsoft.public.sqlserver.olap)
  • Re: equation in form wont equal
    ... query using dlookup- though I read somewhere to stay away from dlookups... ... "Michael J. Strickland" wrote: ... Prev by Date: ...
    (microsoft.public.access.formscoding)
  • Re: concatenating problem when hyphenated first name
    ... And it's neat that you can enlist Access to help you understand what's happening, like the calculated query field giving the character count, or using TypeNameor the Immediate Window ... Allen Browne - Microsoft MVP. ... >> Paste the SQL statement here. ... Michael" without erasing the first Michael. ...
    (comp.databases.ms-access)
  • Re: Slow during Inserts
    ... Do you have any indexes or primary keys defined on these tables at all? ... your query performance will degrade considerably without ... Michael C#, MCDBA ... > FETCH NEXT FROM state_cursor ...
    (microsoft.public.sqlserver.server)