Access Query

[JimmyC]

Access 2010; I have a customer database--with a single table. In the table is 4 fields--Company_Name, Year_End, EPS and RPS. I was hoping to bring the table into Excel but its too big. A sample of the first eight records are:

Company_Name Year_end EPS RPS
ABC 2012 9.25 10.50
ABC 2011 8.75 11.00
ABC 2010 7.66 10.50
ABC 2009 4.24 9.25
DEG 2012 5.00 8.25
DEG 2011 4.85 8.25
DEG 2010 4:95 8.25
DEG 2009 4.75 8.25

I believe the table holds to this pattern --but there are so many records it is impossible for me to manually verify the consistent pattern--though the customer that created the table states that the pattern is consistent. What I need to do is list only the Company_Names for those Company_Names where the EPS for each year is higher---specifically 2009 EPS < 2010 EPS and 2010 EPS < 2011 EPS and 2011 EPS < 2012 EPS (i.e. at this point I have not been asked to do anything with the RPS information).

In my internet searches some advice states I need to use a DLookUp--while others say to use an inner join. I have not been able to get these work to produce the correct result or in some cases I can't even get the syntax correct to generate a query. I just can't seem to be able to reference the "next" EPS record in my query to test whether the currently selected EPS record is greater than the previous year's EPS record.
In my sample data above only Company ABC would meet the criteria that I have been told to match where EPS is higher each year compared to the previous year.

[HansV]

1) Create a crosstab query (I named the table MyTable in the screenshot):

S224.png

2) The result looks like this:

S225.png

3) Create a query based on the crosstab query to specify the criteria:

S226.png

Note that the field names are enclosed in square brackets in the Criteria line, otherwise Access would use the number 2009 instead of the field 2009, etc.

4) The result is what you want:

S227.png

[JimmyC]

Hans,
I am sorry I am still struggling on how to make this work. Can you attached your access file? I am using Access 2010---not sure if you are on 2013 yet or not. Thanks
JimC

[HansV]

I'll try to create a sample database (in Access 2010) later. No time now.

[JimmyC]

Hans--Thank you and thanks for your patience. Jim

[HansV]

See the attached sample database.

CompareYears.zip

[JimmyC]

Hans--thank you. I was able to locate my mistake and I must have done it 4-5 times as I kept deleting my "test" database and starting over with a "new" test database. I somehow always ended up with the index number instead of the EPS values in my crosstab query. Thank you for your patience. I truly find Access difficult to work with even for those assignments that appear as an "easy" request. Thanks again. Jim

[JimmyC]

Hans---I am trying to learn more about the crosstab query.

Does a crosstab query only work when the table is arranged in a particular order? My data always had 4 records for each company and in the table the company data was together (i.e. 2012, 2011, 2010 and 2009 EPS / RPS data). What hapeens if my table had only 3 years for some companies--would a crosstab query still produce accurate results? How about if the table was initially ordered by year and not be company---I assume I would need to then sort the table by company name (i.e. and the customer may have already done a sort prior to sending me the single table database--I just don't know).

I can find plenty of internet info on the crosstab---but haven't found a conclusive site that provides me with an understanding of the prequisites to using a crosstab query. THANKS for your patience. Jim

[HansV]

The original data don't have to be sorted in any particular order, and they don't have to be complete.

Access automatically sorts the data in the crosstab query, and it will leave fields empty if there are no data for a particular company and year. So you don't have to worry about that.

[JimmyC]

Hans--thanks for the explanation as I now understand how useful a crosstab query can be. Jim