Qry Help

[Acropolis]

I have a query which I run in SqlYog at the moment, which is below, what it does is return the last values for each we have deployed in the field.

SELECT inboxoboxrecno, inboxmeterserialp1, inboxmeterserialp2,
SUBSTRING_INDEX(SUBSTRING_INDEX(inboxmeterp1,":",1),":",-1) AS Meter1, 
SUBSTRING_INDEX(SUBSTRING_INDEX(inboxmeterp2,":",1),":",-1) AS Meter2, 
inboxreadingtime, inboxtransmittime FROM inboxdata_v2
INNER JOIN (SELECT MAX(InBoxRecNo)InBoxRecNo FROM inboxdata_v2 i  WHERE InboxOboxRecno >= 66 
GROUP BY InBoxOBoxRecNo)tempTbl ON inboxdata_v2.InBoxRecNo = tempTbl.InBoxRecNo
LIMIT 2500

I then export this data and use it in an excel sheet to identify boards which have faults with them. I then create the jobs and issue them to engineers, using the access system I have made.

I am trying to replicate this query to work in access but it doesn't. I have tried recreating the query from scratch to do the same thing as the above, but cant quite get it right. Until I put 'inboxmeterp1' in there it returns exactly what I am looking for, the data relating to the highest 'inboxrecno', but once I enter the above field into the query it then includes everything that the board has sent in. Query below

What I am looking for is just all the data from the last 'inboxrecno', which I will then use to do some checks on.


Access generated query:

SELECT Max(inboxdata_v2.InBoxRecNo) AS InboxRecNo, inboxdata_v2.inboxoboxrecno, inboxdata_v2.InBoxMeterSerialP1, inboxdata_v2.InBoxMeterSerialP2, osirisbox.OBoxSerialNbr, inboxdata_v2.InBoxMeterP1
FROM inboxdata_v2 INNER JOIN osirisbox ON inboxdata_v2.InBoxOBoxRecNo = osirisbox.OBoxRecNo
WHERE (((inboxdata_v2.[inboxOboxrecno])=66))
GROUP BY inboxdata_v2.inboxoboxrecno, inboxdata_v2.InBoxMeterSerialP1, inboxdata_v2.InBoxMeterSerialP2, osirisbox.OBoxSerialNbr, inboxdata_v2.InBoxMeterP1
HAVING (((inboxdata_v2.InBoxMeterSerialP1)<>""))
ORDER BY Max(inboxdata_v2.InBoxRecNo) DESC;

Any help anyone can give would be great.

 

[CJ_London]

comparing the two sqls this line is different

WHERE (((inboxdata_v2.[inboxOboxrecno])=66))

WHERE InboxOboxRecno >= 66

 

[Acropolis]

The first one doesn't have the table name on it as it isn't pulling the serial number from the osirisbox table with it.

 

[namliam]

I believe CJ is revering to the >= vs the =

 

[Acropolis]

That's intentional, I have just narrowed it down on the Access one for the moment to a single board so it returns quicker, than having to trawl through just over a thousand boards.

 

[CJ_London]

Something else that is different Your SqlYog is using a subquery (in red) which your access sql isn't

SELECT inboxoboxrecno, inboxmeterserialp1, inboxmeterserialp2,
SUBSTRING_INDEX(SUBSTRING_INDEX(inboxmeterp1,":",1),":",-1) AS Meter1, 
SUBSTRING_INDEX(SUBSTRING_INDEX(inboxmeterp2,":",1),":",-1) AS Meter2, 
inboxreadingtime, inboxtransmittime FROM inboxdata_v2
INNER JOIN [COLOR=red](SELECT MAX(InBoxRecNo)InBoxRecNo FROM inboxdata_v2 i  WHERE InboxOboxRecno >= 66 
GROUP BY InBoxOBoxRecNo) [/COLOR][COLOR=black]tempTbl ON inboxdata_v2.InBoxRecNo = tempTbl.InBoxRecNo
[/COLOR]LIMIT 2500