Select only one record from a subset 2

[Smarty]

I have 2 tables: CALL and History with a one to many relation ship between the two
How can i make a query where i get the call records with only the history record joined with the highest historyid?

 

[CosmoKramer]

Look in Access help for the Max function.....

 

[THoey]

Does this work:

SELECT CALL.CallID, MAX(HISTORY.HistoryID)
FROM CALL, HISTORY

If not, give us your table structures... Terry M. Hoey

 

[Smarty]

dbo_CALL
----------
cacallid ( primary key)
castartdate
cacurrentstate
cacustomerid
... (i need all fields)

dbo_HISTORY
----------
hicallid (primary key)
hihistoryid (primary key)
hidate
... (i need all fields)

 

[THoey]

Well, did you try modifying what I gave you to meet your requirements and run it? Not trying to be mean, but even though this is an EXCELLENT site for help, we can't do it all or we would have to bill you... Terry M. Hoey

 

[Smarty]

Hey i tried this one:
SELECT dbo_CALL.caCallID, MAX(dbo_HISTORY.HistoryID)
FROM dbo_CALL, dbo_HISTORY

And got this message:
You tried to execute a query that does not include the specified expression 'caCallid' as part of an aggregate function.

I did try but with this one i don't know where to start at all...

 

[THoey]

Woops, I forgot that you need to group:

SELECT dbo_CALL.caCallID, MAX(dbo_HISTORY.HistoryID)
FROM dbo_CALL, dbo_HISTORY
GROUP BY dbo_CALL.caCallID

Terry M. Hoey

 

[Smarty]

This works but not how i really want it. Problem is that the dbo_history table contains a primary key consisting of two fields (callid and history id). So you can have multiple records where historyid = 1 but where the callid is different. What happens now is that for each record selected (with a unique callid), only the historyid (not history record) with the max historyid is shown without looking at the callid...

 

[tlbroadbent]

The key to writing queries is to break the problem down to steps.

1) Determine the highest historyid per callid. This requires an aggregate query or in Access terminology, a Totals query.

SELECT hicallid, max(hihistoryid) AS maxhistid
FROM dbo_HISTORY
GROUP BY hicallid

2) Select the records from History that have the highest historyid. I've chosen to do this by JOINing the History table to the query created in step one. You could use other query techniques.

SELECT h.HiCallID, h.HiHistoryID, h.HiDate
FROM dbo_HISTORY AS h
INNER JOIN
[SELECT hicallid, max(hihistoryid) AS maxhistid
FROM dbo_HISTORY
GROUP BY hicallid]. AS q
ON (h.HiCallID = q.hicallid)
AND (h.HiHistoryID = q.maxhistid)

3) Create a final query that adds the Call table data to the History data selected. So far as I can determine, the following query will select the rows and columns you need.

SELECT
c.CaCallID, c.CaStartDate, c.CaCurrentState,
c.CaCustomerID, h.HiCallID, h.HiHistoryID, h.HiDate
FROM dbo_CALL AS c
INNER JOIN (dbo_HISTORY AS h
INNER JOIN
[SELECT hicallid, max(hihistoryid) AS maxhistid
FROM dbo_HISTORY
GROUP BY hicallid]. AS q
ON (h.HiCallID = q.hicallid)
AND (h.HiHistoryID = q.maxhistid))
ON c.CaCallID = h.HiCallID;

I note that the "dbo_" prefix on the tables. If these are linked SQL Server tables, you will find it more efficient to create this query in SQL Server as a View and then link the view or select from the view using a pass-through query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Smarty]

My god!!

I tried this and my first opinion is that i got what i want! Congratulations and again, many thanx for the clean and detailed solution.

A happy Smarty