Creating a greater than query

[Guest]

I have a table and it only contains 4 fields.

ID, DDLIL, Status, Time

The ID is auto number, and time is auto generated. DDLID is my main Database
where I enter calls. Status contains 5 entry options E, S, T, TS, A

I want to design a query that says shows only one entry at a time. Show E if
no S, but once I enter a S, E goes away and so on. Now I want the query also
to say if it has a A then it does not show any of the five for the DDLID
Group.

 

[Marshall Barton]

I have a table and it only contains 4 fields.

ID, DDLIL, Status, Time

The ID is auto number, and time is auto generated. DDLID is my main Database
where I enter calls. Status contains 5 entry options E, S, T, TS, A

I want to design a query that says shows only one entry at a time. Show E if
no S, but once I enter a S, E goes away and so on. Now I want the query also
to say if it has a A then it does not show any of the five for the DDLID
Group.

You should create a table that is used to specify the level
of the status codes:

Table: StatusLevels
StatusCode Text (primary key)
StatusLevel Integer

E 1
S 2
T 3
TS 4
A 5

Then create a query to join the two tables and make the
status level available:

Query: ProjectStatus

SELECT T.ID, T.DDLIL, T.Status, T.Time, S.StatusLevel
FROM yourtable As T INNER JOIN StatusLevels As S
ON T.Status = S.Status

Now, your query you can get highest level:

SELECT Q.ID, Q.DDLIL, Q.Status, Q.Time
FROM ProjectStatus As Q
WHERE Q.Status = (
SELECT Max(X.StatusLevel)
FROM ProjectStatus As X
WHERE X.DDLID = Q.DDLID)

 

[Guest]

I have a couple of questions.

Do I link the Status Field in the Time Table as a drop down?
It required me to create a relationship prior to adding a query for the two
tables.

Now for the third part can we take baby steps I am completely lost.

 

[Marshall Barton]

Your table alread has the status field with contents like
E,S,... so there is no true necessity to change the field at
all. If you do change it, which I do recommend, you should
make it a foreign key (long integer) that contains the
status level value instead of the letter code, but this is
your call.

If you do change the status field to contain the status
level number, then you would not need the first query I
posted before.

Whatever you decide to do, DO NOT use a dropdown in the
table. This would just obscure what your table actually
contains and cause all kinds of confusion, Instead you
should create a form to enter/display the data in the table
and using a combo box on the form is very likely the best
way let users select the status.

There is no requirement to create the relationship, but it
is a good idea, not because you have to but to help prevent
garbage from being entered in the status field.