Hi, I hope somebody can help me. This may not be a very difficult question 

but I just can't figure it out.



The problem is that I have, among others, tables called Display, Machine, 

Printer and Device. So this is a database for computer parts. The parts 

are connected with the room number table according to where they are 

situated. When I want to make a query that shows all the computer parts 

together, and I also add to the query info about the devices and printers 

the result doesn't show any of those machine packages that don't have 

printers or devices. I guess this has something to do with that Access 

uses inner join. I've tried to change it from the SQL view to join/outer 

join but then the syntax is wrong according to Access. Is there any 

solution to this or do I have to change the structure of the database? 

Thanks for your help.

Olga

When you look at the query in Design Mode, you can right click the line joi

ning the databases and change the type of join. When you do it this way, yo

u don't have to worry about syntax errors.



Beverly



>>> olga_moller@h... 06/09/2001 8:24:24 am >>>

Hi, I hope somebody can help me. This may not be a very difficult question

b

 ut I just can't figure it out.



The problem is that I have, among others, tables called Display, Machine,

P

 rinter and Device. So this is a database for computer parts. The parts

are connected with the room number table according to where they are

situated. When I want to make a query that shows all the computer parts

together, and I also add to the query info about the devices and printers

t

 he result doesn't show any of those machine packages that don't have

printers or devices. I guess this has something to do with that Access

uses inner join. I've tried to change it from the SQL view to join/outer

join but then the syntax is wrong according to Access. Is there any

solution to this or do I have to change the structure of the database?

Thanks for your help.

Olga

If I understand you right, you might be able to use a UNION query to get

what you want.  Basically, joins are for mashing tables together

side-by-side and UNIONs are for mashing tables to gether one on top of

another.  You've got to manually type UNIONs into the SQL view of the query

as far as I know--there's no wizard or design view method for setting them

up.



If the tables you mentioned have identical structure (e.g. field name, type

and position), you should be able to do something like



SELECT * FROM Display

UNION

SELECT * FROM Machine

UNION

SELECT * FROM Printer

UNION

SELECT * FROM Device ;



If they don't have identical structure, you'll likely have to alias the

fields (e.g. 'SELECT DisplayName as PartName') in order to get the UNIONs to

work.  You may also want to add a literal column to indicate the source for

each record (e.g., tack a "'Display' as RecSource" type statement at the end

of each SELECT clause.  See the help file for other restrictions on using

UNIONs.



NB--if the structures of these tables are very close to one another, it may

pay to combine them in a single table along w/a field for discriminating

between types of parts.



Hope that helps.



-Roy



-----Original Message-----

From: Olga =?UNKNOWN?Q?M=F6ller?= [mailto:olga_moller@h...]

Sent: Thursday, September 06, 2001 1:24 AM

To: Access

Subject: [access] Access query problem





Hi, I hope somebody can help me. This may not be a very difficult question 

but I just can't figure it out.



The problem is that I have, among others, tables called Display, Machine, 

Printer and Device. So this is a database for computer parts. The parts 

are connected with the room number table according to where they are 

situated. When I want to make a query that shows all the computer parts 

together, and I also add to the query info about the devices and printers 

the result doesn't show any of those machine packages that don't have 

printers or devices. I guess this has something to do with that Access 

uses inner join. I've tried to change it from the SQL view to join/outer 

join but then the syntax is wrong according to Access. Is there any 

solution to this or do I have to change the structure of the database? 

Thanks for your help.

Olga