How do I run a multi-table query?

[Guest]

I have a database set up which includes 8 tables. I need to do a query which
searches 4 of those tables for null values. Basically, the tables I've
produced show items in two stages. Four show items in separate depts in
their first stage and the other four tell which items from the first stage
have moved to the second stage. I need a query that will search each of them
and tell us which items for each dept have NOT moved to stage two. Any
chance anyone can assist???

 

[Joseph Meehan]

I have a database set up which includes 8 tables. I need to do a
query which searches 4 of those tables for null values. Basically,
the tables I've produced show items in two stages. Four show items
in separate depts in their first stage and the other four tell which
items from the first stage have moved to the second stage. I need a
query that will search each of them and tell us which items for each
dept have NOT moved to stage two. Any chance anyone can assist???

More information needed. How are the tables related. How are they the
same and how are the different.

From what little information I see, I would guess you may not need all
four tables.

Making sure the table structure is correct is often the first step of
solving an apparent problem.

 

[Joseph Meehan]

I have a database set up which includes 8 tables. I need to do a
query which searches 4 of those tables for null values. Basically,
the tables I've produced show items in two stages. Four show items
in separate depts in their first stage and the other four tell which
items from the first stage have moved to the second stage. I need a
query that will search each of them and tell us which items for each
dept have NOT moved to stage two. Any chance anyone can assist???

More information needed. How are the tables related. How are they the
same and how are the different.

From what little information I see, I would guess you may not need all
four tables.

Making sure the table structure is correct is often the first step of
solving an apparent problem.

 

[Guest]

The tables are basically identical. The difference is the departments. Ok,
let me give you a few more details. I work in a production facility. My
boss wanted me to set up a database to go on the production floor for people
on the assembly line to enter information about problem parts. I was going
to do just one table and have a field where they select the department but he
wants them separated. So I have four tables for stage one which tells when
the item(s) were first reported(one for each of the four departments) and
four for stage two(which tells when the item(s )problem has been resolved and
what action was taken) each of which is connected to a form(for each
department) which is also connected to a button to go to that form. I'd go
back and join them all in one table but I really think that might make
matters worse.

 

[Guest]

The tables are basically identical. The difference is the departments. Ok,
let me give you a few more details. I work in a production facility. My
boss wanted me to set up a database to go on the production floor for people
on the assembly line to enter information about problem parts. I was going
to do just one table and have a field where they select the department but he
wants them separated. So I have four tables for stage one which tells when
the item(s) were first reported(one for each of the four departments) and
four for stage two(which tells when the item(s )problem has been resolved and
what action was taken) each of which is connected to a form(for each
department) which is also connected to a button to go to that form. I'd go
back and join them all in one table but I really think that might make
matters worse.

 

[Pieter Wijnen]

why not make 4 queries linked to the same table???
then both you and the de-normalized bosses would be happy ...

Pieter

 

[Pieter Wijnen]

why not make 4 queries linked to the same table???
then both you and the de-normalized bosses would be happy ...

Pieter

 

[Joseph Meehan]

The tables are basically identical. The difference is the
departments. Ok, let me give you a few more details. I work in a
production facility. My boss wanted me to set up a database to go on
the production floor for people on the assembly line to enter
information about problem parts. I was going to do just one table
and have a field where they select the department but he wants them
separated. So I have four tables for stage one which tells when the
item(s) were first reported(one for each of the four departments) and
four for stage two(which tells when the item(s )problem has been
resolved and what action was taken) each of which is connected to a
form(for each department) which is also connected to a button to go
to that form. I'd go back and join them all in one table but I
really think that might make matters worse.

I agree with Peter. Going along with your bosses idea is going to come
back and bite you time and time again. He is only suggesting this because
he is not a database person and does not understand why it is wrong to do it
this way.

Set up a sample. Copy the original database and combine the tables
(append queries) adding the required ID field. Then make four forms for
entering data, each with the name of the department or whatever and have the
form enter the default ID to each new record.

Set up four queries each filtering for one of the four ID's. Show that
to the boss.

 

[Joseph Meehan]

The tables are basically identical. The difference is the
departments. Ok, let me give you a few more details. I work in a
production facility. My boss wanted me to set up a database to go on
the production floor for people on the assembly line to enter
information about problem parts. I was going to do just one table
and have a field where they select the department but he wants them
separated. So I have four tables for stage one which tells when the
item(s) were first reported(one for each of the four departments) and
four for stage two(which tells when the item(s )problem has been
resolved and what action was taken) each of which is connected to a
form(for each department) which is also connected to a button to go
to that form. I'd go back and join them all in one table but I
really think that might make matters worse.

I agree with Peter. Going along with your bosses idea is going to come
back and bite you time and time again. He is only suggesting this because
he is not a database person and does not understand why it is wrong to do it
this way.

Set up a sample. Copy the original database and combine the tables
(append queries) adding the required ID field. Then make four forms for
entering data, each with the name of the department or whatever and have the
form enter the default ID to each new record.

Set up four queries each filtering for one of the four ID's. Show that
to the boss.

 

[Pat Hartman\(MVP\)]

"I was going to do just one table and have a field where they select the
department but he wants them separated."
Your boss is 100% wrong. He is confusing a relational database with a
spreadsheet.

Your problem will disappear once you create the proper 1-many table
structure. Department is simply a piece of data to be stored in one of the
tables. The one-side table will define the product. The many-side table
will contain a row for each stage for each product.

With this structure, you can add products, departments, and stages with NO
programming required. You can't do that with the poor structure that you
are currently using.

 

[Pat Hartman\(MVP\)]

"I was going to do just one table and have a field where they select the
department but he wants them separated."
Your boss is 100% wrong. He is confusing a relational database with a
spreadsheet.

Your problem will disappear once you create the proper 1-many table
structure. Department is simply a piece of data to be stored in one of the
tables. The one-side table will define the product. The many-side table
will contain a row for each stage for each product.

With this structure, you can add products, departments, and stages with NO
programming required. You can't do that with the poor structure that you
are currently using.