SQL query on displaying distinct data

[hunoob]

Hi Everyone! I am quite new to Access/SQL and I hope you can help me.

I have two tables:
tbl_client_data
tbl_calls

The clientID is the foreign key in the tbl_calls.

I make a search on the clients and their respective calls. Of course one client can have many calls (not the other way around).

SELECT tbl_client_data.ClientID, tbl_client_data.Nev, tbl_client_data.Telefon, tbl_calls.visszahivast_var_datum, tbl_calls.operator_ID, tbl_calls.Call_log
FROM tbl_client_data
LEFT JOIN tbl_calls
ON tbl_client_data.ClientID = tbl_calls.ClientID;

But actually I would like only one call displayed for one customer. Which call it does not really matter. But most importantly I would like one call to be displayed per client.
This is very frustrating but I cannot really make up a query which displays only one call/client. I tried group by also, no avail.

My latest script:

SELECT tbl_client_data.ClientID, tbl_client_data.Nev, tbl_client_data.Telefon, tbl_calls.visszahivast_var_datum, tbl_calls.operator_ID, tbl_calls.Call_log
FROM tbl_client_data
LEFT JOIN tbl_calls
ON tbl_client_data.ClientID = tbl_calls.ClientID;

For this script I get this error:
You have written a subquery that can return more than one field without using EXISTS reserved word in the main querys FROM clause. revise the select statement of the subquery to request only one field.

Could you please help me?

Thank you in advance!

 

[plog]

You need a subquery. And an autonumber primary key in tbl_calls to uniquely identify a record. Let's hope you have a field like that and assume its named CallID. Here's the SQL for your subquery (let's call it 'sub1'):

SELECT ClientID, MAX(CallID) AS LastCall FROM tbl_calls GROUP BY ClientID;

Then you build your main query, it will consist of tbl_client_data, sub1 and tbl_calls. You would link tbl_client_data to sub1 via the ClientID fields and you would link tbl_calls to sub1 via the LastCall and CallID fields in each. You then bring down all the data you want and run. It will generate the data you desire.

 

[CJ_London]

or you can use a group by query, grouping on all fields reporting from tbl_client_data and select first (or last, min or max) for the field you want from tbl_calls

 

[hunoob]

You need a subquery. And an autonumber primary key in tbl_calls to uniquely identify a record. Let's hope you have a field like that and assume its named CallID. Here's the SQL for your subquery (let's call it 'sub1'):

SELECT ClientID, MAX(CallID) AS LastCall FROM tbl_calls GROUP BY ClientID;

Then you build your main query, it will consist of tbl_client_data, sub1 and tbl_calls. You would link tbl_client_data to sub1 via the ClientID fields and you would link tbl_calls to sub1 via the LastCall and CallID fields in each. You then bring down all the data you want and run. It will generate the data you desire.

Dear plog!

Thank you very much for your help!

Indeed I have a call id field and it is named CallID.

I tried to rewrite the scirpt according to your suggestion and I came up with the following:

SELECT tbl_client_data.ClientID, tbl_client_data.Nev, tbl_client_data.Telefon, tbl_calls.visszahivast_var_datum, tbl_calls.operator_ID, tbl_calls.Call_log
FROM tbl_client_data
LEFT JOIN (SELECT ClientID, MAX(CallID) AS LastCall 
                  FROM tbl_calls 
                  Left Join p
                  On p.LastCall = tbl_calls.CallID
                  GROUP BY ClientID) as p
ON tbl_client_data.ClientID = tbl_calls.ClientID;

Unfortunatelly I am doing something wrong because I get a syntactic error (on JOIN operation) therefore the script doesnt work. I am confused what to do and how to go further.
Could you please guide me what am I doing wrong.

Thank you very much in advance!

 

[plog]

I think your trying to eat your elephant all at once. Break it into bites. Build that first query I described, then build that second query I described on it.

In your subquery you have a JOIN, I don't know what that is to achieve. I gave you the exact SQL for that subquery, don't know why you added to it.

 

[hunoob]

Dear plog

Sorry for being away but meanwhile I received a very urgent project and I also was on holiday therefore I could not work in this issue.
However now I am back and as this script is still not running I have to keep working on it.
Thank you for your reply first of all.
I tried to run the subquery and it works perfectly. This part is OK, thank you.
Then I linked the subqery to the main query:

SELECT tbl_client_data.ClientID, tbl_client_data.Nev, tbl_client_data.Telefon
FROM tbl_client_data
LEFT JOIN (SELECT ClientID, MAX(CallID) AS LastCall FROM tbl_calls GROUP BY ClientID) as p 
ON tbl_client_data.ClientID = p.ClientID;

This one also works very good.
And here I am stuck. I really dont know how to add the tbl_calls table to this query. This is probably for me beeing noob to SQL. This is the query I tried:

Select tbl_calls.visszahivast_var_datum, tbl_calls.operator_ID, tbl_calls.Call_log
from tbl_calls
left join (SELECT tbl_client_data.ClientID, tbl_client_data.Nev, tbl_client_data.Telefon
FROM tbl_client_data
LEFT JOIN (SELECT ClientID, MAX(CallID) AS LastCall FROM tbl_calls GROUP BY ClientID) as p 
ON tbl_client_data.ClientID = p.ClientID;) as q
on q.LastCall = tbl_calls.ClientID;

But as I said it is not working.
Could you please guide me how to proceed further.
Thank you in advance!

 

[plog]

Again, you are taking too big of a bites and trying to cram everything onto your plate at once. You need to quit writing subqueries inline and make them their own objects.

SELECT ClientID, MAX(CallID) AS LastCall FROM tbl_calls GROUP BY ClientID

Paste that into a query object and name it 'sub1'. Then in whatever query you need to use it, you use 'sub1' instead of the above code.

You should only have 1 SELECT clause in a query--it will make your life easier.

 

[hunoob]

Wow! Just wow! Finally I was able to understand your point and I was able to break down the queris in query objects and then reference them in one main query. And it works just the way I wanted!
Thank you soooo much for your help! You're a real lifesaver! Thank you so much!