Contact Histroy Queries

[desmondok]

Hi,

I have a database output from web, which contains many customer data, the format is something like below

Customer ID | Name | Company | ApproachDate
----------------------------------------------


1234 | John | ABC Company | Jun-2009
2345 | Mary | BCD Company | May-2009
1234 | John | ABC Company | Apr-2009
2345 | Mary | DFG Company | Apr-2009
3456 | Peter| GHF Company | Mar-2010
2345 | Mary | DFG Company | Apr-2010

What I hope to do is to generate a summary report so that I can see the picture of a customer approach history, could anyone suggest if it is possible to achieve by Access ?

e.g.

Customer ID | Name | FirstCompany | FirstApproachDate | SecondCompany | ApproachDate | DateDifference | ThirdCompany | ApproachDate | DateDifference |
1234 | John | ABC Company | Apr-2009 | ABC Company | Jun-2009 | 2 months | NA |NA |NA |
2345 | Mary | DFG Company | Apr-2009 | BCD Company | May-2009 | 1 months | DFG Company |Apr-2010 |11 months|
3456 | Peter | GHF Company | Mar-2010 | NA |NA |NA |NA |NA |NA


Thanks!

[NTC]

well it is simply performed using an Aggregate Query. This topic is covered in all Access text books. Look for the greek Epsilon E symbol when in query design.

however your post includes a left-right layout which is non standard; and for database norms often not worth the effort. In particular your "NA" fields. You probably can be set up a crosstab query/report using your AggregateQuery as the source to go to this type left-right layout. But it is a hassle I generally steer people away from - - afterall it is only layout...the data is correctly & accurately consolidated in an Aggregate Query, spinning alot of wheels to make it go left-right rather than up-down is often not worth it.

hope this helps.