How to query 2 years from 1 field into a single record?

[Aximboy]

I have a table with CustomerName, YearSampled, and TestResult fields.
How do I create a query that shows 2009 vs 2010 TestResults per Customer?
I am planning to create a report that shows 3 columns, CustomerName, 2009 TestResult & 2010 TestResult.

Thank you in advance.

 

[traingamer]

A self-join might be the best way to that.

Select a.CustomerName, a.TestResults as 2009Res, b.TestResults as 2010Res from
yourtable a
inner join yourtable b on b.CustomerName = a.CustomerName

where a.yearsampled = '2009' and b.yearsampled = '2010'

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[dhookom]

You can also use a crosstab query with CustomerName as the Row Heading, YearSampled as the Column Heading, and TestResult (Sum, Avg, Min, or Max) as the Value.

Duane
Hook'D on Access
MS Access MVP