Query Problem

[Tirelle]

I have 2 tables(Status and Deficiency Tracking) that are related by StatusID.
Purpose of these tables are to track deficiency status until it is corrected
or deferred. I have already created Queries to get corrected and deferred
deficiencies.
Now I want to Query for Outstanding Deficiencies(basically all entries in
Status table that do not have a related entry in Deficiency Tracker table
with a TRUE Corrected or Deferred Checkbox. As I try to create Query, as soon
as I add Corrected and Deferred Checkboxes I get Null results.... Help Please.

Thank You
Tirelle

 

[Duane Hookom]

Could you share your table structure(s) as well as some sample records? I
fear your tables might be un-normalized but you haven't provided enough
information to answer your question.

 

[Tirelle]

Sure
Status Table:COLUMNS(Type) are Name(Text), Date(Date/Time),Status(Memo)
Deficiency(Yes/No), Related Systems(Text) and the Primary Keys which are
StatusID(AutoNumber) and Core System ExpertID(Number).
Deficiency Tracker Table:Columns are Corrected(Yes/No), Deferred(Yes/No),
Date(Date/Time), Actual Correction(Memo),and Primary Keys are Core System
ExpertID(Number), DeficiencyID(AutoNumber) and Status ID(Number).
This database is designed to Track Construction and Testing of systems on a
nuclear submarine during Construction at a shipyard. After imputing a system
into database, it goes in to a table"Core System Expert" which is where Core
System ExpertID is created. As our personnel track status by making entries
into a form for "Status" Table, they note deficiencies by the
checkbox"Deficiency." A Query Selects all Deficiencies related to a system
and related them to the "Deficiency Tracker" Table when entries are made to
update status(Correction or Deferment,etc.) of deficiency. I know would like
to seperate deficiencies into 3 categories. They are Corrected, Deferred and
Outstanding. I managed to get corrected and deferred isolated but can not
isolate what would be left i.e. Outstanding. Hope this makes sense?

Thanks,
Tirelle

 

[Duane Hookom]

Please try again. When posting information to a news group, it is best to
format some informat as a list such as tables:

tblA
============
Field1 Test
Field2 Numeric
Field3 Date

tblB
============
etc

Other information should be divided into small paragraphs. The first
paragraph might have your current situation, then another for what you need.
It might also help to provide some sample data.

If you take some time to format properly, you will get better responses.

 

[Tirelle]

Status Table
===========
Name(Text)
Date(Date/Time)
Status(Memo)
Deficiency(Yes/No)
Related Systems(Text)
StatusID(AutoNumber) Primary Key
Core System ExpertID(Number)Primary Key

Deficiency Tracker Table:
==================
Corrected(Yes/No)
Deferred(Yes/No),
Date(Date/Time)
Actual Correction(Memo)
Core System ExpertID(Number) Primary Key
DeficiencyID(AutoNumber) Primary Key
Status ID(Number).Primary Key

This database is designed to Track Construction
and Testing of systems on a nuclear submarine during
Construction at a shipyard. After inputing a system
into database, it goes in to a table"Core System Expert"
which is where "Core System ExpertID" is created.
As our personnel track status by making entries
into a form for "Status" Table, they note deficiencies
by the checkbox"Deficiency." A Query Selects all Deficiencies
related to a system and related them to the "Deficiency Tracker"
Table when entries are made to update
status(Correction or Deferment,etc.) of deficiency.
I now would like to seperate deficiencies into 3 categories.
They are Corrected, Deferred and Outstanding. I managed
to get corrected and deferred isolated but can not isolate
what would be left i.e. Outstanding.
Hope this makes sense?

 

[Duane Hookom]

First a couple observations regarding your structure:
Name is not a good name for anything in Access since every object has a Name
property. Date is also not good since Date is a function in Access. Most
veteran programmers don't allow spaces in table or field names.

If StatusID in the Status Table is an autonumber than it should be a primary
key by itself. The same is true with DeficiencyID in the Deficiency Tracker
Table.

I wouldn't expect to see the Deficiency field in the status table. A
deficiency is a related record in the Defiency Table that doesn't have a
status of Corrected or Deferred.

 

[Tirelle]

I have noted and understand all your comments. Here is why Deficiency is in
Status table. There are several systems any one person is responsible for
maintaining actual status of. Usually atleast 3 times per week, a system
expert makes a significant amount of entries regarding wha has gone on with a
particular system. At this stage more than 50% of those entries are actual
deficiencies. Database is set up so that you can make all entries in the
status table and annotate via the Deficiency Checkbox if it is a deficiency.
When that checkbox is checked, you are able to track correction, Deferment
and other status of this deficiency via the deficiency tracker table and
forms. Are you able to help me with the query? Keep in mind I am a novice and
ready to learn but I have spent a lot of time building this application and
really do not wish to completely rebuild it from scratch. I do appreciate all
of your comments on structure and will definitely consider them as I develop
but right now I need help with the query, Please.

 

[Duane Hookom]

I'm not sure how you identify the status of a deficiency. You state "when
entries are made to update status(Correction or Deferment,etc.) of
deficiency" but don't provide any sample data or description of what this
means.

 

[Tirelle]

Sorry OK I am working to get better at this. Lets say i have a system and in
the status table using a form(sbfrmStatus) I enter comments about the
system(tblStatus Fields:Name,Date,Status and Related Systems). Now if this
comment was an actual deficient item I would check the deficiency checkbox.
That is all for that entry. A seperate form called frmDeficiencyTracker is
based on a query that selects all tblStatus entries that have a True for
Deficiency checkbox. The tblDeficiencyTracker Uses StatusID from Status table
to identify deficient items and also assigns a DeficiencyID. If the deficient
item's status changes(Corrected or Deferred) you make entries into
frmDeficiencyTracker to update a specific deficiency's remarks. Corrected and
Deferred and checkboxes in tblDeficiencyTracker
to note actual status of that particular deficiency. Is this making since so
far? Is it posible for me to just send you app?

 

[Duane Hookom]

I think you need to provide some sample records and what you expect to
return or display.