[AccessD] Table Join not working

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