I want to combine two tables into one. I have a table with all the field
definitions from two other tables. I now need to update this new table with
the data from the other two tables.However both the existing tables have 2
fields that are common ie txtmonthlabel and txtcompany. I tried running an
append query from each table but I got duplicate records from the two tables
eg if 1 table had data for Company A and the other table also had data for
Company A I got two records whereas I only want one record with the combined
data. The same thing happens with txtmonthlabel.
Can anyone help me here?
TIA
Tony Williams 4 11540
I think that what you need to do is copy one of the original tables,
and then add the extra fields to the copy. Then run an append query to
add in the records from the other table.
On Thu, 16 Dec 2004 11:14:26 +0000 (UTC), "Tony Williams"
<tw@tcpinvalid. com> wrote: I want to combine two tables into one. I have a table with all the field definitions from two other tables. I now need to update this new table with the data from the other two tables.However both the existing tables have 2 fields that are common ie txtmonthlabel and txtcompany. I tried running an append query from each table but I got duplicate records from the two tables eg if 1 table had data for Company A and the other table also had data for Company A I got two records whereas I only want one record with the combined data. The same thing happens with txtmonthlabel. Can anyone help me here? TIA Tony Williams
Thanks Dave I've tried that but I get duplicates. Eg One table has say 1500
records the other has 500. When I combine them I should still have 1500 but
I get 2000
So the data is not being "merged" just added as new records. I've been at
this for three days now and I didn't think something that sounded
simplecould be so difficult!
Thanks
Tony
"Dave Bradshaw" <"dave"&"."&"br adshaw"&"@"&"bo wbros"&"."&"co" &"."&"uk">
wrote in message news:7f******** *************** *********@4ax.c om... I think that what you need to do is copy one of the original tables, and then add the extra fields to the copy. Then run an append query to add in the records from the other table.
On Thu, 16 Dec 2004 11:14:26 +0000 (UTC), "Tony Williams" <tw@tcpinvalid. com> wrote:
I want to combine two tables into one. I have a table with all the field definitions from two other tables. I now need to update this new table
withthe data from the other two tables.However both the existing tables have
2fields that are common ie txtmonthlabel and txtcompany. I tried running
anappend query from each table but I got duplicate records from the two
tableseg if 1 table had data for Company A and the other table also had data
forCompany A I got two records whereas I only want one record with the
combineddata. The same thing happens with txtmonthlabel. Can anyone help me here? TIA Tony Williams
table1 is the table you want to append to
table2 is the table with some but not all duplicate data.
company is the field that is the same in both tables.
INSERT INTO table1
(
SELECT table2.*
FROM table2 LEFT JOIN table1 ON table1.company = table2.company
WHERE table2.company Is Null
)
Your situation may be a little different. If this does not work as planned,
try this:
In the query design part of Access, visually construct a SELECT query to get
the results you want. Then you can change it to a "Make Table" query if you
want. (Not sure if you want a new table3, or just to insert the
non-duplicated records into table1) . Then you can view the SQL, and copy it
to VBA, or run it as a stored query from code.
Darryl Kerkeslager
"Tony Williams" <tw@tcpinvalid. com> wrote in message
news:cp******** **@titan.btinte rnet.com... Thanks Dave I've tried that but I get duplicates. Eg One table has say
1500 records the other has 500. When I combine them I should still have 1500
but I get 2000 So the data is not being "merged" just added as new records. I've been at this for three days now and I didn't think something that sounded simplecould be so difficult! Thanks Tony "Dave Bradshaw" <"dave"&"."&"br adshaw"&"@"&"bo wbros"&"."&"co" &"."&"uk"> wrote in message news:7f******** *************** *********@4ax.c om... I think that what you need to do is copy one of the original tables, and then add the extra fields to the copy. Then run an append query to add in the records from the other table.
On Thu, 16 Dec 2004 11:14:26 +0000 (UTC), "Tony Williams" <tw@tcpinvalid. com> wrote:
I want to combine two tables into one. I have a table with all the
fielddefinitions from two other tables. I now need to update this new table withthe data from the other two tables.However both the existing tables
have 2fields that are common ie txtmonthlabel and txtcompany. I tried running anappend query from each table but I got duplicate records from the two tableseg if 1 table had data for Company A and the other table also had data forCompany A I got two records whereas I only want one record with the combineddata. The same thing happens with txtmonthlabel. Can anyone help me here? TIA Tony Williams
Thanks Darryl first suggestion worked OK
Tony
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message
news:Rr******** ************@co mcast.com... table1 is the table you want to append to table2 is the table with some but not all duplicate data. company is the field that is the same in both tables.
INSERT INTO table1 ( SELECT table2.* FROM table2 LEFT JOIN table1 ON table1.company = table2.company WHERE table2.company Is Null )
Your situation may be a little different. If this does not work as
planned, try this:
In the query design part of Access, visually construct a SELECT query to
get the results you want. Then you can change it to a "Make Table" query if
you want. (Not sure if you want a new table3, or just to insert the non-duplicated records into table1) . Then you can view the SQL, and copy
it to VBA, or run it as a stored query from code.
Darryl Kerkeslager "Tony Williams" <tw@tcpinvalid. com> wrote in message news:cp******** **@titan.btinte rnet.com... Thanks Dave I've tried that but I get duplicates. Eg One table has say 1500 records the other has 500. When I combine them I should still have 1500 but I get 2000 So the data is not being "merged" just added as new records. I've been
at this for three days now and I didn't think something that sounded simplecould be so difficult! Thanks Tony "Dave Bradshaw" <"dave"&"."&"br adshaw"&"@"&"bo wbros"&"."&"co" &"."&"uk"> wrote in message news:7f******** *************** *********@4ax.c om... I think that what you need to do is copy one of the original tables, and then add the extra fields to the copy. Then run an append query to add in the records from the other table.
On Thu, 16 Dec 2004 11:14:26 +0000 (UTC), "Tony Williams" <tw@tcpinvalid. com> wrote:
>I want to combine two tables into one. I have a table with all the field >definitions from two other tables. I now need to update this new
table with >the data from the other two tables.However both the existing tables have 2 >fields that are common ie txtmonthlabel and txtcompany. I tried
running an >append query from each table but I got duplicate records from the two tables >eg if 1 table had data for Company A and the other table also had
data for >Company A I got two records whereas I only want one record with the combined >data. The same thing happens with txtmonthlabel. >Can anyone help me here? >TIA >Tony Williams >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: frizzle |
last post by:
Hi,
I know this might sound strange but i think(/hope)
it's quite simple:
I'm running 2 queries in a mysql DB, first one returns 20 results.
Now how can i echo results from the second query in the 1st query's
result,
like:
|
by: Ron Sorrell |
last post by:
I have a requirment to take data from a large set of tables where the
total number of these tables may change on a regular baisis and
output into another table. All the tables willl have the same
columns. Frequency is being debated but it maybe as much as once per
hour.
Example
1) I need to choose all the following tables
select * from dbo.sysobjects where name like '_CPY%.
|
by: Kenneth Fosse |
last post by:
Hi.
I'm currently working on a project which involves the creation of a
web page which reports selected data to customers from two back-end
systems. I use ASP script language on IIS, the server is MS SQL 2000.
Now I'm struggling with combining two tables from the different
databases. I'm sure it's simple enough, but I'm a little short on the
SQL expertise.
I've got two databases, db1 and db2, and then two tables, db1.t1 and
|
by: Bob C. |
last post by:
I am considering combining one large Access db and several small
Access db's into a single SQL Server 2000 db. The small db's serve
separate but related functions in our operation, and they all share
some tables for read-only lookup purposes. All users(about 24) work
in the main db, as well as in the small db's related to their
particular jobs.
Here are the pros and cons as I see them now.
Pros:
|
by: Max |
last post by:
Hello everyone,
I have a task of combining two tables: tblPatient and tblSolution,
related by a KEY field in a one-to-many relationship from tbl Patient
to tblSolution, respectively. So, I have the following table layout:
tblPatient
----------
REC: Autonum (Primary Key)
KEY: Number (NOT UNIQUE)
| |
by: Frank |
last post by:
Hi there, I'm trying to generate a report for an old database and I'm
having trouble coming up with an elegant way of going about it. Using
cursors and other 'ugly' tools I could get the job done but 1) I don't
want the report to take ages to run, 2) I'm not a big fan of cursors!
Basically there are tables that track history and each table tends to
track only a specific value housed within a date range. I'm trying to
combine the tables...
|
by: cjbutler |
last post by:
Hi,
I am using MS Access 2003 on a Windows XP platform. I am wondering what the best way is to go about combining data from 4 tables. Here is a sample from all 4 tables, with what I would like my results be in in the table below called "TBL_Compiled". Any help is appreciated, and thanks!
TBL_Start_1:
ID, Resource_Name, Start_Date, End_Date
1111, John Smith, 4/11/2007, <blank>
1112, Susan Doe, 5/25/2007, <blank>
TBL_End_1:
|
by: J055 |
last post by:
Hi
I need to search a number of DataTables within a DataSet (with some
relationships) and then display the filtered results in a GridView. The
Columns that need to be displayed come from 2 of the tables but the search
needs check 3 or 4 tables. Do I need to create a new DataTable which has the
columns from both the tables so I can display in the Gridview or can I get
the columns somehow from the DataSet without creating a new table?
I...
|
by: Ken Fine |
last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets
and DataTables should be able to answer fairly easily. The basic question is
how I can efficiently match data from one dataset to data in a second
dataset, using a common key. I will first describe the problem in words and
then I will show my code, which has most of the solution done already.
I have built an ASP.NET that queries an Index Server and returns a...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |