Problem filtering records from query

[crescent-centre]

I have a database with 2 tables. Table1:drawing number and drawing title and Table2: date, action, status. Table2 gives the complete history of our internal review process.

Example:
Number=001, Title=Drawing001
date=01/01/07, action=drawing received, status=
date=02/01/07, action=drawing checked, status=approved
date=03/01/07, action=approval sheet issued.

Number=002, Title=Drawing002
date=04/01/07, action=drawing received, status=
date=05/01/07, action=drawing checked, status=not approved
date=06/01/07, action=approval sheet issued.

I need a report which lists just the 'not approved' drawings. I've been able to add a query which lists the drawings but I loose the complete history, i.e. drawing recieved date.

Is there anyway I can display all the fields but only for those drawings which are 'not approved'

 

[Mile-O]

I have a database with 2 tables. Table1:drawing number and drawing title and Table2: date, action, status. Table2 gives the complete history of our internal review process.

I'm sorry, but I've read this over a couple of times now and I can't seem to understand what it is that your table structure is supposed to do. You don't appear to have any keys linking the tables.

 

[crescent-centre]

Sorry, I'll try to make things a little clearer.
The database is for an inhouse drawing review project. Firstly each drawing is booked into the database. For each drawing it can have many actions, that is table2.

Table 1 [number, title] -------->> Table 2 [date, action, status]
The data is entered from a form with a subform for the actions, (date received, date reviewed, date approval sheet issued and if approved or not). The actual db has a few extra fields as well but I'm trying to simplfy things as much as possible.

I want to produce a report of all the drawings not approved. But I also want to include the other fields i.e. date received.

Hope that makes it clearer.

 

[Wiz47]

Sorry, I'll try to make things a little clearer.
The database is for an inhouse drawing review project. Firstly each drawing is booked into the database. For each drawing it can have many actions, that is table2.

Table 1 [number, title] -------->> Table 2 [date, action, status]
The data is entered from a form with a subform for the actions, (date received, date reviewed, date approval sheet issued and if approved or not). The actual db has a few extra fields as well but I'm trying to simplfy things as much as possible.

I want to produce a report of all the drawings not approved. But I also want to include the other fields i.e. date received.

Hope that makes it clearer.

SELECT tblYourtableName1.*, tblYourtableName2.*
FROM tblYourtableName1, tblYourtableName2
WHERE (tblYourtableName2.notapproved=Forms!YourSubForm!YourtxtBoxName)

Where:
Yourtablename1 is the name of your first table

Yourtablename2 is the name of your second table

YourSubForm is the name of your subform

notapproved is the name of the field in table2

Yourtxtboxname is the name of the textbox in the subform that uses notapproved as its data source.

This query may get you where you want to go.

 

[crescent-centre]

Thanks. I'll give this ago.