Autofill field from previous fields entered

[jucooper1]

I am fairly new to Access, first off. I am a football historian and I collect data on coaches using Access, things like where a coach coached in what season, what his position was, wins/losses, etc. One thing I use this database for is to look for relationships between coaches like who has coached for this coach or who was on so-and-so's staff. What I would like to be able to do in access is when I am entering information about a coach, I would like for Access to autofill the "head coach" field based on the information I entered previously (ie, season, and team). I already have a lot of that information entered and I would like for it to fill automatically. To give an example Joe Smith was the head coach at Football University in 2015, this is already entered into the database. Now I am entering data for Jeff Wilson, who was an assistant for Joe Smith at Football University in 2015. First thing I enter is the season "2015", then the coach "Jeff Wilson" then the team "Football University" then his position "assistant coach." The next field is who his head coach was. I would like to set it up, if at all possible, for Access to see the "2015," "Football Univerity" and "assistant coach" (or in reality NOT "head coach") and the populate the head coach field from this information. Is this possible and how would I do it. Just consider me a complete newbie. Thank you for any help!

[Micron]

If you're the main user of this db and can enter data in a specific order every time, then it should not be too hard. The general idea of the syntax of a query for getting the head coach might be like SELECT headcoach FROM tblYourTable (or query) WHERE TheYear = 2015 AND TheCoach = "Jeff Wilson" AND TheUniv = "Football". You'd set this query as the source for the control you want to autofill and would have to decide on what triggers this update. If it's the AfterUpdate event of the last control to supply the criteria, then it should work if you do things in order. However, the criteria I typed in is not actually referenced that way, since the values likely have to come from your form controls, but I hope you get the idea. Also, not sure if you're creating a record on a form, and if the form is bound to anything. If it is, the 'calculated' field (for lack of a better word at the moment) cannot be bound to that source, so now you're faced with a means of applying the calculation. That may have to be part of the code or macro that auto fills your control and applies the update.

[jucooper1]

Micron, thanks for your suggestions. I understand part of what you're saying and in general I think I know how it should be set up, but I'm not sure exactly how to execute it. You can kinda see my setup from the picture. Do I make the "Head Coach" field some type of special field or would I need to create a form to do this? The Season, Coach, Team and Head Coach fields are pulled from respective "master" tables to create a controlled vocabulary. I don't know if this makes a difference or not.

[Micron]

Copy your form for playing around. Create a query that will look up the correct value for you when you have supplied the other values to the other controls. The criteria row for those fields will reference the form controls, such as Forms!frmYourForm.YourControl. Try a few examples and see if the results make sense. When ok, copy the sql statement and paste it into the control source in design view. That control (the one you want to auto populate) has to be unbound. On the AfterUpdate event of the last control that will be populated in the sequence, you will write the value to the table, either using vba or a macro (I'm not a big macro user), or executes an update query you have also created. The latter method is usually easier, but if going that way, I'd create that query and see if it runs while you have the form open with these fields filled in. It is possible that your table structure would not allow the update, but I don't see why not at the moment. The auto control should automatically update (I think) but if not, this same after update event could take care of that.

Another thought is to make some, or at least the last control in this series, into combo boxes if it makes sense to narrow down the list of possibilities as you make a selection in the one before it. Not sure if that's of any use to you - just thought I'd throw it out there. It's generally referred to as cascading combo boxes if you want to look up more about it.

[jucooper1]

I can't seem to make it work. I tried what you suggested and the cascading combo boxes and on both of them, I get blanks on the second combo box. I'm not sure what I am doing wrong or what to do to fix it.

[Micron]

Can you compact your db then post a zipped copy of it?

[jucooper1]

Here it is.

[ssanfu]

Before moving forward, I would suggest fixing some problems with a few of the tables.

1) "State" is a reserved word in Access. I use "ST"... ("StadiumsT" & "TeamsT")
2) In "CoachesIDT", in the field "CoahesFirstName" "Coaches" is misspelled.
3) Object names should be letters and numbers, NO spaces, punctuation or special characters (exception is the underscore)
4) In table "TeamsT": should not have "/" in field names. (University/Team)
5) In table "EntriesT": should not have "%" in field names. (Win%, CWin%, PWin%)
6) In table "GamesT": should not have "#" as any part of a field name. (#, O#) I am really surprised that Access allowed a field name of "#".
7) Back to "CoachesIDT"... "CoachesID" (Autonumber) should be the PK field.
"CoachesFirstName" and "CoachesLastName" should be a compound Index, not a compound PK.

8) The relationship between tables "CoachesIDT" and "TeamsT" is between "TeamsT.University/Team" (Text) and "CoachesIDT.AlmaMater" (Number-Long). The relationship should be between "TeamsT.TeamID" and "CoachesIDT.AlmaMater".

9) The relationship between tables "PositionsT" and "EntriesT" is between "PositionsT.Position" (Text) and "EntriesT.Position1", "EntriesT.Position2" and "EntriesT.Position3" (Number fields). The link should be between "PositionsT.PosID" and "EntriesT.Position1", "EntriesT.Position2" and "EntriesT.Position3"

10) There seems to be extra (table) links to "EntriesT.Position2" ("PositionsT_1") and "EntriesT.Position3" ("PositionsT_2").


Personal opinion - I NEVER have calculated fields in tables.
EDIT: I also NEVER use Look-up FIELDS in table.
I understand why you have them (because of the datasheet view), but since I never use forms in Datasheet view, ....


Personal opinion 2 - I recommend that PK fields have the suffix of "_PK" and FK fields have the suffix of "_FK". Much easier to see that the linking fields are linked correctly.


------------------------------------------------

<snip> What I would like to be able to do in access is when I am entering information about a coach, I would like for Access to autofill the "head coach" field based on the information I entered previously (ie, season, and team). I already have a lot of that information entered and I would like for it to fill automatically. To give an example Joe Smith was the head coach at Football University in 2015, this is already entered into the database. Now I am entering data for Jeff Wilson, who was an assistant for Joe Smith at Football University in 2015. First thing I enter is the season "2015", then the coach "Jeff Wilson" then the team "Football University" then his position "assistant coach." The next field is who his head coach was. I would like to set it up, if at all possible, for Access to see the "2015," "Football Univerity" and "assistant coach" (or in reality NOT "head coach") and the populate the head coach field from this information.

I don't see any way to find the "Head Coach" from your explanation. Most queries return more than one record.

Do you have a real world example where you could provide a Season, the Team and the Position (Position1 or Position2 or Position3???) to return the head coach?

[Micron]

I can't open it because of incompatible table features.

[ssanfu]

@Micron
I re-saved the original dB. Can you open this one?


Edited........ Yes there are calculated fields in some of the tables..

[jucooper1]

If you were to do a query from the EntriesT and set season to "147", leave the Coach field blank, set team to "1" and Position1 to "1" you should get Chuck Martin. So what I would like to happen then is when I'm entering in a member of his staff it pulls this information and puts it into the Head Coach field.

Those changes you suggested, do they really make much of a difference? And I mean that from an educational standpoint, like how does it affect the database. Also, with the relationships, I have them set that way so I can control the vocabulary and have a list to pull from. Is that not good? I use the datasheet view because a lot of information is repeated across seasons and its easier to copy and paste down the line, then doing each season independently.

[Micron]

Sorry, I meant can't open some tables, forms, queries. I get a general message about incompatibility when opening the db. Still can't, but thanks for trying. Maybe they use calculated table fields.

[ssanfu]

Those changes you suggested, do they really make much of a difference?

2) is just a spelling error. Not Important

10) Not so important

1, 3, 4, 5, 6, 7, 8, 9) are VERY important.

Personal opinion 1: Most experienced programmers do not use calculated fields nor Look up fields in tables.
See
The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm

The Ten Commandments of Access
http://access.mvps.org/access/tencommandments.htm


Personal opinion 2: This just makes it easier to see/follow PK/FK relationships.

[ssanfu]

Deleted.....

[jucooper1]

So, how do you use a controlled vocabulary without look up fields?