Link new records to old ones

[James Frater]

Hello All,

Is there an easy way to link a new record to an old one? For example I'd
like show in a subform for the event we did yesterday (27th January 2008),
what we did last year (28th January 2007), and the previous year and so on.

The closest I could get to returning the results I needed was matching as
many fields as I could through a query and then working this into a subform.
However as a lot of our events have been going on for years I have difficulty
in returning more than 1 years records.

Any help would be greatly appreciated

JAMES

 

[Michel Walsh]

Assuming the 'eventName' is the same, you can try:



SELECT a.eventName, b.*

FROM myTable As a INNER JOIN myTable As b
ON a.eventName = b.eventName



But note that this will be a normalized representation. If you want to
display, per year, the date the event occurred, as example, try:




TRANSFORM LAST(b.theDate)

SELECT a.eventName

FROM myTable As a INNER JOIN myTable As b
ON a.eventName = b.eventName

GROUP BY a.eventName

PIVOT Year(b.TheDate)




Hoping it may help,
Vanderghast, Access MVP