Hi, first post and I am hoping for some direction as I haven't been able to find anything related to this and think it could be a coding solution. Anyway, here we go...
I have a list that details the energy usage of a store throughout a given month, and it will be constant for each separate day of the week. So for example Store 1 will use a given amount of energy across 24 hours on Monday, and a different profile for Tuesday, and Wednesday. Those values will be consistent throughout the month, so for example the first Monday and third Monday of the month will have the same energy profile.
Now each store will have a new profile for each month. So keeping with Store example, I enter in M-Sun data for each month from Jan-Dec, tie that against a 365 day calendar table and I have a query created of my master list of the energy profile for all my stores for every day in the year (Query 1).
The issue becomes I have a holiday schedule that needs to be updated against Query 1. Each store will have a different energy profile on New Years Day, 4th of July, Thanksgiving, Christmas etc. I need to replace 6 days throughout the year with information I have from my holiday chart, and I'm trying to get it through another query because I have linked excel sheets to my tables and need the functionality of when the excel chart is updated the entire database is updated.
Long story short, is there any 'replace' query that I can use against an existing query to say 'if july 4th (holiday), replace existing data columns with this other table that has identical columns'? Again it needs to be a query that will automatically refresh (non-update query) once I change the linked excel sheets.
Much appreciated folks.
I have a list that details the energy usage of a store throughout a given month, and it will be constant for each separate day of the week. So for example Store 1 will use a given amount of energy across 24 hours on Monday, and a different profile for Tuesday, and Wednesday. Those values will be consistent throughout the month, so for example the first Monday and third Monday of the month will have the same energy profile.
Now each store will have a new profile for each month. So keeping with Store example, I enter in M-Sun data for each month from Jan-Dec, tie that against a 365 day calendar table and I have a query created of my master list of the energy profile for all my stores for every day in the year (Query 1).
The issue becomes I have a holiday schedule that needs to be updated against Query 1. Each store will have a different energy profile on New Years Day, 4th of July, Thanksgiving, Christmas etc. I need to replace 6 days throughout the year with information I have from my holiday chart, and I'm trying to get it through another query because I have linked excel sheets to my tables and need the functionality of when the excel chart is updated the entire database is updated.
Long story short, is there any 'replace' query that I can use against an existing query to say 'if july 4th (holiday), replace existing data columns with this other table that has identical columns'? Again it needs to be a query that will automatically refresh (non-update query) once I change the linked excel sheets.
Much appreciated folks.
