Mark A Matte
markamatte at hotmail.com
Mon May 10 12:19:54 CDT 2004
Patti, Your first scenario is the applicable one. They are all saved queries. The only difference in the queries is number of expressions in each. Even the query in question will work in the join if I remove the extra IIF statements...and it doesn't matter which ones I remove...as long as I only leave one. Before the join...the query runs fine with output as expected. The SQL below might help in painting the picture: ***If this is a saved query...and it is joined to the saved date query...there are no matching records ******************************** SELECT tblOneSource.closed_date, Sum(IIf([rc_status]="cls",1,0)) AS ClosedSaved, Sum(IIf([rc_status]="cll",1,0)) AS ClosedLost FROM tblOneSource INNER JOIN tblWorker AS tblWorker_1 ON tblOneSource.CaseRowAdded = tblWorker_1.oprid WHERE (((tblOneSource.rc_status) Like "c*")) GROUP BY tblOneSource.closed_date, tblOneSource.case_type, tblWorker_1.deptid HAVING (((tblOneSource.case_type)="md") AND ((tblWorker_1.deptid)="OS")); **************** ***If this is a saved query...and it is joined to the saved date query...there are matching records *************** SELECT tblOneSource.closed_date, Sum(IIf([rc_status]="cls",1,0)) AS ClosedSaved FROM tblOneSource INNER JOIN tblWorker AS tblWorker_1 ON tblOneSource.CaseRowAdded = tblWorker_1.oprid WHERE (((tblOneSource.rc_status) Like "c*")) GROUP BY tblOneSource.closed_date, tblOneSource.case_type, tblWorker_1.deptid HAVING (((tblOneSource.case_type)="md") AND ((tblWorker_1.deptid)="OS")); *************** I can remove either of the IIF statements...and the query will join fine...any ideas? Thanks, Mark >From: "O'Connor, Patricia " <Patricia.O'Connor at dfa.state.ny.us> >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 11:47:30 -0400 > >Mark > >Is the date query you are talking about a saved query that you bring in >along with the calculate query into a new 3rd query? > SavedDateQuery -> SavedCalcquery > >OR are you trying to do the date query while doing the calculation >query. Sql and specially Access gets confused sometimes on what it is >trying to pull. > >I have found that creating intermediate temporary tables have been the >only way to accomplish some routines. The other is using parenthesis to >force one to be done before the other. > >Patti > >****************************************************************** >*Patricia O'Connor >*Associate Computer Programmer Analyst >*OTDA - BDMA >*(W) mailto:Patricia.O'Connor at dfa.state.ny.us >*(w) mailto:aa1160 at dfa.state.ny.us >****************************************************************** > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > > Mark A Matte > > 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/