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 |
#21
|
|||
|
|||
Lookup Field Issues
John - there's one more thing I forgot to ask - given your query setup
what happens to records in which the plant code (the lookup value) is correct, but data in another field violates a validation rule? Would I have to set criteria in the queries to test all of the fields in the source data? |
#22
|
|||
|
|||
Lookup Field Issues
On Thu, 14 Jan 2010 21:00:53 -0800 (PST), esn wrote:
John - there's one more thing I forgot to ask - given your query setup what happens to records in which the plant code (the lookup value) is correct, but data in another field violates a validation rule? Would I have to set criteria in the queries to test all of the fields in the source data? I guess I don't understand. What do you WANT to happen when you have invalid data? Add it to the table anyway? -- John W. Vinson [MVP] |
#23
|
|||
|
|||
Lookup Field Issues
"esn" wrote in message
... I have a lookup field in a table that is "text" data type and restricted to values in the list. 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. I want to use the field to check for consistency in preexisting data using copy and paste. In my experience this should create a "Paste Errors" table with all the records having a value for this field isn't in the lookup list. Any ideas why I'm not getting paste errors in this case? OK I'm just going to comment on your OP because I don't have the time to read the reams of other stuff. If your goal is to correct errors in your data before importing it then you could use a query with a join on that field to a lookup *table*. That table would be a single field containing permitted values. Use your query to return those records that do *not* exist in the lookup table, correct the errors then run your import routine. Keith. www.keithwilby.co.uk |
#24
|
|||
|
|||
Lookup Field Issues
"esn" wrote in message
... John - there's one more thing I forgot to ask - given your query setup what happens to records in which the plant code (the lookup value) is correct, but data in another field violates a validation rule? Would I have to set criteria in the queries to test all of the fields in the source data? You'd have to repeat the process for every field with suspected bad data. Trying to do all of the fields in one go is asking for trouble. Keith. |
#25
|
|||
|
|||
Lookup Field Issues
"John W. Vinson" wrote in message
... 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. John - just spotted this after I posted my suggestion. Wasn't trying to steal your thunder, sorry Keith. |
#26
|
|||
|
|||
Lookup Field Issues
2b. Nor will a query that uses a company name in that field as a criteria.
Rebuttal: When writing queries, you do have to specify the criteria using the underlying value rather than the user-friendly displayed value. However, you have to assume that someone writing queries is not the target of the end-user abstraction provided by lookups and will know to supply a numeric criteria for “CompanyID.” Having to use an invisible underlying value for a critieria has the potential to be a real problem. If I want to look up a company by name, or if I want to do a Like criteria, the lookup field seems to offer no help. In any case, the OP spoke of end users constructing their own queries. You also wrote "Wrong, Wrong, Wrong" at the idea of other databases not being able to understand the lookup fields. In my test, exporting the lookup field (to Excel, in my test) exported only the bound column. The Company name (to use the current example) is not exported. This could prove inconvenient. The article you attacked referred to the "user", which you took to mean the end use only. In context it would seem to be any person working with the database. In your demonstration early in the blog post about the use of a lookup field you showed the table being used directly for data entry. I would not expose the table to the end users, as there is too much chance for them to do damage to the data. I (and most developers) use forms for data entry, in which case I would build by own combo box there, not in the table. You apparently would send the users directly to the table for data entry, which would seem to indicate you regard the practice as acceptable. The blog posting would have been helped had you started out by saying so. If I misunderstand, what is the point of building the combo box in the table rather than on the form, other than that it is automatically a combo box when you drag it from the field list to the form, or use autoform? If you regard the objections to lookup fields as utterly without merit, do you go so far as to advocate their use whenever possible? JohnFx wrote: 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. 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, [quoted text clipped - 24 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. -- Message posted via http://www.accessmonster.com |
#27
|
|||
|
|||
Lookup Field Issues
esn,
When I write an application that requires a frequent import of data from some other source, I generally use a technique similar to Johns, but slightly different. 1. I start out by writing a procedure to either link to the external data, or import it into a temporary table. I prefer to use a temp table, so that I can add an autonumber field to the table so that I can easily identify each record (assumes that the table does not already contain a unique PK). 2. I then identify all of the fields in that table that contain data that should exist in one of my "lookup" tables. 3. I then loop through each of the fields/lookup tables from step #2. In a listbox, I display all of the values in that field that don't match with a value in the appropriate lookup table. The query for the listbox would look something like: SELECT DISTINCT [Field1] FROM tbl_Import LEFT JOIN tbl_Lookup ON tbl_Import.Field1 = tbl_Lookup.Field1 WHERE tbl_Lookup.Field1 IS NULL ORDER BY tbl_Import.Field1 Next to the list, I add a combo box (cbo_ChangeTo) to allow the user to select one of the values already in the "approved list". I also add a textbox (txt_ChangeTo), to allow the user to change what is in the table that is being imported to a new value that I also want added to my "approved list". Both of these controls are implemented by a button (cmd_ChangeTo) which determines which one of the controls has a value (if the user selects from the combo, the textbox is cleared. If the user types in the texbox, the combo value is set to NULL) and updates the field value in the table being imported, and in the case of the textbox, adds the value to the "approved list". I also have a button that allows the user to simply add the value selected in the list to the "approved list". After either of these buttons is clicked, and the code is run, I requery the list. 4. You could easily extend this procedure to record the mismatches in the table that Piet mentioned and run the update queries for each of the fields identified in step #2 prior to step #3. This would improve your processing and over time significantly reduce the number of mismatches. 5. Once I have done this, then I run an append query to append the data (with the appropriate ID values) to my master table. This append query uses INNER JOINs between the import table and all of the lookup tables (this ensures that only those records where all of the fields with "lookup" values have been corrected or had changes made to the lookup table). Finally, I delete the values from the import table that have been added to my master table (leaving only those records where there is still a data mismatch). This is where the autonumber field mentioned in step #1 comes in handy. 6. At this point, you can either go back to step #3 to correct those records, or go to a more tedious manual process. HTH Dale "esn" wrote in message ... John - there's one more thing I forgot to ask - given your query setup what happens to records in which the plant code (the lookup value) is correct, but data in another field violates a validation rule? Would I have to set criteria in the queries to test all of the fields in the source data? |
#28
|
|||
|
|||
Lookup Field Issues
I like the disclaimer, but why is it "necessary"?
Has the FTC finally done something stupid like finding a newsgroup responder liable for some code that a user implemented that deleted all of their data, or something like that? Dale "Jeff Boyce" wrote in message ... No insult intended. ... but if you didn't want the folks here to use their brains and offer suggestions based on their experience, just say so. Sometimes folks just want to be told "what button to push" ... let us know. 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. "esn" wrote in message ... 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 issue. Since I've been involved with this project all data entry has utilized forms in Access, but there is a mountain of preexisting data to deal with. The data is already entered and was imported from various sources - some of it was entered in excel and some of it was in a preexisting database that was quite a mess. Needless to say, this data is rife with errors thanks to my predecessors NOT grasping the value of forms. So I can A: import the data using an import function, which will exclude all records that don't fit my rules and leave no simple way of knowing which records were excluded, B: use an append query that will do exactly the same thing, or C: copy and paste the records so that I get a paste errors table, which I can then correct as needed. As for the primer on lookup fields, the info you've provided (If it's a lookup field then it just *appears* to contain text. It doesn't.)is incorrect. If the primary key of the row source is a text field, the data is stored as text. This is beneficial in my case because queries written on the data table can use the actual text as criteria without requiring a join to the lookup table. Since I'm building this database for multiple users with various levels of Access expertise this would be preferable to training them all in the finer points of multiple table queries (or doing all their work for them). And as far as critiques of lookup fields in general, if anyone has a better suggestion for how to limit values in a field to a list of 11,000 allowable values let me know. Beats typing "value1" or "value2" or "value3" ... 11,000 times in a validation rule. I tend to agree more with the response posted he http://improvingsoftware.com/2009/10...cess-are-evil/ than with most of the arguements against lookup fields in the link you've provided. It seems to me I've found a glitch in Access that there is no way to correct. If the primary key referenced in a lookup field has a text data type, the database will not check values against the lookup table when records are pasted (even if limit to list is set to yes). I tried everything I could think of to no avail, and eventually had to 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. |
#29
|
|||
|
|||
Lookup Field Issues
Dale
I was recently "introduced" to a FCC ruling that seemed to indicate that they could apply their rules about touting products to anyone even mentioning a product. If I recall, there was something about soccer moms blogging a product that they had received, but failing to disclose that fact. Better safe than sorry... Regards Jeff "Dale Fye" wrote in message ... I like the disclaimer, but why is it "necessary"? Has the FTC finally done something stupid like finding a newsgroup responder liable for some code that a user implemented that deleted all of their data, or something like that? Dale "Jeff Boyce" wrote in message ... No insult intended. ... but if you didn't want the folks here to use their brains and offer suggestions based on their experience, just say so. Sometimes folks just want to be told "what button to push" ... let us know. 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. "esn" wrote in message ... 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 issue. Since I've been involved with this project all data entry has utilized forms in Access, but there is a mountain of preexisting data to deal with. The data is already entered and was imported from various sources - some of it was entered in excel and some of it was in a preexisting database that was quite a mess. Needless to say, this data is rife with errors thanks to my predecessors NOT grasping the value of forms. So I can A: import the data using an import function, which will exclude all records that don't fit my rules and leave no simple way of knowing which records were excluded, B: use an append query that will do exactly the same thing, or C: copy and paste the records so that I get a paste errors table, which I can then correct as needed. As for the primer on lookup fields, the info you've provided (If it's a lookup field then it just *appears* to contain text. It doesn't.)is incorrect. If the primary key of the row source is a text field, the data is stored as text. This is beneficial in my case because queries written on the data table can use the actual text as criteria without requiring a join to the lookup table. Since I'm building this database for multiple users with various levels of Access expertise this would be preferable to training them all in the finer points of multiple table queries (or doing all their work for them). And as far as critiques of lookup fields in general, if anyone has a better suggestion for how to limit values in a field to a list of 11,000 allowable values let me know. Beats typing "value1" or "value2" or "value3" ... 11,000 times in a validation rule. I tend to agree more with the response posted he http://improvingsoftware.com/2009/10...cess-are-evil/ than with most of the arguements against lookup fields in the link you've provided. It seems to me I've found a glitch in Access that there is no way to correct. If the primary key referenced in a lookup field has a text data type, the database will not check values against the lookup table when records are pasted (even if limit to list is set to yes). I tried everything I could think of to no avail, and eventually had to 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. |
Thread Tools | |
Display Modes | |
|
|