Comparing records in one table

[hibbii]

Could someone help me write a query or code that allows me to compare records based on the quarter?

For example, in my table all the records have the PK set as (DCN & Quarter). The DCN is usually the same from one quarter to another. However, 3 fields could change (cost basis, useful life, and start date).

I want to be able to allow a user to enter the 2 quarters they want to compare and then there will be a report showing the changes in DCN's from the 2 selected quarters.

Right now I have multiple tables with the different quarters and I am running somewhat of an unmatched query. This technique is very un-user friendly cus I would need to create a new query with the tables for every combination of quarters I want to compare.

Let me know if I am unclear in explaining this.

[hibbii]

Any suggestions on this. Thanks.

[willakawill]

Could someone help me write a query or code that allows me to compare records based on the quarter?

For example, in my table all the records have the PK set as (DCN & Quarter). The DCN is usually the same from one quarter to another. However, 3 fields could change (cost basis, useful life, and start date).

I want to be able to allow a user to enter the 2 quarters they want to compare and then there will be a report showing the changes in DCN's from the 2 selected quarters.

Right now I have multiple tables with the different quarters and I am running somewhat of an unmatched query. This technique is very un-user friendly cus I would need to create a new query with the tables for every combination of quarters I want to compare.

Let me know if I am unclear in explaining this.

Hi. Well this is not going to be an easy answer. We need to start with the table design. You say that the PK is (DCN & Quarter) and that DCN is usually the same. This means that you can only enter 4 rows in the table before you get duplicate values. Unless you are planning to join to one or more other tables in your query we should drop the PK.

What are the data types for quarter and dcn?

[hibbii]

Hi. Well this is not going to be an easy answer. We need to start with the table design. You say that the PK is (DCN & Quarter) and that DCN is usually the same. This means that you can only enter 4 rows in the table before you get duplicate values. Unless you are planning to join to one or more other tables in your query we should drop the PK.

What are the data types for quarter and dcn?

DCN and Quarter are both Text fields. The DCN remains constant with the record for all of time. There are fields in the record that can change but the DCN wont. There is 1 instance of each DCN every quarter this is what makes it unique.

By the way there are about 980 different DCN's in quarter 1. That number could go up or down depending on if we delete a certain DCN or add one. I am only concerned with the ones that are still there in quarter 2 and running a query that will show any changes made from the 2 quarters.

[willakawill]

DCN and Quarter are both Text fields. The DCN remains constant with the record for all of time. There are fields in the record that can change but the DCN wont. There is 1 instance of each DCN every quarter this is what makes it unique.

By the way there are about 980 different DCN's in quarter 1. That number could go up or down depending on if we delete a certain DCN or add one. I am only concerned with the ones that are still there in quarter 2 and running a query that will show any changes made from the 2 quarters.

You can compare two rows in your table using an sql statement to fetch them

"SELECT DCN FROM [your table name] WHERE [quarter] = '" & UserInput1 _
   & "' OR [quarter] = '" & UserInput2 & "'"

[hibbii]

You can compare two rows in your table using an sql statement to fetch them

"SELECT DCN FROM [your table name] WHERE [quarter] = '" & UserInput1 _
   & "' OR [quarter] = '" & UserInput2 & "'"

Thanks for that, however, I still dont get what that will do.

How would I add it so that it will show me the adjustments that are made between the 2 DCN's from different quarters? The changes could be made in the costbase, startdate, and/or uselife fields.

[willakawill]

Thanks for that, however, I still dont get what that will do.

How would I add it so that it will show me the adjustments that are made between the 2 DCN's from different quarters? The changes could be made in the costbase, startdate, and/or uselife fields.

Then select all of the fields and do what you need to with the result to show differences:

"SELECT * FROM [your table name] WHERE [quarter] = '" & UserInput1 _
   & "' OR [quarter] = '" & UserInput2 & "'"

Using the above as a source for a recordset you will be able to perform any operation you like on the fields

[hibbii]

Thanks, let me give it a try. I really never use SQL too often to build my queries just do it off the query design.

[Kosmos]

Hi I am at work but I can give you a little example of how you can make the form more user friendly. So long as you have a simple system like Q3 - 2004, the user will not be confused as to what to enter...I used the following site to get familiar with creating user friendly forms: http://allenbrowne.com/ser-62.html

Go behind the scenes by looking at the code behind on click events and perhaps some of the VBA and SQL will give you some ideas....he explains things well

This site is commercial since it has advertisements and therefore they don't like to use it here...but it's really a great site for reference: http://www.functionx.com/vbaccess/Lesson01.htm

hope this helps
Cheers,
Kosmös

[hibbii]

Then select all of the fields and do what you need to with the result to show differences:

"SELECT * FROM [your table name] WHERE [quarter] = '" & UserInput1 _
   & "' OR [quarter] = '" & UserInput2 & "'"

Using the above as a source for a recordset you will be able to perform any operation you like on the fields

Hi again. I just tried doing something on my own and have absolutely no idea what it is I am doing. I am having problems setting up how I want the query to do the calculations.

Just a little more background on the table that might allow for you to help me further. The quarters are also sorted by year so they are in "FY06 Q1", "FY06 Q2" etc. format.

Now say when I run the query the parameter boxes pop up, the user enteres FY06 Q1 in the first box and then another box pops up saying "Compare with?" and the user enteres FY06 Q2. I need to write the query to be able to do this. Below is an example of how I do it when the 2 quarters are in different tables.

SELECT DISTINCT ADT_Q3_Complete.DCN, ADT_Q3_Complete.CostBas AS [New Cost Basis], [ADT Calculated Amort Schedule].CostBas AS [Old Cost Basis], ADT_Q3_Complete.[Start Amort Date] AS [New Start Amort Date], [ADT Calculated Amort Schedule].[Start Amort Date] AS [Old Start Amort Date], ADT_Q3_Complete.UseLife AS [New Ueful Life], [ADT Calculated Amort Schedule].UseLife AS [Old Useful Life], ([Old Cost Basis]-[New Cost Basis]) AS [Cost Difference], Abs(DateDiff("d",[Old Start Amort Date],[New Start Amort Date])) AS [Date Difference], [Old Useful Life]-[New Ueful Life] AS [Useful Life Difference]

[willakawill]

Hi again. I just tried doing something on my own and have absolutely no idea what it is I am doing. I am having problems setting up how I want the query to do the calculations.

Just a little more background on the table that might allow for you to help me further. The quarters are also sorted by year so they are in "FY06 Q1", "FY06 Q2" etc. format.

Now say when I run the query the parameter boxes pop up, the user enteres FY06 Q1 in the first box and then another box pops up saying "Compare with?" and the user enteres FY06 Q2. I need to write the query to be able to do this. Below is an example of how I do it when the 2 quarters are in different tables.

do the tables contain fields for different quarters or are the tables named after different quarters and only contain data for a single quarter?

In other words, can I go to one table and get a row of information for each quarter or do I have to go to separate tables to get that info?