Queries :: Merging Two Separate Databases With Duplicate As Well As Unique Information

Jun 12, 2013

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.

Here's an example of what i've been trying to do:

INSERT INTO Contacts ( ContactID, ContactTitle, Name, Surname, JobTitle, TelephoneSTD, Telephone, FaxSTD, Fax, Locale, SubjectKeyword, SchoolID, Reply received] )
SELECT [1Contacts].*, [1Contacts].ContactID, [1Contacts].ContactTitle, [1Contacts].Name, [1Contacts].Surname, [1Contacts].JobTitle, [1Contacts].TelephoneSTD, [1Contacts].Telephone, [1Contacts].FaxSTD, [1Contacts].Fax, [1Contacts].Locale, [1Contacts].SubjectKeyword, [1Contacts].SchoolID, [1Contacts].[Reply received]
FROM 1Contacts;

View Replies


ADVERTISEMENT

Queries :: Merge Duplicate Values With Unique ID

Feb 28, 2014

I have a query with over 85,000 lines of data where each row has a unique id but the unique id appears multiple times within the table. For example, there is a unique numeric id tide to various types of pets across four columns. So the id number 1 could appear in four different rows each with different pets. See the first attachment "Original Table"

I want to be able to create a table that has one for for each unique id with all of the corresponding pet data. See the attached "Desired Table".

View 1 Replies View Related

Merging Databases

Aug 24, 2005

I am new to Acess, I am trying to merge 2 Access DBs and am not sure of the steps to accomplish this, if it can be accomplished and/or are there any tools to reach the same goal.

View 5 Replies View Related

Merging Databases

Oct 25, 2007

Hello-

We are using an Access database for off-site data entry. We therefore have one master database we keep in the office and then a "template" version -which is identical to the master version except no records - we bring to each site, do the data entry and then merge that data back into the Master database.

Can anyone help suggest ways to make the data merging process more efficient. The database has approx 12 tables, so I dont' want to create an append query for each one every time. Is there a way to just "merge" two identical databases??

THANKS

View 7 Replies View Related

Merging Multiple Databases

Oct 12, 2006

Ok, this is only a very general question, but I'm hoping somebody here can give me some general advice.

Basically, I have three separate database which I've written for my company. They were all written at different times, hence the fact there are three and not one that does three things. The first is used to raise, edit, delete, and track RMA records for returned merchandise. The second is used to raise, edit, and track Training data for people who have undergone my training course. The third is my main database that contains all of my clients' details, plus a log of all calls taken from them. This is the largest and most complex of the databases and has some fairly advanced relationships setup to enabled hundreds of call logs to be linked to a single record of necessary.

Now all three databases have been written using the same kind of structure. They all use popup modal forms to prevent malicious/accidental damage to the database by the staff who use them. They are all locked down and Access cannot be broken into whilst they are running. they all also have their own primary keys.

All these databases work just fine, but I'm now a little tired of each staff member having three shortcuts on their desktops, depending on which database they need to use. what I'd really like to do is somehow merge all three of these databases into one single database, and alter the switchboard accordingly to allow users to access any of the three parts of it.

The problems - as I see them - are: (a) that my main database uses a lot of relationship links to maintain certain records, whilst the other two use none, and, (b) that each database, having been written separately, has records with their own ID keys and autonumbers.

I did toy with the idea of having some kind of function on my main switchboard that would allow me to close the current database being used and open another, all without closing Access, but I haven't been too successful in my tests...

...so, my question is, does anybody have any bright ideas as to how I can go about merging these three, or, at very least, centralising access to all three?

View 3 Replies View Related

Merging Two Sample Databases

Mar 15, 2008

Sorry if this is in the wrong place - I am very new to this site

I have asked this on another forum but have had no replies - hoping someone here can help

I am using Acess 2000 and I am not that good at it

Is there any way to merge two of the sample databases which comes with
Access together - I would like to have the Inventory Control and the Order
Entry databases as one database - can this be done? There is no data in the databases yet

Hope someone can help and thanks for looking

View 4 Replies View Related

Merging Two Different Access Databases

Jul 22, 2006

Hi,

I've asked this on a few other websites with limited or no success.

this is from one db. this table originally had about 20-30 different fields,

WarehouseNum ItemNum load_date Shipped
101 31 6/26/2006 200
101 31 6/27/2006 180
101 338 6/26/2006 162
101 338 6/27/2006 357

this table is from another db. Notice how WarehouseNum is '9' instead of '1' and itemNum is '000'. This is just one of many impediments I face. As well, none of the table fields are the same, I changed them here to make it easier to work with.

SupplierNum ItemNum WarehouseNum PlannedDelDate DeliveredQty
869747 00031 901 6/26/2006 250
869747 00031 901 6/27/2006 300
869747 00338 901 6/26/2006 490
869747 00338 901 6/27/2006 490


The goal here is to query the two simultaneously, perhaps with linked tables, type in an ItemNum using a parameter and know for instance that on the 26th, we received 250 item31 and shipped 200 for a remaining balance of 50. The 50 is what I'm after. Basically, my company uses one program for sales and another for purchasing and absolutely nothing for inventory management. I started with this company about 3 weeks ago and unless i can implement some kind of temporary solution, it will drive me insane.

Advice? Best approach? As well, this db will use data on two different servers
Thanks for the help.
As well, my access experience is limited and our company I.T. is seriously backlogged, besides which, I would like to learn how to do this myself.
Thanks again

View 1 Replies View Related

Merging Two Identical Databases?

Sep 6, 2012

Friends own a book shoppe. They have closed up both their retail stores and are working on line only. A long time ago they bought a PC based program to keep their inventory and manage their on line sales. The program is called BookTrakker and still on sale today.

I called them to ask for assistance merging the access databases for but stores with their Master database. The owner sads they dont offer any assistance in combining databases.

I have had no trouble using the append command to combine the 40 odd tables in the database except for one small table called Keywords. This table only has 5 fields but the sallest has over 7,000 rows. This is one of the more important tables because Keywords are used by the search engines on-line for potential buyers find their interests.

The error message I get from Access is "Microsoft Outlook Access set 0 field(s) to nulll due to a type conversation failure, and it didn't add (number of rows in table) to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations"

The table structure is identical. The only difference is the data in the rows.

I tried removing both Primary Keys for both the tables (in and out). Nothing, same error message.

Next I tried appending the tables without the entire Keyword_id (Primary Key) field. Nothing same error message.

View 1 Replies View Related

Queries :: Populate Rows With Duplicate Information

Jul 24, 2013

I am trying to connect individual responses to a survey to a series of variables describing each environment where the survey was conducted. I have about 1,400 individual survey responses (of about 34 questions each that have been separated into columns) and about 42 environments that are described with 30 different criteria each. They are matched by the environment description

- A line for each respondent and their survey answers
- On that same line would be the criteria of each environment.

Thus, the environment criteria should be repeated several times..However, when I join the fields in a query and require both fields to be equal, When I prioritize the environment panel, I only receive 42 records and the survey responses are blank. Prioritizing the survey panel gives 1,400 rows, but omits the environment info.

how do I populate the environment information variables on each row of the survey responses, even if that information duplicates the same variables on other rows?

View 8 Replies View Related

I Would Like To Create 4 Separate Databases From One Master

Dec 12, 2006

I have a large database of Purchase Orders
Orders can be placed by people in one of 4 departments

I would like to automatically make 4 separate databases (so each department can access their own) from the master

Is there a way to do this?

View 4 Replies View Related

General :: Merging Databases - Importing Saved Imports And Exports

Aug 22, 2013

I'm merging a few databases together. They're fairly simple on their own but I'd like them as one big database. I read that I should import each access database into a new, blank one.

It all works fine, but none of the saved imports and exports come with them. Unfortunately, these imports and exports are relied upon quite heavily.

Is there a way to bring saved imports over from other databases, or will I have to rebuild each one?

View 3 Replies View Related

Merging Data For One Table From Date Entered On Separate Computers

Jan 9, 2015

We are creating a database to log data on a project. There will be thousands of files. Can we input data, using the same table, at separate locations and then merge the data into a master table? We will need to do that many times.

View 1 Replies View Related

Join Two Separate Databases Using Common Fields Slightly Different In Each Database

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.

Database 1

Chrom Gene_Name1 Data1
Chr1 ATC XYZ
Chr2 IDH2 ZYZ

Database 2

Gene_Name2 Data2
ATC;ORF34;NM_123 ABC
ORF65;IDH2 IGH

Joined Query

Chrom Gene_Name1 Data1 Gene_Name2 Data2
Chr1 ATC XYZ ATC;ORF34;NM_123 ABC
Chr2 IDH2 ZYZ ORF65;IDH2 IGH

View 8 Replies View Related

Reports :: Separate Pages By Unique Field?

Jul 29, 2013

I have a DB with all my patients and (among other things) all their referral source.

I would like to print a status report of all my patients, grouped by the referral source, and fax them to the appropriate offices. Easily done with the report wizard.

What I need to know is, how do I create one report, but force a new page for each referral source?

View 4 Replies View Related

Tables :: Merging 2 Fields Together To Create Additional Field - Unique References

Jul 23, 2015

Is there a way of merging 2 fields together to create an additional field

my database consists of 4 main tables (in order of relationships)

*HeadOfficeDetails
*SiteDetails
*ContainersOnSite *Contracts2015-2016

For example;

Account Reference: TEST
Site Number: 001

and the field i would like to have;

Site Reference: TEST/001

I would also like that when i add a new site to that account i will have TEST/002....

View 4 Replies View Related

Merging And Deleting Duplicate Records

Jan 6, 2012

I have an Access table with a number of records which refer to the same [InvoiceNo] but with data in different fields. Normally, my database works like this:

1)Import data from Excel File to tblMain in Access
2)Import data from Excel File to tblVendorLine in Access
3)Import data from Excel File to tblDunning in Access
4)Query is sorting from two first tables only records that refer to the third one (by InvoiceNo)
5)We have only items from tblDunning with data from other two tables to be send (mail to the vendor).

The point is, that when I have relation between those three tables, I receive only few records, but sometimes - for example - tblDunning refers to 5 records in tblMain and to 7 records in tblVendorLine - but there is only 4 records related between tblMain and tblVendorLine, so query shows me only two records. Ive made a query for each table (tblMain & tblVendorLine - relation with tblDunning). Then make one query of those two queries. It append data to new table tblDunnAll

So for example the table would look like this after

Vendor | InvoiceNo | CoCode | Status | Clearing_Date | PBk
100000 23333 GB11 23/12/2011
233332 121212 GB18 16/12/2011
233332 121212 Complete K
134444 777777 GB12 Complete
100000 23333 GB11 Complete K

What I'd like to have is a table with:

Vendor | InvoiceNo | CoCode | Status | Clearing_Date | PBk
100000 23333 GB11 Complete 23/12/2011 K
233332 121212 GB18 Complete 16/12/2011 K
134444 777777 GB12 Complete

how to go about merging the data for each name into one record. There are lots of very useful posting about using SQL to delete duplicate but I can't find a posting with addresses this issue.

View 1 Replies View Related

Merging Data From Duplicate Database Files

Mar 18, 2015

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.

View 2 Replies View Related

Multiple Field Unique Information Db?

Aug 14, 2006

Hi, I'm crating this db:

Two tables, in one there are Items and services, in the other purchases.

1st table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text

2ndt table: ID - autonumber primary key
Product Name - Text
Quantity - Number
Barcode - Text
Client - Text
Puchase Date - Date

How can I enter data to my 2nd table using unique fields (barcode, Product name actually ID in 1st table?) Problem Is Access alows only 1 unique key field in table and that is undertandable. Is there any way out in my situation.

barcode will be entered using barcode scanner it can be only text field but information like product name must fill in automatically, Product name must be drop down box if one wan't to select it manually then barcode should fill in automatically.

Please Attach your DB if you have time to look into this issue.

View 6 Replies View Related

General :: Linking 2 Subforms To Parent Form - Merging Duplicate Records

Aug 28, 2014

Users are too lazy to check whether a patient record exists or not. Consequently, we have a number of duplicate records that I would like to merge.

My problem is finding a way of linking 2 subforms to a parent that is not based upon a query.

On the parent form, I want two Comboboxes called 'Patient_to_Keep' and 'Patient_to_Discard'.

The two subforms called Frm_DUP_Patient1 and Frm_DUP_Patient2 should then display the data of the respective combo boxes.

Once I have this working, I can write the necessary code to compare and update the data as required.

View 5 Replies View Related

Combine Two Columns To Display Information In Third Column As Unique Identifier

Jul 23, 2015

So i have a access database with a main field that we can call vendor # (LIFNR) and another called Company code (BUKRS). There are multiple company codes under a single vendor #. Example:

LIFNR
BUKRS

0000010535
1010

0000010535
5060

0000010535
5610

0000010536
1010

0000010536
5060

0000010536
5610

What I am trying to do is create a 3rd column where i can have a unique row for each of these fields without it repeating. There is no unique identifier in this table and that is what i am trying to achieve.

View 3 Replies View Related

Merge Duplicate Values With Unique ID

Feb 28, 2014

I have a query with over 85,000 lines of data where each row has a unique id but the unique id appears multiple times within the table. For example, there is a unique numeric id tide to various types of pets across four columns. So the id number 1 could appear in four different rows each with different pets. Please see the first attachment "Original Table"

I want to be able to create a table that has one for for each unique id with all of the corresponding pet data. Please see the attached "Desired Table".

Attachment 300829

View 12 Replies View Related

Tables :: Merging Two Like Tables From Two Databases

Nov 4, 2013

I've currently come across the issue where an end user copied the database locally to their computer and has been using it for months and months. After fixing an issue in the database on the server and then finding that someone still had a database bug is how I figured this out. Anyway, what would be the best way for me to merge the table 1 from the database she was using to table 1 in the database on the server? Is there a way to export a table so I can then import the data? Like I said, the databases are identical other then the data.

View 3 Replies View Related

Subform Duplicate Information

Nov 1, 2005

I have a form with a subform attached.

on the Main form you select the student then you enter letters sent out in the subform.

Basically there may be 22 letters going out all the same just to different students.

so the standard letter type will always be the same as will the date field
I know you can duplicate a record if its the same student saw that in the wizard however is there away of "holding" the information in certain fields so that its autoamatically filled in when you select the next student?

View 2 Replies View Related

Tables :: Unique Identifier - Increment For Duplicate Records

Jul 10, 2014

I'm currently in the process of doing a an overhaul of the database including a table, some odd 17,500 records long.

What I need to do is to create a unique identifier from the existing part number, almost like an autonumber, but it only increments when there are duplicates. What I mean is that there would be for example, there would be five records of say, part 000135.

What I'm trying to get at is, how I would have to code to grab the existing part numbers (all formatted to be 6 digits), add an extra 2 digits to be its unique identifier, and then save it all to a Unique Identifier column, that I created.

An example of what I want is say, I have part numbers 000135 and 000136. The numbers would go up from 000135-01, 000135-02, 000135-03, 000136-01, 000136-02.... The dash is not as important as implementing the 2 digit unique identifier.

View 5 Replies View Related

Tables :: Unique Multicolumn - Avoid Duplicate Records

May 28, 2014

I have 3 tables

tbl1 :
NameID
Name

tbl2 :
CatID
Category

tbl3 :
ProdID
CatID
NameID
Remarks

How can I avoid duplicate records in tbl3? Since remarks can be Null? product is later used in tblInventory...

View 1 Replies View Related

Modules & VBA :: Information Message For Duplicate Field

Jun 8, 2015

How can I create an alert message to tell the user that there are an existing work order number in the table? I don't want it to restrict the entry, just inform the user that there is an existing work order number, do they want to continue.

Btw, the code will be attached to a combo box (cbowonum).

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved