Last Instance In tbl base on another tbl 1

[PGM2]

Please help I am a newbie and I am stumped. Heres my situation. I have 2 tables that share a common field "SNum". The first one (tblDevices) states whether the SNum is on repair and the second (tblRepairs) holds the repair history of all "SNums". What I need to do is show only the records whose SNum from tblDevice Onrepair is True and the last instance of that SNum from the tblRepairs. Not sure if this helps but in the tblRepair I do have date stamp that I was thinking I could use to help me filter particular SNums from this table. What I ultimately want to do is allow the user to open a form see all SNums on repair and update tblRepair information accordingly. I am not sure what to do but I dont want to use bound field or have the user input any criteria.
Simplified Ex:
tblDevice tblRepair
1212 1212 01/01/04
1212 01/10/04

3626 3626 02/14/04
3626 03/20/04

I am getting all four.

What I want is this in a recordset:
1212 01/10/04
3626 03/20/04

This info would then be displayed and repair info filled in, and saved to tblRepair.
I have tried using Select Distinct but couldnt get it to work??
I then thought why not create two record sets find all SNum from tblDevice whose OnRepair = True during which call a Sub to use these records in the second recordset as criteria and ORDER BY [DateSent] DESC, but then my issue was where to store it all, how to display it, and how to update. As you can see I am a beginner and confused, I am also thinking theres got to be a easier way.
If anyone would be so kind I would appreciate it.
Thank you
PGM

 

[bodmin]

The best way I can think of approaching this problem would be to open the recordset based on an sql query that uses a sub query. ie use a initial query to select those records that are on repair and then nest a sub query within this that selects the latest date for repair.

I know that this is possible but I am unsure on the syntax of the sub query, if you need any further definition on the sub query ask and I will have a look about to find this syntax.

Cheers
Andi

 

[bodmin]

I just had a quick look to refresh myself anyway and this is the type of thing that you will need.

"select top 1 DateSent from tblrepair where SNum In (Select SNum from tblDevices where OnRepair=True) Order By DateSent Desc"

I think that a query along these lines should provide your recordset with the records that are desired

 

[mordja]

Hey,

Why not just join the two tabes on id, do a group by and select those where tdlDevice.onrepair = "Y" and select the max of date.[ie]


SELECT tblRepair.id, tdlDevice.onrepair, Max(tblRepair.date) AS MaxOfdate
FROM tblRepair INNER JOIN tdlDevice ON tblRepair.id = tdlDevice.id
GROUP BY tblRepair.id, tdlDevice.onrepair
HAVING (((tdlDevice.onrepair)="Y"));


Works fine given the above data set. Not sure if thats what you are after.

Mordja

 

[PHV]

My guess:
SELECT R.SNum, Max(DateSent) AS LastRepair
FROM tblRepair R INNER JOIN tblDevice D ON R.SNum = D.Snum
WHERE OnRepair = True
GROUP BY R.SNum;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PGM2]

I thank every one for posting to my question . I tried this
strSql = "Select ...inner join....etc." & _
" GROUP BY RepairHistoryTable.SerialNum, DeviceHistoryTable.[On Repair]" & _
" HAVING (((DeviceHistoryTable.[On Repair]= True ));"

But got syntax error for the "Having" portion. I see there are two )) so I added one ) but error.
Tried ...[On Repair] = " & TRUE, but got expect end of statement.
This field is a boolean field. Any other ideas on correction?
Thanx Again!

 

[jedraw]

Here is a solution:

SELECT tblRepair.id, Max(tblRepair.repairdate) AS MaxOfrepairdate
FROM tblRepair INNER JOIN tblDevice ON tblRepair.id = tblDevice.id
WHERE (((tblDevice.[on repair])=True))
GROUP BY tblRepair.id;

 

[PGM2]

Hey Jedraw thankyou but I have one more question! If I have other fields that I need to include from the tblRepair can I just place them in the first portion of my select statement? I will give you answer and try but if doesnt work it would be nice to have an answer to fall back on.
Thanks!

 

[PGM2]

Ok so when I used this expression I got the right number of records:

SELECT tblRepair.id, Max(tblRepair.repairdate) AS MaxOfrepairdate
FROM tblRepair INNER JOIN tblDevice ON tblRepair.id = tblDevice.id
WHERE (((tblDevice.[on repair])=True))
GROUP BY tblRepair.id;

But as soon as I started to add the other fields that I need from tblRepair I got an error stating...
"You tried to execute a query that does not include the specifed expression 'RMA' as part of an aggregate function."

I'm so close!!!
Thanks though

 

[PHV]

And what about something like this ?
SELECT R.* FROM tblRepair R INNER JOIN (
SELECT tblRepair.id, Max(tblRepair.repairdate) AS MaxOfrepairdate
FROM tblRepair INNER JOIN tblDevice ON tblRepair.id=tblDevice.id
WHERE tblDevice.[on repair]=True GROUP BY tblRepair.id
) M ON R.id=M.id And R.repairdate=M.MaxOfrepairdate
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244