StrSql?? 2

[PGM2]

Can some help with this select statment. Recently I posted an issue and I have been trying to follow the advice but cant get this select string to work.

strSql = "SELECT RepairHistoryTable.SerialNum, DeviceHistoryTable.[On Repair] as OnRepair, Max(RepairHistoryTable.SentDate) FROM RepairHistoryTable INNER JOIN DeviceHistoryTable ON RepairHistoryTable.SerialNum = DeviceHistoryTable.SerialNum" & _
" GROUP BY RepairHistoryTable.SerialNum, DeviceHistoryTable.[On Repair]" & _
" HAVING (((DeviceHistoryTable.[On Repair]= True ));"

Tried also changing the Have portion to:
"Having ((DeviceHistoryTable.[On Repair] = " & True & "));"
but I get expected end of statement.
I am trying to grab all information from tblrepair that whose SerialNum in tblDeviceHistoryTable is on repair and only the last instance if the record in tblRepair.
Thanks!

 

[Aqif]

Hi,

Have you tried like this

Where DeviceHistoryTable.[On Repair]= True
GROUP BY RepairHistoryTable.SerialNum, DeviceHistoryTable.[On Repair]



Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.

 

[oharab]

is the field "On Repair" a text field or boolean?

if it's text I would change it to:

strSql = "SELECT RepairHistoryTable.SerialNum, DeviceHistoryTable.[On Repair] as OnRepair, Max(RepairHistoryTable.SentDate)[COLOR=red] AS MaxSentDate[/color] FROM RepairHistoryTable INNER JOIN DeviceHistoryTable ON RepairHistoryTable.SerialNum = DeviceHistoryTable.SerialNum" & _
" GROUP BY RepairHistoryTable.SerialNum, DeviceHistoryTable.[On Repair]" & _
" HAVING (((DeviceHistoryTable.[On Repair]= [COLOR=red]'[/color]True[COLOR=red]'[/color] ));"

and you seem to be missing the naming of the group.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a

 

[PGM2]

Thanks to everyone who replied but another problem. How can I include the other fields from the "RepairHistoryTable", such as 'RMA'. Since its not part of the aggregate function I recieve an error. Would I create another recordsset and match up the id field and the datestamp that I got from the intial query or perhaps theres an easier way?

PGM2

 

[PHV]

Try something like this:
SELECT R.* FROM RepairHistoryTable R INNER JOIN
(SELECT RepairHistoryTable.SerialNum, Max(RepairHistoryTable.SentDate) AS MaxSentDate
FROM RepairHistoryTable INNER JOIN DeviceHistoryTable
ON RepairHistoryTable.SerialNum = DeviceHistoryTable.SerialNum
WHERE DeviceHistoryTable.[On Repair]='True'
GROUP BY RepairHistoryTable.SerialNum) M
ON R.SerialNum=M.SerialNum AND R.SentDate=M.MaxSentDate

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

 

[PGM2]

Thanks PHV but not sure where you got the M from? Also by stating RepairHistoryTable R...is this creating a alias for repair so that it can be referenced again in the second select statement? Again I must reiterate a thanks as with your help my problem seems to be solved although I need to still test it extensively. One star to you!
PGM2

 

[PHV]

R is an alias for RepairHistoryTable and M an alias for the joined aggregate query.

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

 

[PGM2]

OK so I suppose this is getting a little old PHV but I was just informed that there is more information thats needed in the selecet statement you helped me with earlier. I now need to add other fields from the DeviceHistoryTable. Would my solution be to add a alias to the the already created query and then join that on the DeviceHistoryTable by SerialNum?
So something like:

Select D.PID, D.Warehouse FROM DeviceHistoryTable D INNER JOIN ("code from ur post") F ON D.SerialNum = F.SerialNum;

Would there be a easier way that you can think of? I have studied the statement and tried some things. I even took the statement and placed it in Access query design then added the Devicehistorytable and it ran but of course when trying to place it in the StrSql it errored when trying to create the recordset. Thanks!

 

[PHV]

Quick and dirty (I don't have access at hand for the moment):
SELECT D.PID, D.Warehouse, R.* FROM DeviceHistoryTable D, RepairHistoryTable R INNER JOIN
(SELECT RepairHistoryTable.SerialNum, Max(RepairHistoryTable.SentDate) AS MaxSentDate
FROM RepairHistoryTable INNER JOIN DeviceHistoryTable
ON RepairHistoryTable.SerialNum = DeviceHistoryTable.SerialNum
WHERE DeviceHistoryTable.[On Repair]='True'
GROUP BY RepairHistoryTable.SerialNum) M
ON R.SerialNum=M.SerialNum AND R.SentDate=M.MaxSentDate
WHERE D.SerialNum = R.SerialNum

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

 

[PGM2]

Well placed the select statement in string variable ("strSql") and I am getting error "No value given for one or more required parameters".
Note also that in order for it to work before I had to change the where statement to "WHERE(((DeviceHistoryTable.[On Repair])= True))". Not sure if that makes a difference with the modification of the statement. Will play with statement you gave me some more but any suggestions will help! Thanks!