Select values from Multiple tables

D

daksport00

Hello,

I am trying to come up with a piece of SQL code that will select
data from multiple tables.

The table names are [General Drawings] [Fabrication Drawings]
[Sketches].

All 3 tables are setup with the same field names: [Drawing Number]
[Drawing Name] [Job Number] [Serial Number] [Current Revision] [Date]
[Author] [Furnace Model] [Size].

I have a form that is being used to display drawings that relate
to a given Furnace Model. There is a subform that will give the
information for the drawings. I have a setup where you can select to
see the drawings list limited to just General Drawings, Fabrication
Drawings, Sketches, or see all drawings.

I can use this SQL code to work with the 3 limited lists:
******************
"SELECT [General Drawings].[Drawing Number], [General
Drawings].[Drawing Name], " & _
"[General Drawings].[Job Number], [General
Drawings].[Serial Number], " & _
"[General Drawings].[Current Revision], [General
Drawings].[Date], " & _
"[General Drawings].[Author], [General
Drawings].[Furnace Model], " & _
"[General Drawings].[Size] " & _
"FROM [General Drawings] " & _
"WHERE [General Drawings].[Furnace Model] Like '*" &
Me.cmbModelNumber & "*';"
******************

I was able to modify it to pull valus from all 3 tables by using
this code:
******************
"SELECT [Sketches].[Drawing Number], [Sketches].[Drawing Name], " & _
"[Sketches].[Job Number], [Sketches].[Serial Number], "
& _
"[Sketches].[Current Revision], [Sketches].[Date], " &
_
"[Sketches].[Author], [Sketches].[Furnace Model], " & _
"[Sketches].[Size], " & _
"[Fabrication Drawings].[Drawing Number], [Fabrication
Drawings].[Drawing Name], " & _
"[Fabrication Drawings].[Job Number], [Fabrication
Drawings].[Serial Number], " & _
"[Fabrication Drawings].[Current Revision],
[Fabrication Drawings].[Date], " & _
"[Fabrication Drawings].[Author], [Fabrication
Drawings].[Furnace Model], " & _
"[Fabrication Drawings].[Size], " & _
"[General Drawings].[Drawing Number], [General
Drawings].[Drawing Name], " & _
"[General Drawings].[Job Number], [General
Drawings].[Serial Number], " & _
"[General Drawings].[Current Revision], [General
Drawings].[Date], " & _
"[General Drawings].[Author], [General
Drawings].[Furnace Model], " & _
"[General Drawings].[Size] " & _
"FROM [Sketches], [Fabrication Drawings], [General
Drawings] " & _
"WHERE [General Drawings].[Furnace Model] Like '*" &
Me.cmbModelNumber & "*' AND " & _
"[Fabrication Drawings].[Furnace Model] Like '*" &
Me.cmbModelNumber & "*' AND " & _
"[Sketches].[Furnace Model] Like '*" &
Me.cmbModelNumber & "*'"
******************

The problem I am having is that I would like the valules to append
to each other, but I am reluctant to use a temporary table because this
will be a multi-user database and I am trying to stay away from temp
tables, because you can get into the wonderful world of runtime errors
caused by tables being opened by other users.

I know that using an Append Query, I need a table to append to,
hence a Temporary table. If I try to use an Update Query, it will
update the data itself, not update to a specific field name.

Thanks in advance

-Dave
 
R

Rick B

Sounds like your database is not normalized.

If all three tables have the same structure, then you should have one table.
Add a new field for "DrawingType" and specify the type.

Any time you have data in your field names or table names, you can bet you
have a design flaw.

--
Rick B



Hello,

I am trying to come up with a piece of SQL code that will select
data from multiple tables.

The table names are [General Drawings] [Fabrication Drawings]
[Sketches].

All 3 tables are setup with the same field names: [Drawing Number]
[Drawing Name] [Job Number] [Serial Number] [Current Revision] [Date]
[Author] [Furnace Model] [Size].

I have a form that is being used to display drawings that relate
to a given Furnace Model. There is a subform that will give the
information for the drawings. I have a setup where you can select to
see the drawings list limited to just General Drawings, Fabrication
Drawings, Sketches, or see all drawings.

I can use this SQL code to work with the 3 limited lists:
******************
"SELECT [General Drawings].[Drawing Number], [General
Drawings].[Drawing Name], " & _
"[General Drawings].[Job Number], [General
Drawings].[Serial Number], " & _
"[General Drawings].[Current Revision], [General
Drawings].[Date], " & _
"[General Drawings].[Author], [General
Drawings].[Furnace Model], " & _
"[General Drawings].[Size] " & _
"FROM [General Drawings] " & _
"WHERE [General Drawings].[Furnace Model] Like '*" &
Me.cmbModelNumber & "*';"
******************

I was able to modify it to pull valus from all 3 tables by using
this code:
******************
"SELECT [Sketches].[Drawing Number], [Sketches].[Drawing Name], " & _
"[Sketches].[Job Number], [Sketches].[Serial Number], "
& _
"[Sketches].[Current Revision], [Sketches].[Date], " &
_
"[Sketches].[Author], [Sketches].[Furnace Model], " & _
"[Sketches].[Size], " & _
"[Fabrication Drawings].[Drawing Number], [Fabrication
Drawings].[Drawing Name], " & _
"[Fabrication Drawings].[Job Number], [Fabrication
Drawings].[Serial Number], " & _
"[Fabrication Drawings].[Current Revision],
[Fabrication Drawings].[Date], " & _
"[Fabrication Drawings].[Author], [Fabrication
Drawings].[Furnace Model], " & _
"[Fabrication Drawings].[Size], " & _
"[General Drawings].[Drawing Number], [General
Drawings].[Drawing Name], " & _
"[General Drawings].[Job Number], [General
Drawings].[Serial Number], " & _
"[General Drawings].[Current Revision], [General
Drawings].[Date], " & _
"[General Drawings].[Author], [General
Drawings].[Furnace Model], " & _
"[General Drawings].[Size] " & _
"FROM [Sketches], [Fabrication Drawings], [General
Drawings] " & _
"WHERE [General Drawings].[Furnace Model] Like '*" &
Me.cmbModelNumber & "*' AND " & _
"[Fabrication Drawings].[Furnace Model] Like '*" &
Me.cmbModelNumber & "*' AND " & _
"[Sketches].[Furnace Model] Like '*" &
Me.cmbModelNumber & "*'"
******************

The problem I am having is that I would like the valules to append
to each other, but I am reluctant to use a temporary table because this
will be a multi-user database and I am trying to stay away from temp
tables, because you can get into the wonderful world of runtime errors
caused by tables being opened by other users.

I know that using an Append Query, I need a table to append to,
hence a Temporary table. If I try to use an Update Query, it will
update the data itself, not update to a specific field name.

Thanks in advance

-Dave
Click to expand...
 
J

Jeff L

If I am reading this correctly, I believe you can accomplish what you
want using a Union Query. That would look like this:
SELECT [Drawing Number], [Drawing Name, [Job Number], [Serial Number],
[Current Revision], [Date], [Author], [Furnace Model], [Size]
From Sketches
Click to expand...
UNION
SELECT [Drawing Number], [Drawing Name, [Job Number], [Serial Number],
[Current Revision], [Date], [Author], [Furnace Model], [Size]
From [Fabrication Drawings]
Click to expand...
UNION
SELECT [Drawing Number], [Drawing Name, [Job Number], [Serial Number],
[Current Revision], [Date], [Author], [Furnace Model], [Size]
From [General Drawings]
Click to expand...
 
D

daksport00

Jeff,

Does this have to be done in 3 different steps, or can i just code
it as one line of SQL code??

Rick,

I realize that it would be easier to have 1 large drawings table
instead of 3 (possibly more) seperate tables for each type of drawing.
Originally, the tables had different fields, but all had 3 or 4
common fields.
I was asked to have all the tables have the same fields for
uniformity, but the boss didnt want 1 large table. And the boss wants
what the boss wants, and since he signs my paycheck, I'll do my best to
give him what he wants.

Jeff said:
If I am reading this correctly, I believe you can accomplish what you
want using a Union Query. That would look like this:
SELECT [Drawing Number], [Drawing Name, [Job Number], [Serial Number],
[Current Revision], [Date], [Author], [Furnace Model], [Size]
From Sketches
Click to expand...
UNION
SELECT [Drawing Number], [Drawing Name, [Job Number], [Serial Number],
[Current Revision], [Date], [Author], [Furnace Model], [Size]
From [Fabrication Drawings]
Click to expand...
UNION
SELECT [Drawing Number], [Drawing Name, [Job Number], [Serial Number],
[Current Revision], [Date], [Author], [Furnace Model], [Size]
From [General Drawings]
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select, Update, Append Query through Code 2
Remove Characters From Table / Eliminate Duplicate Values 4
How to get desired results from query 3
Counting records if all associated records have a date 1
I want to enter new data into a lookup field... 1
query probelms 1
OLE, INK and Reports 0
cascade dates 4

Top