Need structure ideas

[audrey]

It's been a LONG time since I've designed a database, but it's slowly coming back to me. However, I'm still having a very difficult time deciding the most effective way to setup my tables and queries. Here's what's going on.

The database will be used for FFA livestock shows to enter the exhibitors for market classes and showmanship classes. The database also needs to be able to break a pay scale down showing how much each exhibitor is awarded for their showmanship class and their market class. The database that the club had been using had 2 tables (1 for the exhibitor and 1 for the animals being shown). Then they had 10 different queries that sorted the animals into weight groups (ex. Class 1 is weight between 90lbs and 100lbs); and then they had 10 different queries to breakdown the showmanship classes (ex. Showmanship Class is is age between 5 and 7). This is a LOT of queries! Now, the manager wants to be able to have more than 10 classes, which their current database cannot do. He also likes to be able to enter the parameters to manually break the classes. As of now, he is manually going into each query and editing the criteria for each class, but he wants to be able to do that from a form.

So my question is.... is it best to leave this in the "multi-query" form, or is there some way I could arrange all of this from tables or from a couple queries... and also, what would be the best way to offer the option to add a new class?

Any and ALL advice is greatly appreciated!

~ Audrey

 

[Moniker]

Can you post a stripped-down version of the DB you're using (one with little to no data but the current structure intact)? It's next to impossible to infer what you need from the description, which hinders help.

~Moniker

 

[audrey]

Thanks for the response Moniker. I stripped all of the data from the DB and it's still far too large even zipped. I do agree that it's very confusing. I'll try to type out a description. If you feel like fooling with it through email, just pm me and i'll be GLAD to email it to you

This is how it was setup.... designed by the kids:

Table: Address
Exhibitor (text)
BD (date)
SMS (Y/N)
PLCN (number)
Money (currency)
Address
------------------
Table: TagNumber
Exhibitor (text)
TagNo (number)
Weight (number)
PLCN (number)
Money (currency)

(As it was, the manager had to type in the kid's name in both tables. Table: Address stored their normal information, Table: TagNumber stored the animals' info that the kid will be showing. I already changed this part of it where the manager enters the kids info in a form (from Table: Exhibitors) and then he enters the animals' info in a subform data sheet (subform: Livestock - sourced by Table: Livestock) that is linked by exhibitorID in both tables. This way the manager only has to enter the child's name once and from the same form can enter all of the animals & animal info that that kid will be showing. And of course, I added primary keys. So that part is working fine.

Here's the "fun" part.

Query: Class 1
Field: Exhibitor (from Table TagNumber)
Field: TagNo (from Table TagNumber)
Field: Weight (from Table TagNumber) Criteria: "Between 100 and 103"
Field: PLCN (from Table TagNumber)
Field: Money (from Table TagNumber)

This query will produce a list of all of the exhibitors that are showing an animal that weighs between 100 and 103 pounds. This forms Class 1. When the class is finished, he opens the query for that class and enters each exhibitor's placing in the PLCN field.


Query Class 2 is exactly the same excpet Criteria is "Between 104 and 107"

Query Class 3 all the way through Query Class 10 follow in order, incrementing the criteria by 3 lbs or how much ever the manager wants to separate them.

As I said, he has been going into the design view of the query manually and changing the criteria for each one.

The showmanship queries are setup the same way except he changes the criteria for the age instead of the weight.

So that makes 20 queries that he has to change (10 for the weight classes and 10 for the showmanship classes).

He wants to be able to "manually" edit the criteria, but from a form instead of having to go into the design view of all the queries (I can do this part). I just need to have some ideas on going around the multiple queries. PLUS he wants to be able to have the option to add more classes.

I hope this clears it up a little. Thanks for any imput!

 

[johncallaghan]

I load a form on startup that is hidden
e.g. DoCmd.OpenForm "frm_VariableStore", , , , , acHidden

Have a text box for storing variables and refer to them by

forms!frm_VaraiableStore!txt_Var1 = ### etc

then in the query place
> forms!frm_VaraiableStore!var1 and < forms!frm_VariableStore!var2 in the criteria row that you want.

change thes varaibles at will.

you will get an error if you try to open these queries in VBa but thye work fine if attached to combobox or a form or a report.

Good Luck, I remember being where you are about five years ago and it is a bit scarry!!

 

[audrey]

Hi John.... thanks for your comments. I will definitely give it a try. And yes, it is scary!! Thanks again.

 

[Bat17]

rather than working in the queries for data entry work from a form.
You can have your criteria showing on the form with the query picking it up from there.
you can always add a combo box of 'common' values that can be used to populate the query and still have the option to enter them freehand. To add new 'common' classes you just add them to the lookup table the combo is based on



Peter

 

[The_Doc_Man]

OK, first things first:

Normalize this puppy VERY CAREFULLY. I would add an internal exhibitor ID so that once you have exhibitor data, you only have to enter it once, then just select via a combobox or something like that to get the data into your tagnumber table. Once the exhibitor data is entered, use a JOIN of the Address table to the TagNumber table to be able to look up ANY information about the exhibitor.

The weight classes can be handled this way, more or less:

This is not a true JOIN but it is still possible to write a query that pulls records from two tables at the same time. Write a SELECT query against the tagnumber table and a third class lookup table. (But this is not a LOOKUP table in the sense of the Access LOOKUP field.)

The ClassLookup table will have:
LowWt, number
HighWt, number
WtClass, number

OK, now
SELECT Exhibitor, TagNo, Weight, PLCN, Money, WtClass
FROM TagNumber, ClassLookup
WHERE TagNumber.Weight BETWEEN ClassLookup.LowWt AND ClassLookup.HighWt
ORDER BY WtClass, (whatever else you are ordering by...);

Note also that if you did the normalization, you can use the query that does the JOIN of Address and TagNumber in place of the isolated TagNumber table here. OR you can choose THIS query and join it back to Address to get the Exhibitor Info. Either order is immaterial. This works because all the query wants for the two items named in the FROM clause is two RECORDSOURCES (Recordsets). Queries and Tables qualify equally for this purpose.

You can also use this query to update any field in the TagNumber table EXCEPT don't muck with the weight field because that materially participates in the WHERE clause. So even if you open it from a form, you can edit anything you want like PLCN or Money. Further, if PLCN is ALSO coded such that money can be looked up based on PLCN, you can join the PLCN field to a money lookup table to show the final standings on a report. In that case, it WOULD be a straight JOIN of the fields. I would avoid making an edit to a field that governs the lookup because you need to force a requery if that happens.

The beauty of this is that you only edit the ClassLookup weights to change the range of the weight classes. Or ADD a weight class, or REMOVE one. Ditto if the PLCN is also a lookup. Do something similar for age, I guess, if it is a class-oriented range.

Look up the BETWEEN ... AND operator in Access help. You will have to make the weight ranges non-overlapping or you will get duplicated entries in two different weight classes.

The basic principle here is "divide and conquer" - by creating queries to do PART of the work and then putting them together to provide the finished result. You will be pleased at how smoothly this will work if you are careful about the table setup and relationships. The only linkage that CANNOT be a formal relationship is the one involving a BETWEEN operator. A true JOIN wants EQUAL or it barfs (I think, unless something has been updated lately and I missed it.)

 

[audrey]

Doc Man, you are an Access God, lol. That worked perfectly. Here's what I've got now (for future reference):

3 Tables: Exhibitors (replaced tbl Address), Livestock (replaced tbl TagNumber), and wtLookup (with the fields you suggested)

3 Queries: Ex_Liv_Join (to join the Exhibitors table with the Livestock tbl), qry_wtLookup (the pseudo-lookup query you suggested creating) and qry_class_view (based on qry_wtLookup with the wtClass parameters set to the value of a combobox on the main form)

On the main form, the user chooses a weight class from the combobox then clicks a button to open qry_class_view which displays only the entries in that class.

PERFECT... thanks to you. Now I have 3 queries instead of 300! AND, the user can add or remove (as you said) as many classes as he likes instead of having a set number of classes.

This "puppy" went to Cujo, but now it's back to a puppy again.

 

[The_Doc_Man]

Glad to help. Sounds like you've learned how to tame this particular beastie.

 

[audrey]

Uhhh... New problem. When i try to edit the placings through the query I get the "recordset not updateable" message. Now, I did a lot of searching and saw where this could happen if you 1) have more than 2 queries being referenced or 2) aggregate functions in one of the queries.

What Doc Man suggested for organization was perfect, except for the query not being updateable. Here's what I've got going:

Tables:
Livestock (stores livestock data, i.e. tag No., weight, gender, etc)
Exhibitors (Stores exhibitor data, i.e. name, address, age, etc)
wtLookup (stores weight class, class beg. weight, class end weight)
(all have primary ID's)

Relationships:
One-To-Many from Exhibitors table ID to Exhibitor ID in Livestock table
(because one kid can have multiple animals)

Queries:
ex_liv_join (supposed to "join" exhibitors table with livestock table)

Code:
SELECT exhibitors.Name, livestock.[Tag No], exhibitors.Address, exhibitors.City, exhibitors.State, exhibitors.Zip
FROM exhibitors LEFT JOIN livestock ON exhibitors.ID = livestock.ExhibitorID;

qry_wtLookup (Puts livestock to a weight class according to it's weight)

Code:
SELECT Livestock.[Tag No], Livestock.Weight, Livestock.Placing, Livestock.Money, wtLookup.wtClass, ex_liv_join.Name, ex_liv_join.Address, ex_liv_join.City, ex_liv_join.State, ex_liv_join.Zip, Livestock.Placing, Livestock.Money
FROM wtLookup, Livestock INNER JOIN ex_liv_join ON Livestock.[Tag No] = ex_liv_join.[Tag No]
WHERE (((Livestock.Weight) Between [wtLookup]![lowWt] And [wtLookup]![highWt]));

qry_class_view (takes the value of a combobox on the main form to select which class to view so that placings can be entered)

Code:
SELECT qry_wtLookup.[Tag No], qry_wtLookup.Weight, qry_wtLookup.Placing, qry_wtLookup.Money, qry_wtLookup.wtClass, qry_wtLookup.Name
FROM qry_wtLookup
WHERE (((qry_wtLookup.wtClass)=[Forms]![frm_main]![view_class]));


Like I said, this all works great, except I can't change the placings in qry_class_view - get recordset not updateable message. This is probably because of all the queries and tables that I've got going on.

Is there anyway around this or do I need to do a complete makeover???

Thanks so much.

 

[audrey]

Ok, again for future reference...

I found a way around the "recordset not updateable" issue:

I created a form with qry_class_view as its source. Then on the new form I set the Recorset Type property to "Dynaset Inconsistent Updates)"..... and viola!! I can now update the queries as needed. Thanks to all for your help and especially to the Doc man!