July 14, 2010 at 8:27 am
Eugene Elutin (7/14/2010)
george.greiner (7/14/2010)
...I have been able to filter the A row but all subsequent lines pull all the records as they do not have a "DateFinished" column. How do I go about pulling those? Even if I drop the FileInfo records into a temp table I am still going to get ALL of the other records in the other tables.
What you meen by "subsequent lines pull all the records as they do not have a "DateFinished" column"? Each of your SELECT query select from FileInfo table, you said that DateFinished is a column of FileInfo. So just use it.
Something along the line:
SELECT [All Required Columns]
INTO #ReqFileInfo
FROM FileInfo
WHERE DateFinished BETWEEN @PeriodStart AND @PeriodEnd
Then use #ReqFileInfo instead of FileInfo in every query of your UNION query (CTE).
Ah I see how it works now! I was under the assumption that even if that Column existed in the FileInfo table and I was pulling from it that it would not ONLY pull the correct records because of the union. It works great! Thank you for your help as this takes seconds as opposed to many minutes in Access.
July 15, 2010 at 2:58 pm
Ah I see how it works now! I was under the assumption that even if that Column existed in the FileInfo table and I was pulling from it that it would not ONLY pull the correct records because of the union. It works great! Thank you for your help as this takes seconds as opposed to many minutes in Access.
It's been quite a while since I used Access but I suspect if you applied a similar approach in Access you'd see a significant performance increase over your original implementation.
You used to be able to write queries in access in a SQL-like syntax. You'd just need to store your data to an interim target table. To simplify your UNION ALL block, start with an insert from the "master" table and then, for the subsequent insert with the UNION ALLs, join back to this interim table instead of the original master table. If your interim table is appropriately indexed and/or the original master table is very large and/or active this could be more performant than going back to master repeatedly.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply