If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Query joining two tables with different dates
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/For...arted/201005/1 |
#12
|
|||
|
|||
Query joining two tables with different dates
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/For...arted/201005/1 |
#13
|
|||
|
|||
Query joining two tables with different dates
On Fri, 28 May 2010 14:36:20 GMT, "bscott via AccessMonster.com" u60332@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] |
|
Thread Tools | |
Display Modes | |
|
|