complicated query

[Krykota]

In Access 2007, I am building a db to help manage a homeowners association.
The task is to create a record of association meetings. In this task, I need
to record attendance at the meetings.

I have a table which holds the memberships of the association. It has
columns for a primary last name, a primary first name, a secondary last name,
and a secondary first name.

I have created two queries which joins the last and first names each for
primary and secondary and then appends to a single column in a new table -
giving me all who can attend in a temporary use table.

I created a macro that will run both queries after first deleting the
current or existing column data - ensuring that I have a current list of all
member names.

My hope is that I can then create a query for a pop up form that will
display the list of names and a radio button control that can be clicked to
indicate attendance for a specific meeting date and then store that
information in a permanent table called attendance.

Any help or guidance would be greatly appreciated.

 

[Dale Fye]

Not sure what the rest of your table structure looks like, but I would
probably organize it a little differently.

I'd have a table of addresses, this will probably never change
I'd have a table of HomeOwners with a reference to the HomeOwnerID,
AddressID, FirstName, LastName, StartDate, EndDate, and maybe a Yes/No field
for Primary (if you think that is necessary). You might also include
columns for cell#, work#

With this new structure, you wouldn't really need a temporary table, as you
could simply query the HomeOwners table for Homeowners where [EndDate] IS
NULL. However, I would probably use a temporary table anyway, and include
Yes/No or Integer field in it so that I could use it as a subform to
identify the homeowners who attended the meeting, with a checkbox tied to
that field.

You might have AssnMeetings table which contains a MeetingID, MeetingDate,
and any other information you think is pertinent to the meeting. Then, your
AssnMeetingAttendees table would contain a MeetingID, and the HomeOwnerID.
You would use the HomeOwners subform (or a popup) to display all of the
homeowners and put a check in the checkbox for all those that attended.
Then, I would have a command button that writes all of the checked records
to the AssnMeetingAttendees table.

Then, when when you create a new meeting, you would fill the temp table with
the names, and set the Yes/No field to 0. If you load an old meeting and
want to see the attendees, you could then set the Yes/No field to true for
those that are in the AssnMeetingAttendees table, and filter the list for
those with a value <> 0.

HTH
Dale