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

psycoperl

Board Regular
Joined
Oct 23, 2007
Messages
248
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
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.

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

thanks
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

thesuggs76

Board Regular
Joined
Nov 15, 2006
Messages
247
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
  1. 2013
Platform
  1. Windows
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:
Code:
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

Board Regular
Joined
Oct 23, 2007
Messages
248
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,500
Messages
5,523,288
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top