Combining Tables

[Peter]

Try creating a union query in the query module, and post
the code something like this. Modify where necessary.

Create a new query, but dont put any fields in the query.
Go to the SQl option and place this code in the blank
space. This query merges the email address, the FirstName
and Last Name into a query. Then make a new table query
from the results.

SELECT ,[ProducerFirstName] as [FirstName],
[ProducerSurname] as [LastName]
FROM ProducerDetail

UNION SELECT [Email], [FirstName], [LastName]
FROM [Email];

 

[Rick]

Peter,
Thanks for the hint. I tested it by trying to combine two
of the tables and I found that I needed to add some blank
columns with column names the same as the other tables.
That is in Table 1 I added blank columns Dept2 L1, Dept2
L2 and Dept2 L3. In Table 2 I added blank columns Dept1
L1, Dept1 L2 and Dept1 L3.
I tried the following code -
SELECT [Region] ,

, [Dept1 L1], [Dept1 L2], [Dept1
L3], [Dept2 L1], [Dept2 L2], [Dept2 L3]
FROM [Dept1]

UNION SELECT [Region], [Code], [Dept1 L1], [Dept1 L2],
[Dept1 L3], [Dept2 L1], [Dept2 L2], [Dept2 L3]
FROM [Dept2];

This almost works except that it gives blanks instead of
just filling blanks with the entries from the other table.
That is I get an entry (not showing all columns)
where "Region" and "Code" are the same.
Where D1L2 = Dept1 L2 etc.
D1L1 D1L2 D1L3 D2L1 D2L2 D2L3
AAA  BBB  CCC
FFF  GGG  JJJ
TTT  HHH  JJJ
KKK  UUU  OOO
MMM  LLL  III
Instead I would like -
D1L1 D1L2 D1L3 D2L1 D2L2 D2L3
AAA  BBB  CCC  TTT  HHH  JJJ
FFF  GGG  JJJ  KKK  UUU  OOO
MMM  LLL  III

Is there a way to do this?

Thanks for the help
Rick


[QUOTE]
-----Original Message-----
Try creating a union query in the query module, and post
the code something like this.  Modify where necessary.

Create a new query, but dont put any fields in the query.
Go to the SQl option and place this code in the blank
space.  This query merges the email address, the FirstName
and Last Name into a query.  Then make a new table query
from the results.

SELECT [EMail] ,[ProducerFirstName] as [FirstName],
[ProducerSurname] as [LastName]
FROM ProducerDetail

UNION SELECT [Email], [FirstName], [LastName]
FROM [Email];[QUOTE]
-----Original Message-----
I am new to Access and wanted to do the following simple
(I think) data base opertion. I have three tables all
structured the same way
with headings as shown below. The descriptions are just
text and could be anything -
Region	Code	Dept1-Desc1	Dept1-Desc2 Dept1-
Desc3
X	1	AAA		BBB		CCC
X	1	AAA		DDD		CCC
Y	2	AAA		BBB		EEE
Y	3	A3a		B3B		CCC
X	3	AAA		BBB		CCC
etc

Table 2 has the following headings -
Region	Code	Dept2-Desc1	Dept2-Desc2 Dept2-
Desc3
X	1	ZZZ		SSS		TTT
X	1	QQQ		BBB		TTT
X	1	GGG		JJJ		KKK
X	2	FFF		VVV		BBB
Y	3	DDD		FFF		HHH
X	3	GGG		BBB		CCC
etc

Table 3 has the following headings -
Region	Code	Dept3-Desc1	Dept3-Desc2 Dept3-
Desc3
X	1	ZZZ		SSS		HHH
Y	1	QQQ		YYY		TTT
X	3	DDD		FFF		MMM
etc

I would like one table as follows -
Region	Code	Dept1-Desc1	Dept1-Desc2 Dept1-
Desc3	Dept2-Desc1	Dept2-Desc2	Dept3-Desc1
	Dept3-Desc2	Dept3-Desc3	Dept2-Desc3
X	1	AAA		BBB		CCC
	ZZZ		SSS		TTT
	ZZZ		SSS		HHH
X	1	AAA		DDD		CCC
	QQQ		BBB		TTT
	"blank"		"blank"		"blank"
X	1	"blank"		"blank"		"blank"
	GGG		JJJ		KKK
	"blank"		"blank"		"blank"
Y	1	"blank"		"blank"		"blank"
	"blank"		"blank"		"blank"
	QQQ		YYY		TTT
X	2	"blank"		"blank"		"blank"
	FFF		VVV		BBB
	"blank"		"blank"		"blank"
Y	2	AAA		BBB		EEE
	"blank"		"blank"		"blank"
	"blank"		"blank"		"blank"
x	3	AAA		BBB		CCC
	GGG		BBB		CCC
	DDD		FFF		MMM
Y	3	A3a		B3B		CCC
	DDD		FFF		HHH
	"blank"		"blank"		"blank"
etc

That is for each combination of region and code just put
next to each other the descriptions and if a particular
dept does not have any description for a code just leave
it blank.
I tried matching on Region and code and only doing one
pair of tables (eg Table1 together with Table2, but I
seem to get mutiple rows). Any help would be appreciated.

Thanks
Rick
.
[/QUOTE]
.
[/QUOTE]