General :: Merge Two Fields From Two Databases That Have Matching Two Data Ranges
Mar 25, 2014
I have two databases (files). Each file has a format like the one below.
File 1:
chr start end
chr1 1000 2000
chr2 3000 4000
File 2:
chr start end
chr1 1500 3000
chr2 3750 4000
I want to be able to merge the two records together if the field chr matches between the two files plus if there is an overlap between the start number and end number from each file. For instance the first record from each file would match because the range from 1000 to 2000 of file 1 has numbers consisting of 500 numbers (1500-2000) that are also present and overlap in file 2 (1500-3000). I possibly cannot use < or > since the ranges from each file will vary to different degrees. Perhaps there is a between function that might work...
View Replies
ADVERTISEMENT
May 18, 2005
Hello,
I have a few tables containing different exam data for students - and a unique identifier with each. I need to add another unique identifier (which is already specified from another system).
Now, can I use the existing identifier (UPN) to automatically put in the new identifier into a new field (AdNo).
If that makes any sense at all, I will be surprised...thank you!
View 1 Replies
View Related
Nov 12, 2006
I have a database with two tables, Buyer and Products, with one-to-many relationship. The key of Buyer is an autonumber ID, which is the foreign key to table Products.
The problem is that i have two copies of this database with different records (with ID starting from 1 to each one) and i want to merge the databases into one.
Any ideas how to do this without lose the connection of the records?
Thank you in advance.
View 2 Replies
View Related
Mar 30, 2007
Hi,
How do I merge these two datbases? I have downloaded them from Microsoft online templates, here
http://office.microsoft.com/en-gb/templates/TC010178391033.aspx?CategoryID=CT101426031033&av=ZAC000
and here
http://office.microsoft.com/en-gb/templates/TC010185481033.aspx?CategoryID=CT101426031033&av=ZAC000
and I want to have the two databases as one so that there will be a fourth tab on the cintact management database with the order form on? I want to be able to link the two so I can have all the customers details and orders together and print off reports. I really have not dealt with Access before so I really dont have a clue. Any help would be truly appreciated. I am sure it is easy but I just cant work out how to do it.
Thanks very much
Danny :)
View 3 Replies
View Related
Mar 5, 2014
I am trying to aput a stock number into a form called (products) but it tells me there is no matching number in another table ( stocklist). yet that number is in another table (stocklist.
View 2 Replies
View Related
Oct 20, 2014
I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error
Private Sub Command5_Click()
If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then
MsgBox "You welcome"
Else
MsgBox "Wrong username or password"
End If
End Sub
View 8 Replies
View Related
Nov 21, 2007
Dear all
I need to merge a number of databases of the same type into one database in access. can you please help me with code itself.
thanks.
Ampah
View 2 Replies
View Related
Sep 27, 2012
I have to coordinate a dbase in the US, one in the UK, and one in the KSA. I have multiple documents that may, or may not, be in each of the three dbases. I need to merge all three, and maintain them so that the document additions, as well as any changes associated with that document, (i.e. "revisions"), are updated. Having looked at access and having read through some of the Access forum questions, I understand that it can do that, but after having identified the information from the dbases that will go into access, I'm trying to establish a consistant process for maintaining and updating it. Can I export my information from each of the dbases, into an excel spreasheet, (1 for US, 1 for UK, and 1 for SA), and have the access dbase search for the document number, compare the data following the document number, and if it's the same ignore it, and if it's different, update it?
I'm trying to muddle through this and get access and excel to do as much of this as possible, as it's a very tedious and time consuming process to manually check each one, one or two times a week.
View 8 Replies
View Related
Jun 4, 2015
I'm trying to make it a little further with my new call answering database at work.
Basically it consists of a main form that has a button leading to a call answering form for each business that we represent. it also has a text box that displays the number of the caller when the telephone picks up, the correct call answering screen is also popped by the telephone software and a macro whenever the phone is answered.
I've got code in place that automatically puts the date and time in the correct field and also copy the incoming telephone number from the main form into the caller number field on the call answering form.
I'd like to take this automation a little further now and get the form to autopopulate the caller details if that caller has called before (we get a lot of calls from the same people) so i'd like to make the form search the table it's linked to for the incoming phone number and to fill in the name, email, company etc... for the caller according to the previous record.
The code for the 2 forms i've currently got setup (the switchboard and one call answering screen) are as follows
Switchboard:-
Code:
Option Compare Database
Dim WithEvents MaxxCom As Metro_MaxxCom_CTI_COM_API.CTI
Private Sub cmd_onnet_Click()
DoCmd.OpenForm FormName:="On_Net_Communications"
End Sub
[Code] .....
View 8 Replies
View Related
Aug 25, 2011
I have a subform in which I want to put in a Command Button to open another form.
I used the wizard as per normal, but when you come to matching specific data fields, there is nothing in my left hand column of the sub form. I have checked the record source property and it is bound to the correct table.
I have read that this is an issue with Access 2007? Is this the case or am I missing something obvious here?
View 3 Replies
View Related
Mar 4, 2014
I have database for each location. Every location will update some information in the databases and send it back to me. In those location based databases, information will be stored in one table called "consolidate_input". I will store these databases in one specific directory and then I want to accommodate the data of "consolidate_input" into one master database having the same identical table in the database.
View 1 Replies
View Related
Sep 27, 2013
I have two db's that I want to merge. they both have the same field names, but different data and some of the have same ID's, How do I merge them?
View 1 Replies
View Related
Jan 20, 2015
I have started working on an existing MS access application (Front-end & Back-end) built with VBA. The frontend linked to a back-end access database which contains only tables. Because of the nature of the work involving vehicles. Each user has their own copy of identical databases with their individual data stored in backend.
Right now we just copy each back-end database via ftp and store it as usr_backenddb_date.accdb Back end databases contain around 16 tables with most of them containing a autonumber field as a primary key. Further tables are connected with each other referencing the primary key as a foreign key .
I would like ideally create a function in vba that lets me select the database and merge all the data from that database to a identical central database. For the initial part, I am thinking of combining each individual table in each database and changing the autonumber field with SQL with possibly trying to cascade the change so the refrences remain intact.
Constant Access to one back end db via internet (or server) is not really possible as the users are able to connect to internet (network) only few times a day. There are over 100 backends (for each vehicle) with over 1000 records in each.
View 9 Replies
View Related
Sep 10, 2013
I have created a rather large data entry form for one of our departments which will be used to run a mail merge document. The merge is run off of a query of the form, rather than the tables themselves...I think that is what I've read is the right thing to do?
Anyways, most of it is working great so far but I've run into an issue where a few of the form fields are combo boxes. In access and in the query the data looks correct, but when you look at it in word (in edit recipient list) and after the merge, it will have a file path name instead.
So a combo box that has "Medical Plans and Visual Plans" in that field in the query, comes over saying "c:Users
ameAppData
Ex: New Hires are provided information about Medical Plans and Visual Plans.
Ex: New Hires are provided information about MeC:UsersbrooksAppData
It always shows the first two letters before putting the filepath name in there.
I have tried doing it multiple ways...a lookup to another table, lookup right in the field itself....get the same results.
View 3 Replies
View Related
Nov 4, 2005
Hello eveyone . . .
i am attempting to create a VERY complicated query . . . it's sort of confusing:
I recieve data from an outside source and it is up to us to format it for our own analysis . . .
i have fields:
MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc
The fields we want to focus on is "AccNo" and "TestCode"
Now i want to filter based on this condition:
If "AccNo" AND "TestCode" BOTH have duplicate data in a record. Show that record only once. Remember. . . "AccNo and TestCode, both these fields have to have duplicate data in a record.
Example:
MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc
CAPT.....M566679.....John Blue.....123456.....CBCA
CAPT.....M566679.....John Blue.....123456.....CBCA
the example above should only display once because of the repeated AccNo and TestCode
Example:
MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc
CAPT.....M566679.....John Blue.....123456..... CBCA
CAPT.....M566679.....John Blue.....123456..... LIPID
the example above is just fine because the TestCode is not matching
How do i create a query to accomplish this? someone please help, i've had troube with this for the passed couple weeks . .. .
View 2 Replies
View Related
Oct 7, 2013
I have a form which I am using child/master fields to create a filter (see attached pic)
For the date ranges I need these to be
>= Start Date and <= Actual Finish Date
How can I set the criteria for these two fields, so that when someone picks a date it knows it's from or before the date entered.
View 14 Replies
View Related
Nov 5, 2012
I have not found the solution to this looking elsewhere, but basically what I am trying to do is the following:
person date
1 1/1/2012
1 12/15/2012
2 6/12/2012
1 6/13/2012
2 6/14/2012
3 6/16/2012
I want a query which shows the count or records for which a person has a date within 30 days of each other. This is being queried from a large table in which records would may not be sequential by person. Thus in my example above, the third record for person 1 would be omitted as well as the record for person 3.
View 1 Replies
View Related
Mar 11, 2014
I often create contract using mail merge. I have an access file that I want to use as data source for word file. But it does not automatically.
Please download the attached file !
If there are 1 customer and 1 property, I do not need to do anything. Conversely, if there are many customers and many properties, I take time to manipulate.
Firstly, I open the word file. I have to copy and paste paragraphs that I want. Highlight of the original paragraphs is blue.
Secondly, I click 'Insert Word Field' -> select 'Next Record'.
In short, I wish to use VBA in access file to automatically perform the steps that I have outlined.
View 3 Replies
View Related
Jun 19, 2007
Hi All,
Wondering if you can help I have a table called "example" which has field "a" "b" "c" "d"...
I then create another table called "importtable" with field "a" (imported in from Excel)...
I would then like to create a query which matches any and shows all the records which I imported in from Excel to my current table called "example" I looked at joining via "relationships" using a Select Query but it doesn't quite show what I'm after...
Should add I would like Field "a" in both tables to show but only if the number exists in the "importtable" if not then don't show..
Any tips :confused:
View 1 Replies
View Related
Nov 13, 2006
In a database I have two tables, one is linked to an excel sheet (our customers order) and the other is created via a "make-table query" from our business system.
In both tables I have the customers part numbers and neither contain a primary key.
What I need to do is compare the part numbers in both tables to find if a part number is present in the order but not in our business system.
I just cannot figure out how to do this.
Any ideas are greatly appreciated
/twallstr
View 2 Replies
View Related
Feb 23, 2006
I have written a VB.net application that uses an Access database. I have a table that displays the following information.
Customer Name
Customer Phone
Customer Address
Shipping status
Now in my vb.net application I am displaying these 4 things in a data grid. But here is the problem. The shipping team updates their database daily with the new Shipping status. So to get the latest shipping status I created a linked table, lets call is link_shipping. Now All I have to do is somehow get the shipping status field from link_shipping and update the respective table in my database with this info.
To do this I created a query. but now I am having to manually go into my database and run this query to make sure the shipping dates sync up. How can I do this in a better way. The only thing I can think of is to find some way to automate the running of this query every day. But I dont know how to do this. Can anyone suggest anything here.
View 3 Replies
View Related
Jun 26, 2013
I have a series of numbers in 30 columns. I want to identify where either of two numbers appear in the same record regardless of which of the 30 columns they are in.
OTHCPT2OTHCPT3OTHCPT4OTHCPT5OTHCPT6OTHCPT7
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 #NULL!#NULL!
2318095978C176788307J0131 J2270
For instance if 23180 and J2270 are in the same record I want to identify them for all of the records.
View 7 Replies
View Related
Mar 21, 2013
For my query I have 3 tables.
TEAMS. This contains TeamID / TeamName and TeamManagerName.
TeamAPPLinks. This is a many to many relationship and contains TeamAppID / AppID and TeamID (TeamID links to Teams.TeamID and AppID links to Applications/AppIS)
Applications. This contains AppID / AppName / Pass/Fail.
So teams can have many applications and applications can have many teams.
I only want to show teams that have all applications that have the Pass/Fail column as PASS. I have managed to do this for FAIL runing a query in a query but obviously its different for PASS as all associated applications have to be a PASS, not just one FAIL for a FAIL.
View 4 Replies
View Related
Aug 14, 2013
How to get the following results using 1 table:
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID
1 5
2 4
3 6
4 2
5 1
6 8
Results records I'm looking to display for would be:
1 5
2 4
3 6
6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
View 5 Replies
View Related
Jun 16, 2013
I am undertaking an analysis of corporate mobile phone data based on data from the phone provider. The base data consists of a number of monthly text files at transaction level for voice calls, data usage, billing/tariff information, international calls/transfers etc. I have set up VBA code to import the text file data into a number of Access tables.I would like to have a way of joining these tables but the only common field is the phone number (a text field with the groups of numbers separated by hyphens).
I confirmed that joining the tables on the phone number text field does not work.My idea was to create an additional table with just one row for each phone number and link that to the other tables by the ID in the new table. I was able to create this table [PhoneNumbers] (by creating a totals query of the phone numbers from the main call transactions table, I then dumped it into Excel and then imported it into a new Access table with an auto-generated ID column).My problem / challenge is how to get the ID column from my [PhoneNumbers] table to appear in each of the other tables so that I can join them effectively. In the Excel-world, I would have used a vlookup function.I even thought of performing this as an interim step in Excel but there are too many records / rows in some of my tables. It seems that the dlookup function is not what Im looking for and even if the IIF function is suitable, I cannot get the syntax to work for me.
View 8 Replies
View Related
Dec 29, 2011
I'm trying to clean up a database and make sure that I don't make an error in future entrys.
Is it possible to make a "key" that highlights the rows with matching keys. The thing is that a the "key" is Switchname + Switchport, data placed in 2 different columns in the same row.
How do I do this? Is it possible?
View 10 Replies
View Related
