Calculate between different records

[Tskutnik]

I’m sure this is silly for some of you, but I’m making the slow transition from the Access UI to writing SQL and I need help with the following concept.
I need to do calculations on data that looks like this: 4 distinct records with the same value in different periods:
Date Value
1/31/16 1,000
2/28/16 1,200
3/31/16 1,400
4/30/16 1,450
The SQL statement must calculate between the periods chosen, meaning query 1 may want to find the different between Jan and March (400), and query 2 may request the difference between Jan and April (450). The UI would have the start and end dates as parameters in the query.
Sorry for such a basic question, but once I get this concept a lot of other things fall into place.
I really appreciate the help. Thanks in advance.

 

[sneuberg]

In the query builder you can add the same table or query more than once and though I rarely do that in this case it seems to make this problem easy. Please check out the form and query in the attached database and see if that type of setup would work for you.

 

[Tskutnik]

Interesting idea. Let me check that when I get back to my laptop but i get you point from your description.
The database I'm working with will have a LOT of data and speed is an issue. Is this type of approach any faster/ slower than others? You said you don't normally use this approach and I'm not sure if it because it's inefficient.
This feels like the type of problem that is commonly solved, but again, I'm new...
Thanks.

 

[sneuberg]

Usually when I do this I use a subquery or DLookup. I know these, like anytime you put a query within a query, can get slow with a lot of records. I don't know how this two table method I suggested performs in comparison. When I get a little more time I'll do this the DLookup way, compare the speed with this two table way and let you know what the difference is.

Edit: On the other hand I don't think I will test this. Since this only involves the lookup of two records it would be difficult to come up with any method that would be slow.

 

[Tskutnik]

Thanks again. Some of my queries will be much more complex and involve more data so I'll check both ideas and see.
I really appreciate the help.
If anyone else has any alternate ideas I'd love to get them and compare.
Much appreciated.

 

[Tskutnik]

The file you included is only downloading as PHP even though the file name has an Access DB extension. Any thoughts?

 

[Galaxiom]

Usually when I do this I use a subquery or DLookup. I know these, like anytime you put a query within a query, can get slow with a lot of records. I don't know how this two table method I suggested performs in comparison.

I would expect the self join to perform better than a query with an embedded DLookup because it would have to keep returning to Access to execute the DLookup.

Best queries stay entirely in SQL so the engine can handle them from start to end.

The embedded DLookup can be handy if you want to get around the problem of non-updateable queries but are usually best avoided.

 

[sneuberg]

The file you included is only downloading as PHP even though the file name has an Access DB extension. Any thoughts?

I put it in a zip file. Maybe that will get through.

 

[Galaxiom]

The file you included is only downloading as PHP even though the file name has an Access DB extension. Any thoughts?

It is a glitch with IE on this site. You can just change the extension to what it should be after downloading.

 

[Tskutnik]

All, Thanks for the input but it is not quite what I need. Sorry if I was not specific enough and I see how my example was confusing.

Your example (thank you BTW) is for a user entering the from and to dates on a form, and those dates controlling the records selected. I need more of a automatic calculation in a field in a query, shown in a dedicated column.

Other wrinkles: The dates will not be logical. They could be any number of days apart from each other and not always on month ends, AND there are multiple accounts in the table.

If this helps: the result would be in the 3rd "Difference" column, always calculating vs. the prior date in the table.

Account Date Amount Difference
123 1/31/16 1,000
123 2/28/16 1,200 200
123 3/31/16 1,400 200
123 4/30/16 1,450 50
234 1/31/16 2,000
234 2/15/16 2,250 250
234 2/18/16 2,150 -100
234 3/31/16 2,400 250
234 4/30/16 2,425 25

I'm hoping there is a formula to be put in the Difference column that can produce this result.

Thanks again for any help.

 

[sneuberg]

You can do that with a subquery like

SELECT Table1.Account, Table1.TransDate, Table1.TransValue, [Table1].[TransValue]-(SELECT TOP 1 Dupe.TransValue
   FROM Table1 AS Dupe
   WHERE Dupe.Account = Table1.Account
     AND Dupe.TransDate < Table1.TransDate
   ORDER BY Dupe.TransDate DESC) AS Difference
FROM Table1;

which is demonstrated in the attached database. The model for this subquery was taken from this site http://www.allenbrowne.com/subquery-01.html#TopN in the section "Get the value in another record". The difference between the subquery in that site and the one above is the one above has nothing to distinguish ties, i.e., dates that are the same. So if the same dates occur within the same account I suggest you modify the subquery to that shown in the referenced WEB site.

Recall that if you download the database you may have to change the extension from PHP to ACCDB

 

[Tskutnik]

Thanks again for your help. There is still something (probably small) that I cant get. I attached my DB - if you could look at the results of your query you will see. I changed the field and table names form your query but everything else is the same.
I really appreciate your time and help.

 

[sneuberg]

The difference is in the data. The data in the Table2 has duplicate dates for the same account. An order is need to do this. If the date are the same something else is needed to decide the order within the same date. I put in ID in the query that follows and the query runs (very slow) but I doubt if this is what you want. Do you want the data summed first by each date? Please show me some sample data with the duplicate dates and the result you want for an output.

SELECT Table2.Account_ID, Table2.Position_Date, Table2.ID, Table2.Position_Value, [Table2].[Position_Value]-(SELECT TOP 1 Dupe.Position_Value    FROM Table2 AS Dupe    WHERE Dupe.Account_ID = Table2.Account_ID      AND Dupe.Position_Date < Table2.Position_Date    ORDER BY Dupe.Position_Date DESC, Dupe.ID) AS Difference
FROM Table2
ORDER BY Table2.Account_ID, Table2.Position_Date, Table2.ID;

 

[Tskutnik]

I think you are right, the query you suggested might not work.
The table will not necessarily have the records in an order, so if that is a requirement this may be tricky.
I attached the database with sample calculations in 4 records in the table so you can see what the math should be.
Basically you find the current date's value for a given records' account/ security combination and the day prior value for the same account/security combination and subtract them.

 

[Tskutnik]

I think you suggested a dlookup - if I create a unique identifier for the record (the concatenated Account_ID & Security_ID then maybe the dlookup would search for the date

 

[sneuberg]

Please take a look a the query in the attached database. I added the Security_ID to the subquery and that seems to resolve the problem. It appears that that the combination of Account_ID, Security_ID and Position_Date is unique so this works. I've include the Security_ID in the output and sorted the output by Account_ID, Security_ID and Position_Date so that you can see how it works.

This query takes about one minute to run. You will see that when you click on the last record arrow. I'll play with this and see if I can speed this up tomorrow. Bedtime now.

 

[sneuberg]

If you put indexes on Account_ID, Security_ID and Position_Date the time drops to five to ten seconds.

 

[sneuberg]

I've attached a database with a version of the query (qryDLUDifference) that uses DMax and DLookup which is faster than the subquery version. Without indexes it takes about 4 seconds to run and less than one second with indexes. The attached database doesn't have the indexes.

The DMAX expression [PD] gets the date from the previous record of the same Account_ID and Security_ID. The DLookup expression [PV] gets the Position_Value from the previous record by using the [PD], Account_ID and Security_ID in the criteria. The Difference is just the Position_Value - [PV].

I've left PD and PV checked in the query just so you could see what they were doing. These could be consolidate into one expression but I'd leave it like it is as it would be easier to maintain; just uncheck them so they don't appear. I put a format on difference that you should take off as it may not be want you want and should be in the report.

 

[The_Doc_Man]

Here is your problem: You are comparing adjacent records using SQL, but there ARE no adjacent records in a simple query - because SQL is based on the idea that you do what you do AS THOUGH everything was done at once. I.e. you are working on a SET of things at once, not one record at a time. The mere fact that your system does not do parallel processing at that level has nothing to do with the behavior of the query.

The question is, how big are these tables? The "pure" SQL approach incurs a LOT of overhead. The reason that having a DLOOKUP in a query is so bad is that if you have N records, you suddenly have N+1 SQL queries because DLOOKUP synthesizes an independent query for you when you execute it - plus the query that contained the DLOOKUP as part of what it does.

Remember that without a query to impose an order on the records, records have no predictable order. This is a case where the SIMPLEST approach in concept is to open a recordset to a query that sorts the dataset by dates, providing a platform for which you can write a loop that looks at the first record, holds aside the value, and then loops through the recordset where it looks at the next value, computes the difference between the new value and the set-aside value, stores that in the record, and then holds aside the new value. You would do this in VBA, not SQL.

 

[sneuberg]

Remember that without a query to impose an order on the records, records have no predictable order. This is a case where the SIMPLEST approach in concept is to open a recordset to a query that sorts the dataset by dates, providing a platform for which you can write a loop that looks at the first record, holds aside the value, and then loops through the recordset where it looks at the next value, computes the difference between the new value and the set-aside value, stores that in the record, and then holds aside the new value. You would do this in VBA, not SQL.

I've done this sort of thing through VBA and it's much faster than the query route. But the drawback has alway been that I've needed a temporary table for the output and have to deal with the potential bloat. When the number of records is low enough that the query execution time isn't a problem why not use a query?

It would be really neat if you could use VBA and recordsets and have a result that acted just like a query.