JOIN problem

[drewby1]

hi

i have 2 tables, Q and QMS

common field's are QMS.AgentCupid and Q.Cupid

i want to list all records in QMS and also the OverallScore field in Q if there is one.

fields to view

QMS.Name , Q.OverallScore

sample output would look something similar to...

Name  OverallScore
J B   75%
A S   80%
C J

(nil entry)

B T

(nil entry)

A B   42%

formatting, order etc.. isn't important as i should be ok doing this, it's just the join i can't get my head round!

any help greatly appreciated and thanks in advance

 

[danvlas]

As far as I can see, it's a simle left join...
Use the Find Unmatched Query wizard to create a query that displays all QMS with no match in Q (match fields: AgentCupid and Cupid). Then open the query in Design view and delete the Is Null condition.

If you want to type it:

Select QMS.[Name], Q.[Score] From QMS Left Join Q On QMS.AgentCupid = Q.Cupid;

And change the field name [Name] to something else...you'll have troubles in the future if you don't...

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[drewby1]

cheers for the quick reply daniel

forgot to mention the "spanner in the works" which is that i only want data from the QMS table for a specific month.

the Q table holds staff details, the QMS table holds assessment details.

i want to list all staff with grade G2 or G3 and show if they've had an assessment (or not) in april 03

i've already tried the left-join and it only yields results from both tables when there are entries in the Q table.

here's the full query i'm running at the mo

SELECT Q.OpsManager, Q.LineManager, Q.Forename & " " & Q.Surname AS CSA, q.Grade, Format(QMS.OverallScore,"0.00%")

FROM Q LEFT JOIN QMS ON Q.Cupid = QMS.AgentCupid

WHERE Format(Q.AssessmentDate,"mmm yy")="APR 03"
AND Q.Grade In ("G2","G3")

ORDER BY Q.OpsManager, Q.LineManager, Q.Forename & " " & Q.Surname;

the "name" field was just for simplification. i can normally get my head round SQL and/or query design in access but i must admit i'm completely stumped on this one!

here's hoping and cheers again!

 

[danvlas]

Well, why don't you create a query on tableQ to display the info for the month and use that query in the join (instead of table Q)?

Just asking...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[drewby1]

daniel

tried it, works like a charm. cheers for your help

andy