?Simple SQL/Query Question

[Bretto99]

Hi - hope you can help out a novice. I have a table of employees which shows who their seniors are and has a review date:

ID Name SuperiorID Reviewdate
1 Jones - 12 May
2 Smith - 15 Jun
3 Brown 2 12 May
4 Fox 1 08 June
5 Peters 3 08 June
6 Stewart 1 10 June
7 Alexander 5 12 May

I want to create a query (or design a form) which will allow me to view/change/enter the review date ONLY for those who DO NOT HAVE ANYONE reporting to them. So for the above, it would be:

ID Name SuperiorID Reviewdate
4 Fox 1 08 June
6 Stewart 1 10 June
7 Alexander 5 12 May

Can anyone help?

Many thanks.

 

[rajeessh]

Select x.* from Employee x LEFT JOIN Employee y
on x.ID = y.superior_Id
where IsNull(y.superior_Id) or IsEmpty(y.superior_Id)
Or Len(y.superior_Id) = 0

 

[JoanneM]

I am a little puzzled
you say you want those people who DO NOT HAVE ANYONE reporting to them and the supervisor id 5 or 1 indicates this?
As these are minimum and maximum you could use a Dmax and a
Dmin function as outlined below

Create your Query in the criteria of the field where you have superior id place the following

DMin("SuperiorID","tblname&quot

in the line below this copy

DMax("SuperiorID","tblname&quot

this will select 1 or 5

hope this helps
jo

 

[Bretto99]

Hi Rajeessh and Jo

Many thanks for your responses:

Rajeesh - one final question: when I use the SQL code in a query it works to give me the list I require - great. Only problem is that I cannot update the Reviewdate field (it says this recordset is not updateable). Is there another way of using this code (eg in a filter on a form based on the original table) to achieve the desired result?

Jo - apologies for the confusion - I see now that its was a bad example given that the only ones who came out were the min/max. To confirm: what Im trying to extract is anyone from my table that DOES NOT have someone ELSE pointing to them as their superiors ID - in my eg Brown (3) reports to Smith (2). Fox (4) reports to Jones (1). Peters (5) reports to Brown (3) etc. However, no one else in my table has their superior's IDs set as 4 (Fox), 6 (Stewart) and 7 (Alexander). These are the people I wish to extract.

Thanks again,
Bretto

 

[sko]

This is updateable.

SELECT tblEmployees.Employee, tblEmployees.Name, tblEmployees.Review, tblEmployees_1.SupervisorID
FROM tblEmployees LEFT JOIN tblEmployees AS tblEmployees_1 ON tblEmployees.Employee = tblEmployees_1.SupervisorID
WHERE (((tblEmployees_1.SupervisorID) Is Null));

 

[Bretto99]

Hi Sko

'Fraid that still says recordset is not updateable Any further suggestions? (the query works - just wont let me update)

 

[sko]

Guess we need to know what field you are trying to update.

 

[Bretto99]

In the original eg above Im trying to update the 'Reviewdate' field.

 

[sko]

Not sure what to tell you except I can update it on my example.

 

[JimWho]

Perhaps it is a case of you not having Update Privileges. I can do all kinds of retrievals but I am not allowed to update the tables unless I have been given the appropriate Oracle grants.

 

[Bretto99]

Jim - thanks for your reply. I am working in Access, dont know if this would affect things. I am writing the database and thus shouldnt have any problems with Update priviledges. Would you know if the fact that Im working in Access and not Oracle affects things? I have tried sko's code a number of times and it still doesnt work!

Thanks
Bretto

 

[sko]

Ok, grasping at straws here. Check your query properties.
Right-click in the upper pane of the query builder. Check "unique values". It should say no.

 

[Bretto99]

Nope sko - that didnt work. Dont know how you would feel, but if you could send through your email address maybe I could send you the trial database (just with three tables in that Im testing on) and you could have a look at it? If that wouldnt be a problem I'd greatly appreciate it. If you dont want to post your email, you can send it to CaptPipers@bigpond.com

Many thanks.
Bretto

 

[JimWho]

Sorry I can't help you Brett. Access, me and SQL as I know it don't get along. I use SQL Navigator although I would like to get a handle on Access SQL.

Jim