Combine Two Tables Into One - AndreaVB Visual Basic and VB.NET source code discussion board

Next Topic (How can I get the total of searched result in Datagrid?)

AndreaVB Forum : Database : Combine Two Tables Into One Solved Topic

Poster

Message

bolendercm
Level: Scholar

Registered: 25-10-2005
Posts: 39

Combine Two Tables Into One

All,

I have a table called annual leave and one called sick leave. Each were fed by their respective forms, an annual leave form and a sick leave form. In the intrest of ease and data base normalization, I have combined the two forms (Annual Leave and Sick Leave) into one form (Type of Leave) with a combo box to select either Annual or Sick. How do I combine the orginal Annual Leave and Sick Leave TABLES into ONE table called TypeOfLeave with out losing and data

____________________________
C.......................

13-01-2006 at 02:45 PM

stickleprojects
Level: Moderator

Registered: 09-09-2002
Posts: 891

Re: Combine Two Tables Into One

Hi,
First off... copy the database!

Create the target table (TypeOfLeave) with the fields you want.
Create and Run 2 insert queries in access the copies the data into this new table... 1 for Annual Leave and 1 for Sick Leave.

done.

Hope this helps,
Kieron

____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

13-01-2006 at 04:50 PM

bolendercm
Level: Scholar

Registered: 25-10-2005
Posts: 39

Re: Combine Two Tables Into One

Insert Query..............................................never thought of that. Thanks K.....................

____________________________
C.......................

13-01-2006 at 06:15 PM

sneha
Level: Scholar

Registered: 28-03-2006
Posts: 29

Re: Combine Two Tables Into One

quote:
stickleprojects wrote:
Hi,
Create and Run 2 insert queries in access the copies the data into this new table... Kieron

Hi, I have similar problem but I don't know about the insert queries . Could you please write the insert querries for me? The following are the fields:

Table1 : Sales fields for this table are as follows:

CustomerName
InvoiceNo
InvoiceDate
InvoiceAmount

Table2 : Payment fields for this table are as follows:

CustomrName
PaymentMode
PaymentDate
PaymentReceived

What I need from the above tables is it should show the following data in Table3.

Table3 : AccountBalance fields for this tables are as follows:

CustomerName
InvoiceDate
InvoiceAmount
PaymentDate
PaymentReceived

Please note that it is not a relational database (What I mean is, there is no relation between CustomerName in two tables). Simple access database with 2 tables.

Thanks in Advance.

____________________________
Sneha

28-03-2006 at 06:09 PM

Chris_871
Level: Master

Registered: 30-11-2002
Posts: 106

Re: Combine Two Tables Into One

Hi

Insert query for your sales table..

SqlSales = "Insert into Sales (CustomerName,InvoiceNo,InvoiceDate,InvoiceAmount) values ('" & trim(custname) & "','" & trim(invno) & "'," & (invdate) & "," & (invamt) & ")"

Regards
Chris

29-03-2006 at 04:07 AM

sneha
Level: Scholar

Registered: 28-03-2006
Posts: 29

Re: Combine Two Tables Into One

quote:
Chris_871 wrote:
Hi

Insert query for your sales table..

SqlSales = "Insert into Sales (CustomerName,InvoiceNo,InvoiceDate,InvoiceAmount) values ('" & trim(custname) & "','" & trim(invno) & "'," & (invdate) & "," & (invamt) & ")"

Regards
Chris

Thanks.

Actually I need the Insert Querry for the Target Table " AccountsBalance" with the following fields from Sales table and Payment table:

CustomerName
InvoiceDate
InvoiceAmount

from Sales table &

PaymentDate
PaymentReceived

from Payment table

Thanks in Advance.

[Edited by sneha on 29-03-2006 at 12:43 PM GMT]

____________________________
Sneha

29-03-2006 at 04:41 AM

Goran
Level: Moderator

Registered: 16-05-2002
Posts: 1681

Re: Combine Two Tables Into One

I dont understand how to you think to join two tables, if no relationship between them exists? And why are those two tables split, since there is no benefit from the split? Anyway, if you know for sure that there is one and only one record in both databases for one customer, then this is how it can be done:

firt run this query

INSERT INTO AccountsBalance (CustomerName,InvoiceDate,InvoiceAmount) SELECT CustomerName, InvoiceDate, InvoiceAmount FROM Sales

and then this query

UPDATE AccountsBalance a INNER JOIN Payment p ON a.CustomerName=p.CustomerName SET a.PaymentDate=p.PaymentDate, a.PaymentReceived=p.PaymentReceived

____________________________
If you find the answer helpful, please mark this topic as solved.

29-03-2006 at 09:16 PM

AndreaVB Forum : Database : Combine Two Tables Into One Solved Topic


	Surf To:



	AndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2007 Andrea Tincani