Simulating A Full Outer Join in Access from 3 Tables

[hbulsara]

Hi,

Hope someone can help me with this. I'm trying to simulate the results
from full outer join in Access across 3 tables using a union query
that someone recommended (or another method if it can be done):

--TABLES--
BOE
INDICES
TECHNICALS


The common field among them is a date field in the fisrt column but
the tables differ in terms of both the number of rows (dates) they
have and also the number of columns/fields. Hence I want to bring back
all rows from all three tables even those that are unmatched by the
date field, but I want to join by date field across the tables so
where there is a match, the data is aligned by the date field. I've
been told I need to use a union query to do this but I am having
problems.

A little background - basicly I'm regulary importing 3 CSV files into
Access and the number of feilds in each file changes with each import
(two of the tables as about 35 feilds). I am now trying to find a way
to consolidate the three files into one (which is the whole reason for
importing into access), and using the date feild as the basis to align
the data from the three files. However in my final file I need to see
all columns from the three tables along with all dates, not just where
there is a match in date accross all three.

I ran the query below, but it generates a message saying that the
"number of columns in the tables does not match. But I thought the
whole point of a union query was to allow this! But I've never really
used SQL for Access (only oracle at basic level) so not sure if the
syntax is right.

Hope someone can shed some light or suggest an alternative approach
for what I need to do. I was thinking would creating a new table using
data from the three tables using SQL allow me to get the output I
need?

Thanks

Lucas

SELECT *
FROM
BOE LEFT JOIN indices ON bedmate=indices.date
UNION select *
from
technicals LEFT JOIN boe ON technicals.date=boe.date;

 

[George Nicholson]

First off, I'm not sure a UNION query is what you want, but let me explain
what a UNION query will do and then you can decide.

You need to specify the same number of columns for each table involved in a
UNION query. Its mandatory (how else would Access know how to "line up" your
data?). However, there doesn't have to be data in those fields, you can use
placeholders:

SELECT Field1, Field2, Field3, Field4 FROM Table1

UNION SELECT Null as Field1, 0 as Field2, SomeField, AnotherField FROM
Table2

UNION SELECT Data1, Data2, "" as Field3, Null as Field4 FROM Table3

The above combines 4 fields from Table1, 2 fields from Table 2 (in positions
3 & 4) and 2 fields from table 3 (in positions 1 & 2). Note that Access
doesn't even care what the fields are named as long as there are always -n-
of them. (If you GROUP or ORDER, use the field name from the 1st SELECT.)

Consider adding an identifier field to each SELECT that specifies the
original source:
SELECT "Table1" as OriginalTable, ...
That can make it a lot easier to backtrack problems/issues, but if each
table's structure is noticeable different, that may not be entirely
necessary in your case.

A UNION query will NOT combine multiple records into one record. Only an
UPDATE query or Totals + Make-table query will do that, afaik.

By default UNION will not return duplicate records. UNION ALL will return
all records, so that for each record meeting the SELECT criteria in the
source tables, you will have a record in your result, duplicates included.

HTH,

 

[Klatuu]

If the objective is to append the data in all 3 external tables to one
destination table and the number of columns is not the same, a Union query
will not do it. Union queries require you select the same number of columns
for each table or query included.

I would suggest a different approach. I would link each file individually
and use an append query for each to append the data to the Access tables,
then drop the links.

 

[hbulsara]

If the objective is to append the data in all 3 external tables to one
destination table and the number of columns is not the same, a Union query
will not do it. Union queries require you select the same number of columns
for each table or query included.

I would suggest a different approach. I would link each file individually
and use an append query for each to append the data to the Access tables,
then drop the links.
--
Dave Hargis, Microsoft Access MVP









- Show quoted text -

Thanks guys. Sounds like a union query will not give me what I need as
the number of columns vary in between the tables. FYI -apparantly I
can easily get the result I need from other database systems such as
oracle, sql server which supports full outer joins, it's just access
doesn't offer this functionality but I was told I could simulate a
full outer join using unions, but looks like this only applies if you
have the same number of columns across the tables.

Ok, so I need some alternative way of doing this.

"I would link each file individually and use an append query for each
to append the data to the Access tables then drop the links".

Dave, Not sure exactly what this means, would be grateful for some
more details. Also, I am looking for the least manual process
possible. Can you do what you suggest using SQL or VBA or would it be
a manual process?

Thanks,

Lucas
---------------------------------------------------------

 

[Klatuu]

It would be a combination of SQL controlled by VBA.
Now, what I did not say earlier, and this may help your situation. Although
the Union query requires the same number of columns, that means columns in
the query, not columns in the table. To match the number of columns, you can
create dummies. For example:

SELECT BEAN_COUNT, USELESS_FIELD, CONFUSED_BUCKET FROM SomeTable;
UNION SELECT BEAN_COUNT, FOOH_BAR, NULL AS DUMMY FROM Another Table;

The fields do have to have compatible data types for this to work.

The other approach I was suggesting may be necessary depending on exactly
what you are trying to do. Can you describe in some detail what it is?

 

[hbulsara]

It would be a combination of SQL controlled by VBA.
Now, what I did not say earlier, and this may help your situation. Although
the Union query requires the same number of columns, that means columns in
the query, not columns in the table. To match the number of columns, you can
create dummies. For example:

SELECT BEAN_COUNT, USELESS_FIELD, CONFUSED_BUCKET FROM SomeTable;
UNION SELECT BEAN_COUNT, FOOH_BAR, NULL AS DUMMY FROM Another Table;

The fields do have to have compatible data types for this to work.

The other approach I was suggesting may be necessary depending on exactly
what you are trying to do. Can you describe in some detail what it is?
--
Dave Hargis, Microsoft Access MVP









- Show quoted text -

Hi Dave, I don't think the union approach is going to work for me as I
need to go through the consolidation process several times a week and
the number of feilds in each table will vary and so will some of the
feild names, so it's not really going to be practical specifying
additional feilds and feild names each time in the process, especially
given that comibed accross three all tables there could be up to 100
feilds!

Now, In terms of what I seeking to do. Kinda spelled it out in my
orginal post, but let me provide a bit more background as there might
be a completely different approach in getting what I need.

a) I have three excel files with varying number of data columns. The
only common feild among the files is the first column which is a date
feild. The number of rows also vary. Effectively the data is a
timeseries, so in file the start date could be 01/01/1990 whilst in
another it is31/01/2002. Furthemore some of dates could be missing in
one file. What I'm seeking to do is consolidate the files into one so
that in my final consolidating file I have one date feild (covering
every single date from the three files and all populated columns/
feilds from the three sheets alinged by the date - what I mean by this
is grouped by the date.

b) I looked into doing the consolidation in excel but it was not
practical so I save the files as CSV and import them into access into
separate tables:

BOE
INDICES
TECHNICALS

This works fine. I thought then I could just join the tables using the
date feild and get the consolidated data (by date) in one file. But
the problem is the join only brings back rows where there is a match
in dates accross all the columns, and consequently there is lots of
data missing - hence I thought a full outer join would do what I need
but did not know at the time that Access does not support that
functionalty.

So that's essentially it - my end objective is simply to consolidate
data from my three my excel files into one file with each observation
for a given field grouped by date. Where there is no observation for a
particular feild for a given date, I just want that cell to be blank
or populated with some character.

Hope that provides more clarity.

 

[Klatuu]

Thanks for the detail. Yes, it does throw a whole new slant on it. The part
I am still unclear on is the columns in each csv file. You say they will not
always be the same. Am I to understand that today, file1 may have 26
columns, but tomorrow it may have 3? Also, what about column names? Will
the column names always be the same regardless of the number of columns?
Will there be any overlap in fields between the files, for example would file
1 have a column name Foobar and file 3 also have a column named Foobar, and
are they the same thing?

Are you sure all the rows in the three files should go into one table?

In any case, you could begin building the final table with a union query to
get all the dates in the 3 files into one table. That would be just
populating a table with only the date.

But, before we go any further, we need to know the detail on the number and
names of columns. Even stored queries (unless you use select *) will be
expecting a specific number of columns with specific names.

 

[hbulsara]

Thanks for the detail. Yes, it does throw a whole new slant on it. The part
I am still unclear on is the columns in each csv file. You say they will not
always be the same. Am I to understand that today, file1 may have 26
columns, but tomorrow it may have 3? Also, what about column names? Will
the column names always be the same regardless of the number of columns?
Will there be any overlap in fields between the files, for example would file
1 have a column name Foobar and file 3 also have a column named Foobar, and
are they the same thing?

Are you sure all the rows in the three files should go into one table?

In any case, you could begin building the final table with a union query to
get all the dates in the 3 files into one table. That would be just
populating a table with only the date.

But, before we go any further, we need to know the detail on the number and
names of columns. Even stored queries (unless you use select *) will be
expecting a specific number of columns with specific names.
--
Dave Hargis, Microsoft Access MVP











- Show quoted text -

Hi,

Yes the columns will vary during each import so file1 may have 26
columns, but tomorrow it may have 20. The column names are likely to
change as well and the number of names will be directly related to the
number of columns. So five additional columns in a chart will mean 5
new column names. There is no overlap between the files in terms of
column names or data content, accept for the date column which is
called date in all three files. All other columns accross the three
files have distinct data and feild names.

Yes I want to return all rows from the three files into one table?

"In any case, you could begin building the final table with a union
query to
get all the dates in the 3 files into one table. That would be just
populating a table with only the date".

Yes I already know how to do this:

select date from indices
union
select date from boe
Union
select date from technicals;

At this point I am indeed looking at using the select * command rather
than specifying column and names.

Thanks,

Lucas

 

[Klatuu]

First order of business - Change the names of the fields named Date. Date is
a reserved word and will cause problems in Access. Reserved words and names
with disallowed characters can be used if they are in brackets [Date], but
this will not guarantee there will be no side affects. I once had a case
where [Date] was used in brackets, but this statement:

rst![Date] = Date()

Gave incorrect results. the only way I could fix it (I was not allowed to
change a field name) was

rst![Date] = VBA.Date()

Now, you can use the union query you have to load the dates. But, we are
still faces with the problem of how many fields we need and what to name them.

This can be done, but it is a lot of work.
First, you will need to determine how many fields you need.
Then you will have to create a table or modify a table to accept the fields.
You also need to evaluate the data in the files so you know what data type
to make each field.
You can use either the properties and methods of the Tabledef object to
define the fields and data types or you can use Jet SQL DDL.

But, the overriding question is "What are you about?" This indicates a
database that is not really a relational database, but a collection of tables
trying to be a spreadsheet. Is there any reason to use Access for this?
Maybe keeping it in Excel would be a better solution. What do you do with
this data once it is imported into this table?

 

[hbulsara]

First order of business - Change the names of the fields named Date. Date is
a reserved word and will cause problems inAccess. Reserved words and names
with disallowed characters can be used if they are in brackets [Date], but
this will not guarantee there will be no side affects. I once had a case
where [Date] was used in brackets, but this statement:

rst![Date] = Date()

Gave incorrect results. the only way I could fix it (I was not allowed to
change a field name) was

rst![Date] = VBA.Date()

Now, you can use the union query you have to load the dates. But, we are
still faces with the problem of how many fields we need and what to name them.

This can be done, but it is a lot of work.
First, you will need to determine how many fields you need.
Then you will have to create a table or modify a table to accept the fields.
You also need to evaluate the data in the files so you know what data type
to make each field.
You can use either the properties and methods of the Tabledef object to
define the fields and data types or you can use Jet SQL DDL.

But, the overriding question is "What are you about?" This indicates a
database that is not really a relational database, but a collection of tables
trying to be a spreadsheet. Is there any reason to useAccessfor this?
Maybe keeping it in Excel would be a better solution. What do you do with
this data once it is imported into this table?
--
Dave Hargis, MicrosoftAccessMVP




...

read more »- Hide quoted text -

- Show quoted text -

Hi Dave,

You are right in your observations, I don't seek to run a database in
Access but simply use access as a data consolidation tool. I have
simply chosen to do this in access because I believe it is much
quicker than an excel to do this kinda stuff. In terms of what I'd
seek to do with the final dataset - is to import it into a stats/data
mining package to undertake statistical modelling. I may be doing
cross section analysis using some or all subsets of indicators or
doing univariate analysis (anysis of a single series), that's why I
all want all the data in one file so that I don't have to create
separate input files but can pick and choose the indicator/indicators
I want to analyse. Obviously for some indicators I have a longer
history than others, if I used only an inner join to build my dataset
then I would not be able to use the full history of observations I
have for some of my variables thus undermining my results.

Hope that makes sense.

 

[Klatuu]

Reviewing your original post, I still think Access is not the tool for this
task unless you have advanced knowledge of VBA and or Jset SQL DDL. There
are too many variables involved.
--
Dave Hargis, Microsoft Access MVP

First order of business - Change the names of the fields named Date. Date is
a reserved word and will cause problems inAccess. Reserved words and names
with disallowed characters can be used if they are in brackets [Date], but
this will not guarantee there will be no side affects. I once had a case
where [Date] was used in brackets, but this statement:

rst![Date] = Date()

Gave incorrect results. the only way I could fix it (I was not allowed to
change a field name) was

rst![Date] = VBA.Date()

Now, you can use the union query you have to load the dates. But, we are
still faces with the problem of how many fields we need and what to name them.

This can be done, but it is a lot of work.
First, you will need to determine how many fields you need.
Then you will have to create a table or modify a table to accept the fields.
You also need to evaluate the data in the files so you know what data type
to make each field.
You can use either the properties and methods of the Tabledef object to
define the fields and data types or you can use Jet SQL DDL.

But, the overriding question is "What are you about?" This indicates a
database that is not really a relational database, but a collection of tables
trying to be a spreadsheet. Is there any reason to useAccessfor this?
Maybe keeping it in Excel would be a better solution. What do you do with
this data once it is imported into this table?
--
Dave Hargis, MicrosoftAccessMVP

Thanks for the detail. Yes, it does throw a whole new slant on it. The part
I am still unclear on is the columns in each csv file. You say they will not
always be the same. Am I to understand that today, file1 may have 26
columns, but tomorrow it may have 3? Also, what about column names? Will
the column names always be the same regardless of the number of columns?
Will there be any overlap in fields between the files, for example would file
1 have a column name Foobar and file 3 also have a column named Foobar, and
are they the same thing?

Are you sure all the rows in the three files should go into one table?

In any case, you could begin building the final table with a union query to
get all the dates in the 3 files into one table. That would be just
populating a table with only the date.

But, before we go any further, we need to know the detail on the number and
names of columns. Even stored queries (unless you use select *) will be
expecting a specific number of columns with specific names.

:
It would be a combination of SQL controlled by VBA.
Now, what I did not say earlier, and this may help your situation.. Although
the Union query requires the same number of columns, that means columns in
the query, not columns in the table. To match the number of columns, you can
create dummies. For example:

SELECT BEAN_COUNT, USELESS_FIELD, CONFUSED_BUCKET FROM SomeTable;
UNION SELECT BEAN_COUNT, FOOH_BAR, NULL AS DUMMY FROM Another Table;

The fields do have to have compatible data types for this to work.

The other approach I was suggesting may be necessary depending on exactly
what you are trying to do. Can you describe in some detail what it is?

:
If the objective is to append the data in all 3 external tables to one
destination table and the number of columns is not the same, a Union query
will not do it. Union queries require you select the same number of columns
for each table or query included.

I would suggest a different approach. I would link each file individually
and use an append query for each to append the data to theAccesstables,
then drop the links.

:
Hi,

Hope someone can help me with this. I'm trying to simulate the results
fromfullouterjoininAccessacross 3 tables using a union query
that someone recommended (or another method if it can be done):

The common field among them is a date field in the fisrt column but
the tables differ in terms of both the number of rows (dates) they
have and also the number of columns/fields. Hence I want to bring back
all rows from all three tables even those that are unmatched by the
date field, but I want tojoinby date field across the tables so
where there is a match, the data is aligned by the date field. I've
been told I need to use a union query to do this but I am having
problems.

A little background - basicly I'm regulary importing 3 CSV files into
Accessand the number of feilds in each file changes with each import
(two of the tables as about 35 feilds). I am now trying to find a way
to consolidate the three files into one (which is the whole reason for
importing intoaccess), and using the date feild as the basis to align
the data from the three files. However in my final file I need to see
all columns from the three tables along with all dates, not just where
there is a match in date accross all three.

I ran the query below, but it generates a message saying that the
"number of columns in the tables does not match. But I thought the
whole point of a union query was to allow this! But I've never really
used SQL forAccess(only oracle at basic level) so not sure if the
syntax is right.

Hope someone can shed some light or suggest an alternative approach
for what I need to do. I was thinking would creating a new table using
data from the three tables using SQL allow me to get the output I
need?


SELECT *
FROM
BOE LEFTJOINindices ON bedmate=indices.date
UNION select *
from
technicals LEFTJOINboe ON technicals.date=boe.date;- Hide quoted text -

- Show quoted text -

Thanks guys. Sounds like a union query will not give me what I need as
the number of columns vary in between the tables. FYI -apparantly I
can easily get the result I need from other database systems such as
oracle, sql server which supportsfullouterjoins, it's justaccess
doesn't offer this functionality but I was told I could simulate a
fullouterjoinusing unions, but looks like this only applies if you
have the same number of columns across the tables.

Ok, so I need some alternative way of doing this.

"I would link each file individually and use an append query for each
to append the data to theAccesstables then drop the links".

Dave, Not sure exactly what this means, would be grateful for some
more details. Also, I am looking for the least manual process
possible. Can you do what you suggest using SQL or VBA or would it be
a manual process?

Lucas
---------------------------------------------------------- Hide quoted text -

- Show quoted text -

Hi Dave, I don't think the union approach is going to work for me as I
need to go through the consolidation process several times a week and
the number of feilds in each table will vary and so will some of the
feild names, so it's not really going to be practical specifying
additional feilds and feild names each time in the process, especially
given that comibed accross three all tables there could be up to 100
feilds!

Now, In terms of what I seeking to do. Kinda spelled it out in my
orginal post, but let me provide a bit more background as there might
be a completely different approach in getting what I need.

a) I have three excel files with varying number of data columns. The
only common feild among the files is the first column which is a date
feild. The number of rows also vary. Effectively the data is a
timeseries, so in file the start date could be 01/01/1990 whilst in
another it is31/01/2002. Furthemore some of dates could be missing in
one file. What I'm seeking to do is consolidate the files into one so
that in my final consolidating file I have one date feild (covering
every single date from the three files and all populated columns/
feilds from the three sheets alinged by the date - what I mean by this
is grouped by the date.

b) I looked into doing the consolidation in excel but it was not
practical so I save the files as CSV and import them intoaccessinto
separate tables:

This works fine. I thought then I could justjointhe tables using the
date feild and get the consolidated data (by date) in one file. But
the problem is thejoinonly brings back rows where there is a match
in dates accross all the columns, and consequently there

...

read more ;- Hide quoted text -

- Show quoted text -

Hi Dave,

You are right in your observations, I don't seek to run a database in
Access but simply use access as a data consolidation tool. I have
simply chosen to do this in access because I believe it is much
quicker than an excel to do this kinda stuff. In terms of what I'd
seek to do with the final dataset - is to import it into a stats/data
mining package to undertake statistical modelling. I may be doing
cross section analysis using some or all subsets of indicators or
doing univariate analysis (anysis of a single series), that's why I
all want all the data in one file so that I don't have to create
separate input files but can pick and choose the indicator/indicators
I want to analyse. Obviously for some indicators I have a longer
history than others, if I used only an inner join to build my dataset
then I would not be able to use the full history of observations I
have for some of my variables thus undermining my results.

Hope that makes sense.

 

[hbulsara]

First order of business - Change the names of the fields named Date. Date is
a reserved word and will cause problems in Access. Reserved words and names
with disallowed characters can be used if they are in brackets [Date], but
this will not guarantee there will be no side affects. I once had a case
where [Date] was used in brackets, but this statement:

rst![Date] = Date()

Gave incorrect results. the only way I could fix it (I was not allowed to
change a field name) was

rst![Date] = VBA.Date()

Now, you can use the union query you have to load the dates. But, we are
still faces with the problem of how many fields we need and what to name them.

This can be done, but it is a lot of work.
First, you will need to determine how many fields you need.
Then you will have to create a table or modify a table to accept the fields.
You also need to evaluate the data in the files so you know what data type
to make each field.
You can use either the properties and methods of the Tabledef object to
define the fields and data types or you can use Jet SQL DDL.

But, the overriding question is "What are you about?" This indicates a
database that is not really a relational database, but a collection of tables
trying to be a spreadsheet. Is there any reason to use Access for this?
Maybe keeping it in Excel would be a better solution. What do you do with
this data once it is imported into this table?
--
Dave Hargis, Microsoft Access MVP




...

read more »- Hide quoted text -

- Show quoted text -

Hi,

Yes I agree, it sounds very complicated just to do a simple thing. As
I mentioned before, I didn't realise Access had limitaions when it
came to doing a full outer join.

To get around the access limitations, do you know if I could use a
front end SQL tool such as Aquadata to connect to and query the Access
database/tables directly without using access front end. Maybe that
would enable me to do a full outer join.

Lucas

 

[Klatuu]

Sorry, I am not familiar with Aquadata, so I can't answer your question.
--
Dave Hargis, Microsoft Access MVP

First order of business - Change the names of the fields named Date. Date is
a reserved word and will cause problems in Access. Reserved words and names
with disallowed characters can be used if they are in brackets [Date], but
this will not guarantee there will be no side affects. I once had a case
where [Date] was used in brackets, but this statement:

rst![Date] = Date()

Gave incorrect results. the only way I could fix it (I was not allowed to
change a field name) was

rst![Date] = VBA.Date()

Now, you can use the union query you have to load the dates. But, we are
still faces with the problem of how many fields we need and what to name them.

This can be done, but it is a lot of work.
First, you will need to determine how many fields you need.
Then you will have to create a table or modify a table to accept the fields.
You also need to evaluate the data in the files so you know what data type
to make each field.
You can use either the properties and methods of the Tabledef object to
define the fields and data types or you can use Jet SQL DDL.

But, the overriding question is "What are you about?" This indicates a
database that is not really a relational database, but a collection of tables
trying to be a spreadsheet. Is there any reason to use Access for this?
Maybe keeping it in Excel would be a better solution. What do you do with
this data once it is imported into this table?
--
Dave Hargis, Microsoft Access MVP

Thanks for the detail. Yes, it does throw a whole new slant on it. The part
I am still unclear on is the columns in each csv file. You say they will not
always be the same. Am I to understand that today, file1 may have 26
columns, but tomorrow it may have 3? Also, what about column names? Will
the column names always be the same regardless of the number of columns?
Will there be any overlap in fields between the files, for example would file
1 have a column name Foobar and file 3 also have a column named Foobar, and
are they the same thing?

Are you sure all the rows in the three files should go into one table?

In any case, you could begin building the final table with a union query to
get all the dates in the 3 files into one table. That would be just
populating a table with only the date.

But, before we go any further, we need to know the detail on the number and
names of columns. Even stored queries (unless you use select *) will be
expecting a specific number of columns with specific names.

:
It would be a combination of SQL controlled by VBA.
Now, what I did not say earlier, and this may help your situation.. Although
the Union query requires the same number of columns, that means columns in
the query, not columns in the table. To match the number of columns, you can
create dummies. For example:

SELECT BEAN_COUNT, USELESS_FIELD, CONFUSED_BUCKET FROM SomeTable;
UNION SELECT BEAN_COUNT, FOOH_BAR, NULL AS DUMMY FROM Another Table;

The fields do have to have compatible data types for this to work.

The other approach I was suggesting may be necessary depending on exactly
what you are trying to do. Can you describe in some detail what it is?

:
If the objective is to append the data in all 3 external tables to one
destination table and the number of columns is not the same, a Union query
will not do it. Union queries require you select the same number of columns
for each table or query included.

I would suggest a different approach. I would link each file individually
and use an append query for each to append the data to the Access tables,
then drop the links.

:
Hi,

Hope someone can help me with this. I'm trying to simulate the results
fromfullouterjoinin Access across 3 tables using a union query
that someone recommended (or another method if it can be done):

The common field among them is a date field in the fisrt column but
the tables differ in terms of both the number of rows (dates) they
have and also the number of columns/fields. Hence I want to bring back
all rows from all three tables even those that are unmatched by the
date field, but I want tojoinby date field across the tables so
where there is a match, the data is aligned by the date field. I've
been told I need to use a union query to do this but I am having
problems.

A little background - basicly I'm regulary importing 3 CSV files into
Access and the number of feilds in each file changes with each import
(two of the tables as about 35 feilds). I am now trying to find a way
to consolidate the three files into one (which is the whole reason for
importing into access), and using the date feild as the basis to align
the data from the three files. However in my final file I need to see
all columns from the three tables along with all dates, not just where
there is a match in date accross all three.

I ran the query below, but it generates a message saying that the
"number of columns in the tables does not match. But I thought the
whole point of a union query was to allow this! But I've never really
used SQL for Access (only oracle at basic level) so not sure if the
syntax is right.

Hope someone can shed some light or suggest an alternative approach
for what I need to do. I was thinking would creating a new table using
data from the three tables using SQL allow me to get the output I
need?


SELECT *
FROM
BOE LEFTJOINindices ON bedmate=indices.date
UNION select *
from
technicals LEFTJOINboe ON technicals.date=boe.date;- Hide quoted text -

- Show quoted text -

Thanks guys. Sounds like a union query will not give me what I need as
the number of columns vary in between the tables. FYI -apparantly I
can easily get the result I need from other database systems such as
oracle, sql server which supportsfullouterjoins, it's just access
doesn't offer this functionality but I was told I could simulate a
fullouterjoinusing unions, but looks like this only applies if you
have the same number of columns across the tables.

Ok, so I need some alternative way of doing this.

"I would link each file individually and use an append query for each
to append the data to the Access tables then drop the links".

Dave, Not sure exactly what this means, would be grateful for some
more details. Also, I am looking for the least manual process
possible. Can you do what you suggest using SQL or VBA or would it be
a manual process?

Lucas
---------------------------------------------------------- Hide quoted text -

- Show quoted text -

Hi Dave, I don't think the union approach is going to work for me as I
need to go through the consolidation process several times a week and
the number of feilds in each table will vary and so will some of the
feild names, so it's not really going to be practical specifying
additional feilds and feild names each time in the process, especially
given that comibed accross three all tables there could be up to 100
feilds!

Now, In terms of what I seeking to do. Kinda spelled it out in my
orginal post, but let me provide a bit more background as there might
be a completely different approach in getting what I need.

a) I have three excel files with varying number of data columns. The
only common feild among the files is the first column which is a date
feild. The number of rows also vary. Effectively the data is a
timeseries, so in file the start date could be 01/01/1990 whilst in
another it is31/01/2002. Furthemore some of dates could be missing in
one file. What I'm seeking to do is consolidate the files into one so
that in my final consolidating file I have one date feild (covering
every single date from the three files and all populated columns/
feilds from the three sheets alinged by the date - what I mean by this
is grouped by the date.

b) I looked into doing the consolidation in excel but it was not
practical so I save the files as CSV and import them into access into
separate tables:

This works fine. I thought then I could justjointhe tables using the
date feild and get the consolidated data (by date) in one file. But
the problem is thejoinonly brings back rows where there is a match
in dates accross all the columns, and consequently there

...

read more ;- Hide quoted text -

- Show quoted text -

Hi,

Yes I agree, it sounds very complicated just to do a simple thing. As
I mentioned before, I didn't realise Access had limitaions when it
came to doing a full outer join.

To get around the access limitations, do you know if I could use a
front end SQL tool such as Aquadata to connect to and query the Access
database/tables directly without using access front end. Maybe that
would enable me to do a full outer join.

Lucas