Solved

Give a letter in a field a numeric value and then add 5 filelds in access 2010

Posted on 2011-03-03
13
306 Views
Last Modified: 2012-05-11
I have 5 fields that have 1 of 3 letters in them. X, T, or A. Each letter needs to have a value. The values need to be added and placed in another field.
These fields are in a form created out of 3 tables. All of the information for the "total" field will be from the same table.
Any advice on how I can accomplish this is appreciated.
thank you


0
Comment
Question by:amyamo
  • 7
  • 6
13 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35030069
You have a lot to consider here.

Remember that we do not have access to your DB, so it is hard to give any direct advice. But as far as your table issue goes:
All of these values should be in one table.
This is basic Table Theory.

tblChoices
ChoiceID (PK, AutoNumber)
ChoiceText

Ex:
ChoiceID, ChoiceText
1, X
2, T
3, A

Then, in your Db, anytime you need the "Choice Text"
You would link to the ChoiceID, but display the ChoiceText

<These fields are in a form created out of 3 tables>
Again, I can't tell you if this is correct or not...

<All of the information for the "total" field>
Again, I have no idea what this is or how it relates to your issue.

I will say that most of the time when you have a field called "Total" it should be a calculated field, not a value stored in a table.
Again, this is basic Database theory.

You may have to post a sample of your DB and clearly state what your ultimate question is here.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Delete any objects that do not relate directly to the issue.
5. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
6. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
7. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
8. Compile the code. (From the database window, click: Debug-->Compile)
9. Run the compact/Repair utility.
10. Remove any Passwords and/or security.
11. Post explicit steps to replicate the issue.
12. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue.


JeffCoachman
0
 

Author Comment

by:amyamo
ID: 35030278
I have attached the database.
The columns M, T, W, Th, F are going to be marked with either an X (present), T (tardy) or A (absent)
I need to total those fields to have a total in the "Att" column. that total number will be the days Absent.
Instructors will be filling this out and I want this to automatically total for them to see the amount of days absent each week.
X should = 0
T should = 0
A should = 1

Let me know if I wasn't clear enough.
thank you
  sampledb.accdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 35031030
Going into your form, I can't see how you would be generating the new MTWTF the values for each "Date"?

And going back even further to the table design...
Your Grades and Attendance tables are not "Normalized"
In addition to all the reasons why this design is inefficient, ...this will be a perpetual source of frustration for you every time you need any type of summary.
This must be done first, before the forms are designed.

So my suggestion to you would be to ask that this question be deleted and you post a new question concerning the overall design (Normalization) of this system.

Could I post a solution that would just "Get this done"?
Sure, but then again, you would be posting question after question about getting similar summaries, and each Expert here will keep reminding you that your tables are not "Normalized".

So don't take this the wrong way, but this is just like building a house, ...the foundation must be sound, everything else should fall into place after that.

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35031048
I have a few of these "Student/Classes" type databases, as sample files, I'll try to post a couple for you here.

;-)

JeffCoachman
0
 

Author Comment

by:amyamo
ID: 35031167
thank you - I would appreciate seeing any samples - I am very new to database design and help is appreciated.
What do you mean by normalized? When I was entering information into the form, it all seemed to go where I wanted it to from the form to the tables.
thank you
Amy
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35031465
You can spend an entire college course learning Normalization.
;-)

The tip-off in your design was the repeating field names in the same table:
xxx1
xxx2
xxx3

Here are some resources to get you started:
http://databases.about.com/od/specificproducts/a/normalization.htm
http://support.microsoft.com/kb/283878
http://www.amazon.com/gp/product/0201752840/ref=pd_lpo_k2_dp_sr_1?pf_rd_p=486539851&pf_rd_s=lpo-top-stripe-1&pf_rd_t=201&pf_rd_i=0201694719&pf_rd_m=ATVPDKIKX0DER&pf_rd_r=1PQGJG5CPQ5K647T2C6D
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:amyamo
ID: 35031516
Thank you - I will look into those tonight -
are you still able to post the samples that you had mentioned before of your "Student/classes" database?
thank you
Amy
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35031561
I'll try to dig up the samples tonight
0
 

Author Comment

by:amyamo
ID: 35031572
If I were to "normalize" and make more tables out of the grades and attendance tables - do I have to create more sub-forms in my form? Or am I able to use more than one table in a sub-form - that's where I was confused when I was creating this originally.
0
 

Author Comment

by:amyamo
ID: 35033422
I rebuilt my tables tonight after doing more reading - I have attached a new database with my new tables - am I on the right track now?

I have also attached a PDF file of what the final form I am trying to create is supposed to look like. Each instructor will enter in the student information, their attendance, as well as the grades. I need to have the attendance and grades total for them.

thank you for all of your help - I really appreciate it.
sampledb2.accdb
Students.pdf
0
 

Author Comment

by:amyamo
ID: 35035724
I am going to post my last comment as a new question (like you had mentioned I should!)
Once I get my tables corrected and resolved - should I come back to this to have my initial question answered, or repost that as well? Thanks for your help so far.
0
 

Author Comment

by:amyamo
ID: 35036267
So, I was just told from my last question that I had asked that my tables all looked good -
I have attached my new database and the PDF again of what my form should look like - could we revisit the initial question now? I need to give the Letters X, T and A a numeric value and then total up the A's into the "Att" field.

What is the best way for me to go about this? Do I need to make a query to get the fields for the Attendance_Class table? And then I'm just going to do the same thing for the classes_departments table?

thank you
amy

sampledb2.accdb
Students.pdf
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35104177
Can you fill in all the tables with some actual data.
(Not the Same data for all records, like you have in the PDF)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now