I have mulitple dbs that are exactly the same in design that I need to combine into one db. The problem that I am having is that the primary keys are autonumbers and I get errors when trying to copy and paste the data into the tables. Is there an easy way to combine/merge all the tables? I have attached a sample for review. Password LOCK
View RepliesWe are a non-profit that does blind mailings for our membership drive.
The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT". The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
"Alt ID","Title","First Name","Middle Name","Last Name","Suffix","Address1","City","State","ZIP","ct y_code","Amount Paid","Run Date","Tender","Fund","Purpose","Solicitation","Me mbership Question","Member Type","Constituent Type","Segment"
I need to combine two tables that have identical structure to one table. Different people have used them to insert data from different parts of our project. I have tried to find information about how to do this but so far with little luck.
View 13 Replies View RelatedI have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.
View 3 Replies View RelatedI have multiple tables that are created from some software that puts them each into the access database. each table is one year of data. the software cannot put all years into one table because of memory. I want to combine these tables into one. In addition, I need to do some manipulatins: the dates come over as MM-DD-YYYY strings and I need to convert them to dates MM/DD/YYYY, and also I need to parse a string to extract the General Ledger account number. I know how to use this easily from within Excel VBA using dateserial, and Instr etc., but now sure how/if I put some code into access and have it called to do this for me automatically. Each night the most recent year is automatically updated in access, so the combination file needs to be refreshed whenever the most recent year file changes.
I currently doing all of this in excel: first initialize the NewCombinedTable, then one by one dumping each year file into an excel worksheet, doing the work needed, then appending it to the NewCombinedTable. However, I think it would work much quicker if I was able to trigger the Combination to happen in Access along with the data manipulations.
I also need to know whether DAO or ADO is best for me to use with 2010...
So I have two tables,
table1: (company_name, company_code, year_month, rating)
table2: (company_name, company_code, year_month, asset, debt, equity,...)
What I would like to do is to call up all the data in table1, and then call up (asset, debt, equity,...) from table2 where the company_code and year-month are equal. What do you think is the best way to do this? Keep in mind a few things:
1. I'm dealing with hundreds of thousands of data lines
2. I may need to get more data similar to table2 in the future and call it up in the same way
I thought I should create a primary key "company_code-year_month"for both tables, but that method doesn't seem like a good one, and if I were to have another table, will have to create the same primary key for that table as well?
I have 2 tables one having primary key and other having secondary key
TblA
pkey
info1
info2
TblB
pKey_Rk
info3
info4
I want a resultset having all the records present in tblA plus records in TblB (if present)
pkey----info1----info2----info3----info4
value---value---value---value---value
value---value---value---notpresent--notpresent
Hello,
I have around 40 tables right now on hand and I would like to combine them into one big table (a table, not table formed by query) and I am wondering is there a easy way of getting it done without me physically copying and pasting all 40 tables? Thanks!
regards,
Anyi
Hi I have Two tables with identical field names with information reagarding a physical catalogue from one site and a catalogue from another site I would like to join them utilizing there manufactorer code. And ignore all the duplicates.
Any assistance greatly appreciated.
I want to combine 3 tables into one table. Each table one after the other. So one table may have 300 records so the next table will have its first record on line 301. Fields are all the same throughout each table.
View 3 Replies View RelatedHi all
I have two tables I need to merge them in one query.
The problem is that I need to have all the records in the new table without dublication. How can I do that?
Regards,
dallagmm
Here I have two tables. The first one categorizes items by certain descriptions from a fixed list. Each description, eg; "Big Size" is categorized in the table below with a minutes key. I want to create a table/query that is of the form of the 3rd table. I showed only the first record as an example, replacing minutes with each description.
Item
Size
Strength
Agility
A
Big
Strong
Slow
[Code]....
I want to "combine" these tables to make the table look like this without changing the design of the above two tables. If I MUST change the design of one of the tables, that's fine.
Item
Size
Strength
Agility
A
3
1
10
I have an Access DB built that links to an excel spreadsheet. The spreadsheet changes so I cannot import and have to link.
Now the source of my spreadsheets is producing two spreadsheets with identical fields but different data. I need to combine the data from these two spreadsheets. Can I link them both and combine them in a query? How?
Appreciate any advise.
I have multiple databases (one for each department). All 4 databases have an Agents table (tblAgents). Now I am creating another database that uses all of the agents on site. What I want to do is link all the agents tables and combine the values in one query.
Fields are:
UserID (PK)
AgentName
BadgeNumber (Employee Number)
Campaign (Department)
Coach (Supervisor)
HireDate
Status (Active / Leave of absense / Terminated)
The tables are linked to my new database. So logically, I'd think my next step is to query the tables. This is where I'm stuck. How do I say:
Agent = tblAgentsDepartment1.AgentName and also tblAgentsDepartment2.AgentName and also tblAgentsDepartment3.AgentName and also tblAgentsDepartment4.AgentName?
I'll have to do this for each field involved...
I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.
i.e.
I have a
Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation.....
Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation.....
Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation.....
Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....
-A person can be within one of these tables more than once but with all the same information.
-A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.
Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers)
Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?
The problem I'm having is that i need to combine 12 tables with multiple columns and not all columns have the same headers. So if a column is present it should be updated, if not it should be added.
The end result should be one column with all records and all columns that exists in all 12 tables.
The tables have an identifier that is the same for all columns (supplier reference).
Table 1 has 56 columns
Table 2 has 42 columns (of which some have the same header)
....
(--doing every column manually is something i would like to avoid if possible --)
I'm trying to merge 3 tables into 1
Made new table which contains all the fields from the 3 source tables
Exported source table 1 to xls
On importing to destination table I get "an error has occurred. Data not imported"
Can anyone shed light on how I can do this or waht teh problem might be?
Many thanks
Paul
I have a very simple question which is anoying me already for a very long time. I have two tables both with a key field, a common field and a table specic field. Like :
Key,common,spec1
222,AAAA,20000
333,BBBB,0000
444,CCCC,22000
Key,common,spec2
111,EEEE,PRO
222,AAAA,PRV
777,FFFF,PSR
Which I want to merge to one table like this :
Key,common,spec1,spec2
111,EEEE,-----,PRO
222,AAAA,20000,PRV
333,BBBB,10000,---
444,CCCC,22000,---
777,EEEE,----- ,PSR
If I set a relation on the keyfield, where both keys should be equal, the result is :
222,AAAA,20000,PRV
If I set a relation on the keyfield, wiith all records from table 1 and from table 2 where both keys are equal, the result is :
222,AAAA,20000,PRV
333,BBBB,10000,---
444,CCCC,22000,---
If I set a relation on the keyfield, with all records from table 2 and from table 1 where both keys are equal, the result is :
111,EEEE,-----,PRO
222,AAAA,20000,PRV
777,EEEE,----- ,PSR
How do I solve this?
I have database A & database B, which is a copy of the first.
I have made some changes to database B but need to merge one table back into the original database.
Any ideas.
Thanks.
How to I combine 2 tables with the same primary key to make one table? I have seen several thigns but none have worked. I seen inner and outer joins. All 4 of my tables have the same primary key. I just want to combine all the tables to make one table. I have a form with subforms but if I can combine all the tables and work off one rather then 4 I will be happier. Every week to 2 weeks I upload new data from excel. How would I after I add the new data combine all tables to one super table?
View 7 Replies View RelatedI get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
View 7 Replies View RelatedI have create 6 tables for library books (which are differentiated by categories). Each table has different category and unique ID name e.g. F1, F2, F3..(for table 1), G1, G2, G3..(for table 2) and so on. How do I combine all those tables into 1 table for easy search for a book rather than open up each table? Tried append query but its ID run as 1, 2, 3... , not F1, F2, F3 and it only append 1 table, not the rest.
View 3 Replies View RelatedHow to combine two tables in one table such that values are neither duplicated nor missed?i wanted to join Table5 and table6 such that the values of Field1 are all populated in the resulted table but neither duplicated nor overlapped!
View 4 Replies View RelatedI have a few input tables and I am trying to combine the fields from 2 tables into one query. How do I go about doing it without having the project amount (highlighted in red) being duplicated?
Table 1
Table 2
Query
Project
Project Amount
Project
Budget
Budget Amount
Project
Project Amount
Budget
Budget Amount
[code].....
Hi,
I have two tables as follows:
___________________________
tableA:
projectid capital
1 200
2 200
3 300
4 400
tableB:
projectid capital
1 100
2 200
3 300
____________________________
I want to create tableC that is a merge of tableA over tableB as follows:
projectid capital
1 200
2 200
3 300
4 400
Can someone tell me how I can get this done please?
I have been struggling with query syntax on this for about 2 days now not knowing how to get this done. Thanks for responding.
I have several tables I have brought in from excel spreadsheets. Each one has one common column (lets call it serial number), but then all the rest have a variety of information (one has location info, one has network info, one has asset tracking info, one has sw load info, etc). What I want to do is a) collect all the records from each table into one, b) merge the records with common key field so I don't have multiple records for this one key field and can have just one record with the information from all the different tables in it, c) not lose any records that don't have a sister record in some other database.
Anyone have any idea the best way of attacking this problem is. Common joins cause problems because of records that are not in both tables get left behind. Union and append queries creae many multiple records with the same ser # but only the bits of info they brought in from their original spreadsheet, not the other info from the other speadsheets. I am an Access Newbie and trying to figure out if this is even possible without extensive coding.
Thanks,
CORiverRat