Complex query criteria & formulas clarification

[sivante]

Planning out a query pulling data from multiple tables, and would like to clarify how to do a few things on/with it...






1. Need to calculate a number of metrics' last 3 days' averages from a 'Daily Records' table for a large number of ads. Thinking the best way to do this would be to include fields from that table for Ad # and each desired metric - putting criteria under each metric's field as "between Date()-1 and Date()-3" and adding 'Total: Average' under each. Would this work - or might there also be another way?




2. Will also be pulling data from a "Cycle Performance" table - which results from this query will also be appended to...


A - how can it be arranged to have all of the appended rows to insert new autonumbers in the "Cycle Performance" table (for something like a "cycle record ID)? Surely, the table would require the field as the Primary Key with an autonumber setting - though would there need to be any particular settings on the query to update that autonumber, or would it just do automatically for the appended records?


B - Need to pull a couple specific figures from an ROI and Profit field for the last two cycles of data into the query that was appended and not sure of the best way to do this...


In addition to the "cycle record ID," it'd make sense to also have a "cycle ID" - and hence a separate table with CycleID as the Primary Key, and a date range field for the cycle period. There may also be another layer of complexity, splitting ads into three cycle groups...


While the query might be run daily, it'd be set to pull up different records each day based on the criteria of "Date()" in the CycleGroup field. Also guessing would use the "Date()" function in the criteria of the "CycleID" field, which would then generate the CycleID for all records appended to the "Cycle Performance" table...


Now - what formula would be needed for the "Last/2nd Last ROI/Profit" calculated fields, so they're pulling values from the Cycle Performance tables' ROI and Profit fields, from the last and second last cycles for that cycle group?


(Guessing "CalculatedFieldName: [tablename].[ROI/ProfitField" --- though what would the structure be of the formula in the criteria for that field?)


(Alternatively, it might be possible to run a separate Update Query each cycle after the Append Query has run and been acted upon - updating a "Last / 2nd Last ROI / Profit" field for each individual ad in the Ads table, and then pull those values from the Ads table instead of trying to reference back to values from previous cycles in the Cycle Performance table. However, it's be preferable to run it all from the one Append Query, with the calculated fields)




3. Somewhat similar to the last question, or perhaps a combination of both - what would the formula be to reference a value from a separate table, specified by another field's relative value, in a calculated field? That question may not be 100% clear, so I shall provide the example to clarify...


There will also be a "Scale Level" field, with an IIF function in the calculated field formula... iif condition met, [scale level value from the last cycle#+1]..."


How would you structure the part of the formula that is to essentially say "the value in the Scale Level field from the Cycle Performance table for the last cycle, plus one?"




4. Lastly - this question may have had its components answered in the variations of the last questions, though would be great to clarify through all perspectives...


There will also be a "Budget Level at Peak ROI" field to pull its data from the Cycle Performance chart...


The figure it'd need to pull is from a "Budget Level" field, from the row where the ROI field is at its maximum - a combination of the "Max()" aggregate function referencing the the ROI field, pulling the figure from Budget Level field in that corresponding row.


What would the formula be for such a function, in the calculated field - would it be a calculated field "Name: [CyclePerformanceTable].[BudgetLevel]" with the criteria as "Max([CyclePerformanceTable].[ROI])"?


(I've thought about the possibility of including a "Peak ROI" and "Budget Level at Peak ROI" fields in the Cycle Performance chart - then using calculated fields in the query such as "iff ROI>peak ROI, [Budget Level], [Budget Level at Peak ROI]" - though curious as to how it's be achieved with the single complex formula.)




Thank you! 🙏🙏🙏

[ranman256]

Use DATEDIFF to calculate elapsed time.

you can name an autoNum anything you want, but you can only have 1 in a table. (And not sometimes)
you can't do calculations in a table,(and you wouldn't), so do it in a query.

[sivante]

Use DATEDIFF to calculate elapsed time.

you can name an autoNum anything you want, but you can only have 1 in a table. (And not sometimes)
you can't do calculations in a table,(and you wouldn't), so do it in a query.

thanks for taking the time to answer, though that doesn't really speak to any of my questions...

[sivante]

crossposted at http://www.access-programmers.co.uk/...00#post1471300 since no answers here yet

[June7]

1. "under each metric's field" sounds suspiciously like non-normalized data structure - if so I doubt your query idea will work but try it and let is know

2. what do you mean by 'insert new autonumbers' - autonumber type field automatically generates numbers - no code required

3. if tables have relationship, join them in query so all related info is available or use domain aggregate functions

4. calculated field in table cannot pull data from another table - build queries - such as an aggregate (GROUP BY) Totals query to summarize data then can join that query to table or other queries

[sivante]

1. pardon my novice - I will ensure the data structure is normalized, though perhaps may have not communicated that well as I’m a newbie with database language and was trying to keep simple without overloading with too much detail of the table structure, focusing on the query part

1. no worries. think that one’s straightforward and probably should have kept out of the post as it’s a more basic question

1. I get that it will be required to create the necessary relationships between tables in the query, though am just not sure on how to structure the complex formula - the main piece I’m unclear on which is essentially the criteria code for “the last entry…”

though in phrasing it like that - seeing a potential solution of adding a field in the query to pull the Scale Level from the last cycle - with that field’s criteria as the max CycleRecordID to specifiy the last cycle - and then a separate calculated field (referencing that “Last Scale Level” field) to generate the new Scale Level.

1. yes, that’s understood. the calculated fields would be in the query. i thought that was clear based on the post title “Complex query criteria"

[June7]

If you need to pull some value from record you consider the 'last' or most recent, this would involve a TOP N parameter or a complex domain aggregate expression. Review http://allenbrowne.com/subquery-01.html

[sivante]

Cool. I haven't come across anything on this function/topic yet, and it definitely looks like will be of value.

Thank you! :-)