Designing MySQL Query for VBA Application

[Kheribus]

Hello all,

I'm having trouble creating a MySQL query that will pull the data that I need into a recordset.

I've designed an application that tracks transit routes. There is table 'tripslog' that logs the route name [tripName], [startTime], [end time], [changeType] ('NEW', 'CHG', or 'DELT'), and [lastUpdate] (date) fields. Essentially an audit log of transit route data over time.

Each distinct route is identified by a tripName that matches a specific pattern.

Route CR01A, CR01B, CR01C
BR01A, BR01B

which all have different last updates.

I have a program loop that calculates the amount of time each vehicle incurred on each day. So, I iterate through currentdate(x).

So, I need a mysql query to put into a recordset each most recent route like 'CR01%' that has the highest lastUpdate that is less than or equal to currentdate(x), and has not been DELETED (ie the most recent distinct tripName changetype <> 'DELT'.


So, find me all routes in the triplog table like 'CR01%' each with the highest current date that is also less than the currentdate(x) in the program loop, and has not been most recently deleted.

Basically - on any given day, find me the most recent trip data on that day associated with this vehicle.

Any idea on how to write a query like this? Would be into a recordset, so
Set recordsetname = dbname.openrecordset("SELECT...")


Thanks,
K

 

[theDBguy]

Hi,


Just curious, which VBA application are you using? If Access, is the table linked to MySQL?

 

[Kheribus]

Yes access vba using linked tables to mysql.

 

[theDBguy]

Ah, perfect. Can you create a query in Access using the query designer for you MySQL table? If so, you can then copy its SQL statement into your VBA.


Hope it helps...

 

[Kheribus]

Do you want my table design or are you suggesting on a way to discover how to write the query? I've been fiddling with this for quite some time.

I have kind of diluted the issue a bit to try to get to the basics of writing the query to select the most recent date of each distinct trip part (that matches the first four characters of the string sequence i've noted), in order to try and figure out how to build around the basics of the query.

 

[theDBguy]

Hi,

Yes. I thought you were having a hard time trying to write the SQL statement from scratch. So, I was suggesting to let Access help you. If you’ve tried that already, can you please post the resulting SQL statement, so we can maybe help you modify it?

 

[sonic8]

So, find me all routes in the triplog table like 'CR01%' each with the highest current date that is also less than the currentdate(x) in the program loop, and has not been most recently deleted.

What is currentdate(x) supposed to be? I assume it is actually the current date incl. time. - If that assumption is incorrect, you need to replace Now() in the following SQL with whatever is appropriate instead.

SELECT *
FROM yourTable y1
WHERE y1.tripName LIKE 'CR01%
AND y1.changetype <> 'DELT'
AND y1.lastUpdate <= Now()
AND NOT EXISTS (SELECT 'x'
                    FROM yourTable y2
                    WHERE y2.tripName = y1.tripName
                    AND y2.changetype <> 'DELT'
                    AND y2.lastUpdate <= Now()
                    AND y2.lastUpdate > y1.lastUpdate
                    );

 

[Kheribus]

Thanks Sonic. The 'NOT EXISTS' didn't occur to me. Ill give this a shot and report back.

Cheers,
K