Mission Impossible?

[cmccarter]

My wife has be given the "oportunity" to be the registrar of an upcoming Garderer Jamboree. The database has only a few requirements and I have started to build it but I am violating the prime rule tall and thin vs shoe and fat. The requirements are


fname
lname
email
returning (y/n)
Referred by
Association
Lunch typr (regulas/salad) {using listbox)
Remarks
Confirmation sent (Y/N
paid (y/n)
dated entered in db (auto assigned)

All the above can be in a normal table (call it Tbl_Attendiees)

Here's where i get totally lost At the jamboree there will be 7 workshops all of which will done during 3 sessions (so a total of 21 workshops. each attendee eill be able to choose 3 workshops to attend. I need to keep track of which workshop they select.

Right now I have the workshops in their own table and use a drop list box on the attendees tbl to show. But I need to figure out how to keep track of the number of people assigned to each session/workshop.

Nuce to have options will be able to send emails to attendeed, build reports bases on number attending weach sorks shop/session

I had a sample somesent me las year but with over 50 tables/queries/reports I was a bit overwhelmed. I know I can do this using Excel and a lot of sorting and manual intervention but can it be done simply and efficiently with access? I am NOT a programmer just a husband with a mission. any and all help appreciated. can posty a copy og present database if needed.

[Bulzie]

Maybe something like :

Table for Workshops:
tblWS
WSID = Autonumber
WSTitle
WSDescription
WSDateTime
...

Table to show who attended which workshop:
tblWSAttended
WSAttendID = AutoNumber
WSID
AttendeeID (from tbl_Attendee, should be another autonumber in that table)


On your main Attendee form, add the Attendee fields and add a subform for tblWSAttended. On that form, have a combo box of tblWS to let them select multiple workshops. In the LinkMaster and LinkChild put AttendeeID. You can also run a query to count the number of records for a specific Workshop from the tblWSAttended table.

[ranman256]

You need PersonID.
this will be used in workshopRoster. (TWsAttend)

[cmccarter]

My wife has be given the "oportunity" to be the registrar of an upcoming Garderer Jamboree. The database has only a few requirements and I have started to build it but I am violating the prime rule tall and thin vs shoe and fat. The requirements are
fname
lname
email
returning (y/n)
Referred by
Association
Lunch typr (regulas/salad) {using listbox)
Remarks
Confirmation sent (Y/N
paid (y/n)
dated entered in db (auto assigned)

All the above can be in a normal table (call it Tbl_Attendiees)

Here's where i get totally lost At the jamboree there will be 7 workshops all of which will done during 3 sessions (so a total of 21 workshops. each attendee eill be able to choose 3 workshops to attend. I need to keep track of which workshop they select.

Right now I have the workshops in their own table and use a drop list box on the attendees tbl to show. But I need to figure out how to keep track of the number of people assigned to each session/workshop.

Nuce to have options will be able to send emails to attendeed, build reports bases on number attending weach sorks shop/session

I had a sample somesent me las year but with over 50 tables/queries/reports I was a bit overwhelmed. I know I can do this using Excel and a lot of sorting and manual intervention but can it be done simply and efficiently with access? I am NOT a programmer just a husband with a mission. any and all help appreciated. can posty a copy og present database if needed.

Here is the actual db I have completed. I can get any info I need using reports/queries but I know there is a better way just not what it is. Hopefully once completed I can clear it out and start again next year with out major changes.


tried to upload but it fails
but have only 2 tbls now see above
I think i need to make a union table using the ids from both the attendees table and the workshop table as primary keys
but what next after setting up a many-many relationship.

please remember you are talking to a total idiot just trying to make some data management easier for my even less knowledgeable wife.

[Bulzie]

I think you need those 2 tables I listed above along with your Tbl_Attendees in which you need to include a primary key which I called AttendeeID but call it what you want. Read #2 comment above again.

[pbaldy]

tried to upload but it fails

You should be able to attach the db after doing a compact/repair and then zipping.

[cmccarter]

Will try that, so far the whole.project seems a bit over my head. I did tak a sql class long ago but as a network engineer never needed db stuff,

[cmccarter]

Will try that, so far the whole.project seems a bit over my head. I did tak a sql class long ago but as a network engineer never needed db stuff,

uploades my basic db, names and emails have been cleaned

[aytee111]

With databases, you cannot use the same word to mean different things! You first say there are 7 workshops, then 21 workshops, then attendees can do 3. Can you explain what each of these are? Remember, databases have no innate intelligence!

[cmccarter]

Will try to explain there are seven basic workshops. Each of those workshops will be hosted 3 time during the day ie session 1,2,3 so each named class is seperate. Kinda like in school there is an English class 1st period, 2nd period, 3 Rd period. Each class is the same just held at a different time

[aytee111]

Here is something to play around with.

[aytee111]

I moved attendees to the sessions table instead. I realized it was in the wrong place.

[cmccarter]

Looks great at first glance. Waiting on wife's approval an gotta make a few small tweaks but at least I can see the table relayionships and how they work together. Much appreciated.

Will need a few more queries but think I can handle that

[cmccarter]

Looks great at first glance. Waiting on wife's approval an gotta make a few small tweaks but at least I can see the table relayionships and how they work together. Much appreciated.

Will need a few more queries but think I can handle that

Gave it a try and have a few questions

1. on the workshops_frm there is a box for Max attendees and the workshop box does not drop down . am i missing where it is reference. says control source is WorkshopName

do i ad/change workshop names in the workshop_tbl. Also added a fiel in workshop table with max attendees.

[aytee111]

The database is on my work computer, will have to wait til tomorrow. I may not have changed the workshop form after I changed the table layouts, sorry! There aren't many workshops so you navigate thru the list of records using the arrows at the bottom of the screen, the star is where you can add a new record. You don't need max attendees on the workshop table, it is on the session table. Not much point in having it in both places, remove it from the workshop form and add it to the subform.

The workshop form is very simple, you can put buttons and comboboxes if you wish. Lazy me! There were so few entries that it didn't warrant all that extra work.