Info from 3 unjoined tables

[hazel4832]

I am trying to create a report from 3 unjoined tables. The information
in these tables is exported out of an online accident tracking system.
The system automatically exports the accidents in 3 tabs (into excel)
and I then import then into Access. All three of these tables have a
closed yes/no field. I'm trying to create a report that prints out all
the information from each tables that shows closed. Is there an easy
way to do this?

 

[Armen Stein]

I am trying to create a report from 3 unjoined tables. The information
in these tables is exported out of an online accident tracking system.
The system automatically exports the accidents in 3 tabs (into excel)
and I then import then into Access. All three of these tables have a
closed yes/no field. I'm trying to create a report that prints out all
the information from each tables that shows closed. Is there an easy
way to do this?

Depends on how the information is stored in the tables.

Is each accident's information spread across one or more of the
tables? Then a normal query join can hook them together. You'll need
to join on the fields that are common between them, like
AccidentNumber or something like that.

Or, are the tables really three separate lists of accidents, where
each accident appears in only one table? If that's the case (and it
seems more likely from your description), then you can use a UNION
query to join the three tables together. Basically a Union query is a
way to join tables "vertically" instead of "horizontally". You can't
build a Union query in the query designer - you need to build it with
SQL view. The key requirement is that you need to select the same
fields from each participating table so that the columns all match.

In both approaches above you can add criteria Where Closed = True.

Hope this gets you started,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[KARL DEWEY]

If totally unrelated that why not a report with 3 subreports?

 

[hazel4832]

Depends on how the information is stored in the tables.

Is each accident's information spread across one or more of the
tables?  Then a normal query join can hook them together.  You'll need
to join on the fields that are common between them, like
AccidentNumber or something like that.

Or, are the tables really three separate lists of accidents, where
each accident appears in only one table?  If that's the case (and it
seems more likely from your description), then you can use a UNION
query to join the three tables together.  Basically a Union query is a
way to join tables "vertically" instead of "horizontally".  You can't
build a Union query in the query designer - you need to build it with
SQL view.  The key requirement is that you need to select the same
fields from each participating table so that the columns all match.

In both approaches above you can add criteria Where Closed = True.

Hope this gets you started,

Armen Stein
Microsoft Access MVPwww.JStreetTech.com

That was exactly what I needed. Thanks. Now I'm having a problem
getting the 'Where Closed=True' part to work. I get the exact same
results with or without this part. I tried
Where Closed = True,
Where [Safety or Health].Closed=True,
and
Where [Safety or Health].Closed=True or External.Closed=True or
Environmental.Closed=True;

Any ideas?

Thank you.

 

[Armen Stein]

Depends on how the information is stored in the tables.

Is each accident's information spread across one or more of the
tables?  Then a normal query join can hook them together.  You'll need
to join on the fields that are common between them, like
AccidentNumber or something like that.

Or, are the tables really three separate lists of accidents, where
each accident appears in only one table?  If that's the case (and it
seems more likely from your description), then you can use a UNION
query to join the three tables together.  Basically a Union query is a
way to join tables "vertically" instead of "horizontally".  You can't
build a Union query in the query designer - you need to build it with
SQL view.  The key requirement is that you need to select the same
fields from each participating table so that the columns all match.

In both approaches above you can add criteria Where Closed = True.

Hope this gets you started,

Armen Stein
Microsoft Access MVPwww.JStreetTech.com

That was exactly what I needed. Thanks. Now I'm having a problem
getting the 'Where Closed=True' part to work. I get the exact same
results with or without this part. I tried
Where Closed = True,
Where [Safety or Health].Closed=True,
and
Where [Safety or Health].Closed=True or External.Closed=True or
Environmental.Closed=True;

Any ideas?

Thank you.

You didn't say whether you are getting too many or too few records.

1. You need to put the appropriate Where clause after EACH of the
tables in the Union query. See
http://www.really-fine.com/SQL_union.html.

2. Is the Closed flag -1, equating to True in Access? If it's
another value (like positive 1), then you can use Where Closed <> 0
instead. Or use whatever actual value you see in the tables.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

If totally unrelated that why not a report with 3 subreports?

Well, that will work if the OP wants to show them as three separate
lists. The Union query would allow them to mix and sort the records
together as one group, and will also suppress duplicates if desired.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[James A. Fortune]

Note BTW that a UNION ALL operation is used. A UNION operation suppresses
any duplicate rows in the result set, but it does inhibit performance. A
UNION ALL operation returns all rows, including any duplicates from each part
of the operation and is faster. As in this case there cannot be any
duplicates a UNION operation would be pointless so the more efficient UNION
ALL operation should be used.

A SQL expert by any other name still excels. Sweet.

James A. Fortune
(e-mail address removed)

 

[Owner]

A SQL expert by any other name still excels. Sweet.

James A. Fortune
(e-mail address removed)