Access Calculated Yes/No

Question

Here is my assignment: Add a calculated field Can Play that becomes true (Yes) if the game platform matches your favorite platform (PS3, Wii, PC, or 360). I want it to return Yes if the Platform is PS3, What expression do I use?

Answer 1

The issue is that I cannot figure out how to return "Yes" if there is a match to "PS3" in the "Platform" column.

All you need to do is to enter a simple expression which will evaluate to True of False.  The following is an example in a table of contacts, where I want a calculated field named Family to indicate whether the contact's last name is Sheridan.  For this the expression is:

[LastName] = "Sheridan"

This simple logical expression will evaluate to True for any row with a LastName value of Sheridan, False otherwise.  Access represents True by storing a value of -1, False by storing a value of 0, so this is what you'd see in the column.  To get it to show Yes or No we need to do one more thing, which is to set the field's Format property to:

Yes/No

It will now show Yes or No, but its underlying value doesn’t change, so if we need to refer to the field we should still use the Boolean constants of TRUE or FALSE.  In a query to return only those contacts with a last name of Sheridan for instance the query's WHERE clause would be:

WHERE [Family] = TRUE

Now can you work out what expression you need to use for the calculated field in your case?

PS: One thing I forgot to mention. The calculated field's ResultType property should also be set to Yes/No. This will ensure it returns a Boolean value. This is the definition of Boolean in the OED:

Boolean /0ˈbu:lɪən/ adjective. Also -ian. M19.[ORIGIN from George Boole (1815–64), English mathematician + -an.]Of, pertaining to, or described by an abstract system of postulates and symbols applicable to logical problems.

Coincidentally George Boole taught at the institute in Liverpool which later became the high school which I attended.

Answer 2

That error is telling you that the field is expecting a different datatype than the expression returns. It could be, therefore, that you defined "Field1" as a number.

Answer 3

Actually, your assignment does not involve the violation of Third Normal Form, as would be the case if you were asked to create a true field in the table. What it does is ask you to create what is termed a Calculated Field. This is something which was introduced into Access by Microsoft some years ago, and is a field whose value is automatically created when a value is entered into one or more other fields in the table. As such it is more akin to a computed field in a query than a true field in a base table. Unlike a true field the value of a Calculated Field cannot be edited by a user. I, like many experienced Access developers, believe that the place for computed fields is a query, not a base table, but the fact is that such fields have been made possible by Microsoft, and you have been asked to create one.

As I said in my original reply the problem with what you have been assigned is not a violation of Third Normal Form, but of Codd's Rule #1, the Information Rule. It's formal definition is:

All information in a relational database is represented explicitly at the logical level and in exactly one way - by values in tables.

Chris Date, another founding father of the database relational model, expanded the definition a little by saying that all data must be stored as values 'at column positions in rows in tables'

Edgar F Codd introduced the database relational model to the world in a seminal paper in 1970. Later, concerned that relational database products were departing from his original model, he formulated a set of 13 rules in which he set out formal principles which a relational database should follow, of which the above is one. Despite its number. it is not the first rule, which is Rule #0, with which we are not concerned here. As I said in my earlier reply, in a correctly designed database there would be Players, Games and Platforms tables (and probably more which do not directly relate to this issue).

As regards your assignment, my advice would be to do exactly as it asks, but at the same time understand in what way the table design is incorrect. You might well wish to draw the attention of your instructors to this. By understanding that what you have done violates a basic principle of the database relational model you will be able to avoid making similar mistakes when you come to design relational databases of your own.

Answer 4

Yup, the assignment is asking you to violate good table design by adding a new field that violates the Rules of Normalization, as Ken and I previously noted.

"...Add a calculated field Can Play that becomes true (Yes)..."

Unfortunately, you now have the choice of explaining to your teacher WHY this is a bad design, or completing a bad design, knowing that you are NOT creating a valid relational database application in Access.

You need at least three tables, and probably others.

The tables will be tblGames, tblMyPlatform and tblMyScores.

One will contain the values for the different games, Title, Platform and Genre.

Score seems to be a value representing one or more scores obtained on different games at different times. That calls for a table of games, dates played, and scores. It's not going to come into play here, I think, but a valid table design would require it.

The other table is just your choices among the various platforms and games. That can change over time, I would expect. No one chooses the same game all the time forever.

The way to return the calculated value "CanPlay" is through a query, not in a calculated field in a table.

I realize the difficulty we're presenting here. You came with a superficially simple question and now you are learning that it's not at all so simple.

The best advice I can give therefore, is to decide how to handle that knowledge.

You could create an invalid table design to satisfy the requirements of this assignment, but then you'd be short-changing yourself in the long run. You'll not learn how to use Access to create valid relational database applications.

I thought about offering that work-around, but I'd rather not facilitate the improper lesson being taught.

Others may feel differently.

Answer 5

Apparently this is a school assignment? What does the study material given to you for this class and this assignment tell you about the problem?

In actual fact, it's describing a bad table design anyway so that's a problem in itself.

One field ([Can Play]) should never depend on the value of another field ([Game Platform]) in the table. It's part of the Rules of Normalization, which everyone working with relational database applications MUST know and understand. As Ken has described in better detail.

So, you can handle this in a couple of ways. Let the teacher know you are aware of the design flaw. Probably a tougher way to go as the teacher might not appreciate being corrected.

Or, you can properly create a query that returns the value you need (Yes/No) as a calculated field in that query.

SELECT PrimaryKeyField, GamePlatform, "Yes" as CanPlay

FROM tblYourTableNameGoesHere

WHERE GamePlatform IN (Select YourFavoritePlatform FROM tblYourOtherTableNameGoesHere)

There are, no doubt, other ways to get to the result; this is just one generic approach.

Also, since there is no information available on your actual tables, this is just a WAG anyway.

Answer 6

I assume there is a Platform or similar column (field) in the table, so you want an expression which would evaluate to True if the value in that column is "PS3".  What do you think that would be?

I would point out, however, that this would not be good relational database design as it encodes data in the expression.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

The correct approach would be to have a table of Platforms, and include a FavoritePlatform foreign key in a Players table referencing the primary key of Platforms.  By joining the relevant tables in a query you can then determine if the Patform value in a row in a Games table matches the value recorded as the player's favourite platform, and return a column with a True or False value in the query.

Answer 7

I appreciate your time. Here is the school assignment:

Hide Submission Folder Information
Instructions
Project: Database Management<br><br>Description<br><ol><li><p data-prewrap="true">Create an Excel spreadsheet with the following columns: Title, Platform, Genre, and Score.</p></li><li><p data-prewrap="true">Fill the spreadsheet with data from this table: <a href="http://www.metacritic.com/feature/best-video-games-of-2013" target="_blank" rel="noreferrer nofollow">Best-Reviewed Games</a> (Any Platform), 2013 and save it as CS102_Project7_YourUserName.xlsx.</p></li><li><p data-prewrap="true">Create a new Access database and import data from Excel. Accept all default options and make sure to check that first row of the spreadsheet contains column headings.</p></li><li><p data-prewrap="true">Add a calculated field <em>Can Play</em> that becomes true (Yes) if the game platform matches your favorite platform (PS3, Wii, PC, or 360).</p></li><li><p data-prewrap="true">Create a simple query that displays titles of the games you can play only.</p></li><li><p data-prewrap="true">Create a crosstab query with Genre as row headings and column headings. Use <strong>Count</strong> function to count number of titles in the intersection of Platform/Genre.</p></li><li><p data-prewrap="true">Save your file as CS102_Project7_YourUserName.accdb and submit it along with CS102_Project7_YourUserName.xlsx</p></li></ol><br>Requirements<br><ul><li><p data-prewrap="true">A table with 5 specified fields</p></li><li><p data-prewrap="true">Data imported from Excel to Access</p></li><li><p data-prewrap="true">Yes/No field properly calculated</p></li><li><p data-prewrap="true">A query that displays titles of the games for your platform</p></li><li><p data-prewrap="true">A crosstab query that counts number of titles of each genre on each platform</p></li></ul>