Query table relationship / select headache 1

[1DMF]

Hi,

I have a requirement to create a query that links two tables together however, I want all records from the first table (within a date range), but only the latest record from the call logs table.

How do you define the query for this?

I've tried creating a linked query where the call logs has 'select TOP 1' , hoping it would get all records from the customer table and then the top 1 records for each customer from the calls log , but this actually only gets 1 record from the calls log table, not one record for each customer record, which I had hoped.

is there a way to do this via linked querries or do i need to use VBA coding to achieve this?

Cheers, 1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

SELECT Contacts.CompanyName, Contacts.Current_Principal, Contacts.Effective_Date, Contacts.Add1, Contacts.Add2, Contacts.PostalCode, Contacts.WorkExtension, Contacts.Notes, Contacts.Prospect_Cat, EX_Pros_Last_Call.Subject, EX_Pros_Last_Call.Notes
FROM Contacts LEFT JOIN EX_Pros_Last_Call ON Contacts.ContactID = EX_Pros_Last_Call.ContactID
WHERE (((Contacts.Current_Principal) Like nz([forms]![Export_Prospects].[Network],"*")) AND ((Contacts.PostalCode) Like nz([forms]![Export_Prospects].[PostCode],"*")));

The query EX_Pros_Last_Call SQL is

SELECT TOP 1 Calls.ContactID, Calls.Subject, Calls.Notes
FROM Calls
ORDER BY Calls.CallID DESC;

Now I know running EX_Pros_Last_Call on its own would only get one record, but my logic (right or wrong) is I have a left join so I want all records from contacts (based on search criteria), and then link in the TOP 1 record for each contact record from the 'calls' table.

it's not working though, can you see my error?


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[PHV]

SELECT ContactID, Max(CallID) AS LastCall
FROM Calls
GROUP BY ContactID

SELECT A.CompanyName, A.Current_Principal, A.Effective_Date, A.Add1, A.Add2, A.PostalCode, A.WorkExtension, A.Notes, A.Prospect_Cat, B.Subject, B.Notes
FROM Contacts AS A LEFT JOIN (
  SELECT X.ContactID, X.Subject, X.Notes
  FROM Calls AS X INNER JOIN EX_Pros_Last_Call AS Y
  ON X.ContactID = Y.ContactID AND X.CallID = Y.LastCall
) AS B ON A.ContactID = B.ContactID
WHERE A.Current_Principal Like Nz([Forms]![Export_Prospects]![Network],'*')
AND A.PostalCode Like Nz([Forms]![Export_Prospects]![PostCode],'*')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

spot on thanks PHV.

Don't suppose you can help with the next problem

I need to allow category selection, but they requested the form front end allow a csv in the field, so the could type...

C,D,E

, this obviously causes me a headace turning that into a query.

No I created a hidden field on the form and used VBA to do a split on the form field, and created the following...

'C' OR 'D' OR 'E'

and put that in the hidden field, then on the query I put [forms]![formname].[fieldname] , only it isn't apply it as 'c' OR 'd' etc.. I beleive it is looking for a text match of the value of the hidden form field.

I've tried to wrap it in an eval command , but still no joy.

how can I use a filed to hold a query criteria that needs to be processed rather than simply being a string match?

hope that makes sense!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[PHV]

This is a brand new topic, so I suggest you start a new thread.

 

[1DMF]

I've given up with it, I don't see how it's possible and so now i'm going to use the SQL code and run it via VBA to generate a record set, the Access Query Deisgner is a pain in the arse!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[PHV]

I don't see how it's possible
With the InStr function ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

tried that, it just errors with

expression entered is either type incorrectly or is too complicated to evaluate

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!