There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
How to create Access 2013 field with only 2 possible values
I have a really simple question. I just need to create a field in a table that will track whether a job opening record requires a Lebenslauf auf Deutsch (German resume) or an English one. I want to be able to choose "Deutsch" or "English" (English is default), but have DE or EN recorded in the database. This field represents a flag for a mail-merge effort to automate cover letter customization -- I want to be able to run the merge for just one language based on a query of this field.
I know it's simple, but you'd be surprised how difficult it is to find this kind of info through a search engine. (Either that, or I'm not very good building search engine queries...)
Thanks in advance.
P.S. I thought about building a lookup table, but it seemed to be overkill for just 2 languages. Is that a correct assessment, or would it be a good approach after all?
I know it's simple, but you'd be surprised how difficult it is to find this kind of info through a search engine. (Either that, or I'm not very good building search engine queries...)
Thanks in advance.
P.S. I thought about building a lookup table, but it seemed to be overkill for just 2 languages. Is that a correct assessment, or would it be a good approach after all?
Who is Participating?
Normally, the way you would do this is to have a form for entering the data into the table. Instead of having a textbox as the data entry control, you would use a combo box. You could create a separate table (tbl_ResumeLanguage) with fields:
LanguageCD and Language
EN English
DE Deutsch
and then use that table as the source for your combo box, or you could use a ValueList for the combo box with RowSource: "EN", "English", "DE", "Deutsch"
Then, you would set the BoundColumn = 0, ColumnCount = 2, and ColumnWidths = 0, 1
Finally, you would add code to the forms BeforeUpdate event which ensures that the column is filled in. Something like
LanguageCD and Language
EN English
DE Deutsch
and then use that table as the source for your combo box, or you could use a ValueList for the combo box with RowSource: "EN", "English", "DE", "Deutsch"
Then, you would set the BoundColumn = 0, ColumnCount = 2, and ColumnWidths = 0, 1
Finally, you would add code to the forms BeforeUpdate event which ensures that the column is filled in. Something like
Private Sub Form_BeforeUpdate(Cancel as Integer)
if me.cbo_Language & "" = "" Then
Cancel = True
msgbox "Select a value from the Language dropdown"
End If
End Sub
I'll present an alternative strategy.
You could have the drop-down box embedded into your table.
Go into your table design mode, click on the relevant field, and click on the second tab.
Once you change Display Control to (say) list box, then you can enter your mini-table values there.
I would have Column Count as 2, and have the Bound Column as 2, so you could have
Deutsch. DE
English. EN
You could change the Column Widths to (say) 3 cm; 0 cm, so that the second column is not visible, but is the one bound.
See http://probiztechnology.com/blog/2012/a-students-access-question-quick-easy-dropdown-lists/ for more information.
You could have the drop-down box embedded into your table.
Go into your table design mode, click on the relevant field, and click on the second tab.
Once you change Display Control to (say) list box, then you can enter your mini-table values there.
I would have Column Count as 2, and have the Bound Column as 2, so you could have
Deutsch. DE
English. EN
You could change the Column Widths to (say) 3 cm; 0 cm, so that the second column is not visible, but is the one bound.
See http://probiztechnology.com/blog/2012/a-students-access-question-quick-easy-dropdown-lists/ for more information.
I strongly discourage:
1. Allowing users direct access to your tables. Users should only access data through forms.
2. Use of the feature that Phillip recommends above. This can be quite confusing to users because the value they see when viewing the table would be "English" or "Deutsch", but what is actually stored is "EN" or "DE". I cannot tell you how many times I have encountered this when trying to help someone who is trying to filter their table and has either forgotten, or does not know that the actual data stored in the table is "EN"/"DE" and they are trying to filter on "English" or "Deutsch"
1. Allowing users direct access to your tables. Users should only access data through forms.
2. Use of the feature that Phillip recommends above. This can be quite confusing to users because the value they see when viewing the table would be "English" or "Deutsch", but what is actually stored is "EN" or "DE". I cannot tell you how many times I have encountered this when trying to help someone who is trying to filter their table and has either forgotten, or does not know that the actual data stored in the table is "EN"/"DE" and they are trying to filter on "English" or "Deutsch"
Since all entries presumably require a Lebenslauf in either english or german, why not decide on a default and then use a Yes/No field to indicate whether the other is required? While it wouldn't be quite so obvious what the field values meant, the form through which they are accessed could provide the explanation?
/T
/T
This field represents a flag for a mail-merge effort to automate cover letter customization
If are you driving the mail merge with a query instead of a table, then why not put a parameter into the quer(ies)
One query has the field = "Deutsch", and another query had the field = "English"
When you do the merge, you pick the appropriate query for the results you want.
If you're going to filter on it, then it matters little how you accomplish it. Whether put in a lookup table with "Deutsch" = 1 and "English" = 2 and then filter by 1 or by 2, or you filter by the text value is little practical difference.
In theoretical terms the lookup table is the preferred method. You could then change the names to "Englisch" and "German" with little hassle in a single locale. And filtering on a number field is the preferred method over filtering on text in a larger context
If are you driving the mail merge with a query instead of a table, then why not put a parameter into the quer(ies)
One query has the field = "Deutsch", and another query had the field = "English"
When you do the merge, you pick the appropriate query for the results you want.
If you're going to filter on it, then it matters little how you accomplish it. Whether put in a lookup table with "Deutsch" = 1 and "English" = 2 and then filter by 1 or by 2, or you filter by the text value is little practical difference.
In theoretical terms the lookup table is the preferred method. You could then change the names to "Englisch" and "German" with little hassle in a single locale. And filtering on a number field is the preferred method over filtering on text in a larger context
I have a mini-app to manage simple lookup tables like this. I import it into each new application so I don't have to reinvent the wheel. It consists of two tables, four forms, and two reports. I've attached a few pictures. I tie it in with security because some tables can be updated by anyone or a supervisor or just IT. It empowers the user and relieves me of having to manage the code lists. The downside is that the codes stored in your tables will be meaningless numbers so unless you memorize hundreds of unique values, you won't be able to "see" them when you look directly at a table so you'll need to use a query so you can see the decoded values. I include a long desc and a short desc because you usually want to see the long description in the combos but for reports, you may be squeezed for space and so you can use the short desc.
And I'll definitely side with @Dale Fye about what belongs in tables.
Real values.
Text and Numbers
The minute you start putting controls like checkboxes , yes/no, listboxes, comboboxes other assorted things is the moment you voluntarily opened the gates of hell and went for a stroll. You WANT the tables to show you the real values stored. That way, you can design queries and criteria based on what you see. The minute the tables become a place for presentation it becomes hellacious to figure out what's truly there. Forms and reports are the places for pretty presentation.
Not tables.
Real values.
Text and Numbers
The minute you start putting controls like checkboxes , yes/no, listboxes, comboboxes other assorted things is the moment you voluntarily opened the gates of hell and went for a stroll. You WANT the tables to show you the real values stored. That way, you can design queries and criteria based on what you see. The minute the tables become a place for presentation it becomes hellacious to figure out what's truly there. Forms and reports are the places for pretty presentation.
Not tables.
Checkboxes represent true/false values (-1 or 0). What is hellacious about that? In queries, if you want true you look for TRUE. If you don't want true, you look for FALSE. If you need a third value - null (unknown), you should not use a Yes/No data type.
One way:
Just modify the field properties in table.
Default Value: "EN"
Validation Rule: ="EN" Or ="DE"
Just modify the field properties in table.
Default Value: "EN"
Validation Rule: ="EN" Or ="DE"
To make a selection between English and German to a selection between True and False equals selecting a bad design.
It may work, but that's no indication of a good design.
Happy New Year.
Gustav
It may work, but that's no indication of a good design.
Happy New Year.
Gustav
We were not discussing making this a y/n design. We were discussing, in general, the use of Yes/No columns in a table.
@PatHartman
What is hellacious about that?
Build an app.
Use checkboxes instead of numerics in table.
Forget to disallow null for the column or set a default, since you've got that looked after in code/forms.
Hand that app to a user with enough knowledge to want to expand or innovate on it, but not a power user.
How do you put an unchecked checkbox in the Query Editor?
Or why, when you put 'Yes' in there that the query returns nothing?
These are all things that the user will then need to discover, that you already know.
And easily avoidable.
You can ensure that the table displays the values that actually exist in it.
And it's odd.
In my app, with the same SQL Server backend some of my bit columns, in Access, show 0 and 1, while others show 0 and -1
Hell, in the same table I've got bit fields that some of them show 0 and 1 and, others show 0 and -1
And when I grab all three in a query, they are then all 0 and -1. Filtering by TRUE, though doesn't consistently lead to the results I expect. And fails when I least expect it. And DESPITE the fact that in the query's datasheet view shows 0 and -1, I CANNOT filter by -1. But I can filter all three by 1 Which is a surprising result to say the least--since Access is showing me -1.
Not surprising is that I can create bulletproof filters with = 0 and <> 0.
Your mileage clearly varies from mine. That's nice. But since you clearly cannot make a bulletproof case for the infallibility of TRUE/FALSE in the way you'd like to handle it, I am not sure what the objection is.
And at any rate, it's all about creating apps that are maintainable over the long run. How does adding controls to tables aid in that regard? It doesn't. It's one of the many things MS has flanged into Access to make it 'friendlier' for the end user. And one of those things -- like hyperlink fields, or permitting subdatasheets -- that it makes long-run sense to avoid.
What is hellacious about that?
Build an app.
Use checkboxes instead of numerics in table.
Forget to disallow null for the column or set a default, since you've got that looked after in code/forms.
Hand that app to a user with enough knowledge to want to expand or innovate on it, but not a power user.
How do you put an unchecked checkbox in the Query Editor?
Or why, when you put 'Yes' in there that the query returns nothing?
These are all things that the user will then need to discover, that you already know.
And easily avoidable.
You can ensure that the table displays the values that actually exist in it.
And it's odd.
In my app, with the same SQL Server backend some of my bit columns, in Access, show 0 and 1, while others show 0 and -1
Hell, in the same table I've got bit fields that some of them show 0 and 1 and, others show 0 and -1
And when I grab all three in a query, they are then all 0 and -1. Filtering by TRUE, though doesn't consistently lead to the results I expect. And fails when I least expect it. And DESPITE the fact that in the query's datasheet view shows 0 and -1, I CANNOT filter by -1. But I can filter all three by 1 Which is a surprising result to say the least--since Access is showing me -1.
Not surprising is that I can create bulletproof filters with = 0 and <> 0.
Your mileage clearly varies from mine. That's nice. But since you clearly cannot make a bulletproof case for the infallibility of TRUE/FALSE in the way you'd like to handle it, I am not sure what the objection is.
And at any rate, it's all about creating apps that are maintainable over the long run. How does adding controls to tables aid in that regard? It doesn't. It's one of the many things MS has flanged into Access to make it 'friendlier' for the end user. And one of those things -- like hyperlink fields, or permitting subdatasheets -- that it makes long-run sense to avoid.
Build an app.
..Will hundreds of apps and thousands of tables do?
Use checkboxes instead of numerics in table.
..Whenever it makes sense (i.e. I only have TWO values!)
Forget to disallow null for the column or set a default, since you've got that looked after in code/forms.
..It is my job to not forget details like this, otherwise the client might as well do it himself or hire an amateur.
Hand that app to a user with enough knowledge to want to expand or innovate on it, but not a power user.
..Not a good idea, but OK
How do you put an unchecked checkbox in the Query Editor?
..?? As I mentioned earlier, if you define a Yes/No data type, it is your responsibility to ensure that it doesn't allow null. If you want to allow null, you ALWAYS have to consider null whenever you are searching for not True.
Or why, when you put 'Yes' in there that the query returns nothing?
.. Because "Yes" isn't what is stored. -1 and 0 are what is stored but you can refer to them as True or False. The only time you would ever see "Yes" is if you changed the display type to NOT be a text box.
These are all things that the user will then need to discover, that you already know.
.. I would never put him in that position.
And easily avoidable.
.. Absolutely
You can ensure that the table displays the values that actually exist in it.
.. It will
And it's odd.
In my app, with the same SQL Server backend some of my bit columns, in Access, show 0 and 1, while others show 0 and -1
.. Most likely the table with the "-1" was converted from Jet/ACE and the "1" table was made in SQL Server. That is why when using Access, using True and False avoids the issue.
Hell, in the same table I've got bit fields that some of them show 0 and 1 and, others show 0 and -1
.. That really doesn't have anything to do with displaying a checkbox.
And when I grab all three in a query, they are then all 0 and -1. Filtering by TRUE, though doesn't consistently lead to the results I expect. And fails when I least expect it.
.. What are you expecting to happen that doesn't happen?
And DESPITE the fact that in the query's datasheet view shows 0 and -1, I CANNOT filter by -1. But I can filter all three by 1 Which is a surprising result to say the least--since Access is showing me -1.
.. Access is definitely confusing the issue which is one more reason to use True and False. NOT "True" and "Not True"
.. In a database linked to SQL Server, I would NEVER use a filter. I don't want Access to bring down all the rows and filter locally. That defeats the purpose of using SQL Server. It is best to use queries with criteria.
Not surprising is that I can create bulletproof filters with = 0 and <> 0.
Your mileage clearly varies from mine. That's nice. But since you clearly cannot make a bulletproof case for the infallibility of TRUE/FALSE in the way you'd like to handle it, I am not sure what the objection is. When I allow users to "filter" forms, I have the querydef refer to the unbound selection fields when the selection is a simple one or two fields. When the selection is complex as it can be for reports, I build the SQL on the fly.
And at any rate, it's all about creating apps that are maintainable over the long run. How does adding controls to tables aid in that regard?
.. You are not adding a control to a table. You are "allowing" Access to display data in a logical way. Most people think that checkboxes are intuitive and easy to read. They don't want to look at 0 and -1, especially users. If you don't want to see a checkbox, DON'T use a Yes/No data type, use an Integer.
It doesn't. It's one of the many things MS has flanged into Access to make it 'friendlier' for the end user. And one of those things -- like hyperlink fields, or permitting subdatasheets -- that it makes long-run sense to avoid.
Your whole argument revolves around avoiding something because you or someone else has misused it in the past.
Let's agree to disagree on another topic.
@Gus,
Apparently I missed a couple of posts and thought you were referring to the exchange between Nick and me. I agree 100% that Yes/No fields should be used ONLY when the field poses a question that can be answered by Yes/No or True/False and NEVER to indicate 1 of 2 choices such as English or German.
..Will hundreds of apps and thousands of tables do?
Use checkboxes instead of numerics in table.
..Whenever it makes sense (i.e. I only have TWO values!)
Forget to disallow null for the column or set a default, since you've got that looked after in code/forms.
..It is my job to not forget details like this, otherwise the client might as well do it himself or hire an amateur.
Hand that app to a user with enough knowledge to want to expand or innovate on it, but not a power user.
..Not a good idea, but OK
How do you put an unchecked checkbox in the Query Editor?
..?? As I mentioned earlier, if you define a Yes/No data type, it is your responsibility to ensure that it doesn't allow null. If you want to allow null, you ALWAYS have to consider null whenever you are searching for not True.
Or why, when you put 'Yes' in there that the query returns nothing?
.. Because "Yes" isn't what is stored. -1 and 0 are what is stored but you can refer to them as True or False. The only time you would ever see "Yes" is if you changed the display type to NOT be a text box.
These are all things that the user will then need to discover, that you already know.
.. I would never put him in that position.
And easily avoidable.
.. Absolutely
You can ensure that the table displays the values that actually exist in it.
.. It will
And it's odd.
In my app, with the same SQL Server backend some of my bit columns, in Access, show 0 and 1, while others show 0 and -1
.. Most likely the table with the "-1" was converted from Jet/ACE and the "1" table was made in SQL Server. That is why when using Access, using True and False avoids the issue.
Hell, in the same table I've got bit fields that some of them show 0 and 1 and, others show 0 and -1
.. That really doesn't have anything to do with displaying a checkbox.
And when I grab all three in a query, they are then all 0 and -1. Filtering by TRUE, though doesn't consistently lead to the results I expect. And fails when I least expect it.
.. What are you expecting to happen that doesn't happen?
And DESPITE the fact that in the query's datasheet view shows 0 and -1, I CANNOT filter by -1. But I can filter all three by 1 Which is a surprising result to say the least--since Access is showing me -1.
.. Access is definitely confusing the issue which is one more reason to use True and False. NOT "True" and "Not True"
.. In a database linked to SQL Server, I would NEVER use a filter. I don't want Access to bring down all the rows and filter locally. That defeats the purpose of using SQL Server. It is best to use queries with criteria.
Not surprising is that I can create bulletproof filters with = 0 and <> 0.
Your mileage clearly varies from mine. That's nice. But since you clearly cannot make a bulletproof case for the infallibility of TRUE/FALSE in the way you'd like to handle it, I am not sure what the objection is. When I allow users to "filter" forms, I have the querydef refer to the unbound selection fields when the selection is a simple one or two fields. When the selection is complex as it can be for reports, I build the SQL on the fly.
And at any rate, it's all about creating apps that are maintainable over the long run. How does adding controls to tables aid in that regard?
.. You are not adding a control to a table. You are "allowing" Access to display data in a logical way. Most people think that checkboxes are intuitive and easy to read. They don't want to look at 0 and -1, especially users. If you don't want to see a checkbox, DON'T use a Yes/No data type, use an Integer.
It doesn't. It's one of the many things MS has flanged into Access to make it 'friendlier' for the end user. And one of those things -- like hyperlink fields, or permitting subdatasheets -- that it makes long-run sense to avoid.
Your whole argument revolves around avoiding something because you or someone else has misused it in the past.
Let's agree to disagree on another topic.
@Gus,
Apparently I missed a couple of posts and thought you were referring to the exchange between Nick and me. I agree 100% that Yes/No fields should be used ONLY when the field poses a question that can be answered by Yes/No or True/False and NEVER to indicate 1 of 2 choices such as English or German.
Your whole argument revolves around avoiding something because you or someone else has misused it in the past.
Yup, misused it isn't quite right, but created/inherited something that makes a Boolean field a bit of a landmine.
And when I say 'filter' in the context here (which is discussing getting an Access query to behave) that is synonymous with a criteria or WHERE clause
I understand that in the wide scope of the universe there are many ways for the WHERE clause of a SQL statement in Access to do things that the query's creator did not anticipate when a Boolean field is in play, like when
a) Null gets involved
b) backends change or get intermixed
c) linked tables to other files/datasources get involved.
d) local tables and linked tables to SQL Server get intermixed
e) you converted your backend to SQL Server back in the day when Access DIDN'T deal properly with bit fields (been there, done that) and so you converted your bit fields to int fields (and maybe back again! after proper support for SQL Server's TRUE value been 1 was added)
f) any number of other things.
I get it.
You'd like to advise everyone to test for TRUE and FALSE when you're using a Boolean field as a criteria.
.. Access is definitely confusing the issue which is one more reason to use True and False. NOT "True" and "Not True"
Indeed, Access is definitely confusing the issue -- which is an absolute, undeniably, reliably, universally and permanently good reason to test for =0 and <>0 because absolutely nothing -- Not Access, Not Excel, Not SQL Server and Not Oracle either -- gets confused about that. False is universally 0.
You don't like negation logic. Fair enough. You would recommend not using negation logic unless it serves a purpose. Fair enough. It almost sounds like you've been tortured with some hideously complex WHERE NOT EXISTS logic somewhere along the line. Fair enough. Be in the business long enough and you'll develop tricks, habits and patterns and want to share those with other and with fervor occasionally (Like folks who date back to the A97 era will religiously rename controls that have the same name as a fieldname -- despite that now being an MS Access default and never having caused grief post A2003.) Fair enough.
Filtering by TRUE, though doesn't consistently lead to the results I expect. And fails when I least expect it.
.. What are you expecting to happen that doesn't happen?
Gee, I don't know. I expect that TRUE should return all values that are NOT FALSE. That's the expected and intuitive result, on my part. Mathematically that's the expected result. But I have learned that no, TRUE does not necessarily equal NOT FALSE. I still think instinctively that TRUE = NOT FALSE and NOT TRUE = FALSE -- but practically, I know that can be incorrect depending upon circumstances, usually very difficult to detect circumstances yet to boot.
Let's agree to disagree on another topic.
Sure. I am not sure why you chimed at the point you did. At that point there was no bone of contention about the 'proper' way to create query criteria on Boolean fields. You brought that up. My point is that in the query editor there's no option to put a checkbox in the criteria row -- so checkboxes in the datasheet is just damn confusing.
Who in their right minds would recommend checkboxes / yes-no and/or lookup fields in tables after the first time they had confused the hell out of themselves when trying to create query criteria? I don't think you recommend 'controls' in tables either. You can use checkboxes and/or Yes/No formatting in the table. Doesn't mean it's a good idea. Neither are lookup fields. Neither is applying formats to DateTime fields until you truly understand what you are actually storing and what that formatting is really doing.
I was reinforcing @Dale Fye's comment @ID: 40525262
Keep your tables for the true values of your data, and don't muck with presentation there.
It's confusing over the very long run.
Yup, misused it isn't quite right, but created/inherited something that makes a Boolean field a bit of a landmine.
And when I say 'filter' in the context here (which is discussing getting an Access query to behave) that is synonymous with a criteria or WHERE clause
I understand that in the wide scope of the universe there are many ways for the WHERE clause of a SQL statement in Access to do things that the query's creator did not anticipate when a Boolean field is in play, like when
a) Null gets involved
b) backends change or get intermixed
c) linked tables to other files/datasources get involved.
d) local tables and linked tables to SQL Server get intermixed
e) you converted your backend to SQL Server back in the day when Access DIDN'T deal properly with bit fields (been there, done that) and so you converted your bit fields to int fields (and maybe back again! after proper support for SQL Server's TRUE value been 1 was added)
f) any number of other things.
I get it.
You'd like to advise everyone to test for TRUE and FALSE when you're using a Boolean field as a criteria.
.. Access is definitely confusing the issue which is one more reason to use True and False. NOT "True" and "Not True"
Indeed, Access is definitely confusing the issue -- which is an absolute, undeniably, reliably, universally and permanently good reason to test for =0 and <>0 because absolutely nothing -- Not Access, Not Excel, Not SQL Server and Not Oracle either -- gets confused about that. False is universally 0.
You don't like negation logic. Fair enough. You would recommend not using negation logic unless it serves a purpose. Fair enough. It almost sounds like you've been tortured with some hideously complex WHERE NOT EXISTS logic somewhere along the line. Fair enough. Be in the business long enough and you'll develop tricks, habits and patterns and want to share those with other and with fervor occasionally (Like folks who date back to the A97 era will religiously rename controls that have the same name as a fieldname -- despite that now being an MS Access default and never having caused grief post A2003.) Fair enough.
Filtering by TRUE, though doesn't consistently lead to the results I expect. And fails when I least expect it.
.. What are you expecting to happen that doesn't happen?
Gee, I don't know. I expect that TRUE should return all values that are NOT FALSE. That's the expected and intuitive result, on my part. Mathematically that's the expected result. But I have learned that no, TRUE does not necessarily equal NOT FALSE. I still think instinctively that TRUE = NOT FALSE and NOT TRUE = FALSE -- but practically, I know that can be incorrect depending upon circumstances, usually very difficult to detect circumstances yet to boot.
Let's agree to disagree on another topic.
Sure. I am not sure why you chimed at the point you did. At that point there was no bone of contention about the 'proper' way to create query criteria on Boolean fields. You brought that up. My point is that in the query editor there's no option to put a checkbox in the criteria row -- so checkboxes in the datasheet is just damn confusing.
Who in their right minds would recommend checkboxes / yes-no and/or lookup fields in tables after the first time they had confused the hell out of themselves when trying to create query criteria? I don't think you recommend 'controls' in tables either. You can use checkboxes and/or Yes/No formatting in the table. Doesn't mean it's a good idea. Neither are lookup fields. Neither is applying formats to DateTime fields until you truly understand what you are actually storing and what that formatting is really doing.
I was reinforcing @Dale Fye's comment @ID: 40525262
Keep your tables for the true values of your data, and don't muck with presentation there.
It's confusing over the very long run.
I expect that TRUE should return all values that are NOT FALSEWrong, Wrong, Wrong IF you have allowed those pesky nulls to creep in.
permanently good reason to test for =0 and <>0 because absolutely nothing -- Not Access, Not Excel, Not SQL Server and Not Oracle either -- gets confused about thatWrong again once you let nulls creep in.
I am not sure why you chimed at the point you didYou were talking about the "badness" of certain elements, one of which was checkboxes and I disagree.
Who in their right minds would recommend checkboxes / yes-no and/or lookup fields in tables after the first time they had confused the hell out of themselves when trying to create query criteria?I can assure you that I am in my right mind and I expect you to refrain from getting personal in your argument.
I don't think you recommend 'controls' in tables eitherControls are not stored in tables. That is simply a clear way of displaying True and False. It is ONLY if you allow null to muddy the waters that there is any issue at all. You might say that textboxes are bad also because people unwitting allow ZLS and that causes significantly more problems since it is much more common.
The recurring theme revolves around nulls in Yes/No fields. Once you understand that Access is wrong to allow them at all, you will define the field as required and give it a default of No or Yes (whichever makes sense) or give them no default and make the user choose. If nulls can't be saved to the field, all your assumptions about True and False returning the expected rows will be fulfilled. Even the convoluted use of Not True and Not False will return the expected rows.
HAPPY NEW YEAR!!!!!!
I expect you to refrain from getting personal in your argument.
I never wish to give offense.
Controls are not stored in tables
Call it what you wish. The appearance to combobox-like and checkbox-like UI elements in the display of a table or query.
I don't find these things to be helpful, at all. In my opinion, tables and queries are not places to gussie up the presentation of data.
That is simply a clear way of displaying True and False.
I will disagree, politely. It is a way of obfuscating the stored values of zero for NULL and FALSE and some other number (usually -1 or 1) for TRUE, and in my considered opinion provides neither clarity or simplicity. We agree to disagree on this point.
It is ONLY if you allow null to muddy the waters that there is any issue at all.
Access permits it. SQL Server permits it. Three value logic for checkboxes exists and someone reading this post in the years and decades ahead may need to deal with the issues that occur because, while Access is willing to permit NULL in a Boolean field, it has no present method to display it differently than FALSE. We do not disagree that the existence of NULL in Boolean fields presents complications
But, someone reading this post may have decided that they had very good reasons for wanting to permit null in their Boolean fields or they may have inherited such circumstances from another developer. There is a broad scope of experience at play.
It becomes worthwhile to know that in the Venn diagram of the values in a Boolean field, there may be THREE possible values. You may religiously wish to eradicate the third as an article of faith, much like the old saying 'to use GOTO is to depart from the path of righteousness.' But that doesn't mean that everyone can or will -- and so, to recognize and guard against the possibility is prudent. And to provide advice with explanation about how to do so is neither incorrect, nor a sin, nor worthy of a jeremiad.
Once you understand that Access is wrong to allow them at all
That is your considered opinion. It clearly differs from MS's. And I doubt very highly that MS will deprecate three-value logic at this point. Like the t-shirt says: 'NULL happens, man. Deal with it!'
:D
Nick67
I never wish to give offense.
Controls are not stored in tables
Call it what you wish. The appearance to combobox-like and checkbox-like UI elements in the display of a table or query.
I don't find these things to be helpful, at all. In my opinion, tables and queries are not places to gussie up the presentation of data.
That is simply a clear way of displaying True and False.
I will disagree, politely. It is a way of obfuscating the stored values of zero for NULL and FALSE and some other number (usually -1 or 1) for TRUE, and in my considered opinion provides neither clarity or simplicity. We agree to disagree on this point.
It is ONLY if you allow null to muddy the waters that there is any issue at all.
Access permits it. SQL Server permits it. Three value logic for checkboxes exists and someone reading this post in the years and decades ahead may need to deal with the issues that occur because, while Access is willing to permit NULL in a Boolean field, it has no present method to display it differently than FALSE. We do not disagree that the existence of NULL in Boolean fields presents complications
But, someone reading this post may have decided that they had very good reasons for wanting to permit null in their Boolean fields or they may have inherited such circumstances from another developer. There is a broad scope of experience at play.
It becomes worthwhile to know that in the Venn diagram of the values in a Boolean field, there may be THREE possible values. You may religiously wish to eradicate the third as an article of faith, much like the old saying 'to use GOTO is to depart from the path of righteousness.' But that doesn't mean that everyone can or will -- and so, to recognize and guard against the possibility is prudent. And to provide advice with explanation about how to do so is neither incorrect, nor a sin, nor worthy of a jeremiad.
Once you understand that Access is wrong to allow them at all
That is your considered opinion. It clearly differs from MS's. And I doubt very highly that MS will deprecate three-value logic at this point. Like the t-shirt says: 'NULL happens, man. Deal with it!'
:D
Nick67
Since you obviously believe that it is OK to allow null in a data type that should only allow true and false, I'll leave it to you to wrangle with the issues that causes. Just don't complain about not getting the results you expect.
OK. I think I'd like to "normalize" the database by adding the extra table with just the two values and index it to the corresponding job opening table. That seems doable and also appears to allow for further refinement if there's a time in the future when I would need yet another country/language.
So the only question left is.... Can I run the mail merge off a query? And if I understand correctly, I am able to create a query that pulls from multiple tables -- that should be a no-brainer, right?
Thanks for all the help! And, despite the moderately heated discussion, the points made actually helped me to understand the underlying processes better, so thanks for the detailed argumentation.
So the only question left is.... Can I run the mail merge off a query? And if I understand correctly, I am able to create a query that pulls from multiple tables -- that should be a no-brainer, right?
Thanks for all the help! And, despite the moderately heated discussion, the points made actually helped me to understand the underlying processes better, so thanks for the detailed argumentation.
>> Can I run the mail merge off a query? And if I understand correctly, I am able to create a query that pulls from multiple tables -- that should be a no-brainer, right?
Yes. And if you couldn't, then you Make a Table from the query anyway.
Yes. And if you couldn't, then you Make a Table from the query anyway.
So I create a table with a primary key and 2 other fields: the long form of the language and the short abbreviation.
To create a one-to-one relationship, I just connect the primary key of the language table to the primary key of the job opening table -- is that correct? And I do not need to enforce referential integrity because if a record in the job openings field is updated or deleted, I don't want anything to be updated or deleted in the language table -- is that also correct?
Thanks...
To create a one-to-one relationship, I just connect the primary key of the language table to the primary key of the job opening table -- is that correct? And I do not need to enforce referential integrity because if a record in the job openings field is updated or deleted, I don't want anything to be updated or deleted in the language table -- is that also correct?
Thanks...
So I create a table with a primary key and 2 other fields: the long form of the language and the short abbreviation.
Yes
To create a one-to-one relationship, I just connect the primary key of the language table to the primary key of the job opening table -- is that correct?
No.
Instead, you will add a field to the Job Opening table. Let's say the you name the Primary Key in your new table LanguageID as an autonumber field. In your job opening table you would add a new field, LanguageID as a long integer field.
To create a one-to-one relationship
No, it won't be a one-to-one, although I think the jargon here may trip you up. Each record in the job opening table (One) has a number of possible (to many) language choices. Only one language will be chosen (your thoughts of one-to-one) but many (well ok, two) are possible.
And I do not need to enforce referential integrity
No
You want referential integrity enforced. If 1 is English and 2 is German, you don't want to be able to enter 3 as a value. It doesn't exist. That's referential integrity--that the values of LanguageID in the job openings table MUST be valid entries in the language table
Yes
To create a one-to-one relationship, I just connect the primary key of the language table to the primary key of the job opening table -- is that correct?
No.
Instead, you will add a field to the Job Opening table. Let's say the you name the Primary Key in your new table LanguageID as an autonumber field. In your job opening table you would add a new field, LanguageID as a long integer field.
To create a one-to-one relationship
No, it won't be a one-to-one, although I think the jargon here may trip you up. Each record in the job opening table (One) has a number of possible (to many) language choices. Only one language will be chosen (your thoughts of one-to-one) but many (well ok, two) are possible.
And I do not need to enforce referential integrity
No
You want referential integrity enforced. If 1 is English and 2 is German, you don't want to be able to enter 3 as a value. It doesn't exist. That's referential integrity--that the values of LanguageID in the job openings table MUST be valid entries in the language table
OK. That makes good sense. And though I enforce referential integrity, I don't need to cascade anything because I don't want to make any changes to the language table from data entry in the job openings table. Am I correct in that assumption?
Another thing happened when I did what you said that surprised me a bit. The "one-to-many" is actually a "many-to-one" from the job openings table. That's correct, isn't it?
As a little background, I've never done any database work professionally -- it's always been private. And, believe it or not, I started way back with Borland's Paradox (and WordPerfect's DataPerfect). I missed the FoxBase craze and the dBase world, but have dabbled in Access ever since Office 97. I know just enough to be dangerous.... ;-)
Thanks again for the help.
Another thing happened when I did what you said that surprised me a bit. The "one-to-many" is actually a "many-to-one" from the job openings table. That's correct, isn't it?
As a little background, I've never done any database work professionally -- it's always been private. And, believe it or not, I started way back with Borland's Paradox (and WordPerfect's DataPerfect). I missed the FoxBase craze and the dBase world, but have dabbled in Access ever since Office 97. I know just enough to be dangerous.... ;-)
Thanks again for the help.
Generally, I check off the cascade update option -- although since I religiously use autonumbers as primary keys (which can't be changed anyway!) this is superfluous. And that's what the option is for. If you change the value of a primary key, that the change propagates everywhere the primary key is in a relationship.
Let's say for fun you used two letters as the primary key (EN and DE) They are unique, which is the only absolute requirement for a primary key. Then later, you decided that you wanted to change DE to GM. Cascade Update would change all the DE values (which you used instead of a number) to GM in the job opening table. Another old, old argument is about the use of anything other than an autonumber as a primary key. Let's not blow up that bridge :)
Few if any developers check off cascade delete. Rubbing out data is something you want to handle thoughtfully. Letting Access go an whack data from multiple tables because something got deleted elsewhere is not something most folks are comfortable with.
Let's say for fun you used two letters as the primary key (EN and DE) They are unique, which is the only absolute requirement for a primary key. Then later, you decided that you wanted to change DE to GM. Cascade Update would change all the DE values (which you used instead of a number) to GM in the job opening table. Another old, old argument is about the use of anything other than an autonumber as a primary key. Let's not blow up that bridge :)
Few if any developers check off cascade delete. Rubbing out data is something you want to handle thoughtfully. Letting Access go an whack data from multiple tables because something got deleted elsewhere is not something most folks are comfortable with.
This is actually clearer...
So then, is this many-to-one between job opening and language tables correct? Can you elaborate?
I understand that I want many possible entries in the phone number tables, or contacts or openings per each company. Do I interpret this as wanting many possible openings for each language? I suppose that makes good sense, but I'm getting that I might not understand relationships quite like I should...
So then, is this many-to-one between job opening and language tables correct? Can you elaborate? I understand that I want many possible entries in the phone number tables, or contacts or openings per each company. Do I interpret this as wanting many possible openings for each language? I suppose that makes good sense, but I'm getting that I might not understand relationships quite like I should...
Looks good.
Now for your mail merge(s)
You'll create a couple of queries that pull all the data together that the merge needs, and in one you'll have JOBOpen.LangID = 1 for the English merge and in the other JOBOpen.LangID = 2 for the German merge.
Now for your mail merge(s)
You'll create a couple of queries that pull all the data together that the merge needs, and in one you'll have JOBOpen.LangID = 1 for the English merge and in the other JOBOpen.LangID = 2 for the German merge.
You always want to check the enforce RI checkbox because that will prevent orphans from being added to or created in child tables.
Cascade update is never used when the primary key of the lookup table is an autonumber. Nothing will ever cascade and so setting it will only confuse someone who doesn't completely understand the RI rules.
Cascade delete is almost never used for "lookup" relationships. I say almost never but in reality, I have never encountered a situation where I would want to delete Orders if someone deleted California from the state table. Lookups simply don't control related records. By enforcing RI and not specifying Cascade Delete, the database engine would not allow you to delete California from the state table if there were any orders with CA as a shipping destination. It is however usually used in hierarchical relationships such as Order/OrderDetails. If your business rules allow Orders to be deleted, then the most efficient solution is to use CascadeDelete to delete the OrderDetails when an order is deleted. If your business rules do not allow orders to be deleted, then you would not specify Cascade delete on the relationship with OrderDetails.
I think I mentioned the lookup tables mini-app I use in all my applications. Due to its nature, it requires an autonumber as a primary key. However, if I were to make this particular lookup table, I would use the language abbreviation as the PK rather than an autonumber. I believe in using natural keys when they are available and this is a case where you have a natural key. The benefit is that you will see something meaningful when you are looking at raw data.
Cascade update is never used when the primary key of the lookup table is an autonumber. Nothing will ever cascade and so setting it will only confuse someone who doesn't completely understand the RI rules.
Cascade delete is almost never used for "lookup" relationships. I say almost never but in reality, I have never encountered a situation where I would want to delete Orders if someone deleted California from the state table. Lookups simply don't control related records. By enforcing RI and not specifying Cascade Delete, the database engine would not allow you to delete California from the state table if there were any orders with CA as a shipping destination. It is however usually used in hierarchical relationships such as Order/OrderDetails. If your business rules allow Orders to be deleted, then the most efficient solution is to use CascadeDelete to delete the OrderDetails when an order is deleted. If your business rules do not allow orders to be deleted, then you would not specify Cascade delete on the relationship with OrderDetails.
I think I mentioned the lookup tables mini-app I use in all my applications. Due to its nature, it requires an autonumber as a primary key. However, if I were to make this particular lookup table, I would use the language abbreviation as the PK rather than an autonumber. I believe in using natural keys when they are available and this is a case where you have a natural key. The benefit is that you will see something meaningful when you are looking at raw data.
:) LOL Boom!
Another old, old argument is about the use of anything other than an autonumber as a primary key. Let's not blow up that bridge :)
SInce it is in ruins now anyway...
Long ago I was persuaded by @harfang's analysis of 'natural vs surrogate' keys in @Jim Dettman's article here The minute a sequential autonumber key is created, it begins to capture information. Let's say you enter 20 language values. With an autonumber sequential key, you now know what order you created them in. Since the key is now capturing information it is no longer strictly 'synthetic' since it has meaning and relationship to the data.
And indexes on numeric data perform better than indexes on text.
And an autonumber CANNOT be changed -- which I like.
And when you look at raw data you won't see anything immediately useful--which indicates that another table is involved that has the useful data in it's most compact form. When you do see meaningful data in a table, you know you are looking at its repository.
But that's me.
Clearly @PatHartman and I (and many, many others) differ in this. And that's ok. Choose what makes sense for you, your data and your application.
Another old, old argument is about the use of anything other than an autonumber as a primary key. Let's not blow up that bridge :)
SInce it is in ruins now anyway...
Long ago I was persuaded by @harfang's analysis of 'natural vs surrogate' keys in @Jim Dettman's article here The minute a sequential autonumber key is created, it begins to capture information. Let's say you enter 20 language values. With an autonumber sequential key, you now know what order you created them in. Since the key is now capturing information it is no longer strictly 'synthetic' since it has meaning and relationship to the data.
And indexes on numeric data perform better than indexes on text.
And an autonumber CANNOT be changed -- which I like.
And when you look at raw data you won't see anything immediately useful--which indicates that another table is involved that has the useful data in it's most compact form. When you do see meaningful data in a table, you know you are looking at its repository.
But that's me.
Clearly @PatHartman and I (and many, many others) differ in this. And that's ok. Choose what makes sense for you, your data and your application.
I understand that I want many possible entries in the phone number tables, or contacts or openings per each company. Do I interpret this as wanting many possible openings for each language? I suppose that makes good sense, but I'm getting that I might not understand relationships quite like I should...
It's always funny how Access creates relationship visually.
It does look backward.
Try deleting/recreating it.
Drag LangID from JOBLang and drop it on LangID in JOBOpen.
Does it reverse?
It's always funny how Access creates relationship visually.
It does look backward.
Try deleting/recreating it.
Drag LangID from JOBLang and drop it on LangID in JOBOpen.
Does it reverse?
OK. You've seen the relationships. So if I do stay with the autonumber primary key, I'm still not sure how to link it in the form.
I have 2 subforms on tabs that appear in the main form -- one for contacts and one for openings. They are both in tabular(?) format; like a normal datasheet with rows for records and columns for fields. The question is, then, how do I get the field to function as a lookup field for what's listed in the languages table? (So that when I add a job opening record, the language field appears as a drop-down -- perhaps with a default value already listed -- that pulls the long name values from the language table. Does that make sense?
What I did was to use "Lookup Wizard..." under Data Type and it created the relationship for me. I now have the lookup possibilities when I look at the table in datasheet view. I'm hoping that will be automatically pulled over when I create the subform. Does it sound like I did that right?
So bottom line, I no longer show a one-to-many or many-to-one in relationship view. It's just a single line. But when I edit the relationship, it still shows One-To-Many and it does not enforce referential integrity. Remember, this was the automatically-generated relationship from Access' own wizard.
Oh, and it didn't change anything regardless of which table I dragged it from...
I have 2 subforms on tabs that appear in the main form -- one for contacts and one for openings. They are both in tabular(?) format; like a normal datasheet with rows for records and columns for fields. The question is, then, how do I get the field to function as a lookup field for what's listed in the languages table? (So that when I add a job opening record, the language field appears as a drop-down -- perhaps with a default value already listed -- that pulls the long name values from the language table. Does that make sense?
What I did was to use "Lookup Wizard..." under Data Type and it created the relationship for me. I now have the lookup possibilities when I look at the table in datasheet view. I'm hoping that will be automatically pulled over when I create the subform. Does it sound like I did that right?
So bottom line, I no longer show a one-to-many or many-to-one in relationship view. It's just a single line. But when I edit the relationship, it still shows One-To-Many and it does not enforce referential integrity. Remember, this was the automatically-generated relationship from Access' own wizard.
Oh, and it didn't change anything regardless of which table I dragged it from...
The infinity sign is on the "many" side of the relationship and the "1" is on the "one" side. So in this case JobOpen is the "many" and JobLang is the "one". Lookup tables will always be on the "one" side of the relationship. the "one" table is the parent and the "many" table is the child.
Stop. Do not do anything until you remove the lookup you created on the table. This will cause nothing but trouble once you start writing queries and code. It is a crutch for people who will never learn how to use a query.
Lookups are done with combos on a form. You can let the wizard build the form if you like but then you will have to go to the language field and right click on it. Choose the change to -> Combo option. You will then have to set all the properties to make it function correctly.
Data Tab:
ControlSource -- this should already be populated with the LangID column from the JobOpen table.
RowSource -- this can be a table or a query. It should be JobLang
RowSourceType -- defaults to Table/Query
BoundColumn -- defaults to 1 (this will be the ordinal position of the LangID column in the RowSource query/table
LimitToList -- Yes
AllowValueListEdits -- No
Format Tab:
ColumnCount -- should be 3 which is the number of columns in the RowSource query/table
ColumnWidths -- should be 0";1";.5" -- 0 will hide the ID column, the next two will show the other two columns
OtherTab:
Name -- defaults to LangID, I would change to cboLangID
There are other properties you might need to adjust but these will get the job done.
If you go at this from a different direction, you can let the wizard build the form and then delete the langID field. You can then drag the combo control from the Design Tab. This will activate a new wizard that will set up the combo for you. But, how that I have empowered you with the knowledge of which properties you need to set, you don't have to use the wizard. However, if you use it, you absolutely must change the Name property on the Other tab at the end because you want to have a meaningful name not something like combo133.
Stop. Do not do anything until you remove the lookup you created on the table. This will cause nothing but trouble once you start writing queries and code. It is a crutch for people who will never learn how to use a query.
Lookups are done with combos on a form. You can let the wizard build the form if you like but then you will have to go to the language field and right click on it. Choose the change to -> Combo option. You will then have to set all the properties to make it function correctly.
Data Tab:
ControlSource -- this should already be populated with the LangID column from the JobOpen table.
RowSource -- this can be a table or a query. It should be JobLang
RowSourceType -- defaults to Table/Query
BoundColumn -- defaults to 1 (this will be the ordinal position of the LangID column in the RowSource query/table
LimitToList -- Yes
AllowValueListEdits -- No
Format Tab:
ColumnCount -- should be 3 which is the number of columns in the RowSource query/table
ColumnWidths -- should be 0";1";.5" -- 0 will hide the ID column, the next two will show the other two columns
OtherTab:
Name -- defaults to LangID, I would change to cboLangID
There are other properties you might need to adjust but these will get the job done.
If you go at this from a different direction, you can let the wizard build the form and then delete the langID field. You can then drag the combo control from the Design Tab. This will activate a new wizard that will set up the combo for you. But, how that I have empowered you with the knowledge of which properties you need to set, you don't have to use the wizard. However, if you use it, you absolutely must change the Name property on the Other tab at the end because you want to have a meaningful name not something like combo133.
Got it. I tried both ways, but the wizard worked better. The individual instructions were great, but I was left with a blank combo box dropdown. I looked, and just putting in the table name alone for the Row Source didn't work. The wizard built a great little query and that worked fine.
But in any event, I am grateful that I got some better insight into the back end through your explanation. I made sure to rename the combo box (so it can be referred to in other programming, right?). And at the end of the wizard it asked if I wanted to store the value in the table, or just in memory; I stored it in the table in the LangID field...
I sure appreciate the help. I do like how it's shaping up. I'll make sure that I give some answer credit in the next little while...
But in any event, I am grateful that I got some better insight into the back end through your explanation. I made sure to rename the combo box (so it can be referred to in other programming, right?). And at the end of the wizard it asked if I wanted to store the value in the table, or just in memory; I stored it in the table in the LangID field...
I sure appreciate the help. I do like how it's shaping up. I'll make sure that I give some answer credit in the next little while...
However, if you use it, you absolutely must change the Name property on the Other tab at the end because you want to have a meaningful name not something like combo133.
Amen that!
Of course, whether to name the control after the field it is bound to, or not, is another one of those bridges that we need not blow up :) Have a solid naming convention. Stick to it.
Nick67
Amen that!
Of course, whether to name the control after the field it is bound to, or not, is another one of those bridges that we need not blow up :) Have a solid naming convention. Stick to it.
Nick67
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month5 days, 12 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
No it is not. Plain simple and bullet-proof.
1. Lookup table, two records, three fields: ID (Unique autonumber), Code ("EN" or "DE"), Language ("English", "Deutsch")
2. Language field in your main table of data type Long.
3. Referential Integrity between lookup table (PK) and main table (FK)
Now use a combobox to select the language.
/gustav