[Solved] Multiple table selection, spreadsheet as data source

[Mikey J]

Hi,
I am new to Open Office and it's peculiarities but not new to SQL. I have created a database using a spreadsheet with 2 sheets. These have translated into 2 tables. I now want to compare the 2 tables so that I can extract information that is in one of the tables that is not matching in the other table. Basically I want to join the 2 tables on a common field(s) and where the values in table 2 are null extract table 1 data. If I union and duplicate the query but look for data where table 1 is null I can find a different set.
Something like

Select a.* from sheet1 as a join sheet2 as b on a.initial + a.surname = b.initial + b.surname where b.surname is null
union
Select b.* from sheet1 as a join sheet2 as b on a.initial + a.surname = b.initial + b.surname where a.surname is null

I'd like to add an additional column, say, "From" that would contain the word "Left" or "New" so that I could tell which table the result came from.

The problem is I cannot get the 2 tables in the wizard at the same time. In other words OpenOffice won't allow multiple table selection. How can I enable this?

Regards
Mike

[Villeroy]

So the status bar of your database document looks like this, right?

Spreadsheet_Connection_Statusbar.png (5.26 KiB) Viewed 5072 times

This database connection to a spreadsheet is good enough for simple tasks like mail merge. It will not convert your spreadsheet into anything like a database. The connection is read-only, availlable functions are limited and there are no relations. You can not SELECT from more than one table.
However, you can create a combination of a form with a subform where the subform shows the records that are related to the main form's current record based on some common field(s).

[UnklDonald418]

Since you are conversant in SQL, instead of the wizard use Create Query in SQL View.
Unfortunately, it doesn't provide any editing help, you must manually type everything.
There is another icon along the top of the screen, Run SQL command directly. You may need to select that to bypass the Base parser on complex queries.
In most cases, table and field names should be enclosed in double quotes.

[Mikey J]

Thanks for that. I'll try the double quotes. It is all very basic unfortunately I don't have access to Microsoft SQL Server anymore.

Regards
Mike