Doing a calculation once and having it display on all rows in a query

[psycoperl]

Good Evening,
I am working on a query that i will be using for exporting data from my database to be used in an external system. I have a function that I am calling in my query CalculateRelease() which takes the latest date in the Periods Column and adds a fixed number. Since there are 4000+ records in my query, it is calculating the value on each record it is running the same function and returning the exact same value on ALL the records, i was wondering if there was a way to have the value calculated once and just put into every row, saving long calculation periods.

SELECT DISTINCT  CourseNumberWithCrosslist AS [School Course Number], [Course Name],  
[Instructor ID], [Start Date], [End Date], CalculateRelease("Stu") AS [Release Date]
FROM _tblRegistrationInfo_SR00034 ;

thanks

 

[thesuggs76]

Can you not turn it into a make table query. It will create a table with the figures and any other querie can then be based on the table

 

[xenou]

Hi,
This might depend on the details of CalculateRelease(). Probably yes is the answer (using static variables and/or a dictionary style data structure).

If the answer is always the same you could also use a join/relation approach (with a lookup table) rather than a function approach:

SELECT 
    x, y, z, 
    (SELECT Release_Date FROM Table2 WHERE ReleaseType = 'Stu') As [Release Date] 
FROM 
    tblRegistrationInfo_SR00034

If you are communicating with other data systems I would avoid using spaces in any table or field names. Access allows spaces in names but most databases do not: Letters, Numbers and underscores are the only safe characters (and always starting with a letter, not a number, as the first character).

 

[psycoperl]

Thank you all for your advise.

Xenou - We have to export the data to match their specificiation in a CSV. The spacing in their name is actually the vendors system.

After some work this weekend, I took thesuggs76's recommendation and am using VBA to load the data into a query and just pluggin the calcuaterelease() info as a variable that is ran once at the start of the proceedure.

Which is fortunate when i got handed another part of this project and see that i can make use of this table for other aspects.