Query on all tables?

  • Thread starter Thread starter FpwL1399
  • Start date Start date
F

FpwL1399

Hello, I would like a query to run through every table in my database
and pull out the entries that have a certain 'run number.' Everything
in my database has a run number associated with it, so that's not an
issue. One issue that I forsee is the varying data associated with
each run number. Maybe it would be better if I had a query that could
run on one table, export that data to an excel spreadsheet and then
move to the next table and do the same for every table. I feel like
I'm asking alot. I'm not scared of programming, I think I just need to
be pointed in the right direction. Thanks.
 
One, and only one, table should have 'run number' as its primary key. Other
tables may or may not have 'run number' as a foriegn key. If one table does
have 'run number' as the PK, you would then join the other tables in a
relationship to return all the proper 'run number' data. That's how a
database is suppose to work.

Have you check the Relationships Window to see if the tables happen to be
joined by the 'run number' ? If so, you do not need to export to Excel. You
just need to know how to right a proper query to extract the needed data.

However let's say that you have 'run number' spread out willy-nilly all over
tables in the database. For example there are Jan06, Feb06, ect., tables
(which is very bad). Are the 'run numbers' all in the same field name? You
would have to make sure of that first. Then you could write something like
for each table:

SELECT *
FROM TheTableName
WHERE [run number] = 1234 ;

As far as the right direction, I highly suggest getting some relational
database training or reading "Database Design for Mere Mortals" by Hernandez
before proceeding any further on this database. Such training could help you
use your existing database or rewrite it properly.
 
Maybe I should explain a little better before you continue to
criticize. I work in a production facility and I am building a
database to collect data throughout our FAB(fabrication area). We run
many different types of equipment that each have different capabilities
and all play a role in the production of our product. Through each
step in the FAB, a run number is associated with a lot of 50 product as
it travels. I have set up my database with tables to collect the data
from each process (not each machine). So I am required to have a run
number associated with each entry in every table. What I would like to
do is have a table or something (preferably exported to Excel for
easier viewing and manipulation of data) where these run numbers are
listed with their respective peices of data from each processes laid
out nice and neat. If there's a way to do this, it would be great.
Thanks for any help.

Perhaps I am doing it wrong....but perhaps I have only started using
Access in the past few weeks. And Jerry, you absolutely did not
attempt to find out , and that's where you erred.

I've had professors that look down on their students because of their
lack of knowledge, not realizing that it is their duty to pass on
knowledge. Perhaps you should read a book on common courtesy. Or
perhaps reading comprehension as I did not say whether run number was
primary key in every table and that's where your criticizm began.

If I sound slightly upset, you have made an astute observation and I
believe your reading comprehension skills are growing. So thank you
Jerry, and please do not respond to any posts of mine again.

If someone else would like to take a more apropriate shot at my
problem, I would greatly appreciate it.

Jerry said:
One, and only one, table should have 'run number' as its primary key. Other
tables may or may not have 'run number' as a foriegn key. If one table does
have 'run number' as the PK, you would then join the other tables in a
relationship to return all the proper 'run number' data. That's how a
database is suppose to work.

Have you check the Relationships Window to see if the tables happen to be
joined by the 'run number' ? If so, you do not need to export to Excel. You
just need to know how to right a proper query to extract the needed data.

However let's say that you have 'run number' spread out willy-nilly all over
tables in the database. For example there are Jan06, Feb06, ect., tables
(which is very bad). Are the 'run numbers' all in the same field name? You
would have to make sure of that first. Then you could write something like
for each table:

SELECT *
FROM TheTableName
WHERE [run number] = 1234 ;

As far as the right direction, I highly suggest getting some relational
database training or reading "Database Design for Mere Mortals" by Hernandez
before proceeding any further on this database. Such training could help you
use your existing database or rewrite it properly.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


FpwL1399 said:
Hello, I would like a query to run through every table in my database
and pull out the entries that have a certain 'run number.' Everything
in my database has a run number associated with it, so that's not an
issue. One issue that I forsee is the varying data associated with
each run number. Maybe it would be better if I had a query that could
run on one table, export that data to an excel spreadsheet and then
move to the next table and do the same for every table. I feel like
I'm asking alot. I'm not scared of programming, I think I just need to
be pointed in the right direction. Thanks.
Click to expand...
Click to expand...
 
So I am required to have a run
number associated with each entry in every table.
Click to expand...

That would be true IF the only way to view or export data was from a
Table.

That is not the case.

You can - and usually would - view and export data FROM A QUERY
joining multiple tables. Only one table would need to contain a Run
Number in this case.

It would be very common to have a Runs table with Run Number as its
Primary Key; this table might well be joined to other "many" side
tables which would also have a RunNumber. But if a given machine can
be involved in more than one run, then the Machines table certainly
should NOT contain a RunNumber - because that would prohibit its being
involved in multiple runs.

I think if you reread Jerry's suggestions, and experiment with
queries, you'll find that it really will work.

John W. Vinson[MVP]
 
As I said in my previous post; I'm just starting out with this access
thing and haven't learned proper form or whatever you want to call it,
but I'm picking it up pretty quick and all I need is a point in the
right direction. I don't understand how I could have one table with
run number in it and have the correct run number linked to the correct
data. Also, i don't see how one run number in this so called run
number table could be associated with many different processes
throughout our FAB. And what if there can be multiple run numbers for
each run in a process? I'm just not seeing how this can happen, and
happen correctly. Thanks for any help.
 
I have to say congratulations on writing that message. That response can be
quite common when it comes to database design. I wrote a similar response a
few weeks ago due to my lack of database skills. How would you like your
information to be laid out? Ask yourself where you want to end and then work
backwards.

How many tables do you have? Can you write out the structure of each table
here?
 
I have 16 tables so far and the number is growing as I work. Each
table has a run number associated with each entry. Each entry has
different information with it depending on what table it's in. As I
said before, where I work we have many different processes that have
different data collected. So I see no way to cut down on table numbers
due to the varying information.

The goal of this database is to take over what was once done in Excel.
We have had many issues with operators accidentally getting into the
scripts and wreaking havoc within the system. We are hoping that this
access database will provide an error free data collection system where
the operators have no way to back end into the system and break
something. Ultimately an outside Statistical Process Control program
will be called to view and process the data being collected in a
realtime scenario allowing for the operators to make better decisions
in the fab, and allerting them when the data is trending to out of
spec.

What my original question in this thread was dealing with is finding
every entry with a certain run number and displaying it in an easy to
read, orderly fashion where it can be viewed by engineers or
management.
 
As I said in my previous post; I'm just starting out with this access
thing and haven't learned proper form or whatever you want to call it,
but I'm picking it up pretty quick and all I need is a point in the
right direction. I don't understand how I could have one table with
run number in it and have the correct run number linked to the correct
data. Also, i don't see how one run number in this so called run
number table could be associated with many different processes
throughout our FAB. And what if there can be multiple run numbers for
each run in a process? I'm just not seeing how this can happen, and
happen correctly. Thanks for any help.
Click to expand...

Bear in mind that I do NOT fully understand your business rules or
data structure, so this may be off base: but consider this concept.

A "Many to Many" relationship seems to be indicated here. Each Run may
include several Processes; each type of Process may be involved in
several Runs.

*IF* that is the case (and again, I don't know), you can use three
tables:

Runs
RunNumber
<information about the run itself as an entity, e.g. DateStarted>

Processes
ProcessID
<information about the process itself, e.g. a Description>

RunProcesses
RunNumber <link to the Runs table>
ProcessID <link to the Processes table>
<information about this process in the context of this run>

If there are multiple run numbers for each process and vice versa, you
would have multiple records in the RunProcesses table.

This three-table principle can be applied whenever you have a many to
many relationship.


John W. Vinson[MVP]
 
Thank you, that is probably the best help that I have gotten on the
linking tables issue. I have a couple questions and comments on it
though.
Runs
RunNumber
<information about the run itself as an entity, e.g. DateStarted>
Click to expand...

I like this idea. It's sort of what we've looked at here. Thank you.
Processes
ProcessID
<information about the process itself, e.g. a Description>
Click to expand...

I don't really know what I would do with this table being that we don't
really care (because we already know) the description of the process
and what it entails. Is this necessary.

If I were going to use this table set up and If I had more than one
process table (because each process has a different set of data
associated with it), would that present and issue? Or maybe it would
be better if I had multiple RunProcesses tables? And is it possible?
I'm kinda of confused on this.
RunProcesses
RunNumber <link to the Runs table>
ProcessID <link to the Processes table>
<information about this process in the context of this run>
Click to expand...

Can the process ID be a word or a number? Or a combination of the two?
As long as it's consistent? Would it be hard to link multiple
RunProcesses Tables to the Run and Processes Tables?

Again, thanks for being so helpful. I like you're advice.
 
Back
Top