Matching Non Exact Cells When Merging Two Database
Apr 2, 2012
I've a company database with everyone's software license but it doesn't have their employee ID number. I have another database with everyone's ID Number and I need to merge the two, How ever the problem is the names aren't exactly the same in both data bases. Some have small changes like
John, Sargent M :: John, Sargent M.
Samantha, Williams Anne :: Samantha, Williams A.
It's all the people with more than 2 names entered that his problem happens the most and I was wondering how to solve this in access?
Once again, I'm working with a file that I got from somewhere else that isn't in *quite* the right format.
What I have is a table listing all of the titles and their holdings we have in various journal databases. The table looks something like this:
|____TITLE____|___COVERAGE______|____DATABASE___| |_ABC Journal_|_1998 until 2000_|_EBSCOhost DB__| |_ABC Journal_|_1953 until 1986_|_Proquest DB___| |_ABC Journal_|_1980 until 2006_|_Free Journals_| |_XYZ Journal_|_1899 until 1956_|_CINAHL DB_____| |_XYZ Journal_|_2000 until 2006_|_EBSCOhost DB__| |_XYZ Journal_|_1955 until 1999_|_Proquest DB___|
The titles, as you can see, are listed more than once, with one record for each holdings record from each database where that title is found. For my purposes, I need the holdings to show up all together. I can do this in a report, by sorting by the title, but I really need to have it all together in its own cell--one line per title. The above table should look more like this: |____TITLE____|_______________________COVERAGE____ _________________| |_ABC Journal_|_1998 until 2000_,_1953 until 1986_,_1980 until 2006_| |_XYZ Journal_|_1899 until 1956_,_2000 until 2006_,_1955 until 1999_|
I don't really care what happens to the database field, but if it helps, the title and the database fields together would be a unique combination in the table.
Currently, I am working on an exported version of this table in excel, merging the cells 2 by 2 with a function command like this: =A2&" "&B2. Since there are 32366 records, I hope there is a quicker way. At this rate, I'm not sure if I'll be done for 100 years.
I must admit I am a "newby" to Access but I have bought my Access 2000 bible, as I am running Access 2000, and attempted to create my desired database. I have linked to 2 tables that I download on a daily basis. This is my inventory from two different suppliers. I then have created two queries that filters each of these files to only show positive quantity items, filters out item specifics, etc.What I want to do is join the 2 queries with their data already filtered. Both of the files from 2 different suppliers contain some of the same data. Once joined if there is an item that is the same I want to delete the higher cost item and only show the lower cost item, I also want to show the rest of the items that do not match. It is easy to identify the same items as every item contains a 12 digit identifier called a upc.I can create a UNION ALL query that shows every item from the two queries. However, I am unsure as to how I go about deleting the higher cost item and only showing the lower cost item if the item is the same. On a side note after I get the results desired I will then be createing an append to query and append all this information to another file that is then uploaded to another system.I will try to attach some sample data that I am using from the two queries I want to join, this data is only a sample of a much bigger file.Thank you and any and all comments or suggestions is much appreciated.
Background: I have a database that keeps track of containers -what they belong to, and what is in them. Each container has a “Call #” which is labeled on the container and is a primary key in the database. The “Call #” field is set to only allow 5 characters in this field. A standard “Call #” for example is "A 152". When a new container comes into the system, which has not been physically labeled yet, I enter it in the database a little different. So let us say container "A 152" just came in, and has not been physically labeled yet. I enter the “Call #” into the system as "MA152". The "M" tell me it still needs to be physically labeled. When I go to label all the unlabeled containers, I print a report with all the "Call #''s that start with "M". I have another field in the same table called "Mark". In my forms I can then go through and click a "Mark Button". The Report prints all containers where the "Mark" field = yes(True). This feature is used for a number of different reports, not just unlabeled containers. To keep the database clean and in order, all “Mark” fields reset to no(false) whenever it opens up. Because, I used the “Mark” field for other reports, simply clicking the “Mark Button” when I enter the new container into the system will not work.
My Question: Is There a way to have a Query search for the first letter in a field. So when I go to print my report, a query runs looking for all "Call #s" that start with the letter “M”.
I have made this database for a company that I work at, and I seem to be haveing some problems. I split the Database and placed the "'back-end" on the company's shared drive, however it seems that only when I log in as myself, on any computer, that it works. On other people's profiles, when a form is opened, there is nothing there, it is blank, no textboxes, labels or anything. However, if you use the database window to open one of the linked tables, you can see all of the existing data and cells just fine. I am only 17, and I have used Access for only two weeks, so I may have missed some setting in the splitting, or a database property that is affecting it. Please help me, as I need to finish this DB ASAP.
PS: the folder settings for the shared drive (at least as far as i know...) allow full access, so there should not be an issue there.
I need to merge data from one DB into another.I have a split database with front end DBcompanyFE and back end DBcompanyBE. BE is on the server so users at company (3 users) can access it with their own FEs.I also have 2 users that are working at some other location (geographically) and they have identical BE of database (DBcompanyBE) and their own FEs.Now, my problem is, that at each location there is different data entered, but on both locations all the data is needed. What would be the easiest and mybe most automated way to merge/combain those data.
We have a client who has managed to muck up their Access database by means of having two users on two computers attempting to use and modify it at the same time, and the primary (actually, the only) location for the database is on Dropbox.
What seems to have happened is that Dropbox detected a conflict, with two different versions of the same file, and resolved this by creating two separate files. How long this went on before the client noticed that they were entering data into two separate files, I can't say.
Their client list is 28,000 someodd people long. What I need to know is if there's a process for merging the two files together, without creating 28,000 duplicate entries. This is being run on Access 2002.
I have to create a database with more than ten thousand records. There is a field on which image by clicking the image to be displayed with the default program based on the image on which you clicked.
Code:
Private Sub Campo1_Click() Dim L As Long L = ShellExecute(0, "Open", """" & "C:UsersNickDesktopDocumentiPicturesDioDiego.jpg" & """", vbNullString, vbNullString, 1) End Sub
When onClick event happens on the image is displayed the same image regardless of the record on which you clicked. How do I change the code to open the image of the field that was clicked?
How can I get an exact match in a search. If I search for say C1 I get results showing everything with C1 i.e C1, C11, C12, C13 etc. I just want to show C1's This shows my query at present.
I'm using an array with the Instr function. The array (or the Instr function) doesn't seem to be case sensitive... which is a pain because its picking words within words where the case doesn't match!! I'd like the vales in the array to match exactly when using the Instr function
For a couple of days now I've been trying to devise a way to achieve what I want, but I just keep going in circles and hitting errors (thanks to forum members, I've been able to solve most of them.) So, here's what I want to be able to do:
Input a product using the iproduct combo box (which gets its list of values from the Products table); its brand, using the ibrand combo box; and its size using the isize textbox, all in the frmFoodSub form. I then want it to check to see if an exact record already exists (ie. the same product, same brand, same size.)
If it doesn't exist, I want to create it. If it does, I want to do nothing. Then I want the product - either the one I just created, or from a record that already exists matching the iProduct input - to be input into the product field of the PurchaseDetails table. I then want to use iQuantity and iPrice textboxs, already linked to the PurchaseDetails table, to input the newly added product's quantity and price.
I hope my explanation was clear enough. I'd appreciate any help with this you may be able to offer.
Database to query. It holds tables for the machines we have and the parts each machine requires. We also have a database of spare machines and the parts that are in them.
Given the nature of the machines, some parts aren't worth trying to pull out singularly, only as part of a set.
I've been asked to put together something queries which of our old parts/machines can be reused.
So I have a table of current machines and their parts and when they were last replaced.
I have a query that says 'Show me all the machines that have parts over 5 years old.' I get a list of machines and their parts
Can I then take that whole list of parts and search the table of spare machines any parts and find that exact list of parts?
I don't mind if the machine has more parts, but it must match that exact set of parts and that set of parts should be contained in one machine.
I've added an example that is a cut down version of what I have:
Basically say we might want to replace the parts in Current machine 10002.
I query CurrentMachines for which parts it has.I then want to use the results of that to query the spares. But I only want to return spares that has all three parts that machine 10002 has.
All I can make it do at the minute is find the spares with any one of the parts that match the 10002 list.
Does anyone know how to perform an exact match using file search. The code I have below searches for the users AcroRd32.exe file (I have done it this way to allow for different versions of the reader). The problem is that foundfiles returns also AcroRd32info.exe ie FoundFiles(1). i only want to search for the prior!
Set fs = Application.FileSearch With fs .NewSearch .LookIn = "C:Program FilesAdobe" .SearchSubFolders = True .FileName = "AcroRd32.exe"
If .Execute > 0 Then 'check if there is such a file
stAppName = .FoundFiles(1) 'then define this as the applicate string name stAppName = stAppName & " " 'append a space or file string not correct 'specify path name for location of file.pdf stlocation = GetPrivateProfileString32("C:WINDOWSSSI_PROGRS_DATABASE.ini", "DIR", "DATABASE_FILELOCATION") stpathname = stlocation & "file.pdf" Shell stAppName & stpathname, vbMaximizedFocus Else 'if no file is found then need to instruct user to install reader MsgBox "You need to install Adobe Acrobat Reader to open this file", vbExclamation End If End With
Hello, i'm not sure how to write a query, maybe it'll take VBA function to accomplish this, but I need to display the results for all invoices for an item.
Little background: 1. This set of invoices are of only one particular product line as it's this product line that needs this special handling. These are Guitar orders. 2. Each guitar item can have option items on the same invoice. So basically these are all considered item numbers. 3. The guitar starts as the bare / basic guitar. The customer can choose to select one or many options for a custom guitar. 4. These orders will need to have special guitar programming codes that need to be entered into their guitar programming machines. Call these "Codes". 5. Depending on the option items for the various guitar items, the Codes could vary. 6. Need to be able to display ONLY the Codes specific to the guitar or guitar and option combinations.
I have several tables: 1. Guitar for all the guitar items 2. Options for all the option items 3. ProgrammingCodes for all the programming Codes 4. ProgramCodes for creating the above combinations with a field named ComboID. 5. GuitarHeader - the invoice number and guitar item 6. GuitarDetails - the invoice number and option item (invoice number to invoicenumber link to the GuitarHeader)
Then there's a main form and a sub-form for the end-user (guitar programmer). 1. The main form is the Guitar items 2. The sub-form is the ProgramCodes fields this form is used for this end-user select the Guitar and options (where necessary) and select the Codes and enter the ComboID for all.
for instance: These are all the possible codes for AE185. AND depending on the Options selected for AE185 on invoices, the Codes will differ. The ComboID is the differentiator. Code:GuitarOptionCodeComboIDAE185 185RR 1AE185 186RHT1AE185 187RT 1AE18538185RR2AE18538186RHT2AE18538187RT382AE185BB185RR3AE185BB186RHT3AE185BB188RT-B3AE18538185RR4AE185BB185RR4AE18538186RHT4AE185BB186RHT4AE18538188RT38B 4AE185BB188RT38B 4
so for the Codes that repeats in the ComboID is only because the Option(s) also requires that Code as well as the other Codes.
for instance, if the invoice ONLY has Option 38 from the Options defined for Codes, then the result would be:
Code:Invoice Guitar Codes123456 AE185 185RR 186RHT 187RT38 <-- say this invoice had Option 38 that was the only match234567 AE185 185RR 186RHT 188RT-B <-- because BB was the only one that matches345678 AE185 185RR 186RHT 188RT38B <-- because both 38 & BB matches
So guess the question is how do I write a query or function (VBA) to be used in a query to get the results for ONLY the matching options so the correct codes for just these options are displaying?
It's difficult to me in figuring that out. how to make sure that the result does not show the ALL the codes that match but ONLY the codes in the combinations. Again, hence the reason why I got to the point of having Combination IDs to differentiate that.
hope I made sense here. Not sure how else to ask the question other then to try and provide examples let me know how else to explain if this is not helping.
I don't seem to find any query formula in the forum where the age changes on the date of birth. I tried all the once I could find but all of them seem to calculate the age as of 1 January. I find it a bit strange that it doesn't work.
I got BirthDate and Date in the table I want to update the age column back in the history with an update query.
there are certain txt boxes that once filled out, will filter a subform of a table of 1000's of records to give them a number to use on the form around 200 of the records have a depot in the "depot" field on the table, when they input a depot, it filters to them 200 fine if a depot is there,
what i want is, when they input the depot, if its there, filter it, if its not, to do nothing, as they could still get a unique number if the depot isnt in the list what happens now is, if the depot isnt in the list, it displays no records
Field : Fld_Depot Table : Tbl_Agreement_Summary Show : False Criteria : Like "*" & SearchForText([Forms]![Frm_New_Accounts]![Fld_Depot]) & "*" Or : Is Null
I did export the table data into a tabbed delimited format and will include that at the end. If you want to reproduce my bug copy that data in a txt file and import that into the table tblMain. Make a search form and a sub form. the sub form is linked to the table and the main form is unbound with two search buttons.
Problem Statement:The code works fine. I did find a bug that seems to arise with the wild cards when the entire field values are entered. You can replicate the bug by testing the search criteria listed below.
This is a brief example of the bug. A detailed description is near the code below.
If my name is "Devtycoon" and I search "Dev" the SQL statement will build "*Dev*" and it will pull up my name, "Devtycoon". On the contrary if I search "Tycoon" the SQL statement will build "*tycoon*" and it will pull up my name, "Devtycoon". If i search "DevTycoon" the sql statement will build "*Devtycoon*" and no results will be returned. That is buggy because the name is in the database but no wildcards would be needed.
Form1 contains the controls for search criteria. Three text box controls are used to filter a sub form control called "DS". The sub form is called sFrmMain and is a datasheet that shows results of the search. there is a button that runs the code and another button that clears search criteria and shows all records. Both button's code set the sub form's record source using an SQL string built using a function that returns a segment of the overall search string using the contents of each control that then is concatenated into a SQL statement used to set the record source.
There is a function for the following components of the SQL statement
SELECT / FROM WHERE controlA = me.txtSurname controlB = me.txtOrganization controlC = me.txtProgramTitle This is how you can replicate the bug.
I tested two additional surnames organizations and program titles as follows:
Try example (1). you will get both the 4's and the 14's records returned.
If you type in letter for letter of the second record (the one with the 14's) no records populate. It is like the wild card does not like it if you type in the entire field value. Uncle Gizmo's and Allen Brown's method do the same thing were no records populate if the 14's entire surname organization and program title are entered into the text controls. Can you reproduce this error? Other than that I think either method is bulletproof.
Example criteria
1) put the following criteria in each control then hit the search button
4 surname 4 organization 4 program title [two records returned]
2) put the following criteria in each control then hit the search button
[no records returned] 14 surname 14 14 organization 14 14 program title 14
How to find an exact match in a Access DB table using Sql Query in VB6 ?I know that "Like" keyword will give out all those rows which contain the search-for-string. But I want exact match.
I did a cursory search of the forum and didn't find anything (probably more my search than the content). I also think I know the answer to this, but I want to clear it up once and for all.
Is it true that I cannot insert an image into an Access Table in a way that it treats the image like data (e.g., it comes up on reports as entered).
If so, and I suspect it is so, what would be the best way to associate a row with a certain image in Access? Here's what I'm hoping to do: I have a list of projects and all of them have a status. Right now the "status" column includes the text "Green," "Yellow," or "Red." I would rather have this text display as green, yellow, or red color rather than text. Is this possible?
Hello, say for example I have a particular column in Access that I would like to force users to enter as percentage only, is there a way to format the cell as in Excel to create such effect? Thanks a lot!
In a query design view, I have two fields, LastName and FirstName. In the Field of a blank column I enter [LastName]&[FirstName]. In the query the last and first names are now connected , like SmithJohn. How do I put a space and or a comma to separate them?
I'I need to randomly input values into row cells and when press enter key, it calculates the total value and show in another sheet. Example, in sheet1, I input random values in A1,C1,D1,Z1 and when I press enter key, it calculates the total value and appear in the first row cell, A1, of sheet2. All by VBA coding.
I have a text box on my main form, that filters records on a subform, where its record source is a query.The text box works okay for filtering, however it is not doing something that I would like it to.Say we have these records
TM STEELS TM Steels
If I was to type in STEELS, then only the top record would be displayed, likewise, if I typed in Steels, only the bottom would be displayed.What I would like, is if you were to type steels, for example, both of the records would be displayed.In the criteria of the field this is based on, in the query, I have:
Code: Like "*" & [Forms]![MainWindow].[NameTxt] & "*"
As I say, this works for bringing in results that EXACTLY match the text entered in the text box.
