Solved SPEECHLESS IN NEW ENGLAND (1 Viewer)

[esturgesjr]

Just when I thought I'd seen everything, I get this .... the yellow highlighted field should be the field in the list circled in red. Totally unsure how this error came about. I delete the field from the form and replace it with the correct field (the errant field stays in the Field List) and as soon as I click on Save, the field changes back to Expr1011. I try selecting the field and choosing the correct field in the dropdown ... same result. I deleted the form and imported the form from a backup version in Dropbox ... when I open the backup form ... same result. What in Hades is going on here? As the post title says, I am speechless ... well, not really, but all of it would be censored! Any ideas would be welcomed!

 

[Gasman]

Export as text and reimport perhaps?
Upload enough of the DB to see the issue?

 

[Mark_]

Could be a corrupted form.
If Gasman's suggestion doesn't work, I'd suggest recreating. When you do, I'd also look at your data source to figure out what EXPR1011 is. That looks like it is referencing something that no longer exists and should be cleared out also.

Separate item you may want to look at in the very near future is moving all of your training requirements into a child table. For some reason those mandatory trainings keep getting added and never removed. Best to address that early before it becomes a real problem.

 

[esturgesjr]

Here's the SQL behind the form; didn't see anything in here that jumped out. The training is in a separate table as you'll probably see in the code. I'll try recreating the form. Thanks!

SELECT Personnel.AFD_ID, Personnel.[Last Name], Personnel.[First Name], Personnel.Email, Personnel.Phone, Personnel.DOB, Personnel.StartDate, Personnel.TermDate, Personnel.SecStartDate, Personnel.SecTermDate, Personnel.Station, Personnel.Rank, Personnel.Unit, Personnel.Position, Personnel.HomeAddress, Personnel.Status, Personnel.Rank, RT_NIMS.[Tot NIMS], RT_PhysicalsExp.[Expiration Date], RT_PhysicalsExp.[Training Certification] AS [Training Certification_PhysicalsExp], [RT_Blood Borne Cert].[Expiration Date] AS [Expiration Date_Blood Borne Cert], [RT_HazMat Cert].[Expiration Date] AS [Expiration Date_HazMat Cert], [RT_Mask FIT Test].[Expiration Date] AS [Expiration Date_Mask FIT Test], RT_RequiredTraining.[Tot Classes], RT_LiveBurns.[Tot Burns], RT_Drivers.Apparatus
FROM (((((((Personnel LEFT JOIN RT_NIMS ON Personnel.AFD_ID = RT_NIMS.ID) LEFT JOIN RT_PhysicalsExp ON Personnel.AFD_ID = RT_PhysicalsExp.ID) LEFT JOIN [RT_Blood Borne Cert] ON Personnel.AFD_ID = [RT_Blood Borne Cert].ID) LEFT JOIN [RT_HazMat Cert] ON Personnel.AFD_ID = [RT_HazMat Cert].ID) LEFT JOIN [RT_Mask FIT Test] ON Personnel.AFD_ID = [RT_Mask FIT Test].ID) LEFT JOIN RT_RequiredTraining ON Personnel.AFD_ID = RT_RequiredTraining.[Agency Personnel Id]) LEFT JOIN RT_LiveBurns ON Personnel.AFD_ID = RT_LiveBurns.ID) LEFT JOIN RT_Drivers ON Personnel.AFD_ID = RT_Drivers.ID
WHERE (((Personnel.Status)="Active" Or (Personnel.Status)="Suspended") AND ((Personnel.Rank)<>"Junior Firefighter"))
ORDER BY Personnel.[Last Name], Personnel.[First Name];

 

[isladogs]

Your SQL contains Personnel.Rank twice so one of them is renamed Expr1011

As that is the first reference to that field, Access acts correctly in trying to use it.

The error is yours not Access. Delete one of those in the SQL and it will work correctly

 

[DHookom]

While you are fixing your form, you might want to research “data normalization”. You are store data in your field names. A friend suggests you are “committing spreadsheet”.

 

[esturgesjr]

Thanks for picking that duplication up, isladogs! I guess these feeble eyes missed that multiple times! Fixed!

DHookom: as for "storing data", you'll need to be more specific so I'll know what to zero in on.

Thanks all, for your input. I may be old, but I'm still learning!

 

[isladogs]

It’s usually easier to see mistakes in someone else’s work. In this case, I knew the likely cause having done it myself more times than I care to remember.

 

[DHookom]

Thanks for picking that duplication up, isladogs! I guess these feeble eyes missed that multiple times! Fixed!

DHookom: as for "storing data", you'll need to be more specific so I'll know what to zero in on.

Thanks all, for your input. I may be old, but I'm still learning!

Certifications and expiration dates should be stored in a separate table with the AFD_ID (assume this is your primary key in your personnel table). Each certification for each person creates a new record. Your current structure doesn’t allow for easy addition of new certifications. You would be required to modify tables, forms, queries, and reports. You might want to search for “normalized database for training data erd”. I expect someone here will suggest some normalized tables and fields. @Ken Sheridan has tons of great samples.

 

[mike60smart]

Just when I thought I'd seen everything, I get this .... the yellow highlighted field should be the field in the list circled in red. Totally unsure how this error came about. I delete the field from the form and replace it with the correct field (the errant field stays in the Field List) and as soon as I click on Save, the field changes back to Expr1011. I try selecting the field and choosing the correct field in the dropdown ... same result. I deleted the form and imported the form from a backup version in Dropbox ... when I open the backup form ... same result. What in Hades is going on here? As the post title says, I am speechless ... well, not really, but all of it would be censored! Any ideas would be welcomed!

View attachment 122890

As previously mentioned you need to Normalize your Personnel table.
The following needs to stored in separate related tables:-
Start / Termination Dates
Unit/Position
Training
I am sure there will be more.
Can you upload a copy of the database with a few random not confidential data?

 

[esturgesjr]

As previously mentioned you need to Normalize your Personnel table.
The following needs to stored in separate related tables:-
Start / Termination Dates
Unit/Position
Training
I am sure there will be more.
Can you upload a copy of the database with a few random not confidential data?

Mike: Thanks for the suggestions! Start/Termination Dates, Unit and Position must be stored in Personnel. They are dates specific to one person and usually don't change (Start is equivalent to Hire, Unit is the person's radio call sign and Position is whether the individual is Interior or Exterior qualified). All training is already stored in separate tables that are tied to the personnel ID. Once I removed the duplicate Rank field, everything cleared up. Moving on .... (with many thanks to all who chimed in)!

 

[esturgesjr]

Certifications and expiration dates should be stored in a separate table with the AFD_ID (assume this is your primary key in your personnel table). Each certification for each person creates a new record. Your current structure doesn’t allow for easy addition of new certifications. You would be required to modify tables, forms, queries, and reports. You might want to search for “normalized database for training data erd”. I expect someone here will suggest some normalized tables and fields. @Ken Sheridan has tons of great samples.

DHookom:

All training is currently stored in separate tables linked by ID. As for data stored in Personnel, see my explanation to Mike as to why some info must be kept within Personnel.

 

[mike60smart]

If you think your Personnel table is normalized then good luck.
You will find that further down the line you will not be able to produce the required output for queries and Reports.

 

[The_Doc_Man]

Start/Termination Dates, Unit and Position must be stored in Personnel. They are dates specific to one person and usually don't change (Start is equivalent to Hire, Unit is the person's radio call sign and Position is whether the individual is Interior or Exterior qualified)

The key word in this declaration is "USUALLY." If the correct term isn't "INVARIABLY and WITHOUT EXCEPTION" then you have a structural (normalization) error. Can a person ever leave the job and after a few months, come back? Where would you put the new dates? Can a person ever change Unit or Position? The Personnel table is where you normally put data about a person - but if some attribute can vary, will you want to track that information? EVER?

Normally, people hate absolutes. But for Access JET/ACE and many other database engines, there ARE absolutes. This is a discussion about one such absolute - how to treat variable vs. invariant data.