Planning out a query pulling data from multiple tables, and would like to clarify how to do a few things on/with it.
did post this same inquiry in another forum at accessforums.net/queries/complex-query-criteria-formulas-clarification-57561.html, but haven't received any responses so figured I'd try here instead...
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!
did post this same inquiry in another forum at accessforums.net/queries/complex-query-criteria-formulas-clarification-57561.html, but haven't received any responses so figured I'd try here instead...
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!
