50+ check boxes problem 2

[cantwellt]

I'm trying to help a Serior Citizen Olympics group that has an Access 97 databse with 50+ check boxes in the main table with all the participants personal info in the same table. The check boxes were put in as yes/no to mark  the events that each person is competing in.  How can I get this data into a reasonable format, assuming that the object is to create reports that list participants for each of the 50+ events? About 90% of the records are entered in this database, and they are still adding up to the last minute. I hate to think of writing 50+ reports or sub reports. Have I got an unsolveable problem?

 

[JimHorton]

cantwellt,<br>A common way to structure this is to use an 'associative' table.&nbsp;&nbsp;This will be a 3 table approach.&nbsp;&nbsp;Table 1 is Person, Table 3 is 'Event' and logically in between these 2 is Table2, the 'Link' table.&nbsp;&nbsp;The Link table has 2 fields (at it's simplest)--PersonID and EventID.&nbsp;&nbsp;Each person/event pair is a record in this table.&nbsp;&nbsp;This can appear more confusing at first but it is far more flexible than the 50 checkbox approach.&nbsp;&nbsp;An example of the flexibility would be adding a couple more fields to the Link table: DateParticipated and Result...This way, you can have a record of each time the person participated in an event and what his score/result was that time.&nbsp;&nbsp;Also, you can add unlimited events without having to change the table to add a 51st, 52nd etc checkbox, change names of events without changing field names, etc.&nbsp;&nbsp;Reporting is a simple report over the Link table, drawing from Person table for Name and Event table for Event Name.<br>--Jim

 

[lhugh]

Jim<br><br>Sorry but I don't completely understand the idea. Can you explain this again, please and thank you.<br><br>I will need to create a database for my co-worker so your explaination will help a lot<br><br>Thanks Jim

 

[gol4]

I believe he is saying you need to take the one main table and create 3 different tables<br>something like<br>&nbsp;&nbsp;&nbsp;PersonTable<br>nameid&nbsp;&nbsp;first last etc (Same as Existing table excluding the 50 events)<br>Primary Key on name ID<br><br>EventsTable<br>Eventid&nbsp;&nbsp;&nbsp;eventname&nbsp;&nbsp;etc...<br>Primary key on Event, can be autonumber<br><br>LinkTable<br>recordid&nbsp;&nbsp;&nbsp;eventID&nbsp;&nbsp;&nbsp;&nbsp;NameID&nbsp;&nbsp;&nbsp;date&nbsp;&nbsp;&nbsp;&nbsp;score? etc<br>primary key on recordid, can be autonumber<br><br>using an Append or a make table query import all of the data from the main table into the PersonTable (except the 50 Event Fields data) (You could skip this step and just use your main table removing obsolete fields later) <br><br>Enter each of the fifty events into the events table so each one gets an id<br>eventId&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;event<br>&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Checkers <br>&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Chess tournment<br>&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Barrel Racing<br><br>Now you will need to run several Append queries&nbsp;&nbsp;setting the event id to match the event each person participated in&nbsp;&nbsp;<br>example:&nbsp;&nbsp;assume previous event field1 is checkers<br>&nbsp;in QBE grid put in your original maintable, you will be appending to the linktable<br>drag in the NameID and field1 {checkers}<br>under maintable.nameid will append to linktable.nameid. Under field1 append you will type in 1 (for checkers)<br>SQL statement is something like this<br>INSERT INTO&nbsp;&nbsp;linktbl.nameid, 1 <br>SELECT [maintbl].[nameid], [maintable].[fieldid]<br>FROM naintable<br>WHERE ((([fieldid])=Yes));<br>this will append a record for anyone who participated in that one event. Now all you have to do is one of these for each event. Then redesign your forms etc..<br>You could save steps using a Union Query.<br>&quot;Good Luck&quot;<br>&quot;You can't make a silk purse from a sows ear and you can't get good data from a poorly designed database&quot;<br><br><br>

 

[JimHorton]

lhugh,<br>I spent about 10 minutes and slammed together a quick example.&nbsp;&nbsp;If you don't mind posting your email (I don't know if this forum has a 'files' area, i'd put it there if they did), I'll send it to you.&nbsp;&nbsp;You don't need all those append queries.&nbsp;&nbsp;I did this with about 5 lines of code, it has a form that has all the Seniors on it, a listbox with the events, which you can add to at will with no change in code; double clicking the listbox adds the event/person link and displays in a subform.&nbsp;&nbsp;Also I did a report wizard report that displays all people and their events, dates, etc.&nbsp;&nbsp;10 minutes...like I said, this seems confusing at first, but it is really not, and is very flexible.<br>--Jim

 

[cantwellt]

Jim,<br><br>My email is <A HREF="mailto:cantwell@wf.net">cantwell@wf.net</A>. <br><br>The whole point of this whole deal is to try to make the framework of this database work and allow the user to keep inputting while I work. By the time they finish, it'll be too late. I haven't thought out the answers you guys so graciously sent, but I will and thanks! <br><br>It's a real bear to, as you say, make a silk purse out of a sow's ear. I'd still like to try though. The guy who wrote this database is a very good friend, and I wasn't there for him when he did it due to my job. I'm a real glutton for punishment huh? <br><br>

 

[JimHorton]

cantwell,<br>I've sent the db...let me know if this will help...<br>--Jim

 

[lhugh]

thank you gol4<br><br>and thanks Jim<br><br>here is my email <A HREF="mailto:lhugh@hotmail.com">lhugh@hotmail.com</A><br><br>Jim, that was very very nice of you to created a database for us. Thank you VERY MUCH