setting up info in tables / forms

[Glock232]

Hi gang, first post here. (ok 2nd, i copied / pasted from different topic) I consider myself an intermediate PC user and Beginner/Intermediate Office user. Of course im sure ill get blown away by some of the advice / ideas on here and learn more than I ever thought and feel like a total Newb...

Anyways.. my problem.. I need to track some information based on.. .

1. Event: Date and Lengt in hours
2. Attendees to specific event and hours they were there
3. I need to be able to add multiple attendees to events from a Member table..

Basicly im with a rescue squad, we need a better way to track who attends what meetings and drills and fund raisers.. I have been toying with things for weeks and cant get this DB set up the way I want to. I need to add all the members to the DB, then as they occour I need to add Events. When I add the events I need to be able to choose multiple persons from the members table. and submit all that data and have it record the info for each person.

Best example I can think of to describe exactly what I want is if you search MS online for the "Book Collection" database, the way you can add authors to books is what I want to do.

Hope that is a clear description of my issue..

Thanks
Joe

 

[dhookom]

Do you have any tables set up yet. I would expect these three to start with (using my preferred naming convention)

tblPersons (1 record per person)
=============
perPerID autonumber primary key
perFirstName
perLastName
perPhone
perAddress
perCity
perStatus

tblEvents (1 record per event)
=============
evnEvnID autonumber primary key
evnName
evnDate
evnStartTime
evnEndTime
evn....

tblPersonEvents (1 record per event per person)
===============
pevPEvID autonumber primary key
pevPerID link to tblPersons.perPerID
pevEvnID link to tblEvents.evnEvnID
pevMinutes number of minutes of participation
pevComments

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Glock232]

I do have tables set up, names are slightly different but I like yours better than mine, and I have the Primary Key for perID set to a number, only because that is every member has an operator number, i.e. my old one is 232, hence my screen name =)

but anyways, I have 2 tables

Member Table:
memberID Number Primary
lastName
firstName

Event Table:
eventID (auto#) Primary
eventDate
eventType (which I did for lookup and put in my own values)
eventLenght (Just a # field for time in hours)
eventDescription (so we can see what we did)

Thats all I have/think i need.. I am totally open to suggestions
And someone on the other thread I started suggested to modify the Northwind Sample DB.. I didnt have time to try that, but If you open that and click on orders, I want it something like that... When you open the "Orders" form how you put in the order information, which is where I would put in the event information, and then the subform for order details, which would be a drop down to select the names of those who attended.

 

[Glock232]

I guess I realy need 1 record per event with multiple persons assigned to it..

 

[dhookom]

I can't imagine how you would create "1 record per event with multiple persons assigned to it" without creating the junction table tblPersonEvents.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Glock232]

I'm not sure I dont need the junction table... I was just putting down what I had made so far. I need to know how to make the form with the members subform in it so their names appear in individual drop down boxes. I probally don't have nearly enough tables, but just put what I though I needed up to this point..

 

[dhookom]

You can build a main form from tblEvents so you can enter information about the event. Then create a subform based on the junction table tblPersonEvents. Use a combo box for the Person ID field so you can select persons who attended the event. Set the Link Master/Child properties to the Event ID field.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

Here are a couple of very helpful documents for beginning database developers.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi