Update query: compare fields - NEWBIE

[TLelie]

Hey everyone, access is totally new for me so...

I have the following "Students" table

With columns name, surname, adviseTeacher1, adviseTeacher2, adviseTeacher3, decision, decisionDate

I would like to make a query that compares the advise of the 3 Teachers and if all of them are "positive" then the decision should be updated to positive and the date on the day of today. If one of the advises is "negative" then the decision should be put on "stand-by", again data = date today; and if all advises are negative then ofcourse the decision should be "negative" and again decisionDate = date of today.

Any help is much appreciated!

Thanks,

Timmy

 

[Alansidman]

Try this:

SELECT Students.[Name],
Students.Surname, 
Students.adviseTeacher1, 
Students.adviseTeacher2, 
Students.AdviseTeacher2, 
IIF(Students.AdviseTeacher1 ="Positive" And Students.AdviseTeacher2 = "Positive" and Students.AdviseTeacher3 = "Positive", "Positive", "Stand-By") As Decision, 
Date() As decisionDate
FROM Students;

BTW, I would change the field name for NAME to something else, perhaps fName as the term Name is a reserved word in Access and may cause you some issues in the future. I wrapped it in square brackets to help Access avoid confusion.

 

[jdraw]

Timmy,

Alan has been very kind and has answered your specific question. I would like to ask you a question seeing that you are new to Access and have already built a database.

Could you tell us briefly how the need for this database came about? How did you determine what tables and fields were required? Is this part of a computer/database course?

I'm not trying to pry or judge, I am just curious how "new Access" users/developers get introduced to database and Access.. And how they find this forum.

Good luck with your project.

 

[TLelie]

Try this:

SELECT Students.[Name],
Students.Surname, 
Students.adviseTeacher1, 
Students.adviseTeacher2, 
Students.AdviseTeacher2, 
IIF(Students.AdviseTeacher1 ="Positive" And Students.AdviseTeacher2 = "Positive" and Students.AdviseTeacher3 = "Positive", "Positive", "Stand-By") As Decision, 
Date() As decisionDate
FROM Students;

BTW, I would change the field name for NAME to something else, perhaps fName as the term Name is a reserved word in Access and may cause you some issues in the future. I wrapped it in square brackets to help Access avoid confusion.

Alan, thank you for your help, i changed the name in fname .
There's something i don't understand about this function though. If i understand it right then the function checks if the 3 advises are positive, if so then it returns positive as decision if not it changes it in stand-by? But what about negative advises?
Maybe i was not clear enough with my explanation .
I'll try it again.

I have the following columns:
studentNr, fName, sName, adviseTeacher1, adviseTeacher2, adviseTeacher3, decision, decisionDate.

I'd like to make a query that updates de decision and decisionDate in the following:
If adviseTeacher1, adviseTeacher2, adviseTeacher3 = "positive" then decision = "positive" and decisionDate = date of today
If one of the advises (teacher1, 2, 3) is "negative" then decision = "stand-by" and decisionDate = date of today
If one of the advises (teacher1, 2, 3) is "negative" then decision = "stand-by" and decisionDate = date of today
If adviseTeacher1, adviseTeacher2, adviseTeacher3 = "negative" then decision = "negative" and decisionDate = date of today

There is one more thing though if the decision was allready filled in then the decision and decisionDate may not be changed.

I hope it is a bit clear

 

[TLelie]

Timmy,

Alan has been very kind and has answered your specific question. I would like to ask you a question seeing that you are new to Access and have already built a database.

Could you tell us briefly how the need for this database came about? How did you determine what tables and fields were required? Is this part of a computer/database course?

I'm not trying to pry or judge, I am just curious how "new Access" users/developers get introduced to database and Access.. And how they find this forum.

Good luck with your project.

Jdraw,

Well actually i'm working on a database to have less workload, so its strictly personal, not commercial. I was figuring out a way to have less paperwork, etc and found a possible solution in access by way of a database. The problem is that it is quite new for me. I'm looking after some books at this moment to learn some more about it (i have access 2007). If you would have some recommendations for good books with clear examples and which go also further than the basics please feel free to inform me .
So while i was looking for a solution for my specific problem, Google listed this forum, so thats why i registered.
Ofcourse i would like to learn as much as i can from books as i can imagine this forum is not made to ask every question . But it seems a nice place to resolve specific issues in the future.

Greets,

Timmy

 

[Alansidman]

Timmy;
My apologies, I missed the part about all three being negative. Change the IIF statement to read:

IIF(Students.AdviseTeacher1 ="Positive" And Students.AdviseTeacher2 = "Positive" and Students.AdviseTeacher3 = "Positive", "Positive", IIF(Students.AdviseTeacher1 ="Negative" And Students.AdviseTeacher2 = "Negative" and Students.AdviseTeacher3 = "Negative","Negative","Stand-By"))

Alan

 

[jdraw]

Tim,
Welcome to the forum. Thanks for responding to my questions.

There are a number of links I would recommend, and I can assure you that learning Access and database generally is often described as a journey. You can always learn, and learn more. Sometimes when you "solve" a situation, you discover other approaches/techniques etc, and adjust your original to take advantage of some newer approach.

Most database people will tell you that getting your tables and relationships set up to support your business is the most fundamental thing you can do. Well designed tables facilitate information retrieval and modification.

Once you have your tables and relationships established and tested, you could implement that database in any of a number of database packages. That's why I say the data model (tables and relationships) is key. You can build databases without normalized structures, but you will be fighting against yourself trying to get data in and/or out of such a database.

There is a great article on relational database principles at
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

In several posts I have recommended links on Entity Relationship Diagramming which follows a process with a worked out example to create the tables and relationships to support a "business".

see http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation. The example relates Customers, Orders and Items.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

There is an excellent online book by Crystal at
http://www.accessmvp.com/strive4peace/

For Access functions and examples
see http://www.techonthenet.com/access/index.php and
http://www.datapigtechnologies.com/AccessMain.htm

For vba help/tutorial see
http://www.functionx.com/access2007/index.htm

Good luck with your project.

 

[TLelie]

Timmy;
My apologies, I missed the part about all three being negative. Change the IIF statement to read:

IIF(Students.AdviseTeacher1 ="Positive" And Students.AdviseTeacher2 = "Positive" and Students.AdviseTeacher3 = "Positive", "Positive", IIF(Students.AdviseTeacher1 ="Negative" And Students.AdviseTeacher2 = "Negative" and Students.AdviseTeacher3 = "Negative","Negative","Stand-By"))

Alan

I'm feeling such a moron right now. But i don't seem to get this to be running as i want .
What kind of query do i need to make?
I thought an update query, but then if i change the view to sql view and paste the above code:
SELECT Students.[Name], Students.Surname, Students.adviseTeacher1, Students.adviseTeacher2, Students.adviseTeacher3,
IIF(Students.adviseTeacher1 ="Positive" And Students.adviseTeacher2 = "Positive" and Students.adviseTeacher3 = "Positive", "Positive", IIF(Students.adviseTeacher1 ="Negative" And Students.adviseTeacher2 = "Negative" and Students.adviseTeacher3 = "Negative","Negative","Stand-By"))
and then save, it becomes a select query and then when i run the query i get a "enter parameter" fill in box for adviseTeacherA, adviseTeacher2, adviserTeacher3.

Damn I think I'll have to do a lot more reading before I get to understand access

 

[TLelie]

Thank you jdraw, i will try to find the time to get through all that

 

[Alansidman]

Timmy, firstly an update query would change data in your table. Is that what you want to do or just extract information for reporting/analysis?

You are getting a parameter request because Access cannot find the fields you have referenced in your query. Double check the names of your fields and tables and make sure that in your query they are spelled exactly the same. Sometimes trailing or leading spaces can be the issue.

 

[TLelie]

Alan,

That was so stupid of me, spelled the names 3 times wrong :/

In regard of changing records. Yes, that's exactly what I want.

I'll try again to make clear what i'd like.
I'd like to make an UPDATE query that changes the field "decision" and "decisionDate" in all records in the database in the following:

If adviseTeacher1, adviseTeacher2, adviseTeacher3 = "positive" then "decision" should be changed in "positive" and decisionDate should be changed in the date of today

If one of the advises (teacher1, 2, 3) is "negative" then decision should be changed in "stand-by" and decisionDate changed in date of today
If one of the advises (teacher1, 2, 3) is "negative" then decision should be changed "stand-by" and decisionDate changed in date of today
If adviseTeacher1, adviseTeacher2, adviseTeacher3 = "negative" then decision should be changed "negative" and decisionDate should be changed in date of today

BUT only if for each case the decision was not allready filled because then the decision and decisionDate may not be changed.

Hope it's a bit clearer now

 

[Alansidman]

Why cannot you just run a query for the latest on the fly evaluation. Tables that have calculations tied to them need to be continuously updated when there is a change. What happens if the table is not updated and someone searches it for latest data. It is out of date. I would urge you to keep your calculations in your query and run them when you need the information.

If you insist on continuing down this road, then look at this video on manipulating data in a query and pay close attention to when he shows you how to run an update query.

http://www.datapigtechnologies.com/flashfiles/datamanipulate.html