How Do I Make Multiple Lists?

[Guest]

Morning,

I'm trying to make it possible to track multiple lists in our contact db
(access 2003). Basically, I want to know who got Christmas cards in '04, who
came to the seminar in 10-05, etc. I want to be able to pull up a form that
has a dropdown for which list I want and all of the contact's names with a
little checkbox next to them. This checkbox would add them to a user-defined
list. Here's how I have it structured so far...

tblClients (contact information - no duplicates)

tblListMaster (name of list for future lookup - no duplicates)

tblListConnector (yes/no and linking foreign fields to each of the previous
tables - duplicates allowed)

This is where I get lost. If there were just two tables, I could see doing
some kind of left join, but I'm getting all messed up and think my brain is
going to melt. I need some fresh brain to help.

Thanks!

Aaron G
Philadelphia, PA

 

[Guest]

All you need is a transaction table- contact / type (xmas card, seminar, etc)
/ date. You don't even need the date if you have that in the type.

 

[Guest]

If I do it that way I would be duplicating too much info. Say I have 200
clients. Basically, I want a form with a checkbox next to each name that I
can assign to a list. I also want to be able to have multiple lists.

I want to be able to go to a form and select the list title from a combo box
(or type in a new one). then in a subform underneath it I want every
client's name with a checkbox next to it that's unique to each list from the
combo box.

Is this possible?

Thanks again.

Aaron G
Philadelphia, PA

 

[Rick B]

This is a many-to many relationship.

tblPeople
PersonIDNumber
FirstName
LastName
etc.


tblEvents
EventIDNumber
EventName
EventType
etc.

tblEventsAttended
PersonID
EventID
Date


That's it!
Good Luck!

 

[Guest]

Rick,

Thanks for the help. I need a little more hand holding if you wouldn't
mind. I think I'm having difficulty in the join-type area.

Here's what I have:

tblClients
ClientID (autonnumber and key)
LastName
Etc.

tblEventMaster
EventMasterID (autonumber and key)
EventName
Etc.

tblEventAttended
ClientID
EventMasterID
Attended (yes/no checkbox)

For the relationships I just drag the corresponding ID fields to each other.
The first two tables create a one-to-many relationship with the third.
Should I be doing something specific with the join type? I've tried creating
queries to help me out with this, but I always run into difficulties with the
autonumber fields. SHould I turn off autonumber for the EventMAsterID? I
feel like I'm sooo close!

Thanks again. My brain hurts.

Aaron G
Philadelphia, PA

 

[Rick B]

Ok, there is no need for the checkbox. ONLY events that the person attended
should end up in the third table.

The relationships would be...

tblClient => tblEventsAttended
tblEventsMaster => tblEventsAttended

The joins would be of the type "include all records from tbleEventsAttended
and only those records where..."

 

[Guest]

Thanks again Rick.

The reason I wanted a checkbox was so that I could have a form with all
clients (say 200 of them) on one spot and easily select them one by one for a
particular event (and also keep a record).

I will do it the way you suggested, but I was hoping there was something
that would be a little quicker.

Thanks again.

Aaron G
Philadelphia, PA

 

[John Vinson]

Thanks again Rick.

The reason I wanted a checkbox was so that I could have a form with all
clients (say 200 of them) on one spot and easily select them one by one for a
particular event (and also keep a record).

I will do it the way you suggested, but I was hoping there was something
that would be a little quicker.

You can actually get that effect, if desired, in a couple of ways. One
would be to have a Form based on the event table, with a subform based
on the attendance table; you could have a combo box on the subform to
present all the names.

A bit more sophisticated approach would be to base the Subform on a
Query joining the client table to the attendance table, using a Left
Outer Join (show all records in Clients and matching records in
Attendance); be sure to select the ClientID from BOTH tables in this
join (though it need not be displayed on the form). Entering data into
any field in the attendance table (a date, with a doubleclick and a
line of VBA) or clicking a checkbox will fill in both the ClientID
(from the join) and the EventID (from the master/child link).

John W. Vinson[MVP]

 

[Guest]

John,

I think I have it set up like you said, but I'm still running into issues at
the very end.

I have a form based on the tblEvents. I created a subform based on the
query where I left outer joined the ClientID from the tblClients and
tblAttendance. I link the child subform to the master form with the EventID
fields. As soon as I do this, the subform only shows clients who have that
event selected. If I delete the relationship (from the child to master) then
all the clients show up with the proper checkboxes, however, when I try to
select another checkbox it won't allow me to becuase it won't autopopulate
the EventID since there is no longer any relationship.

It seems I need some kind of left join relationship between the master and
child form. Is this possible? What am I missing?

Thanks again.

Aaron G
Philadephia, PA

 

[John Vinson]

John,

I think I have it set up like you said, but I'm still running into issues at
the very end.

I have a form based on the tblEvents. I created a subform based on the
query where I left outer joined the ClientID from the tblClients and
tblAttendance. I link the child subform to the master form with the EventID
fields. As soon as I do this, the subform only shows clients who have that
event selected. If I delete the relationship (from the child to master) then
all the clients show up with the proper checkboxes, however, when I try to
select another checkbox it won't allow me to becuase it won't autopopulate
the EventID since there is no longer any relationship.

It seems I need some kind of left join relationship between the master and
child form. Is this possible? What am I missing?

Please post the SQL view of the subform's query, and the master/child
link field properties.

John W. Vinson[MVP]

 

[Guest]

John,

A quick word on my naming conventions - the three tables involved are named
"Clients", "ListsMaster", and "ListsConnector". Linking fields that are not
native to the table have a "Foreign" prefix.

Here's the sql code from the query creating a relationship between the
Clients table and the ListsConnector table:

SELECT Trim([LastName] & ", " & [Nickname]) AS ClientName, Clients.ID,
ListsConnector.ForeignClientsID, ListsConnector.ForeignListsMasterID,
ListsConnector.OnList
FROM Clients LEFT JOIN ListsConnector ON Clients.ID =
ListsConnector.ForeignClientsID;

Here is what's listed under the Data tab for the child subform's properties:
Source Object... ListsMasterSub
Link Child Fields... ForeignListsMasterID
Link Master Fields... ListsMasterID
Enabled... Yes
Locked... No

I'm not aware of anywhere else I can manipulate this master/child
relationship. That may be my problem if there's another way.

Thanks again for all your help.

Aaron G
Philadelphia, PA