Code to input value based on dates and names and query on "show"s

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello: in the example below, I want to cycle through my records and 1: pick
up the most recent two records based on date and name id; input a "show" flag
IF it has a "Fail" status in either of the latest two (or in the latest).
Then I want to query on the "shows" . Using the example below, the end
result would be a query that only returns Name 123.

I forgot how to do this in Access (would rather NOT export to Excel and do
this.)


Detail ID Name ID Name Date Status Flag
456 123 Name123 01-Nov-04 Fail Show
457 123 name123 27-Oct-04 Pass Show
458 124 Name124 01-Nov-04 Pass Don't show
459 124 Name124 27-Oct-04 Pass Don't show
 
pick up the most recent two records based on date and name id; input a
"show" flag IF it has a "Fail" status in either of the latest two (or
in the latest). Then I want to query on the "shows" . Using the
example below, the end result would be a query that only returns Name
123.

I forgot how to do this in Access (would rather NOT export to Excel
and do this.)


Detail ID Name ID Name Date Status Flag
456 123 Name123 01-Nov-04 Fail Show
457 123 name123 27-Oct-04 Pass Show
458 124 Name124 01-Nov-04 Pass Don't show
459 124 Name124 27-Oct-04 Pass Don't show
Click to expand...

UPDATE MyTable
SET Flag="Show"
WHERE Detail IN
( SELECT TOP 2 Detail
FROM MyTable
ORDER BY MyDate DESC
)
AND Status = "Fail"

SELECT DISTINCT NameID
FROM MyTable
WHERE Detail IN
( SELECT TOP 2 Detail
FROM MyTable
ORDER BY MyDate DESC
)
AND Flag = "Show"


Notes:
1) column names like Date and Name are just bugs waiting to happen. As
you will have noticed, I renamed your Date field to MyDate

2) I strongly suspect a Serious Design Error here... the Name and NameID
look very much as if one is functionally dependent on the other, and the
setting of Flag dependent on Status suggests that they aren't in 2NF
either.


Still, I think that the queries above should do what you have described,
although be aware that I have not tested them. Try them out on a backup
first!

All the best


Tim F
 
Hello Tim:
Thanks for your reply. Before I implement, a comment and a question. These
are NOT the real names of the fields (NameID and Name are not functionally
dependent--NameID is a key which is numeric, Name is text (not the actual
field names either), but are just simplified examples. With regard to your
comment:
the setting of Flag dependent on Status suggests that they aren't in 2NF
either.
Click to expand...

What is "2NF"?
 
What is "2NF"?
Click to expand...

Second Normal Form -- it's one of the stages of acheiving normalisation in
a relational database design. Essentially, it means that all fields are
functionally dependent on the primary key. My suspicion was that Flag was
FD on Status, although of course I cannot see your application from here!

All the best


Tim F
 
Hi Tim: I agree with you...however I am pulling in data from another database
and am working with what I've got at the moment. So, yes, the flag is
dependent on status and not a "statusID".
 
Back
Top