[AccessD] Table Join not working

Lambert,

For what I am trying to accomplish...dates are the key I must use.  If I 
limit myself to only 1 IIF statement in the query...it joins fine...it is 
when I use more than 1 IIF statement that it does not find matching records( 
the IIF does not evaluate, compare, or even reference the date field in 
question)...and they are all dates...no time included.

Thanks,

Mark

>From: "Heenan, Lambert" <Lambert.Heenan at aig.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: RE: [AccessD] Table Join not working
>Date: Mon, 10 May 2004 12:03:29 -0400
>
>I can't help thinking that its got to have something to do with the fact 
>you
>are joining tables on a date field. For obvious reasons fields used to join
>tables have to have exactly the same value in order to make the join.
>Typically we use AutoNumber fields for the task.
>
>Are you SURE that all the date fields you are using for the join only
>contain a date, and that none of them also have a time in the field? If you
>have two date values that are one second off then you won't achieve a join.
>
>Of all the "natural" keys you might choose, I'm inclined to think that 
>Dates
>are amongst the least suitable.
>
>Lambert
>
> > -----Original Message-----
> > From:	Mark A Matte [SMTP:markamatte at hotmail.com]
> > Sent:	Monday, May 10, 2004 11:23 AM
> > To:	accessd at databaseadvisors.com
> > Subject:	[AccessD] Table Join not working
> >
> > Hello All,
> >
> > In A97 I am building a number of queries from data that is date 
>oriented.
> >
> > Each query counts a different combination of data elements.  All of 
>these
> > queries then use an Outer join (Right Join) to join to a date query(on a
> > date field) that includes all dates even if there is no data in one of 
>the
> >
> > other queries.
> >
> > The problem I am having is 1 of the queries will not bring any rows back
> > when joined to the date query even though there are matching records.
> > Each
> > query has at least 1 expression, whether it be COUNT or the SUM of an 
>IIF
> > statement.  The problem query has 4 expressions(sum of IIF statements).
> > The
> > query executes fine...just not when I join to the date query.  If I 
>remove
> >
> > any 3 of these statements...the query joins fine.  It doesn't matter 
>which
> > 3
> > I remove...as long as there is only 1 remaining.
> >
> > If I create a table with the output of the problem query...the table 
>joins
> >
> > fine with the date query.
> >
> > Am I missing something...or does someone know of an issue with joining
> > queries when IIF statements are used.
> >
> > FYI...none of the IIF statements evaluate, compare, or even reference 
>the
> > date field in question...the query groups on the date and that is all.
> >
> > Any ideas/insight about the issue would be greatly appreciated.
> >
> > Thanks,
> >
> > Mark A. Matte
> >
> > _________________________________________________________________
> > Getting married? Find tips, tools and the latest trends at MSN Life
> > Events.
> > http://lifeevents.msn.com/category.aspx?cid=married
> >
> > --
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar – get it now! 
http://toolbar.msn.com/go/onm00200415ave/direct/01/