I have setup an Access db as a tracking system. There are 5 tables for the various processes The processes are: FE PS PW T and EL. There are no relationships between these tables as I need the tables to be individual. All 5 tables have User-name, S/N, Date, Passed etc in their fields. A s/n can not move to the next process until it has passed from the previous. All this has been setup and working fine.
The problem I have is I want to setup a query for searching for a S/N to see where in the process is the S/N (if passed field is Yes/No) and give me the name of the process but not all the Previous processes it has passed or failed only the current process where it is now. Hope someone can help, Thanks in advance
Gala

First, I think you should reconsider your database design.
You're storing the same sort of data in five different tables.
Access is a relational database, so you'd best use it as such.

When you're talking about processes, I think you're talking about phases.

Second,

>A s/n can not move to the next process until it has passed from the previous. All this has been setup and working fine.<

What's a "S/N"? How do you pass it from the previous?How do you store "S/N" in your tables, which datatype are we talking 'bout?
Can a S/N pass to the next phase even if the previous phase is not passed successfully (Passed is "No")?
What do you use the Date column for?
If you named it Date, rename it as Date is a reserved word in Access.

RV

Hi RV
The answer to the questions are
Processes meaning a certain stage or a Phase

A S/N meaning Serial Number it is passed by the operator at the relevant stage by clicking on a Yes/No option in a form. If the unit passes he would say Yes and No if it fails. So for the next stage the Yes S/N's will be avalable but the No S/N's will not be avalable. So in this case the unit has to be fixed at this stage and passed with a Yes for it to be avalable at the next stage.
The Date column is hidden from the operator but will automaticly record the time and date when the unit went through the relevant stage.

[This message has been edited by gala (edited 04-08-2002).]

>A S/N meaning Serial Number<

Of course!!!

Are we dealing with some kind of Quality Control?
I don't quite understand the process itself.
Are the processes (phases, stages) linked to projects or (production) orders?
What's the unique key for your processes?
What does identify a certain process?
Where does the S/N come in?
Is each S/N unique?

RV

Yes the DB is used for Quality control. The stages are setup so that there are no failures escaping the process and that we could keep track of all the S/N's which went through the system. There is a unique key for each process called Unit ID and this is auto generated by access (Auto number). The process is identified by the 5 tables as they belong to the five stages. Each stage has its own table. The S/N comes in at production when it is first generated or already assigned. Yes each S/N is unique and there can be no duplicates.

You really must redesign your database structure (one table with Stages, not five kind of Stages)!!
The process of Quality Control is not identified by five tables, but by stages, which only differ in stagename, total stagetime (expected and realized).

Assuming (as you indicate) there is only one way to pass the total quality control:

Create a SQL statement which gets DateCreated and whatever column you want from each Process table under the condition that DateCreated (your hidden datecolumn) is not null, for each table, using a UNION.

Your statement would look somehow like:

SELECT DateCreated, Columnname1, Columnname2
FROM YourTableProcess1
WHERE DateCreated Is Not Null
UNION
SELECT DateCreated, Columnname1, Columnname2
FROM YourTableProcess2
WHERE DateCreated Is Not Null
UNION
SELECT DateCreated, Columnname1, Columnname2
FROM YourTableProcess3
WHERE DateCreated Is Not Null
UNION
SELECT DateCreated, Columnname1, Columnname2
FROM YourTableProcess4
WHERE DateCreated Is Not Null
SELECT DateCreated, Columnname1, Columnname2
FROM YourTableProcess5
WHERE DateCreated Is Not Null;

Save this query (I'll call it Qry1).

Then vreate a new query getting the most recent record selected by Qry1:

SELECT Max(DateCreated)
FROM Qry1;

Hope this helps.
I don't intend to be a pain in the ...
but again, redesign your structure!!
You even don't need S/N's in your Procees table, Passed Yes/No has IMO the same function as your datecolumn (the "hidden" one)!!!

You're kinda violating normalization rules and making life unnecessary difficult...

Suc6,

RV