Join two tables

[Lanox]

Hello,

I would like to join two tables: TableA and TableB. The first column of both tables is the same: Date. The other columns are different and also the number of columns is not known.
I would like that the resulting table starts with column Date, then shows the remaining columns of TableA and then the remaining columns of TableB.

At the moment I am using the following query:
SELECT [TableA].*, [TableB].*
FROM [TableA] INNER JOIN [TableB] ON [TableA].Date = [TableB].Date
ORDER BY [TableB].Date;
The resulting table has joined both TableA and TableB completely, including two times the Date column. I would like the second Date column to be deleted.

Thanks a lot,
Lanox

 

[jdraw]

A couple of things:

Date is a reserved word in Access -- and you should avoid using it.
Why is the number of columns not known?
You aren't rally creating a "resulting table", you have selected fields/columns in a query.

In order to not show the second date column, you would explicitly name each of the other fields in that Table.
Eg. Select A.*,B.name,B.phone,B.other from .....

However, since you say the other fields are not known, I see no way to do this with SQL.

If this is really a serious question, you could use DAO and work with a Fields collections, and only Select fields(1), field(2) etc up to FieldCount. But I'm not convinced this is a serious issue.

Post back details as appropriate.

 

[Lanox]

Hello jdraw,

I am completely new to access and need to work with an advanced access program (at least it is advanced from my point of view) and change it to some of our new needs.

The tables that I was talking about are not really tables but are the results of a query (which looks like a table for a complete newby like me). The queries construct their columns depending on some data in tables and therefore I don't know the exact number of columns (this depends on the data).

What is "DAO"?

The question is serious in the sense that this issue is blocking the complete automation of a process. At this moment I have to copy the results to excel, remove the second date column manually (since I don't know the exact spot of this column) and use this result in the remainder of the process.

Lanox