Help Please - how to compare two records? (1 Viewer)

[Niroth]

So I have a table:

table1: company_name, year_month, company_rating, rating_change

For each company, I would like to compare the rating from 201201 and 201202 (and keep going for each month) and if there's a change in the rating, update rating_change to the difference in rating values.

The company_rating is a value from 1 to 20.

How should I go about doing that? Thanks.

 

[plog]

First, you shouldn't do it as you have proprosed. You don't store calculated values in a database--instead when you need them, you calculate them. You would build a query to determine the ranking difference between one month and a next.

Second, instead of a company_name field your table should have a numeric company_id field that links to a company table.

Third your year_month field should be converted to a date field. Because you want to essentially do subtraction/addition to that field (determine previous/next month) you need it in a datatype that easily allows that. Your current format doesn't. It particularly fails when trying to find the month prior to January (201201 - 1 = 201200). So you need to put it into a date datatype which will allow you to use the DateAdd function (http://www.techonthenet.com/access/functions/date/dateadd.php) to do math on your date.

Make those changes to your database and getting the information you want becomes easier.

 

[The_Doc_Man]

There is a theoretical problem that makes your goal tricky.

Technically, a query should not be able to do this (by itself) very easily anyway. You see, SQL queries are based on set theory, for which all actions are treated (and defined) as though they are done simultaneously. In such a context, there is no "previous" or "next" record for comparison anyway.

If you build a sorted recordset by taking your data and including an ORDER BY clause, you could then open the recordset in a VBA module and use the .MoveFirst, .MoveNext methods on that recordset to step through the records in order. In VBA, you in essence "throttle" the actions of the query processor to only show you records when you ask for them. Therefore, you can capture two successive records and compare them as two different entities. Any other time, you are skating on thin ice in trying to generate a query that would do something this complex.

I also agree with plog that you should never compute and store this sort of thing in a table. Having said that, it would make sense to do it this way if there is no other reasonable way to capture and store the data.

As an observation, if you have your company ID or name in the table, that would be one key. The date is another key. For this table, you should have a compound primary key that includes both Date and Company as the PK. Doing so would not lead to a normalization violation as long as you were careful.

 

[Niroth]

Thanks both. I understand that it's not ideal, but I'm only dealing with the data for my research, it's not a long term database.

First, you shouldn't do it as you have proprosed. You don't store calculated values in a database--instead when you need them, you calculate them. You would build a query to determine the ranking difference between one month and a next.

Second, instead of a company_name field your table should have a numeric company_id field that links to a company table.

Third your year_month field should be converted to a date field. Because you want to essentially do subtraction/addition to that field (determine previous/next month) you need it in a datatype that easily allows that. Your current format doesn't. It particularly fails when trying to find the month prior to January (201201 - 1 = 201200). So you need to put it into a date datatype which will allow you to use the DateAdd function (http://www.techonthenet.com/access/functions/date/dateadd.php) to do math on your date.

Make those changes to your database and getting the information you want becomes easier.

After I make all those changes, how can I get the information I wanted? Can you elaborate on how I can build that query?

 

[CJ_London]

your query would look something like this psuedo code:

SELECT YearMonth, Rating, (SELECT TOP 1 Rating From myTable AS TMP WHERE CompanyID=myTable.CompanyID AND YearMonth=DateAdd("m",-1,myTable.YearMonth)) AS Previous
FROM myTable 
ORDER By CompanyID, YearMonth

There are assumptions that you have taken the previous advice

 

[plog]

First let's assume you've changed the [year_month] field to a date field called [ReportDate]. Once you've done that and my other suggestions I would create a query based on your table. It would include all the fields of the table I described, plus 1 calculated field--[PriorReportDate]. It would be [ReportDate], minus 1 month (you would use the DateAdd function for this). Call that query 'subQ1'

Then, I would build another query based on 2 instances of subQ1. You would link those two instances by the [ReportDate] of one of them to the [PriorReportDate] of the other. That would link a record to the data with the prior month's data. Then all you have to do is bring in your data and subtract the correct fields.

 

[Niroth]

First let's assume you've changed the [year_month] field to a date field called [ReportDate]. Once you've done that and my other suggestions I would create a query based on your table. It would include all the fields of the table I described, plus 1 calculated field--[PriorReportDate]. It would be [ReportDate], minus 1 month (you would use the DateAdd function for this). Call that query 'subQ1'

Then, I would build another query based on 2 instances of subQ1. You would link those two instances by the [ReportDate] of one of them to the [PriorReportDate] of the other. That would link a record to the data with the prior month's data. Then all you have to do is bring in your data and subtract the correct fields.

Thanks. It's a really great way to do it.


ETA: Just want to let you know that it works beautifully. Saved me a lot of time and head scratching.