Queries :: Join Two Databases Based On Common Text
May 31, 2013
I am trying to form a join between two separate databases. Database 1 has a column (Gene_Name2) that consists of common names of human genes. In this database there is only one name for each gene per field. The other Database 2 has a column (Gene_Name2) that has various names for the same gene in each field. Most genes have been given different names over time.
I want to join the databases into a separate query database so that all of the information in Database 1 is retained and only when the name in Database 1 matches one of the names in Database 2 does the row of information get tacked on to the query database. The problem is that in a normal query the text in both Gene_Name columns do not match and the query returns no matches. I have tried Instr and like criteria statements with no luck. The following is an example of what I have and what I need.
Database 1
Chrom Gene_Name1 Data
Chr1 ATC XYZ
Chr2 IDH2 ZYZ
Database 2
Gene_Name2 Data
ATC;ORF34;NM_123 ABC
ORF65;IDH2 IGH
Joined Query
Chrom Gene_Name1 Data Gene_Name2 Data
Chr1 ATC XYZ ATC;ORF34;NM_123 ABC
Chr2 IDH2 ZYZ ORF65;IDH2 IGH
I am trying to form a join between two separate databases. Database 1 has a column (Gene_Name2) that consists of common names of human genes. In this database there is only one name for each gene per field. The other Database 2 has a column (Gene_Name2) that has various names for the same gene in each field. Most genes have been given different names over time. I want to join the databases into a separate query database so that all of the information in Database 1 is retained and only when the name in Database 1 matches one of the names in Database 2 does the row of information get tacked on to the query database. The problem is that in a normal query the text in both Gene_Name columns do not match and the query returns no matches. I have tried Instr and like criteria statements with no luck.
I'm a complete novice when it comes to dynamic data so forgive my ignorance. My database contains details of projects in progress. A user can click a link in a list and display a summary of that project (okay so far - I've done that bit) - in the database though there are two tables the second one lists project events/comments (title, date, job number and description) - when something interesting happens during a project we add to the 2nd database a summary of what happened (we made 2nd db because the list of things happening could be one entry or 50 entries) - the tables are links by the job number. On the web site I would like that when a user clicks the link to display the info about a project it all also pulls info from the second db but for the project with the same number.
I've hard coded a similar thing before "select * from tblProducts where category like 'Off Road and Dirt Bikes'" but in this case instead of hard typing 'Off Road and Dirt Bikes' I'd like it to automatically insert the job number of the project being looked at from the first database.
I don't think I've explained this as well as I could've - but if anyone could help I will be really grateful.
Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.
Context is I have a TABLE 1 that has a record for every package shipped with various details about it, include how it shipped, and the date. Table 2 is a key to go from the ship method to a better grouping we deem service level. Table 3 is a Fuel Surcharge (FSC) Table. It includes various FSC % that vary by different service levels and different date ranges. In the end I need to know the amount of pkgs shipped (by month and servicelevel) and the relevant FSC.I have several tables in my database but we should be able to focus on 3 to explain my situation and only the relevant fields:
Table 1 is Left join on Table 2 based on ShipVia Table 2 is Left join on Table 3 based on FSCType The issue is Table 3 is dependent on Table1.ShipDate being between the date ranges as well.
There are overlapping date ranges, however they are differentiated by the FSCType field.The only semi-feasible solution I currently have is to expand Table 3 to include every date, and not date ranges. This will create an issue due to size of date ranges etc.
I have 2 tables. One has employee info and the second has time data based on thresholds reached in the employee info. Obviously, a basic join will bring in all of the threshold records. How do I get it to only pull in the one that satisfies the threshold condition?
Using Access 2010 and ODBC connections to pull data from 3 sources (SQL Server 2008, a customized Documentum application, and Windows AD accounts). Problem is user_login_name is sometimes recorded as lowercase, sometimes as propercase, and sometimes as uppercase depending on which table or source being used. For example, lowercase would be "abcd123", propercase would be "Abcd123", and uppercase would be "ABCD123". I thought I could just wrap each join comparison in the From stmt in the query(s) with the UCase() function, but Access doesn't like that.
How can I convert or cast the various user_login_names within the FROM join statement to be the same text case so joins will pull correct data?
So i want a control that matches the column1 in datatable against DGV.Column1, and if value matches only add second column value and if not, then make a new row in DGV. Final decided outcome:
I have a big Excel file with payroll information about employees. Per employee per date, there are a couple of rows with mostly identical data (such as the employee's home address) but 2 differences: one will have paycode A with amount B, another will have paycode C with amount D, etc. I want to simplify the file to have 1 row per employee per date: in the example given that row would have paycode A with amount B as well as paycode C with amount D, in 4 separate columns.
I think I can technically solve this by creating Excel files per pay code/amount, and then linking them together by person and date, 1 by 1, through Access. This is feasible because there aren't that many different pay codes. But still, I was wondering if there was anyway to solve my problem in Access itself.
My access database is used to process vehicle trips. Each shift, a vehicle is assigned a docket number (paper based), and on this docket the driver fills out each trip he/she makes. In a shift (thus on a docket) there may be 5 - 15 trips. I want to create an incrementing number for each trip in a new field - can be created as they are processed or after the fact by looping through the resultant data - whatever is easier.
So in simple terms, return all trips for each docket number then number each trip.
I have two tables, identical in terms of structure (number & names of fields etc.) but different in terms of content (records not necessarily the same, some may be common to both, some may only be present in one or other table)
I want to set up a query to count the number of records which are present in one table (Table A) but not in the other (Table B) using a common identifier field, and put the numerical result in a text box on a form (i.e. use the query as the control source for the text box)
I've managed to come up with the following which provides the list of ID's :
Quote:
SELECT tblA.CommentId FROM tblA LEFT JOIN tblB ON tblA.Id = tblB.Id WHERE tblB.Id Is Null;
Which will come in useful elsewhere but how do I adjust this SQL to only return the number of records? I think I have to use COUNT(*) somehow but I'm not familiar with it and not sure of the syntax?
I'm trying to perform a DELETE query at run-time. The criteria for deleting records is somewhat complex due to various one-to-many dependancies between the tables, overlapping of date fields etc., so in order to restrict the DELETE to only those records which qualify, I've set up a load of smaller queries to produce the list of eligible records.There is only one parameter required - a 'CutOff' date which is arbitrarily chosen by the user and is available in a textbox control on an open form when the code is triggered.
Here is the main DELETE query :
Code: DELETE DISTINCTROW [tblComments].* FROM [tblComments] INNER JOIN [qryCommentsToPurge] ON [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID] WHERE [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
I've put qryCommentsToPurge and all the cascading sub-queries below - have highlighted in red where the parameter value is required (it is used in the queries to identify the records which must be retained, i.e. not deleted)Each of the individual sub-queries works fine and correctly (they return the correct population of records that they were designed to)If I run the sub-queries manually, I am prompted to provide the CutOff date, as you would expect - and the recordset returned in each case is correct for that date.
If, however, I run the DELETE at run-time (with the form containing that textbox open and containing a valid date value) I get a 3061 error :Suggesting that the queries can't pull the parameter value from the textbox on the form. But I've stuck a watch in the debugger on [Forms]![frmArchive]![txtCutOffDate] and I'm getting the correct date value? The form is open and available at the time.So why can't the query see it? I use these kinds of control references elsewhere in other queries and they work fine. I don't understand why it's a problem here?
Code: SELECT [tblComments].* FROM [tblComments] INNER JOIN [qryIssuesToPurge] ON [tblComments].[IssueID] = [qryIssuesToPurge].[IssueID] ORDER BY [tblComments].[CommentID];
I've created an outlook task to export a common query I do every day, is there a way to automate it so it will happen automatically without me pressing Run?
I use office 2007 on a company laptop with tight network restrictions (so no Windows Task Scheduler)..
I have an asp web page that allows a user to request information from an Access Database, the results of this request are then displayed on a second asp web page and finally the user can then select to view the individual results of the request on a third asp page.
For instance, a user can select all the registered names beginning with the letter 'A', the result of this request will be shown on page2.asp, the user can then select one of the names and the results of that request will be shown on page3.asp.
What I want to happen now is that on page3.asp I need to display some text, the amount of text is too long to put it directly in the Access Database, it needs to be in a separate file like a txt file or something. I need to know how to set up the field on the Access Database to call the text file and the asp code I need to use on page3.asp.
I need help with an update query that will change records for me based on the date
In the record I have a TEXT record called [GI1] that can have a multitude of data in it. The data can be “EVA”, “CON 10/12/06”, “REQ 10/12/06”, “PAS 10/12/06”, or “B 10/12/06”, and finally “A 10/12/06”
What I need is a query will look at the Data that starts with “B” and change the letter to “A” if the date in the record has passed. {The Date can be any date in any year }
I have a question regarding counting of text values base on their status and using that result to a calculation.
Say, I have a table of Demand of Positions, wherein, I have a specific Job Title for a certain Department that have number of workers needed (demand quantity) and a table of candidates for that job title and their status, say, Arrived, Visa Processing, Visa Applied, Visa Issued, and With Ticket.
What I would want is to make a summary out of the two tables, where the query will count how many candidates are there in that specific job title and have a field of status say, field of Count of Arrived, Count of Visa Processing and etc., and a field where I can add all of the count of candidates per status and deduct the result to the demand quantity where that field would be named Balance.
The problem is that the status varies on every candidate on that specific job title because the status field is used to track the progress of each candidate and this scenario will make the query blank because there would be no such record due to their status.
I tried making a summary following my requirement and you will see that in the attached file together with the SQL code of that query that the balance field value is blank.
1 - Maintenance Order 2 - Breakdown Order 3 - Greasing Order
On my form I have two list boxes: An Order Type List box, and an Area Listbox.What query criteria or VBA code would I use so that I could populate a text box with the relevant order number based on the selections of the list boxes. i.e. MaintenanceOrder & Area Z would display MaintenanceOrderNoZ.
I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
I have two tables: Focus and Comp..I want to join these two tables based on field: DwId...For this example, I made a Comp table that has 3 DwId's not present in the Focus table.When I run the below statement, I am not getting the 3 records only present in the Comp table in the result.
SELECT Focus.DwId, Focus.PRD_May2012, Focus.PRD_May2012 - Comp.PRD_May2012 AS PRD_DiffMay2012 FROM Focus RIGHT JOIN Comp ON Focus.DwId = Comp.DwId
I have nearly 100 queries in an Access database which links to a SQL Server database. The queries are working just fine.
I have created a new Access database, linking to a different SQL Server database which has the same table layouts as the first. Is there a way to automatically copy the queries from the first Access database to the second?
I imported a table into Access from a SAP generated text file. The integrity of the data is fine after import. I then created a new table from the original that has the exact same amount of records and has the same unique field, which is MATERIAL. The problem I have is when I join the two tables in a query, Access returns approx 140,000 records. I am doing a regular join, what could be the problem? Is it possible the table data types are not formated correctly, b.c I imported them all as text so Access would import all lines.
Currently am trying to get together two separate Access Databases into one, which is the way it was always supposed to be.
Basically, this person accidentally created a duplicate of the database in question, and has been updating both independently at different times, meaning that whilst a lot of the database information is duplicate, there are some instances where each separate database copy has unique information on both existing contacts, and new unique contacts.
The database itself is about schools, and has tables concerning information such as calls made, contact details, school details, etc etc. Obviously all of this needs putting back together into one set of tables in one file.
I've been messing around with append and import functions, but am having issues with duplicate ID fields with append queries, and a general lack of direction on the best way to go about fixing this problem, if its even possible.
In QueryB, the entry MUJA 2 is missing. What I need is to take the X1 field from QueryB and join those values with the A1 field from QueryA as follows:
