Merge records in Access 2007

[Katie Howard]

Hi,

I have a Contacts database which also tracks the years that each person was invited to specific events and the years he attended. My data currently looks like this:

Event_ID Contact_ID Record_ID Yr Invited Attended
26 644 2199 2010 -1 0
26 644 310 2011 -1 0
26 644 1754 2009 -1 0
26 644 2781 2010 0 -1
26 644 3343 2009 0 -1

What I'd like to do is to somehow merge the data together so that it looks something like this:

26 644 310 2011 -1 0
26 644 1754 2009 -1 -1
26 644 2781 2010 -1 -1

I've searched this forum as well as several other places and I can't quite find what I'm looking for. Is anyone out there able to help me? I'd really appreciate it! Thanks in advance!

[NeoPa]

To start off with, you need to organise your data correctly in the first place. [Record_ID] appears entirely irrelevant so should be dropped. Whatever else is unique (I would guess [Event_ID], [Contact_ID] & [Year]) should be set as a unique index, if not the primary key. Indices can be made up of multiple fields and you'd need to for this.

Next you'd need a system whereby the related record is amended when entering data, if one already exists. Records should only be added when a related one doesn't exist.

Lastly, you'd need a couple of queries. First to update any records with [Invited] set to reflect those with [Attended] set, then another to remove any records with [Attended] set which have a match to records with [Invited] set.