Generating Totally New Tables via Data Input

[tbaxter]

I seem to have all the "off the wall" questions but here goes anyway.

I'm building (or trying to!) a database for our local 4-H club (at no charge -- they don't even know it yet). I'd like to present it to them later this winter to use for their 4-H shows. Generally, the class list will be the same for each show. However, if they decide to put on a "specialized" show, I want to give them the ability to click a menu item (Make New Class List), which will then save it as a different Class List. For example, let's say the default class list is called ClassList1. Now, they want to build ClassList2 which is quite different than the default. They'd click on the menu item, it would open a conversation box asking for table name, they'd fill it in, press enter, and the form would open (datasheet view) where they would then enter the classes, giving the class # (not autonumber), class description (typed in), sponsor (typed in) and fee type (selected via drop-down). Then when they would close the form after entering all the classes, the table would be saved and could then be accessed as usual in building that particular show.

I know there is the Make Table Query but unless I'm remembering incorrectly, that takes data from one table and builds another table with it. I want to build a new table altogether where none of the data is coming from the existing table(s) -- although the structure is identical.

I know this has to be possible, I just don't know how! I'll be digging out my Access 2000 Bible this weekend; in the meantime, any help will be appreciated! Also, if necessary, I can send what I have so far for the database if it will help for you to see the structure as it stands now. Oh, and I'll probably also need help with setting up the database so we can track points for each horse/rider combination over a series of shows (circuit).

Yes, there are show software programs/databases out there already but our 4-H can't afford to buy one. I have a soft spot for 4-H (was a 4-H leader for several years) and want to help out by donating this database. Perhaps it can serve them well until they CAN afford to buy a COTS show program.

If all else fails, the 4-H leaders and I may end up visiting the local community college to see if the Spring Semester Access class will do it as an end-of-term design project. I think I've just been out of it for too long and using it too inconsistently to be able to do it easily. <sigh>

Thanks!

 

[The_Doc_Man]

You don't say clearly whether the following option I am going to suggest is even a possibility for your intent, but I'm going to be presumptive enough to suggest a conceptual change.

If you have classes already, and the difference is a separate table, you are doing it the wrong way.

What you REALLY need is a class table that allows you to somehow flag a discriminator datum. Then if you want to create a new class list, you create a new discriminator in another table and start adding new records to the class table with the new discriminator.

If you have to make a new table, all you are doing is adding so much of a headache that I need to go out to by some stock in aspirin companies before you have a chance to read this. When you add a new table, why should any existing code, queries, and other infrastructure in your DB care that it exists? (That is a rhetorical question.)

If the structure of this "new" class table looks very much like the structure of the "old" class table, then just add a field to the "old" class table that says "this is the newest class-table subset." Then choose the subset of the table with the new discriminator I mentioned. That way, you can re-use SO much of your existing infrastructure with just a little tweaking. As opposed to the wholesale bashing you would have to do when creating new tables that have nothing to do with anything that existed before.

 

[tbaxter]

Wow, I must not be as much of a lost cause as I originally thought! What you are saying almost makes sense to me!

Would it be possible, if doing it this way, to "assign" class numbers for each show? Argh! I'm having trouble articulating this.

Okay, let's go with making a short story long, then.

Show 1 has 20 classes, to be numbered 1 through 20. Show 2 has 25 classes with all of the same classes as show 1 plus 5 new ones, but the numbering sequence is different. IOW, the 5 new classes are interspersed within the original list. If I assign each class with a specific ID (that is not necessarily seen by the user), can they then assign a class # that is associated with that particular show and only with that particular show? I'm guessing that would be a pivot table. What I'm getting at is that we don't want a class list to show Class 1, 2, 3, 4, 38, 5, 9, 7, etc.

Let's look at 2 extremely abbreviated sample class lists (with only 8 and 10 classes, for brevity's sake):

Show 1
1 Halter mares
2 Halter geldings
3 Showmanship Jr
4 Showmanship Sr
5 Western Pleasure, Jr
6 Western Pleasure, Sr
7 Hunter Pleasure, Jr
8 Hunter Pleasure, Sr

Show 2
1 Halter mares
2 Halter geldings
3 Showmanship Jr
4 Showmanship Sr
5 Western Pleasure, Jr
6 Western Pleasure, Sr
7 Trail Horse, Jr
8 Trail Horse, Sr
9 Hunter Pleasure, Jr
10 Hunter Pleasure, Sr

Here are my tables as they currently stand:

ClassListTable:

ClassID (autonum, pk)
ClassDescr (text)
Sponsor (text)
FeeType (number, fk)

ShowTable:

ShowID (autonum, pk)
ShowDate (Date/Time)
ShowName (text)
Judge (number, fk) -- relates to judges table

RiderTable:

RiderID (autonum, pk)
Last
First
MI
DoB
Addr
City
ST
ZIP
Phone

HorseTable:

HorseID (autonum, pk)
HorseName
DoB
etc.

FeeTable:

FeeType (text, pk)
FeeDescr (text)
Fee (currency)

JudgesTable:

JudgeID (autonum, pk)
Last
First
Addr
etc.

PivotHorseRiderClass:

ShowID (number, pk)
RiderID (number, pk)
HorseID (number, pk)
ClassID (number, pk)
Placing (number)


Where do I go from here? And how off base am I?! It would be ideal to be able to "build" a new show, select the (numerous) classes from a list, be able to number those classes once the list is built, and take that specific show's class list and running with it.

Gee, now I know why these show programs are so darn expensive!