Join

[artur]

I have two queries containing this information (example):

QUERY 1 "People having cars":
Name  Number cars
Peter        2
John         1
Louis        1
Cris          3

QUERY 2 "People having bikes":
Name  Number bikes
David       2
John        1
Louis       2
Phil         3

TABLE Names
Peter
John
Louis
Cris
David
Phil

I want a QUERY with this information:

QUERY 1 "People having cars":
Name  Number cars  Number bikes
Peter        2                   0
John         1                   1
Louis        1                   2
Cris          3                   0
David        0                   2
Phil          0                    3

I try to do a normal join, but those who have any 0 at any field do not appear. How can I do that? THANKS

 

[boblarson]

What you really need for this is a UNION query.

Select Name, [Number of Cars] FROM YourTableNameHere
UNION
Select Name, [Number of Bikes] FROM YourTableNameHere

This is simplified. Also, if you have a field name with the name NAME you should change it now. NAME is an Access reserved word and you will run into problems later using it.

Also, I would suggest removing spaces from your field names as it will simplify your life without them due to having to have brackets in certain places due to the spaces and if you don't have them you won't have to worry about that.

 

[MStef]

Look at "DemoCarsBikesA2000.mdb".
Instead of Table1 and Table2 (in query), you can put your Query1 and
Query2.

 

[artur]

Thank U!!!

THANK YOU so much for your help, you have been very kind with a newbe like me.

Best regards, colleagues ;-)

Artur