If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Lookup Field Issues
On Thu, 14 Jan 2010 09:39:22 -0800 (PST), esn wrote:
I'm not trying to insult anyone who's trying to help me, but I don't feel like anyone but Gina is actually making any effort to help. Any post that mentions "data entry" or "forms" is ignoring my situation. Ok, I'm really confused now. In your original post you say, to quote: It works properly when entering data, meaning it won't let the enterer remove the focus from the field if the text they've entered isn't in the list. But for some reason it will allow text values to be pasted into the field even if they aren't in the list. So it would seem that you ARE doing data entry - by copying and pasting - directly into a table. Tables aren't designed for data entry; lookup fields aren't designed to do what you're asking them to do. No one here has actually suggested an alternative to my methods that would work given my circumstances, unless they are really implying that I should re-renter 3 years worth of data from 3 different projects using forms. Certainly not. A form is just a tool, a window to enable manual interaction with the data. Maybe I'm confused on the use of forms for importing preexisting data and that's my issue. Would it even be possible to use forms to do what I'm trying to do? Meaning import data from various files in various formats? Importing should probably be done using File... Get External Data... Link to link to the external file, and then running an Append query to migrate the data into your table. If you have Lookup Fields in your table, then the table in fact contains a numeric ID, not the text; if you do not have that numeric value in the external file that you're attempting to import, your task becomes more complex - you'll need to join to the lookup table to ascertain the numeric value, if it exists, and (perhaps) to create new records in the lookup table if the incoming data does not match any existing record. As for commenting on my "end-users" etc., I know them (and my situation) a lot better than you do. It's not possible for them to do their jobs without writing queries and it wouldn't be possible for me to write every possible query they might need for them. I'm working in a completely different field than most of the people who use Access, and juggling several different databases from several different projects. Making them as intuitive as possible is indeed high on my list of priorities, which is why I'm using a lookup field in the first place. The databases I've designed for my own use don't have a single lookup field, but my boss thinks they're the bees knees and doesn't want to stare at a bunch of foreign keys that don't mean anything to her. You can take it up with her if you think that makes her an idiot. On the contrary, I think she's perfectly right to want to see the human meaningful data. That doesn't mean that she should be routinely using *TABLES* to interact with the data. They're simply not the right tool to do so! Table datasheets (even with lookup fields and subdatasheets) are very limited in functionality. If you choose to sacrifice the functionality of forms, then you are stuck with the limited capabilities of table datasheets. For now I need a way to import preexisting data and data from other sources that will ensure that it complies with the data integrity rules I've set up. Using a lookup field is the simplest way I know of to do that. I could also import the data into new tables and use subselect queries to compare the values in the imported table to the allowable ones, but that leaves me with no simple way of correcting the errors, and I still need to copy and paste or append the data to the existing table when it's corrected. What advantages would that really have in my situation? And how could that possibly be more intuitive? You'll need to explain how your current situation is better. Obviously it isn't, because it lets you append garbage data. If you have garbage data in your input files, then - at some point - you MUST do the comparison to allowable entries; this can be automated to a great extent, but there'll still be cases where the computer CANNOT ascertain the correct value, and a human interaction will be needed. I'm here looking for help with MY SITUATION, not to be your soapbox. I don't need a lecture but a couple suggestions would be nice. And don't worry, when all the data is finally brought together and I know it's accurate, I'll work on convincing my boss to let me convert that lookup to a simple foreign key, and the comboboxes I have in my forms will do their job. Getting to that point is my issue. If you could explain a bit more about the nature of the data you're importing (you have not yet done so), the nature of the tables into which you're importing (you have not yet done so), the nature of the mismatches (we don't know that either), we'll be glad to do so. I apologize of the didactic tone of my previous posts... but given your intense tone in the message to which I'm replying, I'll wait a bit to apologize for this one. I'd like to be able to help, but I really do need to understand the question first. -- John W. Vinson [MVP] |
#12
|
|||
|
|||
Lookup Field Issues
If you are adverse to soapboxes that blog posting was not a good way to
advance that preference. People are in fact trying to help by advising you of the pitfalls of lookup fields in tables. I can find a posting on any point of view I wish to advance, but the fact of its being on the internet does not impart automatic credibility. Imported data either complies with your data rules or it does not. Lookup fields in tables are not going to change that. Without knowing anything about your data it is difficult to be specific about what may help with the irregular data you need to import. BTW, I want to make it clear that the lookup fields to which many people are adverse are the ones that show up in the table as combo boxes. There is at least a chance we are not all talking about the same thing. You seem to have some misunderstanding about using forms to import data. Forms are interfaces that may contain code for importing data, but they do not actually contain any data, any more than windows contain what you see through them. You can use File Get External Data Import, and import data from any number of formats, including spreadsheets. You can code that into a form, but in the end you are importing into a table, or displaying in a query. Your boss, and the "Novice User" in the blog, indicate they to not want to "stare at a bunch of foreign keys." It is bewildering that the person writing the blog makes reference to the Novice User's comment that "users don’t want to have to look up the CompanyID to enter a new customer! It’s just easier to put all the company information in the customers table." That is absurd, and indicates the author has little understanding of the issue. Of course the Company information is all in one Company table. That's what a table is for: to store data about a specific entity. CompanyID is one such datum. Anyhow, how could somebody look up a CompanyID for a new company that doesn't have a CompanyID yet? Neither my users nor I want to look at meaningless numeric key fields, so we don't. I create combo boxes with (typically) a two-column Row Source: the ID field, and a text description. The first column is hidden, but it is the bound column. I don't need a Lookup field in a table to achieve that result. In fact, they may be a hindrance. At best they do no harm. If I am looking up companies to insert a company into a record, the combo box displays the company name, but stores the hidden, unchanging ID field. If I am going to use an ID field I want it where I can keep track of it, not have it hidden in a lookup field. I as the developer am the only one who sees that numeric ID field. From the end-user's point of view there is no difference in the form's interface if you use a lookup field or a standard combo box. For you as the developer, the lookup field is hidden from view, so you must remember it is there when you run across the almost inevitable anomalies that will result from its use. As for building queries, it should be possible for users to enter the query fields and/or criteria from a user interface that does not require them to work directly with the query design grid. If inexperienced users have that kind of access to the data they are very likely to cause problems at some point. The details depend on the situation. Regarding the claim that you are in a diferent field than most Access users, I submit to you that most Access users are in a different field than other Access users. I don't think there is a "most". I build specialized applications in a niche industry, but design principles are universal. They can apply in your situation, too. esn wrote: I'm not trying to insult anyone who's trying to help me, but I don't feel like anyone but Gina is actually making any effort to help. Any post that mentions "data entry" or "forms" is ignoring my situation. No one here has actually suggested an alternative to my methods that would work given my circumstances, unless they are really implying that I should re-renter 3 years worth of data from 3 different projects using forms. Maybe I'm confused on the use of forms for importing preexisting data and that's my issue. Would it even be possible to use forms to do what I'm trying to do? Meaning import data from various files in various formats? As for commenting on my "end-users" etc., I know them (and my situation) a lot better than you do. It's not possible for them to do their jobs without writing queries and it wouldn't be possible for me to write every possible query they might need for them. I'm working in a completely different field than most of the people who use Access, and juggling several different databases from several different projects. Making them as intuitive as possible is indeed high on my list of priorities, which is why I'm using a lookup field in the first place. The databases I've designed for my own use don't have a single lookup field, but my boss thinks they're the bees knees and doesn't want to stare at a bunch of foreign keys that don't mean anything to her. You can take it up with her if you think that makes her an idiot. For now I need a way to import preexisting data and data from other sources that will ensure that it complies with the data integrity rules I've set up. Using a lookup field is the simplest way I know of to do that. I could also import the data into new tables and use subselect queries to compare the values in the imported table to the allowable ones, but that leaves me with no simple way of correcting the errors, and I still need to copy and paste or append the data to the existing table when it's corrected. What advantages would that really have in my situation? And how could that possibly be more intuitive? I'm here looking for help with MY SITUATION, not to be your soapbox. I don't need a lecture but a couple suggestions would be nice. And don't worry, when all the data is finally brought together and I know it's accurate, I'll work on convincing my boss to let me convert that lookup to a simple foreign key, and the comboboxes I have in my forms will do their job. Getting to that point is my issue. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201001/1 |
#13
|
|||
|
|||
Lookup Field Issues
Didn't intend the "intense tone" mentioned, just frustrated with the
slew of responses recommending a combo box on a form, which clearly is not a solution to my particular problem. You guys know a lot more about this stuff than I do and I'm not trying to claim otherwise, but that doesn't mean I'm a complete idiot either. Thanks for putting in a little more effort to understanding the problem. So it would seem that you ARE doing data entry - by copying and pasting - directly into a table. I guess I'm considering the copy and paste process "importing." What I'm trying to do is import data that has already been entered. If my terminology there is screwed up I apologize, but I feel like that's very different objective from "entering." Any time I have referenced "data entry" I'm referring to punching in the records one by one. The suggestion about using "File... Get External Data... Link to" hits closer to the problem, but as I wrote above combining this feature with an append query results in an message containing the number of records that were not imported due to violations, with no simnple way of determining which records those are. If I then want to determine what needs to be corrected I have to use another query to compare the external data to the imported data, which will show unmatched records as results but doesn't allow the corrections to be made within those results. Therefore I need to go back and find the records in the source data separately, correct them, and then repeat the append query with only the unmatched records. This ends up being highly inefficient given the number of records and errors I'm working with. I'm importing a few to 10 thousand records at a time and the number of records with errors is somewhere around ten percent. Some of the erroneous values are repeated many times in the data, but others only appear a few times. The errors have little consistency and arise in various ways. In other words, if I were to list unique incorrect values there would be at least a few hundred. I don't have the time or patience to automate all of these corrections in an update query or use the Replace function a few hundred times to fix the source data. The chances of my own mistakes creeping into the data at that point also seem unacceptably high. What I find to be an easier method is to copy and past the data directly into the data tables from the source files, either spreadsheets or existing tables. This way any records that comply with all of my rules are pasted and those that don't are pasted to a separate table. I can use this table to make all the corrections I need by hand, then paste the records from "Paste Errors" into the data table. This, while clearly looked down upon, ends up being a much faster and more reliable way to get the records corrected and appended to the table in my experience. I understand that this is far from an ideal process, but having a bunch of unorganized data from various sources is far from an ideal situation. The purpose of the lookup field in this process I think is obvious. There are other ways I could work around using a lookup field but as far as I understand it they would be more complex and not particularly advantageous. An example would be to paste all of the data to a new table before correcting it, then relate the values in the Lookup table to the values in the new table. From there I can find unmatched, but the "unmatched records" results aren't going to be upadateable, so I will need to look up the records in the source data independently based on the results of that query, then correct them. In essence I'm using the lookup feild as a shortcut for this process, which will isolate the records with values that don't match and allow me to correct them immediately without things being overly complex. The original question, then, is why does this work if the primary key of the lookup table is an ID field, but not if the primary key is text? It was suggested to me by someone in a different group that things would be greatly simplified if I did away with the ID field in the lookup table, because the corresponding text values are required and unique already. As I stated above this would make my boss happy. (I know she shouldn't be messing with the data in tables but you try telling her that and see where it gets you.) Another consideration is that some of the data I'm importing comes from older databases with lookup fields based on a text column in a lookup table. Making that conversion resulted in a breakdown of my copying and pasting workflow, because incorrect values were no longer rejected by the field, and no matter what changes I made nothing seemed to help. And strangely enough, as I originally posted, the field will not except incorrect values when they are typed in. The only solution I can find is to move the primary key in the lookup table back to the "ID" field and tell my boss to deal with it. As for providing more info on the data and the tables, I understand it may be hard to grasp my perspective without more info, but at this point I've invested too much time in this discussion already. If you're willing to assume I have a decent grasp of database design (and I know most people here aren't as soon as "lookup" is mentioned) and that I'm doing the best I can based on the various objectives and end- users this database has to serve, then I'd appreciate any insight. |
#14
|
|||
|
|||
Lookup Field Issues
Me too, to tell the truth. Most of my databases have at least some users who
use them just a few times a year, so I want the interfaces to be as obvious as a good shopping web page. Granted, some training is usually necessary, and I don't always live up to that ideal, but it is my objective. Keith Wilby wrote: A good portion of my development time is spent making the interface as intuitive as is possible. But that's me, and may not be the approach others would take. It certainly *is* the approach that I take and IMO is the *only* approach. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201001/1 |
#15
|
|||
|
|||
Lookup Field Issues
Minor correction. I never said that I don't use Access much. I said "...don’t
do nearly as much Access development as I once did.." More specifically, for a period of about 10 years I used it daily as one of my primary tools. Recently I have been down to only about 5 hours a week with it. I've been using Access since version 1.0 professionally and have written countless apps in it and trained dozens of people how to use it over those years. I'd also like to know which "real objection" you think I glossed over. I didn't find any of them to be valid and didn't think I implied otherwise. BruceM wrote: If you want to talk about condescending, that blog post beats anything I've seen in a while. The person writing it admits he doesn't use Access much, then dismisses every objection to lookup fields as irrelevant without investigating any of the claims, or by glossing over what he seems to acknowledge are real objections, or by acknowledging he does not understand the objection but that it is irrelevant anyhow. I use Limit To List to restrict allowable values in a combo box on a form. No validation rule is needed. I regard tables as places to store data, not as substitute forms. If I need a combo box, I build it on the form, not in the table. Preexisting data can be a problem, but sometimes there's no solution but to import it all and fix it, or fix it before importing if that makes more sense. It seems I approach database design differently than you do. I typically build databases in support of the jobs other people need to do. I don't need to know all the details of their jobs to build a database, and they should not need to know much about databases to do their jobs. If I am asking low- level users to build queries or otherwise get under the hood then I am not doing my job as developer. A good portion of my development time is spent making the interface as intuitive as is possible. But that's me, and may not be the approach others would take. I have to say those are pretty condescending responses. I understand the value of forms for data entry and that really doesn't address my [quoted text clipped - 36 lines] return the primary key to the ID field to get the behavior I'm looking for. Which means I get to do everyone's work for them. |
#16
|
|||
|
|||
Lookup Field Issues
Esn,
While I happy for the thanks please understand I dislike look-up fields in tables as much as the next guy. I just thought since you'd gotten that information I would offer a way to handle our situation that would eliminate using a look-up field AND as a by product, give you what you want which is to Limit the List. Does that work for you? Because appeneind to a table and making the field dependent on the newly created table you get what you want with no retyping or re-entering AND if you have an import routine set up on a form you can actually append any new values to that table with no one the wiser. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "esn" wrote in message ... I'm not trying to insult anyone who's trying to help me, but I don't feel like anyone but Gina is actually making any effort to help. Any post that mentions "data entry" or "forms" is ignoring my situation. No one here has actually suggested an alternative to my methods that would work given my circumstances, unless they are really implying that I should re-renter 3 years worth of data from 3 different projects using forms. Maybe I'm confused on the use of forms for importing preexisting data and that's my issue. Would it even be possible to use forms to do what I'm trying to do? Meaning import data from various files in various formats? As for commenting on my "end-users" etc., I know them (and my situation) a lot better than you do. It's not possible for them to do their jobs without writing queries and it wouldn't be possible for me to write every possible query they might need for them. I'm working in a completely different field than most of the people who use Access, and juggling several different databases from several different projects. Making them as intuitive as possible is indeed high on my list of priorities, which is why I'm using a lookup field in the first place. The databases I've designed for my own use don't have a single lookup field, but my boss thinks they're the bees knees and doesn't want to stare at a bunch of foreign keys that don't mean anything to her. You can take it up with her if you think that makes her an idiot. For now I need a way to import preexisting data and data from other sources that will ensure that it complies with the data integrity rules I've set up. Using a lookup field is the simplest way I know of to do that. I could also import the data into new tables and use subselect queries to compare the values in the imported table to the allowable ones, but that leaves me with no simple way of correcting the errors, and I still need to copy and paste or append the data to the existing table when it's corrected. What advantages would that really have in my situation? And how could that possibly be more intuitive? I'm here looking for help with MY SITUATION, not to be your soapbox. I don't need a lecture but a couple suggestions would be nice. And don't worry, when all the data is finally brought together and I know it's accurate, I'll work on convincing my boss to let me convert that lookup to a simple foreign key, and the comboboxes I have in my forms will do their job. Getting to that point is my issue. |
#17
|
|||
|
|||
Lookup Field Issues
FWIW... if the real problem you are dealing with is dirty/imperfect
data, then this is the way I have solved it in the past. Not saying it's the best way or the only way - only that it worked for me. And it will do cleaning better and better the more you use it. The problem I had was that I was trying to summarize cancer treatment data and people would misspell symptoms and so on. The solution was to create a bridge table... something like this: CREATE TABLE SlangTermToMedicalTerm( SlangTerm TEXT(50) PRIMARY KEY, MedicalTerm TEXT(50) ) then at least you can use a simple outer join to identify incorrect terms and fix them... granted you have lots of variations of "SlangTerm" per medical term, but as the table grows, your workload will decrease... You could use this cross-reference table for updates if you wanted... Hope I didn't misunderstand the problem... |
#18
|
|||
|
|||
Lookup Field Issues
Piet
Are you saying that your users were still entering misspellings and slang terms, but you used your translation table to handle this? And that it falls to you to figure out which slang terms and misspellings to connect to which medical terms? I may be misunderstanding, but wouldn't it also work to provide a list of medical terms from which the users would have to choose? It seems to me that this approach would prevent misspellings ... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Piet Linden" wrote in message ... FWIW... if the real problem you are dealing with is dirty/imperfect data, then this is the way I have solved it in the past. Not saying it's the best way or the only way - only that it worked for me. And it will do cleaning better and better the more you use it. The problem I had was that I was trying to summarize cancer treatment data and people would misspell symptoms and so on. The solution was to create a bridge table... something like this: CREATE TABLE SlangTermToMedicalTerm( SlangTerm TEXT(50) PRIMARY KEY, MedicalTerm TEXT(50) ) then at least you can use a simple outer join to identify incorrect terms and fix them... granted you have lots of variations of "SlangTerm" per medical term, but as the table grows, your workload will decrease... You could use this cross-reference table for updates if you wanted... Hope I didn't misunderstand the problem... |
#19
|
|||
|
|||
Lookup Field Issues
On Thu, 14 Jan 2010 11:41:38 -0800 (PST), esn wrote:
What I find to be an easier method is to copy and past the data directly into the data tables from the source files, either spreadsheets or existing tables. This way any records that comply with all of my rules are pasted and those that don't are pasted to a separate table. I can use this table to make all the corrections I need by hand, then paste the records from "Paste Errors" into the data table. This, while clearly looked down upon, ends up being a much faster and more reliable way to get the records corrected and appended to the table in my experience. I understand that this is far from an ideal process, but having a bunch of unorganized data from various sources is far from an ideal situation. Ok, I'll try to post an example that will do so. It will be a crude and imperfect example because you have chosen not to post any information about your tables, so I'll make the following assumptions: your master table tblMaster has a lookup field MyData, actually containing a Long Integer foreign key MyDataID; and you have a lookup table tblLookup with a Long Integer primary key MyDataID and a text field MyData. You're getting input from an external text file (or spreadsheet, the details will differ but the principle is the same) named MyFile, with nothing corresponding to MyDataID but containing a text field MyData. This will mostly match the records in tblLookup but will have errors. I'll suggest that you create a new table, tblBadLookup, with an autonumber primary key and all of the other fields in your import file. You would use File... Get External Data... Link to link to Myfile. A query INSERT INTO tblMaster (MyDataID, fieldname, fieldname, ...) SELECT tblLookup.MyDataID, MyFile.* FROM MyFile INNER JOIN tblLookup ON MyFile.MyData = tblLookup.MyData; will insert the matching data; a query INSERT INTO tblBadLookUp (fieldname, fieldname, ...) SELECT MyFile.* FROM MyFile LEFT JOIN tblLookup ON MyFile.MyData = tblLookup.MyData WHERE tblLookup.MyData IS NULL; is an "unmatched query" which will find all records in the input file which do NOT have matching MyData. You can then manually correct the errors in tblBadLookup and run another append query resembling the first one to insert them. Hope this helps! -- John W. Vinson [MVP] |
#20
|
|||
|
|||
Lookup Field Issues
Thanks for the suggestions
Piet -- you seem to be the only one reading this who has faced the same issues. Your suggestion is a fantastic one and I wish had done things that way from the get go. I'm working on the third database that requires the same sort of error checking process and the same table would have worked for all three. Next time I end up in a similar situation I will definitely be creating that cross-reference table so that I can at least automate the corrections that I've previously dealt with. I hope someone in the same situation stumbles across this post some day and avoids some of the headache by following your advice. Gina - I think you are trying to help me with creating a list of allowable values based on data that is already in the database. I already have the list, so that's not the issue. I need to bounce the data I'm importing off of the list I already have and track which ones are rejected so I can correct them. Jeff - I think you still misunderstand the fact that this data already exists in some sort of electronic form. Clearly having controls on what values people can enter during the process of data entry would avoid this problem altogether, but unfortunately I've inherited a bunch of files that were entered with little or no restrictions. You are simply pointing out what someone should have done years ago, before any of the data was entered, and before I was involved with this project. I agree completely with what you're saying, but it doesn't do me much good now. The damage has been done, my job is to undo it, and re-entering thousands of records isn't really an option. John - You've hit on exactly what I'm looking to do, but my question remains this: what is the advantage of doing things that way versus the method I described? If the only advantage is that I don't need to have a lookup field in a table that way, then do the disadvantages of having a lookup field really outweigh all that extra trouble? I guess that's more of a personal preference question when it comes down to it, and it's already abundantly clear what most people prefer in this case. I'll give your method a shot and see how well it works out. And since everyone seems so curious, the data is wildlife survey data from three different projects that all have similar but not identical protocols. More specifically I'm importing vegetation data that includes a "plant code" field and trying to exclude values that are incorrect due to a variety of reasons. Some field guides disagree on the correct scientific name (which is the basis of the code) for a particular plant for example. People forget exactly what the code is when writing it down. In previous years the data has been entered into excel or into poorly designed databases and is therefore full of typos. Officially recognized names of plants change. All this leads to the nightmare I'm trying to work out, so that I can actually make this data consistent and accurate. Therefore I'm comparing every plant code that I import to a list of plants found in the counties where the surveys take place. This still isn't really good enough, since there are still codes that are typos or errors that correspond to a plant in the list by pure coincidence, but as far as I can tell it's the best first crack at getting the data looking like it should. The best first crack, I should say, assuming no one took the time to design a proper database before the projects began. As so many people have pointed out, that would really be the best first crack - so maybe I should stop wasting my time with funky data and start working on a time machine. Thanks for your help and suggestions |
Thread Tools | |
Display Modes | |
|
|