Tricky Select Distinct

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

Guest

Hi there,

I have an Approval Table which records whenever a quote gets printing
approval or has it taken away. There are 3 levels of approval which get
stored as numbers 1,2 or 3.

In the main management screen for that particular project I want to be able
to display the most recent approval records for each of the 3 approval levels.

My SQL is still at the baby stage so if someone could help me through this I
would really appreciate it.

Something Like

SELECT Distinct Approval.Approval_Level, Approval.Date_App, Employee.Last_Name
FROM Approval INNER JOIN Employee ON Approval.Approval_Emp_ID =
Employee.Employee_ID
ORDER BY Approval.Date_App

????? Help ! =)
 
Dear Nick:

I'm not sure where you got the idea that DISTINCT would help with
this. I don't think it will.

You say you want to display the most recent approval (Date_App I
believe) for each Approval_Level of a "project" but I don't see what
column(s) you have that identify a project. And where does
Employee.Last_Name figure into this.

The final surprise for me is that this is to be ordered by Date_App.

Anyway, given your general question, if you group by "project" (and
again, I don't know what column(s) that would be) and by
Approval_Level, you could then show the date with MAX(Date_App). This
would be a typical "totals query" or aggregate (synonyms). If you
want to show some other column(s) that correlate to the specific value
of that MAX(Date_App) you would typically filter the rows using a
correlated subquery to the MAX(Date_App) for the project and
Approval_Level.

However, you've left a lot of confusion in my mind where all the
pieces have gone.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi, thanks for your detailed reply. I guess I am in kind of a mess.

When a person approves or dis-approves a quote then a record is appened to
the approval table with the Quote Number, Approval Level, Approval State,
Time Changed and the Employee ID who changed it.

There's no limit to the number of times someone can approve or disapprove
something so that's why I want get the most recent "Approved" record for EACH
Approval Level (there are three).

That's why I was thinking Distinct. Pick the most recent "Approved" record
for each of the three states.
 
Dear Nick:

If you wish to work with me, I am hopeful we can get something to
work. The problem so far is my inability to understand the starting
point for this issue and understand from that what you want. If
you'll reread my former post and try to answer some questions I asked,
perhaps we can go forward.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top