Clud DB - has to be a better solution

[telephoto]

I know you don't design dbs for questioners - but I have a db, it runs in Access 2003, I just want it better.

Simplified situation:
The db is a record of club sporting events taking place in the current year, there are two tables:
Table 1 contains serial no, date, event name, home or away.
Table 2 contains club member's name,and every date when an event takes place (this set of fields are all yes/no check boxes, that's 72 fields of check boxes!)

Each year I print a form with every event date shown as a label, and check boxes for availability. Members check the box if they can play and return it to me.

On receipt I open the form, select that member and check the boxes.

I can now print reports showing who is available for the various events, displaying dates across the page, members names down it and a check box tick in the columns.

PROBLEM
It works well but each year ALL the dates and event names change, meaning I have to relabel and reset record sources on 10 forms and records, as well as renaming all the date fields in the member table (no 2)

The events will change, and I use Excel to sort them and then import the data into table 1, but the aggravation each year is changing form and record labels, and resetting record source for the check boxes - there must be a better way of structuring or using the data, and I'd love to know what it could be. Any-one any ideas on this please?

Telephoto

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[telephoto]

Hi PH

Thanks for the response, I am familiar with relational dbs - I have set up a number of them. The reference was interesting as the levels of normalisation concept was explained.

This db is annoying because the dates change each year. Feb 21 this year is on a Tuesday, next year it is Monday. Some events are only on Mon and Fridays, another group is on Tues and Thurs.
For each of these the dates allocated to the check boxes have to change.

I have to set up a new table of events with the date as a field of a record, are you suggesting 72 links between two tables??!!

Is there some way I can programatically use the date data in the events table (table1) to set the recordsource on the checkboxes which link back to table 2 - which lists each members availability for that date?
This would save me a lot of detail amendments (and subsequent checking) each year.

Specific dates can be identified by query from table 1 as there is a field detailing the events in each league.

Telephoto

 

[papadba]

"I know you don't design dbs for questioners"
If you mean ad-hoc queries, sure we do. . . These can be serviced by the "real" database (where transactions happen) or what has become known as a "data warehouse".

Hopefully, these forms are created as needed rather than wholesale and are indivudually customized for the specifics of the event.

If your goal is to use a database to support this, suggest you do all of the "maintenance" within the database and if there is a need to provide something in a spreadsheet, extract the needed data from the database and load it into a spreadsheet.

 

[telephoto]

I know I could get help with a query design - I've done it before, I just mean I don't expect someone to design the whole db!


"Hopefully, these forms are created as needed" No problem with the concept, I've just never got that far with VBA


Nothing is exported to spreadsheet.
I use Excel to set up the data just because it's quicker, then it's used as mail merge source for the handbook, then it's discarded.
All info from the db is sent to reports based on queries/SQL.
EG list all home games (for the ground keeper)
or List all available for Mon and Fri games (by date, for the team selectors).

T

 

[randy700]

Table 2 contains club member's name,and every date when an event takes place (this set of fields are all yes/no check boxes, that's [blue]72 fields of check boxes![/blue])

I am familiar with relational dbs - I have set up a number of them

OK, then where is your EVENTS table?
You might consider re-reading the link PHV provided.




Randy

 

[telephoto]

Steady Randy

First post refers to table 1, each record contains serial no (key), date, event, home or away, (and, for completeness, the league or type of match).

Table2 and its associated forms and records are the problem, as each record contains members name, league in which they play, and every date on which there is a game. In other words each date is a field of the members record, to show when they can play.

There is just one place where an additional table could be used, that would be a table showing the leagues with one to many links to tables 1&2. In practice I can't see an advantage, and this would just be aggravation. No links are shown directly between tables, any required are set up in queries.

Does that explain it better?

T

 

[JoeAtWork]

Telephoto

PHV and Randy understand your problem perfectly, as do I. Your database isn't normalized - the symptom is that you are putting the same information (i.e. the event date) in multiple places. You need an additional table in between your two existing tables that relates the Event to the Club Member. Your Yes/No fields will be in this middle table (along with the key fields to relate to the Event and the Club Member).

Using this design you will never need to change the schema of your database. Each year you simply add the new Events to your table. Your report that you print out to your players will be based on a query of the middle table joined to the Events table (it will need to be an outer join) filtered by the Event Date for that current year.

However, it sounds like you will have to change some of your existing forms as well, specifically the one where you enter who is attending what event.

 

[flyboytim]

There is nothing actually wrong with using a date value as a key field - it is stored as a serial number after all (though not an integer), but it is cumbersome and error-prone if entered manually, but not so if offered as a lookup value in a form/table.
However, for flexibility, three tables are needed - one for events, one for members, and one to link members to events. Events and Members both have one to many joins to the third table.

What would I want to do as administrator?:

EVENTS for the coming year (new table each year)
Fill in the name of the event/opponents, maybe the time – am, pm, eve, - the venue, and click the event date off a calendar.
Possibly need to reschedule an event due to unforeseen problems and inform members?

Requirements: Onscreen form with calendar object for date selection
->(TblEvents2011)

MEMBERS
Add/remove members

Requirements: Onscreen form, Printed membership form for new members
->(TblMembers)

SUBSCRIPTIONS?

PRINTED QUESTIONNAIRES for potential players (different each year)
Report (or Form) for printing
MemberName MemberID (in case there are 2 Mike Smiths) DOB (just checking!)
EVENT 1 Date Day of Week TimeOfDay Venue Participate? Checkbox
EVENT2 …

and so on down to
EVENT72…

data from TblMembers - mainform header
data from TblEvents2011 - subform body

Members required to check events where playing, leave blank if not.

DATA ENTRY FROM RETURNED QUESTIONNAIRES
Onscreen data entry form: associates participating members with particular match events – could use a mask overlay for the printed returned forms during data entry so that checked boxes align with event ID numbers for less error-prone data entry:

MemberName MemberID DOB
Event ID Checkbox
Or
Enter event ID No. …… OK Finished
->(TblPlayers2011)

?
Tables:

Date (Inbuilt date, can be used to calculate day of week etc)
EventSerial(key)
EventName
Home/Away
Time of Day?
EventOtherInfo*	- links to associated data (Location, principal contacts, results of past matches, statistics,etc)

Onscreen FrmMember for administration to enter data into membership table:

MemberID(Key)
MemberOtherInfo*(Name,	Surname, DOB, contact details, game strengths etc)

(* XxxxOtherInfo can be a whole load of separate fields within the tables)

Onscreen form for data entry from FrmQuestionnaire2011 feeds directly into:

MemberID(Key)?
EventSerial(key)?

joins participating members(many) to events(many)

Finally some SQL queries to transform the above data into input with the correct fieldnames and structure for the existing reports that the OP has already and is happy with.

http://www.fxxxingcomputers.co.uk

 

[JoeAtWork]

Flyboytim - why create a new Events table every year? That's an unnormalized approach, and requires changing (or creating new) forms and reports at the beginning of each year. If you use one Events table you only need to set everything up once. Your queries for reports and forms can be filtered for the current year.

 

[flyboytim]

You may be right, Joe, there are many ways of skinning a cat. Ask the OP why you need to set up the events table every year - I am assuming that the leagues and fixtures change annually, and not several years in advance, although a season may cover part of one year and some of the next one.

Form does sometimes need to follow function and not blind normalization. In this case the function covers the coming year of events as far as we have been advised.

By keeping the working events table to a single year it avoids complication and keeps the queries short without reduplicating "where [year(date)= 2011]" type statements.

I has thought about using a single Events table for all years, but it seemed unnecessarily cumbersome and error-prone - such an archive table of past events is easily created with an append query at the end of each year.

http://www.fxxxingcomputers.co.uk

 

[flyboytim]

As you say, forms and reports can be based on queries, rather than tables. If it is a select query like "select * from TblEvents2010" it is trivial to change the SQL to "select * from TblEvents2011".

You certainly do not have to change the forms or reports to reflect the change of data source.

http://www.fxxxingcomputers.co.uk

 

[dhookom]

flyboytim,
I would have to agree with JoeAtWork. IMO, separate tables for each year would be a mistake. It is much easier to filter a single table by year than manage multiple tables.

Duane
Hook'D on Access
MS Access MVP

 

[telephoto]

Hi Joe - either at work or at home...
And to you Tim

Joe, Thanks for a more explicit explanation, not a way I had looked at before, but I want to explore your date table concept. This would be easily renewable each year from the Excel sheet I use to compile the events.
I use Excel because I find it the easiest way to collate the information, probably because I know the program - if the system works don't mend it.

Tim, You are right, I do need to compile fresh each year, because of three reasons:
1: The events change, from year to year matches against opponents can be on different days in different months. If a team is promoted then all matches would change, and in any case there is a promotion/demotion from the divisions at season end.

2: There a occasions when you have more than one match on the same day. On these occasions the second match date has a suffix "Thu 12 May BHP" where BHP is the competition.

3: From year to year specific dates fall on different weekdays.

Changing form and report data source - that's what is causing all the aggro!

Tim "Form does sometimes need to follow function " I love it! That's why I have set it up as I have (and that's why I have the problem!). The tables are in one db - but they are never used together. The events table is used for producing lists of games and displays for the skips to insert players names (using a biro!), the other table for giving availability by date - the event name is never used as the availability form is issued with a handbook showing all events. This enables me to get all dates over three different leagues onto one sheet of A4.

Like your expansion idea Tim, however I have developed a db for the treasurer (of a different club), and using that I could have the tblMembers as a linked table to the other db. Treasurer knows who has paid and keeps the member list up to date (one job less for me!).

If you wanted to see the forms I use I could put one up on the club website

Thanks both for your input, I really do feel a redesign coming on, and I will be attempting to use some of the suggestions you have made.
As ever I want to retain the option to ask for more help!!!

If you have any more thoughts, then please reply.


Telephoto

 

[telephoto]

Duane, Joe

As you can see above, I have to reset each year, there is no other option.

T

 

[dhookom]

It's your application and you can perform the maintenance each year. I don't typically deliver any application that requires structure modifications as part of the plan.

Good luck.

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

As you can see above, I have to reset each year, there is no other option.

I disagree. Add the new table(s) that you need and set up the relationships properly and you should never need to touch the schema of your database again.

In fact, if you do a little more advanced design to the interface you wouldn't even need to change the queries that the forms and reports use. For example, on your form you could make a Year dropdown (2008,2009,2010,2011, etc.) and the query for your form would use the dropdown value as a filter. You could launch reports that dynamically filter this way as well.

 

[Andrzejek]

From your OP:

The events will change, and I use Excel to sort them and then import the data into table 1

I can understand using Excel because you know it, but you can do the same in your program in the code and by-pass creating it in Excel and moving the data into your DB.

As far as sorting the data before import to a table - data in DB tables is NOT sorted. I know it looks this way any time you look at your data, but unless you sort it when you query it - you can never be sure you get it properly sorted. So if you want to get it sorted when you request it from your DB - always use [tt]ORDER BY <fieldname>[/tt]

Have fun.

---- Andy