Plural
By Richard Rost
3 years ago
How to Display Singular or Plural Forms of Words
In this Microsoft Access tutorial, I will teach you how to display the singular or plural form of a word (or a different word altogether) based on some value and whether it's 1 or not 1. We'll use the IIF function.
Prerequisites
Recommended Courses
Usage
- DogMsg: [NumDog] & " " & IIf([NumDog]=1,"dog","dogs")
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, singular, plural, iif, number of dogs
Subscribe to Plural
Get notifications when this page is updated
Intro In this video, we will learn how to display the correct singular or plural form of words like "dog" or "person" in Microsoft Access based on numeric values in your tables, such as the number of dogs a customer owns or the size of a family. I will show you how to use calculated fields, string concatenation, and the IIF (immediate if) function in queries to create messages like "1 dog" or "2 people" automatically. This technique is useful for making your reports and forms read naturally based on your data.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost, and today I'm going to show you how to take words and show either the singular or plural form of that word, whether it's dog or dogs, or person or people, based on some value like the number of dogs they have or the number of people in the family.
So, I log on yesterday to Experian and check my credit score. Rookie mistake. Do you see it anywhere on here? Maybe somewhere right there? My score is increased by one points. Come on. I don't want to pick on Experian too much because I don't want them to tank my credit score on me, but come on. That's a rookie mistake. You don't do that. That's programming 101.
I know a lot of you are asking, how do I do that? Let me show you how.
Before we get started, you should know how to make calculated fields in queries. It's the easiest way to do this. You should know how to use string concatenation - that's putting together two strings to make one. You should also know how to use the if function - the immediate if, IIF. Yeah, it's "if," that's how you have to pronounce it. That's a rule.
All these are free videos - go watch them if you're not familiar with these concepts. The if function, for example: let's just say if a value is this, then do that, otherwise do that. It's like a mini if-then statement. Go check all these out if you're not familiar with them already and then come on back.
Here I am in the TechHelp free template. This is a free database you can download a copy of from my website if you want to. I have a customer table, and in this customer table, I've already got a field over here called "family size." We can use that to display whether or not you've got one person or four people in your family. But we'll get to that in a minute.
Let's do something simpler. Let's add a field called "num dog." What is num dog? Well, it's the number of dogs this family has. I like to keep all of my field names singular if I can, because that way, later on, when I'm doing advanced programming, I don't have to think to myself, "Okay, is it num dog or num dogs?" While you're typing it, you can obviously see it if you're in a table or query. But that's just one of my personal things; you don't have to do that if you don't want to.
I'll make this a number of type long integer. We'll save that real quick, come in here, and give a few people some dogs. I got two, one, eight, four, two, zero, zero, two. Some people got some dogs.
Now, the thing with singular or plural - it's interesting. If you have one of something, it's singular. You'd say one dollar, right? Also, negative one is technically singular; you say negative one dollar. That's really weird. So I guess you really want to look at the absolute value of a number to determine whether it's singular. That's strange rules, but one and minus one would be singular. For the purposes of this video, we're just going to worry about positive numbers.
Let's make a query. We'll bring in that customer table, and I'll bring in the dog field - num dog, there we go. I just want to see values; I want to see customers who have a value. If it's null, that means I don't know if they have any dogs or not, so we'll just say "is not null" down here.
Now I'm just going to see all the people who have dogs, and those are cool people (except for the people with zero).
Let's say I'm going to format a report to go out, and in that report, I want it to say whether they have one dog, two dogs, zero dogs, and so on. We'll make a field called "dog message." I'll zoom in so you can see this better, Shift-F2.
The dog message is going to be:
If num dog equals 1, then "dog."
Otherwise, "dogs."
Just like that - your singular and your plural. And maybe in front of that, we want to actually put the value itself, so we'll say num dog and a space and that entry right there. So we'll see like "2 dogs," "0 dogs," "1 dog."
So that works. I'll save this, Control-S. I'll save this as my dog Q, my dog query, and I'll give it a quick run. See? Look at that.
Two dogs, one dog, eight dogs, red fish, blue fish, green fish, whatever fish. Fishes.
You can do something similar with the family size field. We have the same thing here - family size, right? We have some numbers there. I'll put a zero in here, whatever, doesn't matter.
Do the same thing over here:
Shift-F2 to zoom in. We'll call this the "family message," which is going to be family size and a space and:
If family size equals one, then "person,"
Otherwise, "people."
Take a peek; there you go: two people, one person, four people, zero people.
I already told you I have people skills.
That's all, just a quickie today. You could use the same thing in a query, or you can use it in a form, or you could put it directly in a report field if you want to. I usually put it inside a query because then I use the query to build the form or the report. You could put it in VBA code too. The if function works in VBA code, so if you're a developer you can do that.
I guess I forgot to label this one not quite beginner, but there's no programming, so we'll call it "expert." Experts, you need some functions and stuff like that. It's like the tween beginner and developer.
There you go. There's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What primary concept does the video teach regarding word forms?
A. How to change the text color in Access reports
B. How to show singular or plural forms of words based on a value
C. How to generate random numbers in a query
D. How to sort data alphabetically in Access
Q2. Which Microsoft Access feature is recommended for handling the logic for singular or plural words?
A. Create a new table
B. Use a calculated field in a query
C. Use a macro to change field names
D. Modify the database relationships
Q3. What function is suggested for conditional evaluation in Access to choose between singular and plural?
A. DLookup
B. IIF (Immediate If)
C. NZ
D. Sum
Q4. What is the format for showing the number of dogs a family has, according to the video?
A. Just the word "dog" regardless of number
B. The number only
C. [Number] dog(s)
D. [Number] dog or dogs depending on the value
Q5. According to the video, what field name does the instructor prefer for the number of dogs?
A. num dogs
B. numberOfDogs
C. num dog
D. dogsCount
Q6. Why does the instructor prefer keeping field names singular?
A. It helps in sorting records easily
B. It avoids confusion later in advanced programming
C. It is a requirement in Access
D. Access does not allow plural names
Q7. When formatting the dog message, what is the logic for displaying "dog" versus "dogs"?
A. "Dog" if number is zero, otherwise "dogs"
B. "Dogs" if there are more than two, "dog" otherwise
C. "Dog" if the value equals one, otherwise "dogs"
D. "Dog" if negative, "dogs" if positive
Q8. What alternative data output does the video also demonstrate, using the same concept?
A. The number of cats owned by customers
B. The family size field, displaying "person" or "people"
C. The number of emails received
D. The product inventory count
Q9. Why might you use calculated fields in queries instead of putting logic directly into report fields?
A. Queries are faster than reports
B. Logic in queries can be reused in forms and reports
C. Report fields are case sensitive
D. Only queries allow conditional logic
Q10. Which of the following statements about the IIF function in Access is TRUE based on the video?
A. IIF can only be used in tables, not forms or reports
B. IIF requires advanced VBA skills
C. IIF can be used in queries, forms, reports, and VBA code
D. IIF is used to format dates
Q11. What assumption is made for the sake of simplicity in handling singular/plural forms in this video?
A. Only negative numbers are considered
B. Only values of zero are displayed
C. Only positive numbers are considered
D. All decimal values are rounded to the nearest integer
Q12. If the family size is 1, what message will be shown using the demonstrated logic?
A. 1 people
B. 1 persons
C. 1 person
D. person 1
Q13. What does NULL value in the num dog field indicate in this database example?
A. The person has exactly one dog
B. The number of dogs is unknown
C. The person has no dogs
D. It counts as two dogs
Answers: 1-B; 2-B; 3-B; 4-D; 5-C; 6-B; 7-C; 8-B; 9-B; 10-C; 11-C; 12-C; 13-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on working with singular and plural words in Microsoft Access, such as displaying "dog" or "dogs" and "person" or "people" depending on a number value, like the number of dogs someone owns or the number of people in a family.
This issue came up for me recently when I was checking my credit score at Experian. I noticed their system reported that my score had increased by "one points" instead of "one point." Mistakes like this are common but easy to avoid with just a little bit of programming knowledge.
If you're interested in how to manage this in your own Access databases, you'll need to be comfortable with creating calculated fields in queries. You'll also need some basic experience with string concatenation for combining text values and knowledge of the IIF function, which acts as an inline If-Then-Else statement in Access. If any of these are unfamiliar to you, I have separate free tutorials available on these subjects that you can check out before coming back to this lesson.
For this example, I'm using the TechHelp free template database, available for download on my website. Inside the customer table, there is a field called "family size," which we can use to demonstrate displaying messages like "one person" or "four people" based on the value.
To start with a simpler example, I added a new field to the table called "num dog" to track how many dogs each customer has. As a personal best practice, I tend to keep my field names in the singular form, such as "num dog" instead of "num dogs." This helps avoid confusion later during more advanced development, but whether or not you follow this convention is completely up to you.
This "num dog" field is set as a long integer so it can store whole numbers. I entered some sample values for a few customers, ranging from zero up to eight.
The challenge with choosing the correct singular or plural word is that you generally use the singular form when the value is one, for example "one dollar" or "one dog." Interestingly, most people also use the singular when talking about negative one, such as "negative one dollar," though for simplicity's sake in this video, I am only dealing with positive numbers.
Now let's create a query to pull this together. I include the customer table and bring in the "num dog" field, filtering out records where the value is null by looking for records "is not null." With that, we can see all customers who have a value entered for number of dogs.
Suppose you are formatting a report and want to display something like "2 dogs," "0 dogs," or "1 dog." You can create a calculated field in your query called "dog message." This field will evaluate whether the number of dogs equals one, in which case it will display "dog," and otherwise it will display "dogs." If you concatenate the number value along with a space and this text, your report will show entries such as "2 dogs," "0 dogs," "1 dog," and so on.
A similar calculation can be done for the "family size" field. You can create a new calculated field called "family message," which displays the number along with the appropriate wording: "person" if it is one, or "people" for any other value, resulting in correct descriptors like "two people," "one person," or "zero people."
These techniques can be used in queries, on forms, or even directly in report fields. I generally prefer adding these calculated fields to queries and then basing forms and reports on the queries. If you are writing VBA code, the IIF function works there as well, so you could use the same method directly in your code if needed.
This tutorial is suitable for those who have moved just beyond beginner level, but you do not need any advanced programming skills for this lesson.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a calculated field in a query for singular and plural words
Using the IIF function to determine singular or plural
Concatenating numbers and words in a calculated field
Conditionally displaying "dog" or "dogs" based on quantity
Conditionally displaying "person" or "people" based on quantity
Filtering query results for non-null values
Adding a new numeric field to a table
Updating records with numeric values
Applying singular or plural logic in reports
Applying singular or plural logic in forms
Using Shift-F2 to zoom into expression editor
Naming and saving queries in Microsoft Access
Using the calculated field from a query in forms or reports |
