Creating a grid on a form

[Megacitizen]

Hi, new member here. I've not used Microsoft Access before and am not that conversant with it's relevant lingo, so can you please keep your answers simple. I don't mind confessing that I'm a bit of a computer thicko!

I'm trying to create a database which includes a gridform to record statistical information on a series of activities performed by various teams over a period of time. I have my activities down one side and number of weeks across the top.

My first attempt had numbers being repeated across a line or down a row, when each box should be unique. Do I have to create a unique field for each individual box. At 13 activities over 11 weeks, that's 143 boxes to create. My table would be extremely long.

Also, I'm trying to create a subform storing basic info on individual team members, which will be hidden on the main form but accessible by clicking on a specific field or button. How do I set that up.

 

[plog]

The first part of setting up an access database is properly structuring your tables. This process is called normalization (http://mikehillyer.com/articles/an-introduction-to-database-normalization/).

Start there and then come back with any specific questions you have.

 

[Megacitizen]

Thank you for the information, although I must admit my eys did start glazing over somewhere near the top of the article - between the first subtitle "Introduction" and the subsequent paragraph, if you must know -

My task is to build a database that records the various activities of numerous teams in a particular city, over a given number of weeks. The grid will be just one part of the form.

EG. Team 1 has performed during Week 1, X of Activity 1, Y of Activity 2 and Z of Activity 3. For weeks 2 and 3, the activities are repeated but the resultant amounts may or may not differ.

The rest of the form, aside from the grid will give info on stuff like name of team, where they're from, where they are operating and when.

 

[rodmc]

Hi Megacitizen

re plog's link - Although It may look daunting but that really is the "must know" stuff of databases, without it you will really struggle to get what you want out of it.

If you follow the normalisation rules, then when you come here for help you wont have to restructure your tables and such, as the members here will ask you to fix those problems first before going to fixing more complex problems that you may have.

OK, so what do you have currently in the way of tables and relations?

 

[Megacitizen]

Thanks Rod

At the moment I have two tables, one I intend to be the subform, which I want hidden behind a button rather than displayed on the main form.

As for the other, I get confused as to what info needs to be 1NF, 2NF, 3NF, etc. Basically, each sheet of my DB should display the following info:-

Team Name
Base
Base Location
Team Size
(The subform, containing basic info about individual team members is intended to be hidden behind one of these fields above)

Outreach Location
Year
Wave No.
Borough
Arrival and Departure dates

There will also be a checklist of whether a team has made an enquiry, applied to go, been accepted, arrived in city or already been and gone, with relevant dates for each stage. I then have my grid, as mentioned in an above post - 13 activities x 11 weeks = 143 fields. There will also be 4 text boxes, 3 for short texts (a few words or a sentence) and one for memo format, which will be unique for each team.

The numbers entered into the Stats grid will be what we are going to be most interested in analysing. The rest of the info is really on a "who-what-where-when" basis, which I already have pretty much sorted out as far as I know.

The Grid is the only bit I'm concerned about. I'm not sure what fields I have to relate to each other to make each box unique. My first attempt had entire lines or rows with the same information when I entered a number into the first box. After unbinding the boxes, I could enter my data in each box, but found the info wouldn't store when I shut down for the evening and restarted again the next morning. Each week's info should be independent of other weeks, likewise the activities, on each sheet.

The whole idea is to record each team's activities over an 11 week period in their respective outreach location, viewed on one sheet, although we are intending to use the same sheet from initial enquiry right the way through to final departure from city, updating regularly as teams move through each stage.

The GOOD new is, the DB I'm currently building is a test case only, the real McCoy will be developed only once I know how the thing works and how I can get it to work. No-one else at my base has a clue about constructing such DBs either. The last one we did was basically text and checklists only, and was used for storing testimonies.

Hope that's not too much info for you

 

[plog]

Like any discipline, databases have their own set of jargon to convey ideas (3NF, relational database, normalization, etc.). Don't worry about the words as much as the ideas.

Essentially, you want to break your data into discrete entities (teams, team members, activities etc.) which become tables. In those tables you want to capture discrete data about each entity (team name, member name, activity name, etc.) which become the fields of your tables.

Don't even think about forms, subforms until you have your tables properly structured into entities and the proper fields of those entitites. If you get that part correct, forms and sub-forms will come easily (ok, not as hard).

What entities do you think you have? From what you've said I see these:

Teams
Team Members
ActivityTypes
Activities

You also brought up Outreach Locations. Can a team have more than one Outreach Location?

FYI, that's a very important question in data normalization: Can [Entity1Name] have more than one [PossibleEntity]? If the answer is yes, then you probably need to make PossibleEntity its own table. If not, you can probably include PossibleEntity as fields in Entity1Name.

 

[rodmc]

Plog's put it better than I could, get the normalisation right and you'll never go wrong.

I would say you would need a table (in addition to the ones plog has mentioned) for Enquiries as one would imagine a team members or teams would need to make more than one enquiry and the enquiries related data (e.g. date of enquiry, outcome etc) would have to be stored with the enquiry, yes?

See we're getting there now and have a possible 6 tables, how many did you reckon you would have originally?

 

[Megacitizen]

Plog, thanks for breaking explanations down more, it's starting to make sense. BTW, we don't have an "ActivityType" field, just "Activity". All teams stats will be based on anything between 1 and 13 activities each week. Where 1 particular activity is NOT performed in any one week, then a ZERO value would be entered into the relevant box. (It's possible that one or more activity could get a ZERO across all 11 weeks.) The 13 activities have already been defined. Any actions outside of those listed will NOT be recorded as they are not relevant to the records we will be keeping.

Generally, each team attending will be based in one borough only, although a couple of teams will be moving from borough to borough during their trip. Most teams will only be in the city for 7 weeks only, either weeks 1-7 or 5-11. The teams moving from borough to borough will be in the city for the full 11 weeks. I think we'll be writing a new sheet for each borough, where those teams are concerned. There will be occasions where two or more teams join together in one borough on a particular day, but those stats will only be recorded as part of the records for the team already in that borough.

Hope that's enough information to provide more guidance.

 

[Megacitizen]

Rod,

I'm not sure we need a seperate table for Enquiries. If a potential team makes an enquiry about joining up, that enquiry will be recorded as such and won't change until they actually send an Application, irrespective of how many enquiries they make. The two are seperate entities. If a team enquires but progresses no further, our sheet will record only the enquiry and no more. The same applies for teams that DO apply but don't end up travelling.

Of course, we always hope that EVERY team enquiring will go through the ENTIRE process and actually GO, but that won't always be the case.

 

[Megacitizen]

OK guys, thanks for your help so far. I think I'm getting the hang of the various tables, it's working out the correct relationships next

Next question though is regarding the chexkbox bit of our Enquiries table. The users will want the checkboxes to be filled as each process of application is reached, but NOT unchecked as the next stage is reached each time. We are looking at having 6 boxes checked on a completed form, rather than the last box only. By binding each box to a field (as indicated on another thread query), does that allow for boxes to remain checked as more checks are completed?

 

[rodmc]

again using an IF statement will do that for you, if condition met (ie true/false) checkbox = true

very simplified, but it will work

 

[Megacitizen]

One thing I didn't mention on the OP (because I didn't think to do so at the time but now realise I should really have) is that the user(s) of this DB will appending records and running queries off the same form ie the formsheet rather than a table will be used for data entry purposes in adding new records. The same form will also be used for running queries.

Does this make any difference in how I programme the DB?

 

[Megacitizen]

Huge thanks to those who have helped so far, especially those who've been posting links to videos and tutorials on other threads, which I've been able to view and learn bits from

I've managed to get my info into 5 tables so far, but I fear I may have wrongly categorised some of the fields, meaning they would have to be re-ordered. As far as I can tell, all table to table relationships will be many to many.

So far I have the following:-

Team
Team Name
Base
Base Location
Team Member
Nationality

Outreach City
City
Year
Wave
Borough
Date From
Date To

Application Process (Checkbox/Date only)
Enquiry
Date Enquired
Applied
Date Applied
Accepted
Date...
In City
Date...
Been there
Date...
Thankyou Letter
Date sent.

Statistics
Fields for each activity in each week - 1 box per activity per week = 143 boxes in total

Extras (General Text boxes)
Word
Giftings
Other
Notes/Reports

In nearly all cases, searches will made on Teams and Bases. I have not created Team_ID or Base_ID as we prefer to identify teams by their school names, although it is theoretically possible for two or more bases to run schools with exactly the same name. In such cases, we identify the team by the base they came from. Where schools split to make more than one team, we also have ways of identifying each one.

Is there any way to turn off the autoID in our tables as we only really want it showing on our MainForm.

Your help greatly appreciated

 

[Megacitizen]

A bit of a side issue here, whilst dragging my fields onto my form in Design View, I notice that Access constantly opens ALL table lists when I want to keep open only the table I'm dragging fields from and keep minimised all other table lists. Some of the tables have hundreds of fields to work with and it's a pain having to constantly minimise those lists I'm not working on, before scrolling down to the fields I want.

Is there a way I can keep lists minimised until I need them?

 

[Megacitizen]

Just when I think I understand what I'm supposed to be doing, someone comes along and informs me I've been getting the whole thing wrong -again!!! Not getting at you btw Pat, I'm just grateful that someone is brave enough to attempt to educate a complete idiot like me

I suppose my next question would be How do I know if and when my tables are being correctly normalised? or something like that. What information needs to be going into which table and which areas need to have relationships.

Our end goal is to have each record visible in single sheet form, showing all our "Who, what, when, where and how?" queries in one view.

 

[Megacitizen]

Thanks for your help and advice so far Pat, much appreciated. I would love to post up various stages of my work, however we had some issues with our server last week, resulting in all our work since last Wednesday NOT being saved. I'm now having to start again from scratch, this time on my own computer and will transfer the final work onto the Base Server when it's finished. Good job we've had a long weekend for me to forget about things for a few days and relax.

Now, in starting again, do I write everything out as before so that my errors can be pointed out and laughed at.... I mean corrected, or should I work on trying to find the correct solutions straight away?

 

[Megacitizen]

Someone on here posted a link in an earlier thread, to a page that lists all the various Reserved Words and their reasons for reservation. However, I had already managed to suss out the field naming issues quite early on. Most of our preferred field names won't be affected by the rules as it were. As far as we are concerned, the only issues would be with dates, so I decided to use a DateAct formula (Date_Enq; _App; _Acc; etc) to differentiate the various stages of Application. We are dealing with Teams more than individuals, so TeamName and/or SchName have already been chosen. Where individuals are concerned, we are looking to put those in a hidden table, behind a button or something, but related to Team or School Name. We would probably only have two fields in that table, Team Member and Nationality - we are only interested in the latter of those two.

With regard to Dates, there will be one set of fields where we only want to show the day & month and NOT the year, as that is displayed alone in another field, ie "wc 'dd/mmm' ". I take it that's fairly easy to set up (but maybe not with that string).

Overall, would I be right in thinking I need more tables and fewer fields or columns in each?

 

[Megacitizen]

This is what I've managed so far with tables and relationships. Where am I going wrong?

 

[plog]

Without completely understanding your data, there might be more, but here's what I found wrong:

City---You have a primary key field (MC City), but when establishing links to other tables you use the CityBorough field.

Weeks---Numerated fieldnames (Week1, Week2, etc) are the telltale sign of an improperly structured database. Weeknumber (or possibly a beginning date of each week) should be its own field within this table

TeamName---As it stands now, TeamID in TeamName links to CityBorough in City. That doesn't sound right.

TeamMembers/TeamName---This should be 3 tables: Players, TeamName, TeamRoster
Players would have a PlayerID, PlayerName, Nationality
TeamName would remain the same
TeamRoster would consist of 2 fields: PlayerId from Players and TeamId from TeamName

 

[Megacitizen]

Thanks Plog, everything's slowly starting to make sense. The constant drip, drip, drip method is usually the most effective way for me to learn, although it's not the most efficient.

With regard to my Weeks table, I think I probably need to make that relational to the Wave field in the City table, and then try to rename it Wave. (I notice that once I give a name to a table and save it, I cannot go back and edit it at a later time, should the want or need arise. Is that right, or is it just my system?) Our eventual aim is to show that data in grid form, similar in style to the example Pat Harman posted in this thread last week, most likely a subform with an excel-style view, showing date (labelled Week1 etc and displaying the actual date below the title) as columns and activities as rows. It's not me making these requests btw, it's what my superiors are seeking.

I've made a couple of alterations as suggested (left the week1 etc fields in the Weeks Table - ideally want to re-title that one "Wave" - until I know what I need to do with them, and how). Am I on the right tracks here?