access query - stumped

[basement131]

i am trying to develop a query with 4 tables as follows
Table "JC" has the job codes (i.e. 100, 200, 300, etc)

Table "96" has the full time equivalents FTE for each job code in 1996
Table "97" has the full time equivalents FTE for each job code in 1997
Table "98" has the full time equivalents FTE for each job code in 1998

each table for 96-98 has the following fields
Job code (as above), Location (a, b, c, d) and the FTE (1, .25, .99)
within each of these tables there can be 1 job code with 2 locations each having a different FTE.

the query i am trying to develop will allow me to pull every job code for each location and the FTE for each year. (i.e. i would have the following fields at the top (job code , location, 96FTE, 97FTE, 98FTE) and data could be as follows (621, a, 1, 0, .5) then if location b had any FTE in any of the years for that job it would show (621, b, 1, 2, .8).

now i tried to do the query i pulled up the 4 tables & i selected JC with a left join by job code to each of the years i ended up getting duplicates.

could you please give me some assistance with this one.

Ian.

 

[stephenk1973]

View the SQL and SELECT DISTINCT. If you still get the duplicates, i would think they are not duplicates.

Hope this helps

Stephen

 

[basement131]

i tried the following query but it did not work

SELECT Distinct [Job codes].chgjob, [96-97].chgdept, [96-97].FTEs, [97-98].chgdept, [97-98].FTEs
FROM ([Job codes] LEFT JOIN [96-97] ON [Job codes].chgjob = [96-97].chgjob) LEFT JOIN [97-98] ON [Job codes].chgjob = [97-98].chgjob;

but this did not work unless i did not do it correctly. here are the results.
chgjob 96-97.chgdept 96-97.FTEs 97-98.chgdept 97-98.FTEs
116
191 S440 1.01 S440 1
193 S440 10.31 S440 10.5
194 S440 1 S440 1
196 S440 9.17 S440 9.56
308
309
320 R440 1.0038 R440 0.996153846153846
334
335
336
344
360 R442 0.642307692307692
362 R442 0.660769230769231
584 R440 1.92307 R440 1.55
584 R440 1.92307 S440 0.95
584 S440 1 R440 1.55
584 S440 1 S440 0.95
600
621 C440 1.00102 C440 0.996923076923077
621 C440 1.00102 R440 0.992820512820513
621 R440 0.27078 C440 0.996923076923077
621 R440 0.27078 R440 0.992820512820513
622 C440 1.28527 C440 5.12769230769231E-02
623
624 R440 0.74412
626 C440 7.3214 C440 8.25234358974359
626 C440 7.3214 R440 17.4058358974359
626 R440 16.8972 C440 8.25234358974359
626 R440 16.8972 R440 17.4058358974359
629 C440 0.80769 C440 0.185128205128205
631
715 R440 0.71180
716 R440 17.0545 R440 18.2011641025641
731
774 R440 2.81929 R440 1.99895384615385
775 R440 6.65230 R440 0.651923076923077
782
798
807
831 C440 11.0628 C440 11.1692461538462
856 R440 1.01961 R440 0.999569230769231
872 R440 0.98961 R440 1.00974358974359
984 S440 1 S440 1


notice the duplicates in FTE for the job code 621.

please help.

 

[Golom]

DISTINCT will include a record if the concatenation of all the fields in the select is DISTINCT. In your example, for Job Code 621, the first FTE (i.e. 96-97.FTEs) is duplicated but the 97-98.FTEs are different so both records are included.

It is unclear exactly what result you expect to see when that happens. Since you are posting, obviously you don't want what you are getting but I don't understand what you do expect to see when some, but not all, "FTE" or "chgdept" values are duplicated.

 

[basement131]

Golom
i would like to have pull ALL the job# (chgjob) from the Job Codes table. I would like to join the chgjob to the 96-97 table which also has the chgjob field. i would like to display the 96-97.chgdept and 96-97.FTEs from the 96-97 Table. So far so good. Now here is where the problem arises. I would also like to join the chgjob to the 97-98 table which also has the chgjob field. i would like to display the 97-98.chgdept and 97-98.FTEs from the 97-98 table. When i go to do this i get duplicates. this is b/c there may be 1 chgjob # in the Job codes table but there may be 2 of the same chgjob # in both of the 96-97 & 97-98 tables. This results in me getting 4 records as apposed to 2.
if you need i can paste the tables as there are 3 tables with approx 20-30 recors in each with only 2 or 3 fields
thanks

 

[Golom]

Here's the issue. Using the first two "ChgJob" 584 records from your example.
[blue][tt]
chgjob 96-97.chgdept 96-97.FTEs 97-98.chgdept 97-98.FTEs
584 R440 1.92307 R440 1.55
584 R440 1.92307 S440 0.95
[/tt][/blue]
In this you have (far as I can tell) correctly pulled all the records where "ChgJob" is 584 from the tables.

Looking at these records, the values from [96-97] are the same but the values from [97-98] are different. You want to see only one record for this. The issue is ... which one? SQL can't tell which record to drop. You could show (for example) only the FIRST values, LAST values, MIN values or MAX values and that would give you only one record but, as long as you are selecting all unique records you will get this result.

Focusing on this pair of records only, can you state what result you expect to see, given that multiple occurences of "ChgJob" exist in the [97-98] table?

 

[basement131]

chgjob 584 should not have 4 records but rather only 2. i would like it to show like the following.
chgjob 96-97.chgdept 96-97.FTEs 97-98.chgdept 97-98.FTEs
584 S440 1 S440 0.95
584 R440 0.019230769 R440 1.55

notice how each row has the same chgjob and same chgdept. this is how i would like mine to come out when i run my query.

 

[Golom]

OK. Try this SQL
[blue][tt]
SELECT DISTINCT
J.chgjob,
X.chgdept,
X.FTEs,
Y.chgdept,
Y.FTEs

FROM ([Job codes] J LEFT JOIN [96-97] X
ON J.chgjob = X.chgjob)

LEFT JOIN [97-98] Y
ON Y.chgjob = J.chgjob
AND Y.ChgDept = X.ChgDept
[/tt][/blue]
I have used some table aliases to make it a bit easier to read.

I have added a join condition requiring that [96-97].ChgDept = [97-98].ChgDept. With that you should see the first "584" record but not the second one.