Query joining two tables with different dates
in [Getting Started]
|
From: bscott via AccessMonster.com on 28 May 2010 10:36 I'm working on giving that a try right now Ken. While I'm working on this, I thought that I would double check something with you. When I try to run the JOIN qry it says that there is a syntax error at "FROM". When I remove the comma from the end of "EndDate" the error goes away. Was this the correct thing to do without messing up the qry? KenSheridan wrote: >I think its also worth giving the second method a try. You'll probably find >it considerably faster than the first with the large number of rows you are >dealing with. Have you tried it? > >Even better would be to use a JOIN in Q1 rather than a subquery: > >SELECT SS1.row_date, >NZ(MIN(SS2.row_date),#2099-01-01#)-1 As EndDate, >FROM tbl_ScorecardStep1 AS SS1 LEFT JOIN >tbl_ScorecardStep1 AS SS2 >ON SS1.rowdate < SS2.row_date >GROUP BY SS1.row_date; > >BTW if you open this query independently you may well find that the EndDate >column is returned as a number, not a date. Don't worry, it's just the >underlying number as which the date/time data type is implemented in Access. >Normally this query would not be opened of course as its only purpose is to >return a result table to be joined to tbl_Monitors_ScorecardStep3 in the >final query. > >Ken Sheridan >Stafford, England > >>I figured out why it wasn't matching all of them and it was on my end. So >>looks like it works as should! Thanks! If you are aware of a way to further >[quoted text clipped - 6 lines] >>>>>>>corrected that and am about 30 minutes into the query running. I will post >>>>>>>how successful I am. Thanks again! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1
From: KenSheridan via AccessMonster.com on 28 May 2010 11:51 Mea culpa! That was just a typo on my part when adapting the SQL statement from a query on one of my own tables. Ken Sheridan Stafford, England bscott wrote: >I'm working on giving that a try right now Ken. While I'm working on this, I >thought that I would double check something with you. When I try to run the >JOIN qry it says that there is a syntax error at "FROM". When I remove the >comma from the end of "EndDate" the error goes away. Was this the correct >thing to do without messing up the qry? > >>I think its also worth giving the second method a try. You'll probably find >>it considerably faster than the first with the large number of rows you are >[quoted text clipped - 24 lines] >>>>>>>>corrected that and am about 30 minutes into the query running. I will post >>>>>>>>how successful I am. Thanks again! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1
From: John W. Vinson on 28 May 2010 13:36
On Fri, 28 May 2010 14:36:20 GMT, "bscott via AccessMonster.com" <u60332(a)uwe> wrote: >I'm working on giving that a try right now Ken. While I'm working on this, I >thought that I would double check something with you. When I try to run the >JOIN qry it says that there is a syntax error at "FROM". When I remove the >comma from the end of "EndDate" the error goes away. Was this the correct >thing to do without messing up the qry? > >KenSheridan wrote: >>I think its also worth giving the second method a try. You'll probably find >>it considerably faster than the first with the large number of rows you are >>dealing with. Have you tried it? >> >>Even better would be to use a JOIN in Q1 rather than a subquery: >> >>SELECT SS1.row_date, >>NZ(MIN(SS2.row_date),#2099-01-01#)-1 As EndDate, >>FROM tbl_ScorecardStep1 AS SS1 LEFT JOIN >>tbl_ScorecardStep1 AS SS2 >>ON SS1.rowdate < SS2.row_date >>GROUP BY SS1.row_date; PMFJI but... yes, removing the comma was the right thing to do, and will not mess up your query. -- John W. Vinson [MVP] |