Single list with different fields

[Douglasrac]

Hi,

I want to make a simple list, can be a query or a report, to just show some records. The problem is that I have fields in different tables, and they should be treated as equals.

Like this:
Fields:

Table Clients
Name
Date of Bithday
Phone Number



Table Drivers
Name
Date of Birthday

The report or query should show a simple list
Name | Date of Birthday | Phone

In one query I didn't manage to do it, since it will try to make Name of Drivers linked to Name of Client. Which is linked, but as I said, should be single list.

So I did 2 queries. One for Clients and one for Drivers. Then I tried to list all records on a report, but the report wizard didn't accept data from more than one query.

Anyway to solve this? Maybe transfer the queries list to a table? But in an automatic way, since the query is updated every month.

[kennejd]

Try a union query.

[pbaldy]

You can achieve one list with a UNION query:

SELECT Name, Birthday, Phone
FROM Clients
UNION ALL
SELECT Name, Birthday, Null AS Phone
FROM Drivers

[Douglasrac]

You guys are perfect!
So simple!

Now I wonder if it's possible to change the name of the column in the query. Now it shows Expr1000, because the Name in Client's table is Drivers in Driver's table. So he Union All and put no name.

[pbaldy]

The query will use names from the first SELECT, so try

SELECT Name AS TheirName, Birthday AS TheirBirthday, Phone AS TheirPhone

or whatever you want as the alias names.

[Douglasrac]

Thanks. Works perfectly.

Now I just don't understand why the phone number is not showing with the mask, just the number. Even in the Table it shows with the mask, and now in the query its just the number. I have queries with the mask, why this one is different?

P.S.: And another problem: I cant edit a UNION ALL query. There is a check box that I would like to check from the query. I'm wondering how can I solve this. I read that I would need to populate a temporary table and then edit there. But how? Copy paste, edit, then copy paste to the original table? Seems very risky.

P.S.2: Seems very hard the solutions out there. Maybe if you know the purpose of the table you can find a better solution. The birthday table is done. Now I want to make a UNION query for other purpose. I have a check box for each record that says "Imported?". If the record have 0 in this field he will go to a query. This query will put together all records that I need to import to Google Contacts. So, when I finish to import the records, I mark them all. If I mark in the query is very easy. If I need to search in the form, one by one, and enter in several forms to reach this field and then mark, is a huge work load.

[pbaldy]

A UNION query will always be read-only. If you're importing everything that has a 0, you can simply run an update query on the underlying table. This type of thing:

UPDATE TableName
SET FieldName = TRUE
WHERE FieldName = False

[Douglasrac]

A UNION query will always be read-only. If you're importing everything that has a 0, you can simply run an update query on the underlying table. This type of thing:

UPDATE TableName
SET FieldName = TRUE
WHERE FieldName = False

Love Access!

But I need to update 2 tables at the same time.
Here is what I did:

UPDATE TableName, TableName2
SET FieldName = TRUE And FieldName2 = True
WHERE ((FieldName = False) AND (FieldName2 = False))

But didn't work. And as I read, Access can't run multiple statements in one query. So I need to found another solution. Why the code above does not work?

And what about the mask for the phone number? Union queries also don't show masks?