John W. Colby
jwcolby at colbyconsulting.com
Sat Dec 25 10:21:18 CST 2004
>Next and perhaps more important, why are you building a SQL statement on the fly? This is a bad habit inherited from Access MDB development, I think. True, there are times when no other approach is possible, but I think that those times amount to maybe 10% at most of the typical app's requirements. I suggest that you re-think this part of the app and think about feeding the parms to one or more sprocs instead of constructing a dynamic query. 1) This is an Access database, not a SQL server database. 2) I am building a system that is used to import professional license lists provided by the states. These are lists of people who hold licenses such a physical therapy, occupational therapy etc. Each state has their own system for generating these lists, from word or excel documents to Access or paradox databases. Likewise, each state, and even list within each state, has different information (and field names) that they capture about the individual. Thus the Florida OT list may have 15 fields named Fname, Lname etc, while the FLORIDA PT list has 12 fields named Last Name, First Name etc. In order to handle this process in a flexible "end user programmable" way, I ask a technically savvy person in the organization to get the various files into a single file format (Excel or Paradox). I specifically tell that person to NOT attempt to modify the field names provided by the states for any given list. The reason is that with dozens of lists from dozens of states, the probability that they would be able to manually "rename" the fields to a consistent naming convention is remote. "Just give me the original field names and let me deal with it". I further ask that person to name the files using a file name of SSDDD.XLS where SS = the state code and DDD = the "discipline" code (license type). Thus I can pull the first two characters out and look it up in the state field. If it is found I have the PK of the license state ready to go. I then take everything after that up to the "dot" and look that up in the discipline table. If it is found, I have the PK of the discipline (license type). I build a pair of tables to handle the import specification. tblImportFile holds the path to the file being imported, the name of the file, the State PK and the discipline PK, and the date that the file was imported - these files are received every 3 months or so depending on the state. tblImportSpec allows me to map the field names - their field name, my field name. I have a parent / child form with the tblImportFile as the parent and tblImportSpec as the child. For each file to import I can map the field name in my table with the field name in the import file. I then generated an import "Raw data table" with the specific set of fields that we want to import IF AVAILABLE in the list. In the child form I use the fields collection of the linked table to full the list of field names and display that in a combo. I do the same thing in another combo for the field names in my raw data table. Thus the user now selects a file one time in the parent form, then in the child uses these two combos to match or pair up their field to our field. Each file is different, they all have basic first name / last name / address / phone info but some have license number some don't (for "privacy reasons"?!), none have a SSN. At any rate, when the user is finished I now have a list of the fields in the import file that we wish to extract data from, and a matching set of fields in our raw data table. I then build "on-the-fly" a query to append their data to my raw data table. The whole point of this conversation - On-The-Fly. This process allows the user to do this field matching process a single time and then, assuming that the state doesn't change their field names, the process just works. As the user selects a file to import, I read out the list of their field names we captured previously, then I compare those field names to the fields in the linked tabledef.Fields() collection. Thus if a field we were capturing changes name, I discover that and inform the user. When I am done, I have dozens of files in tblFile and hundreds of field names (a dozen +/- per file) in the tblImport. As the user selects the file, I build the append query and then when they press a button, I check that the link to the table functions (the file is there), and generate the query to pull the data from that file into the raw data table for further processing. After AI import the file into the raw data table, I mark the import file as imported (time stamp field added by my tech user) so that it never gets imported into raw data twice. At this point the data is ready to be processed. Processing is another whole issue. I have to map the records to a specific person in my live data table, which is trivial if I am given a license number by the state. I have the State PK, the Discipline PK, and the license number, that maps to a specific record in my live data table just by joining these fields between raw data and live data. If the state does not provide a license number, then all hell breaks loose. It turns out that well over 50% of these people are women which means they marry, change names, addresses, phone numbers etc. With no "unique identifier" such as a SSN or License number it is a crap shoot trying to find them in the live data table. Obviously I do things like joining the phone number (if any), the first N characters of Addr1 plus the city etc. It is just a PITA and requires an iterative "try these fields, next try these fields" etc. As we "find" these people in the raw data that appear to match the live data, we examine "changes" between the raw data and the live data. IF we get a license number (we already have the state and discipline) I can discover those in the database, and thus from that those NOT in the database, and I append those NOT IN to our live data. The whole point of this process is to get a clean record that can be mailed to or called to offer them a job. There are currently more than 30 lists from a dozen states, with more lists and states added as the company gets business in those areas. We have about 150 thousand people in our live data table. We get updates from the states periodically. New people have to be merged in to our live data. Changes in address / phone reported to the state need to be updated in our live data. The process was done completely manually, i.e. the lists were just examined by hand and compared to the live data. We are attempting to automate the process such that new records can just be pulled in, records with no changes can be identified and excluded, then the remaining that have changes can be examined or called, And resulting changes entered into the live data. As we all know, any given problem can be solved a dozen different ways. I went with the "field matching" method to try and minimize the problems created by our people trying to standardize the field names directly out in the lists. They used to import the lists into paradox, changing the filed names as they went and they ended up with dozens of differences just caused by this field renaming process. I say "leave the original field names" and I'll do the mapping to what our field names are. Then as long as the field names out in the original lists don't change we are good to go. I check the field names we have against the import table each time to discover changes if any. It seemed logical to me. Given the process I don't see any way to avoid "on-the-fly" building of queries. John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Saturday, December 25, 2004 12:35 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Query SQL correct but not visible in design view Trust me on this, JWC. Don't use SELECT * ever. Really ugly things can happen when dealing with MS-SQL -- not in any simple query but insert a column in some table referenced by views or sprocs that say SELECT * and let's go get drunk. I'm guessing, but I think that the underlying reason is that the query optimizer/compiler refers to columns numerically not by name, and if you suddenly change a table or a view that uses SELECT * then all hell can break loose. I haven't exhaustively tested this thesis to see what breaks under what conditions, but I have been bitten enough times to stay away from the SELECT * dog. Next and perhaps more important, why are you building a SQL statement on the fly? This is a bad habit inherited from Access MDB development, I think. True, there are times when no other approach is possible, but I think that those times amount to maybe 10% at most of the typical app's requirements. I suggest that you re-think this part of the app and think about feeding the parms to one or more sprocs instead of constructing a dynamic query. A tip from one of my SQL Tips columns.... Allow nulls to be passed to the sproc, then CASE the parms and then branch to the appropriate sproc. That way each sproc is optimized for the parms and won't screw up with choosing the wrong plan. If you write a sproc that involves different order by's etc. all in the same sproc, you can get ghastly performance because SQL will use the existing plan unless you specifically tell it not to. Arthur John W. Colby wrote: >I am building a SQL statement "on the fly" which essentially compares >matching fields in two different tables - Tbl1.Lname <> Tbl2.Lname. >The SQL runs but only the tables and their joins are visible in the QBE >grid. There are no fields visible etc. Does anyone understand what >causes Access to do this? I have had the same thing occur even with >something as simple as a SELECT * from tbl1 inserted into the SQL view. >Tbl1 is displayed, but nothing in the grid. The query executes >correctly. > >Anyone? > >John W. Colby >www.ColbyConsulting.com > >Contribute your unused CPU cycles to a good cause: >http://folding.stanford.edu/ > > > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.298 / Virus Database: 265.6.4 - Release Date: 12/22/2004 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com