Compare two records in Access 2010

[Savaot]

Hello all,

Here is the problem that I have and for which I need help.
In db I have a table that have one record per customer per year that has answers from the yearly survey on 15 questions. Answers are numerical values between 0 and 5, and here is potential scenario.

Customer Date Q1 Q2 Q3 ... Q15
JohnDoe 2011 1 2 1 5
JohnDoe 2012 2 1 3 5
JaneD 2011 2 2 2 0
JaneD 2012 2 1 1 1
....

Using Query wizard like this one

SELECT Questionnaires.Client, Questionnaires.[Q of L Date], Questionnaires.Q1, Questionnaires.Q2, Questionnaires.Q3, Questionnaires.Q4, Questionnaires.Q5, Questionnaires.Q6, Questionnaires.Q7, Questionnaires.Q8, Questionnaires.Q9, Questionnaires.Q10, Questionnaires.Q11, Questionnaires.Q12, Questionnaires.Q13, Questionnaires.Q14, Questionnaires.Q15FROM Questionnaires
WHERE (((Questionnaires.Client) In (SELECT [Client] FROM [Questionnaires] As Tmp GROUP BY [Client] HAVING Count(*)>1 )))
ORDER BY Questionnaires.Client;

I was able to produce grouping and have tmp table where the records are grouped by client and sorted by date in ascending order.

I want to use that query as a foundation for the report that will look something like this

Client's Questionnaires
Customer Date Q1 Q2 Q3 ... Q15
JohnDoe 2011 1 2 1 5
JohnDoe 2012 2 1 3 5
Analysis Up Down Up ... Same

JaneD 2011 2 2 2 0
JaneD 2012 2 1 1 1
Analysis Same Down Down ... Up

Where I can replace words 'same', 'up' or 'down' with arrows and/or colours

In report wizard I can only choose between SUM, AVG and so on but nothing that I need. I guess there is need for some macro or VBA and that is where I'm stuck.


Your help is appreciated.

[June7]

I don't understand what you want. What does 'Analysis Up Down Up ... Same' mean? What behavior are you trying to implement?

Reports are not really intended to be interactive.

[Savaot]

I don't want reports to be interactive indeed. Analysis is the label for the client's summary; it can be 'Results of the comparison' or anything else. Words 'Up', 'Down' or 'Same' are the results of the comparison of the values in the Q1 columns and other columns respectively i.e. for JohnDoe for 2011 in Q1 I have '1' and for the year 2012 in Qq I have '2'. So in the 'Results of the comparison' for column Q1 value went up from 1 to 2 and I'll like to see word 'Up' or painted in green in the Q1 summary and so on.
Hopefully it is more clear with this explanation.
Thanks

[June7]

So you want to compare the data in pairs of records from the same table?

Never seen this one before. Don't think will be easy. Comparing data from records of same table usually isn't.

One approach:

Build a query that has all the data fields and also creates a field with calculation:
PriorYear: [Date]-1

Now build a query that joins the source table with the first query by linking on the [Date] and PriorYear fields. This will get the 2 years of data side-by-side.

Now expression in textbox like:

=IIf(Query1.Q1=Tablename.Q1, "Same", IIf(Query1.Q1<Tablename.Q1, "Down", "Up"))

[Savaot]

Thanks so much. It is really intriguing approach and I'll give it a try and post my results/hurdles.

[Savaot]

Thanks June7,

It worked nicely. Using your approach there was no need for macros or VBA, just a bit of tweaking the report and it solved my problem. If anyone wants specific details and June7's idea need an example do not to hesitate to send me a request. Thanks again JUNE7.