Create a new table based on an event procedure

[julie67]

I have a table called "Names". This table has links to property info and interest groups these names are affiliated with.

I want to build a form that allows the end user to go through the names and decide whether or not to invite them to a particular social function. I want an update table so I can track only the names that I have invited to the social function and track RSVPs.

There are many different social functions so I don't want to change the main "Names" table and add Y/N fields every time there is a new social function.

I would like for the user to name the new table as well. They can then have a query to determine how many guests are attending the social function.

I don't write much VB, so I do have any direction on how to go about doing this.

Any suggestions?

TIA,
Julie

 

[traingamer]

One way - Instead of creating a new table every time:

Have an event table with Eventid, Event Name, Num guests which gets one row for each event

Have attendeees table with AttendeeId, EventID, NameID which gets n rows for each event.

You could have a combo box or lookup for the NameID and fill in as needed.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[julie67]

Greg,

I am a little confused. How do I have the user select records from the "Names" table and insert them into the Attendees table?

Also, I don't understand the Num guests from the Event table you are referring.

Forgive me, I a little slow at this.

Thanks,
Julie

 

[traingamer]

Ok - forget about the [Num guests] field. I set up a

tblNames table With NameID (autonumber) and Name (& whatever other fields you want)

tblEvents with EventID (autonumber), Event (text) (the description of the event

tblAttendees with AttendeeID (autonumber), EventId, (number), NameId (number), Rsvp (checkbox)

tblAttendees has a lookup set to Combo Box, table/query, a source of "SELECT tblNames.NameId, tblNames.Name FROM tblNames; ", bound column 1, Column count 2, column width .5"; 2"

Define relationships in tools->define relationships for these three tables. Link tblNames to tblAttendees on NameID one-many; link tblEvents to tblAttendees on EventID one-many

Create a query qAttendees with all three tables like: "SELECT tblEvent.EventId, tblEvent.Event, tblAttendees.AttendeeID, tblAttendees.NameId, tblAttendees.Rsvp
FROM tblEvent INNER JOIN (tblNames INNER JOIN tblAttendees ON tblNames.NameId = tblAttendees.NameId) ON tblEvent.EventId = tblAttendees.EventID;"

Then, the really easy part:
Forms->New->Form Wizard - choose the query just defined qAttendees, put all fields on form, hit next, (view your data by tblEvent), choose form with subform, subform as datasheet, ta da! A form to enter events, and choose attendees.

The only tricky parts are the relationships and the lookup in the tblAttendees to link with the tblNames as described above. Whole thing took about twenty minutes.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Better yet, change the qAttendees (before creating the form) to

SELECT tblEvent.EventId, tblEvent.Event, tblAttendees.AttendeeID, tblAttendees.NameId, tblAttendees.Rsvp, [COLOR=red]tblNames.Name[/color]
FROM tblEvent INNER JOIN (tblNames INNER JOIN tblAttendees ON tblNames.NameId = tblAttendees.NameId) ON tblEvent.EventId = tblAttendees.EventID;

(That way the Name will also show up on the form (and not just the NameID)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[julie67]

Greg,

Silly question....can I add a new event in the same form? I can't seem to make that work.

It probably would be easier to add an event from that form. Also, how do I add a button to give me a count of those who has RSVP'd and those who are definitely attending? I added two fields to the tblAttendees table: RSVP & Attending

TIA,
Julie

 

[traingamer]

To add an event, choose the 'new record' selector at the bottom of the form (the '>*').

To get just a count, write a new query (and link it to a report if you wish) to display the results.
Something like

SELECT tblEvent.Event, Count(tblAttendees.Rsvp) AS CountOfRsvp, Count(tblAttendees.Attending) AS CountOfAttending
FROM tblEvent INNER JOIN tblAttendees ON tblEvent.EventId = tblAttendees.EventID
WHERE (((tblAttendees.Rsvp)=True))
GROUP BY tblEvent.Event;

You could develop other queries to get a list of who is attending.

Greg

 

[julie67]

greg,

Can I put the count query in the form, so they can see a head count right from that form?

Thanks for all your help. You're making my job a lot easier!!

Julie

 

[traingamer]

There are a number of ways. One way is to use a subform.

Create a form with your count query as the record source. Place just the count field on the form, remove the label that comes with it, remove the record navigation, move the field to the upper left hand corner of the form, reduce the form to just the size of the text box and save it as frmsubCount or something.

Go into your main form in form design, add a subform control and use the sub form you just created, linking on event id.

There are other ways to do it as well. Good luck.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

An even better way that VBSlammer just pointed out: thread705-1018428

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Although that will just give you the total records, NOT the total attending

 

[julie67]

I have finished this, but the client does not like that you have to add attendeess to the events table by NameID. He wants to look up by last name with a view of the full name in case there are multiple people with the same last name.

I have fiddled with the subform, fmAttendees, and if I change the control source of the combo box to last name, it does not link all the information. I also tried leaving the control source NameID, but if my sort order is by last name and then NameID, it is trying to put a last name in the NameID field when looking up a name.

I need to have this subform allow the user to look up names by last name with a view of full name and hide the key column, NameID. How do I do that?

Any suggestions?

Thanks,
Julie

 

[traingamer]

Did you do something like this (as shown above)?

tblAttendees has a lookup set to Combo Box, table/query, a source of "SELECT tblNames.NameId, tblNames.Name FROM tblNames; ", bound column 1, Column count 2, [COLOR=red]column width .5"; 2"[/color]

Both columns should appear - adjust the column widths so you get a full view of the name. Or you could make column width 0";2" to not display the ID (although you'd still be saving it).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill