HI

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

anyway..
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;


the problem:
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?
<s>
Also is there a way to get .FieldCount similar to .RecordCount property?

Thanks - Kirk</s>


 Try something like...

SELECT DISTINCT FieldX, FieldA, FieldB

Mike
EchoVue.com
 As soon as another field is added 'fieldX'loses it's distinctness.
This may well be no solution.




unfortunately... I need that solution
thanks again


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.

__________________
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.

i guess that out of the 4 ppl who looked didnt know the answer and i think i found it

so i suppose ill share with you guys

cuz its very interesting

if you open the proporties window in Query Design view

and click on the relation screen

an option appears : Output All Fields

i think this may get me to do this project

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.

scratch that idea.. it just adds a * to sql and makes it not distinct

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.

i believe i found a solution it will be posted tomorrow for anyone who has this problem later

till now, keep guessing guys :P

just remember whose smarter

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.

Solution is as follows, i created a query with 4 fields

here is sql SELECT DISTINCT adj_titles.DeptName, adj_titles.AdjNum, Last(adj_titles.ID) AS LastOfID, Titles.TitleNum
FROM Titles INNER JOIN (adj_titles INNER JOIN [latest employments] ON adj_titles.AdjNum = [latest employments].AdjNum) ON Titles.Title = adj_titles.Title
WHERE (((adj_titles.FullPartTime) Is Not Null))
GROUP BY adj_titles.DeptName, adj_titles.AdjNum, Titles.TitleNum
HAVING (((adj_titles.DeptName) Is Not Null));


as you can see it takes these 4 fields
adj_titles.DeptName, adj_titles.AdjNum, Last(adj_titles.ID) AS LastOfID, Titles.TitleNum

department because i need a different record for each dept
adjnum is the number of the employee to link to First Last & Soc
LastOfID This will take JOBID # from the most recent position to link to latest title step(used for payrate), last PT/FT standing
TitlesNum this takes the titles with different steps and catogorizes them as one. since i dont list title name because the name varies with every step, it groups them all together and takes the latest one

ie:
asst. custodian step 1 TitleNum 1
asst. custodian step 2 TitleNum 1
asst. custodian step 3 TitleNum 1
asst. custodian step 4 TitleNum 1
Chief Custodian step 1 TitleNum 2
Chief Custodian step 2 TitleNum 2
Chief Custodian step 3 TitleNum 2

so if someone has step 1 and step 2 in their record it will only show step 2 because of (LastOfID) field



Next is the rest of the data
Which requires a second query

SELECT DISTINCT Query3.DeptName, Query3.AdjNum, Query3.LastOfID, Query3.TitleNum, Adjuncts.LName, Adjuncts.SocSec, Adjuncts.FName, Adj_Titles.Title, Titles.CurrentPayRate, Adj_Titles.FullPartTime
FROM (Adj_Titles INNER JOIN (Adjuncts INNER JOIN Query3 ON Adjuncts.AdjNum = Query3.AdjNum) ON Adj_Titles.ID = Query3.LastOfID) INNER JOIN Titles ON Adj_Titles.Title = Titles.Title;


This takes the fields from the first query and matches them to the tables
It links LASTNAME, FIRSTNAME, SOCIAL though ADJNUM
It links LATESTTITLE, FULLTIMEPARTTIME, through LASTOFID
It links correct PAYRATE according to TITLE

if you need help with somethign like this, i guess u can ask me
i havent met an access problem i couldnt solve :P
i noticed this question was never answered in any boards of how to use DISTINCT and also list nondistinct values
So i hope whoever comes after me with the same question
wil know that im the only one that answered it here :P

Enjoy

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.