Strange result from Query: Please help (1 Viewer)

sparkyboy2406

Registered User.
Local time
Today, 04:09
Joined
Feb 15, 2010
Messages
25
Hi Guys - any help is greatly appreciated.

I have a customer table and a calls table.

I want a query that will show show a customers most recent call. The result must show all the fields in both tables.

SELECT Master.GUID,Master.BISource, Master.Name, Master.Postcode, Max(Calls.CallDate) AS LatestCallDate, calls.Calltime
FROM Calls Left JOIN Master ON Calls.guid=Master.GUID
GROUP BY Master.GUID,Master.BISource, Master.Name, Master.Postcode, Calls.calltime

This code generates all records and does not filter the latest call. but if i remove Calls.calltime field. It will work.

There are numerous other fields in the calls table but i have found that if i include any other field apart from the max function in the query above the query returns all records.

Any ideas?

Thanks in advance for the help
 

John Big Booty

AWF VIP
Local time
Today, 22:09
Joined
Aug 29, 2005
Messages
8,263
The problem I'm assuming is that your calls contain various values for Master.Postcode so can therefore not be grouped. So you are getting Max(Calls.CallDate) for each distinct value in Master.Postcode

You will need to do this in two steps. You will first need a query that selects Max(Calls.CallDate) and Master.GUID, you will then need to use the result of this query in a second query that then grabs the rest of the data you require, linked via the Master.GUID.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:09
Joined
Jan 20, 2009
Messages
12,849
BTW, Change the fieldname "Name" to something else. Name is a reserved word. This can potentially cause intractable problems for you later, particularly in VBA, because it may refer to the name property of the table instead of a field within it.
 

sparkyboy2406

Registered User.
Local time
Today, 04:09
Joined
Feb 15, 2010
Messages
25
Thanks for the replies guys?

can you give me an example of the SQL to complete this task? how you use the result of one query in another query?

Thanks
 

DCrake

Remembered
Local time
Today, 11:09
Joined
Jun 8, 2005
Messages
8,632
Sparky
Can you send a sample of your data to look at. This may make our task easier.
 

sparkyboy2406

Registered User.
Local time
Today, 04:09
Joined
Feb 15, 2010
Messages
25
David,

I will post this over the weekend. The database is very big so i will have to alter this a bit.

thanks
 

sparkyboy2406

Registered User.
Local time
Today, 04:09
Joined
Feb 15, 2010
Messages
25
Hi Guys

Please find attached a sample of the database.

Any help is greatly appreciated

Thanks
 

Attachments

  • sample.mdb
    156 KB · Views: 67

John Big Booty

AWF VIP
Local time
Today, 22:09
Joined
Aug 29, 2005
Messages
8,263
I think Query2 is what you are looking for.
 

Attachments

  • sample.zip
    12.9 KB · Views: 76

sparkyboy2406

Registered User.
Local time
Today, 04:09
Joined
Feb 15, 2010
Messages
25
thanks for that John.

but what i need is all columns from both tables.

When i try to add the calls.calltime to either query it will either show all records or as me to enter a parameter value.

for the query that you have given me at the minute i want to be able to add the other columns in the table. so i can see the calls.calltime and calls.callsidsync that is matched to the most recent call.

Thanks again for the help and any further assistance you can offer is greatly appreciated.


thanks
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Sep 12, 2006
Messages
15,614
you cant do this with group by = you would think there was a way to add extra fields as information, but there isnt - it either gives you an error (field not part of an aggregate function) - or gives you multiple values in the query

one way to do it for a single customer, is to have a query of calls sorted in decending date order, then the top items will have all the info for the latest call - this only works for a single account though.
 

sparkyboy2406

Registered User.
Local time
Today, 04:09
Joined
Feb 15, 2010
Messages
25
Hi,

thanks for the reply. I am sure there must be a way to show all the fields of the most recent call for each customer from the calls table as well as the customer details from the master table.

Anyone else have anymore ideas?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:09
Joined
Jan 20, 2009
Messages
12,849
Do the aggregate query first then join the rest of the details back to the results using a join on the CustomerID and the date/time of the call.

This can be done as a subquery or two separate queries.
 

sparkyboy2406

Registered User.
Local time
Today, 04:09
Joined
Feb 15, 2010
Messages
25
Hi, thanks for the reply,

can you give mr an example of how the query will look.

I am a bit of a novice at this.

Any help is greatly appreciated! Thanks
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:09
Joined
Jan 20, 2009
Messages
12,849
Easiest way is to design the aggreate query with only the CustomerID and Call Date/Time fields.

Then do another query based on the original table and the aggregate query.
 

sparkyboy2406

Registered User.
Local time
Today, 04:09
Joined
Feb 15, 2010
Messages
25
Hi,

if i use the query below i get the correct results.

SELECT DISTINCTrow calls.guid, Max(calls.CallDate) AS MaxOfCallDate
FROM calls
group by calls.guid;

but when i add calls.callsidsync or calls.calltime to that query it prints out all the records.

The main aim of the task is to show the latest call made to the customer. and i need to see all fields in the master and calls field.

can you tell me where i am going wrong?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:09
Joined
Jan 20, 2009
Messages
12,849
Combine the CallDate and CallTime fields into one Date/Time. If they are already Access Date and Time fields you can simply add them together and convert the result back to DateTime format with CDate function.

Otherwise depending on the format they are currently using you would probably need the Left, Right, Mid, DateSerial and TimeSerial functions to achieve this.

Then get the Max of the combined DateTime field.

Then in the next query, pull the other fields back in using a join on the DateTime and guid fields which will now uniquely define a call record.
 

Users who are viewing this thread

Top Bottom