Updates vs daily logging

[uwlchemist]

I am managing an asset where multiple locations can have multiple pumps, each with an injection rate that can be changed at most daily. Currently the total injection rate is managed using a spreadsheet where all rates are updated daily whether they have changed or not. I want to change this to a database format where only rate adjustments are logged. So, my adjustment table looks something like this...

AdjustmentDate	Location_ID	Pump_ID	Chemical_ID	InjectionRate
1/1/14	1	1	1	10
1/1/14	1	2	1	15
1/5/14	1	1	1	5
1/7/14	1	1	1	12

Those daily updates need to be configured in a final query so that any given day outputs the correct total injection rate for that day...for each chemical at each location. The final query needs to look something like this...

Date	Location_ID	Chemical_ID	TotalInjectionRate
1/1/14	1	1	25
1/2/14	1	1	25
1/3/14	1	1	25
1/4/14	1	1	25
1/5/14	1	1	20
1/6/14	1	1	20
1/7/14	1	1	27

Thanks in advance for your help...I just can't seem to get it to work.

[June7]

There are no records in the raw data for 1/2/14, 1/3/14, 1/4/14, 1/6/14. Can't report data that doesn't exist.

Would need a dataset with a record for every possible date/location/chemical combination to accomplish this with queries alone. Even then I am not sure because of the daily rate calculation.

I don't really understand the calculation. If the records were only adjustments (+/-) then a RunningSum would calculate the rate and on 1/7/14 it would be 42. What I see is each record is added to just the previous record.

[uwlchemist]

I guess I wasn't quite clear with what I wanted. I have an additional table with other data that is daily...I did not show that data for simplicity, but it contains fluid flow data for every day (in this example all days 1-7). The current practice is to log the injection rate daily as well...although most days it doesn't change. I would like to create a table only for the updates, and then use a query to use the last known injection rate as the injection rate for that day. My example above may not have been good enough to make what I want clear...but if my starting rate is 17, and I change the rate to 21, I want all of the dates before I changed the rate to show a rate of 17, and all of the dates after I changed the rate to show 21...even though there are only the two entries in the injection rate table. Any clearer?

[June7]

The easiest way to do this is to enter the change in rate (+/-) each record. Example (the Daily column would be a calculated result):

AdjDate	LocID	PumpID	ChemID	RateChange	Daily
1/1/2014	1	1	1	10	10
1/1/2014	1	1	1	15	25
1/5/2014	1	1	1	-5	20
1/7/2014	1	1	1	7	27

Doing running sum in query and on forms is tricky, review http://support.microsoft.com/kb/290136.
Doing running sum in report is easier because textbox on report has a RunningSum property.

Getting a daily rate for each date/location/pump/chemical combination will require a dataset with a record for every possible combination. So if that other table also has those 4 fields, can join to the RateChange table by linking on the 4 common fields. Then the RunningSum can be calculated for every day.

However, that requires utilizing every record from day 1 in the calculation. Restricting the report output to a selected range requires calculating a balance forward from all the records prior to the range, usually with a DSum() expression in report Header section.

[uwlchemist]

Is there a better way to design the database to allow for only logging updates, instead of logging the rate daily? I need to be able to know the injection rate on any given day, and I need it to be able to sum the injection rates from different pumps at the same location. I feel this should be much easier than I am making it...but for some reason I just can't see the solution!

[June7]

As shown in the referenced link, running sum in query or form is tricky. But this is easily handled by report.


However, to return rate in effect on specified date, should be possible with:

SELECT Sum(RateChange) AS SumOfRateChange
FROM tablename
WHERE (((AdjDate)<=[enter date]));

That will return one record and does not consider location, pump, chemical. If you want separate values dependent on those elements, then use GROUP BY clause.

[uwlchemist]

OK, I have set up an experimental table using rate change instead of rate, but I am still not able to get a table showing injection rate per day. Actually, the sum function I entered sums all of the injection rate changes regardless of pump or chemical or location. I tried the DSum explained in the article you attached, but had similar difficulties in sums up categories I didn't expect it to. Finding a singular injection rate on any given day doesn't really help me as I need to look at 3-month segments and run deeper analysis on that data. I really need to be able to generate a table like the table below...but it has to be correct. The expression I used for injection rate is InjectionRate: DSum("InjectionRateChange","tblInjectionRateChange ","AdjustmentDate <= " & Chr(35) & [ProductionDate] & Chr(35))

ProductionDate	ProductionPlatform_ID	Product_ID	InjectionRate
1/18/2014	9	1
1/18/2014	10	1
1/19/2014	9	1	50
1/19/2014	10	1	50
1/20/2014	9	1	50
1/20/2014	10	1	50
1/21/2014	9	1	50
1/21/2014	10	1	50
1/22/2014	9	1	55
1/22/2014	10	1	55
1/23/2014	9	1	55
1/23/2014	10	1	55
1/24/2014	9	1	70
1/24/2014	10	1	70
1/25/2014	9	1	70
1/25/2014	10	1	70
1/26/2014	9	1	65
1/26/2014	10	1	65
1/27/2014	9	1	65
1/27/2014	10	1	65
1/28/2014	9	1	63
1/28/2014	10	1	63
1/29/2014	9	1	63
1/29/2014	10	1	63
1/30/2014	9	1	63
1/30/2014	10	1	63
1/31/2014	9	1	48
1/31/2014	10	1	48

[June7]

As noted in post 2, need a dataset of ALL possible dates to join to the log table in query.

Other alternative is VBA code writing records to a table. This could be a 'temp' table where only records needed for the process are created and then purged afterwards.

[uwlchemist]

Yes, I have a table with all possible dates. I need a way of calculating the total injection rate on that day. Ideally that would involve finding that latest injection rate change for each pump that is <= the specified date on the table, then summing all of the injection rates at that location. I have a table that contains production data for every single day. I have the appropriate relationships set up to link the production data with the location with the chemical with the pump with the injection rate (whew!)...but I can't quite fill in the gaps. Would an example database help? I still think this should be a relatively common practice...I think I am not explaining myself clearly!

[June7]

Posting db can't hurt.

[uwlchemist]

Sample Database.zip

Sample database with very generic data. Please let me know if this helps!

[June7]

AdjustmentDate and DataDate fields should be date/time type, not text.

Consider:

Query1 - AllDatesPumps
SELECT DISTINCT tblData.DataDate, tblPump.Pump_ID FROM tblData, tblPump;

Query2 - AllDatesPumpsChanges
SELECT AllDatesPumps.DataDate, AllDatesPumps.Pump_ID, Nz([InjectionRateChange],0) AS Change
FROM tblInjectionRate RIGHT JOIN AllDatesPumps ON (tblInjectionRate.Pump_ID = AllDatesPumps.Pump_ID) AND (tblInjectionRate.AdjustmentDate = AllDatesPumps.DataDate);

Query3 - DailyRates
SELECT AllDatesPumpsChanges.Pump_ID, AllDatesPumpsChanges.DataDate, DSum("Change","AllDatesPumpsChanges","DataDate<=#" & [DataDate] & "# AND Pump_ID=" & [Pump_ID]) AS Rate
FROM AllDatesPumpsChanges
ORDER BY AllDatesPumpsChanges.Pump_ID, AllDatesPumpsChanges.DataDate;

[uwlchemist]

Hello again, and thanks for the help again.

Yes, I forgot to change the data types on those fields...just threw it together quickly.

When I set up the queries you suggested, I get the following table (filtered just for pump#1)...

Pump_ID	DataDate	Rate
1	1/1/2014	60
1	1/2/2014	60
1	1/3/2014	60
1	1/4/2014	40
1	1/5/2014	40
1	1/6/2014	40

However, the starting injection rate for pump one was 15, with a change on the 4th of -5, so it should have changed to 10...where are the 60 and 40 coming from? Why is everything multiplied by 4?

[uwlchemist]

OK, nevermind, I figured it out. Needed to use "Group By" in Query number 2...now everything comes out right. Let me apply it to my real database, and see if I can make it work there. Thanks a lot!

[uwlchemist]

OK, seems to be working, but WOW is it slow. Oh well, I guess you can't have everything. I am just afraid it is going to be impossibly slow as the database gets bigger...anyway, one more question.

I would still like to have the injection rate adjustments be in terms of the current injection rate, not the change in injection rate. I can easily calculate the change in injection rate on the data input form, but I am wondering if a query might not be the better way to do it. The question is how do I set up this query? I appreciate your help. Sometimes I think I am OK at this stuff...then the simplest things trip me up. Anyway, Thanks for all of your help.