Make Table Query - unique records from other two tables

[Nicolae Fieraru]

I have two tables, they contain:

Table1: ID1, Name1, Address1, Purchase1
Table2: ID2, Name2, Address2, Purchase2

I need a query which creates Table3 with content from Table1 and Table2.
The records in Table3 have to contain all distinct records from Table1 and
Table2 (records where Name2 and Address2 do not already exist in Table3) and

Any help appreciated. I need these queries as a reference, I consider they
are a starting point in building other queries.

Regards,
Nicolae

[WindAndWaves]

Here are a number of SQL statements that you can paste into queries to
achieve this.

The way to paste them is to create a new query then goto SQL view and paste
it in there. Make sure you get it right, otherwise you will have to close
the query without saving.

Alternatively, you can always type SELECT * from [tbln];

where tbln is a name of table in your database

here we go:

1. SELECT [table1].ID1 AS ID, [table1].name1 AS name, [table1].address1 AS
address, [table1].purchase1 AS purchase FROM [table1];

save this query as T1

2.SELECT [table2].ID2 AS ID, [table2].name2 AS name, [table2].address2 AS
address, [table2].purchase2 AS purchase FROM [table2];

save this query as T2

3. select [T1].* from [T1] UNIION SELECT [t2].* FROM [T2]

save this query as T3

4. SELECT name, address, purchase FROM [t3]
GROUP BY name, address, purchase;

save this query as T3_unique

I have broken down the steps, so that you adjust each part as required.

If I am unclear, please ask and i will explain.

Nicolaas
PS the idea is to give the names of the fields in T1 and T2 the same names,
then UNION them. That is, merge them. Next you select each unique name,
address, purchase (did you want to include this???) combination.

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

I have two tables, they contain:

Table1: ID1, Name1, Address1, Purchase1
Table2: ID2, Name2, Address2, Purchase2

I need a query which creates Table3 with content from Table1 and Table2.
The records in Table3 have to contain all distinct records from Table1 and
Table2 (records where Name2 and Address2 do not already exist in Table3) and
Any help appreciated. I need these queries as a reference, I consider they
are a starting point in building other queries.

Regards,
Nicolae

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[Nicolae Fieraru]

Hi,

Thank you very much. Your explanation was very clear and it showed me that
instead of using a complex query, I can use a few simple queries to achieve
my task. I will test your code later.

Regards,
Nicolae


"WindAndWaves" <ac****@ngaru.com> wrote in message
news:ZW******************@news.xtra.co.nz...

Here are a number of SQL statements that you can paste into queries to
achieve this.

The way to paste them is to create a new query then goto SQL view and paste it in there. Make sure you get it right, otherwise you will have to close
the query without saving.

Alternatively, you can always type SELECT * from [tbln];

where tbln is a name of table in your database

here we go:

1. SELECT [table1].ID1 AS ID, [table1].name1 AS name, [table1].address1 AS
address, [table1].purchase1 AS purchase FROM [table1];

save this query as T1

2.SELECT [table2].ID2 AS ID, [table2].name2 AS name, [table2].address2 AS
address, [table2].purchase2 AS purchase FROM [table2];

save this query as T2

3. select [T1].* from [T1] UNIION SELECT [t2].* FROM [T2]

save this query as T3

4. SELECT name, address, purchase FROM [t3]
GROUP BY name, address, purchase;

save this query as T3_unique

I have broken down the steps, so that you adjust each part as required.

If I am unclear, please ask and i will explain.

Nicolaas
PS the idea is to give the names of the fields in T1 and T2 the same names, then UNION them. That is, merge them. Next you select each unique name,
address, purchase (did you want to include this???) combination.

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

I have two tables, they contain:

Table1: ID1, Name1, Address1, Purchase1
Table2: ID2, Name2, Address2, Purchase2

I need a query which creates Table3 with content from Table1 and Table2.
The records in Table3 have to contain all distinct records from Table1 and Table2 (records where Name2 and Address2 do not already exist in Table3) and

Any help appreciated. I need these queries as a reference, I consider they are a starting point in building other queries.

Regards,
Nicolae

---
Please immediately let us know (by phone or return email) if (a) this

email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[John Winterbottom]

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

I have two tables, they contain:

Table1: ID1, Name1, Address1, Purchase1
Table2: ID2, Name2, Address2, Purchase2

I need a query which creates Table3 with content from Table1 and Table2.
The records in Table3 have to contain all distinct records from Table1 and
Table2 (records where Name2 and Address2 do not already exist in Table3) and

select Name1, Address1 into Table3
from
(
select Name1, Address1
from Table1
union
select Name2, Address2
from Table2
)

[Nicolae Fieraru]

Hi John,

Thank you again for your support. Unfortunately this query doesn't work. It
sais there is an error in from clause. I am able to use the clause as a
query in its own, I don't know why it doesn't work all together

Regards,
Nicolae

"John Winterbottom" <as******@hotmail.com> wrote in message
news:2i************@uni-berlin.de...

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

I have two tables, they contain:

Table1: ID1, Name1, Address1, Purchase1
Table2: ID2, Name2, Address2, Purchase2

I need a query which creates Table3 with content from Table1 and Table2.
The records in Table3 have to contain all distinct records from Table1 and Table2 (records where Name2 and Address2 do not already exist in Table3)

and

select Name1, Address1 into Table3
from
(
select Name1, Address1
from Table1
union
select Name2, Address2
from Table2
)

[John Winterbottom]

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Hi John,

Thank you again for your support. Unfortunately this query doesn't work. It sais there is an error in from clause. I am able to use the clause as a
query in its own, I don't know why it doesn't work all together

What version of Access are you using?

[Nicolae Fieraru]

Hi John,

I am using Office 97. I checked now the query on a system with Office2002
and it worked
Is there a way to build that query for Office97?

Regards,
Nicolae

"John Winterbottom" <as******@hotmail.com> wrote in message
news:2j************@uni-berlin.de...

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Hi John,

Thank you again for your support. Unfortunately this query doesn't

work. It

sais there is an error in from clause. I am able to use the clause as a
query in its own, I don't know why it doesn't work all together

What version of Access are you using?

[John Winterbottom]

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Hi John,

I am using Office 97. I checked now the query on a system with Office2002
and it worked
Is there a way to build that query for Office97?

Regards,
Nicolae

I think you'll have to do it in two stages.

1. create the union query.
2. create a second query using the insert into clause (a "make table" query
in Access-speak), using the first query as the sorce for the select.