im working for a school system and im having trouble with my current assignment
i have 4 tables that are used
here are the tables, fields are blue Key is bold
1) Adjuncts: FName, LName, SocSec, AdjNum
i.e.: bob | Dole | 988-23-1234 | 1
bill | clinton | 123-45-4321 | 2
2) Adjunct_Titles: DeptName, TitleName, PartFullTime, AdjNum, ID
i.e.: English | Assis. Prof. Step1 | PT | 1 | 1
English | Assis. Prof. Step2 | PT | 1 | 2
English | Assist. CLT Step1 | FT | 1 | 3
Biology | Assist. CLT Step1 | FT | 2 | 4
3) Titles: TitleName, TitleNum, CurrentPayrate, TitleID
i.e.: Assis. Prof. Step1 | 1 | $35 | 1
Assis. Prof. Step2 | 1 | $38 | 2
Assis. CLT Step2 | 2 | $18 | 3
4) Dept : DeptName, DeptNum
i.e.: English | 111
Bio | 112
i need to make a report on all employees in a spacific dept to send to each dept seperately
just replace adjunct with employee to understand
my query is
SELECT DISTINCTROW Adjuncts.LName, Adjuncts.FName, Adjuncts.SocSec, Adj_Titles.DeptName, Adj_Titles.Title, Titles.CurrentPayRate, Adj_Titles.FullPartTime, Adj_Titles.AdjNum
FROM Adjuncts INNER JOIN (Titles INNER JOIN (Depts INNER JOIN Adj_Titles ON Depts.DeptName = Adj_Titles.DeptName) ON Titles.Title = Adj_Titles.Title) ON Adjuncts.AdjNum = Adj_Titles.AdjNum
WHERE (((Depts.DeptNum)=[Enter Dept Number:]))
ORDER BY Adjuncts.LName;
i want to return distinct results PLUS non destinct results
problem2: i only want to show the later steps for title is more than one step exists on record
here is the query i have to get it
SELECT DISTINCT Adj_Titles.AdjNum, Adj_Titles.DeptName, Titles.TitleNum
FROM Titles INNER JOIN Adj_Titles ON Titles.Title = Adj_Titles.Title
GROUP BY Adj_Titles.AdjNum, Adj_Titles.DeptName, Titles.TitleNum;
it returns ADJNUM, DEPT, TITLENUM
ADJNUM says who the employee is
DEPT says which dept.
TITLENUM makes it specific to differnt titles minus the duplicates of titles on differnt steps
now i wanna take this table that has X amount of records
and add data to it from other tables
so that i still have X records
instead i keep getting Y records because it adds extra
i found someone asking the same question except i guess he had alternative motives and changed his mind so his post didnt answer my question :
When one applies Select Distinct to field X in a query only those records which have a unique field X are displayed.
But what if you want all fields in the record, but still based on field X being unique?
Also is there a way to get .FieldCount similar to .RecordCount property?
Thanks - Kirk</s>
Try something like...
SELECT DISTINCT FieldX, FieldA, FieldB
As soon as another field is added 'fieldX'loses it's distinctness.
This may well be no solution.
unfortunately... I need that solution
I was here but now I'm gone.
I leave this messege to be carried on.
Whoever knew me, knew me well.
Whoever didn't, go to hell.