1

Topic: Connection of tables in MS Access

Good afternoon! It is necessary for me to connect 5 tables in MS Access. The problem consists that some columns in separate tables do not suffice, therefore the request about join at me has about the following appearance:

SELECT * INTO Svodnaja2015
FROM
(SELECT [Year], [Month], [DataFaktOM], [a material Full name],
CDbl ([Diam, mm]) as [Diameter, mm],
CDbl ([Walls, mm]) as [the Wall, mm],
[1], [], [], [a covering Type], [], [thread Type],
CDbl ([Razmer1]) as [1],
CDbl ([Razmer2]) as [2],
[], [Regularity of length]
FROM [1]
UNION ALL
SELECT [Year], [Month], it [is empty], [a material Full name],
CDbl ([Diam, mm]) as [Diameter, mm],
CDbl ([Walls, mm]) as [the Wall, mm],
[1], [], [], [a covering Type], [], [thread Type],
It [is empty] as [1],
It [is empty] as [2],
[], [Regularity of length]
FROM [2]
UNION ALL
SELECT [Year], it [is empty, empty, empty],
CDbl ([Diam, mm]) as [Diameter, mm],
CDbl ([Walls, mm]) as [the Wall, mm],
[1], [], [], it [is empty], [], [thread Type],
It [is empty] as [1],
It [is empty] as [2],
[], [Regularity of length]
FROM [2]

At this example in the first table there are all demanded columns, and in the subsequent two tables some miss.
That tables could incorporate, I create a column [is empty] in each table and I refer to it if the demanded column misses.
But for this purpose I should create in each table this most  it [is empty], and it is inconvenient. Prompt, whether there is a method to avoid this operation?

2

Re: Connection of tables in MS Access

kudim;
Try so

SELECT * INTO Svodnaja2015
FROM
(SELECT [Year], [Month], [DataFaktOM], [a material Full name],
CDbl ([Diam, mm]) as [Diameter, mm],
CDbl ([Walls, mm]) as [the Wall, mm],
[1], [], [], [a covering Type], [], [thread Type],
CDbl ([Razmer1]) as [1],
CDbl ([Razmer2]) as [2],
[], [Regularity of length]
FROM [1]
UNION ALL
SELECT [Year], [Month], it [is empty], [a material Full name],
CDbl ([Diam, mm]) as [Diameter, mm],
CDbl ([Walls, mm]) as [the Wall, mm],
[1], [], [], [a covering Type], [], [thread Type],
"" as [1],
"" as [2],
[], [Regularity of length]
FROM [2]
UNION ALL
SELECT [Year], it [is empty, empty, empty],
CDbl ([Diam, mm]) as [Diameter, mm],
CDbl ([Walls, mm]) as [the Wall, mm],
[1], [], [], "", [], [thread Type],
"" as [1],
"" as [2],
[], [Regularity of length]
FROM [2]

3

Re: Connection of tables in MS Access

1. Instead of it [is empty] specify NULL.
2. In UNION request the type of fields is defined on 1st section, therefore CDbl in 2nd and 3rd section the superfluous.
3. Application CDbl in 1st section is disputable. Explain the reason.
Besides in the presence of empty fields it leads to errors.

4

Re: Connection of tables in MS Access

Thanks! Both methods work.
Anatoly, I apply CDbl as these in these columns meet places where the data is not put down in flow of hundred and more lines. Therefore the data in them is located in the general table as the text (and it is necessary as number). And after that, when I create the pivot table from the received general file, the totals of it are not considered.
About CDbl in the second and subsequent section understood. Thanks!