Selecting Correctly Dated Values from Audit Table Log (1 Viewer)

[Kheribus]

Hello All,

I have an audit table that tracks changes to 'transit routes'. The table is of the order:

Name, ChangeType, ChangeDate

CR01A, New, 11/5/2015
CR01AP, New, 11/5/2015
CR01AP, Chg, 11/7/2015
CR01AP, Chg, 11/8/2015
CR01B, New, 11/5/2015
CR01BP, New, 11/5/2015
CR02A, New, 11/5/2015
...
CR0#x, New, 11/5/2015

These three fields are a composite key. There is other data associated with these rows but for the purpose of this example I only need to show these three fields.

Now, my invoicing application cycles through a user specified date range, lets say 11/5 -> 11/8, for this example.

First, I want to load into a recordset all the trips with the name Like CR01% <= the current invoice date in the loop.

So Iteration 1 (11/5/2015) should return, in a recordset:
CR01A, New, 11/5/2015
CR01AP, New, 11/5/2015
CR01B, New, 11/5/2015
CR01BP, New, 11/5/2015

Iteration 2 (11/6/2015) should return, in a recordset:
CR01A, New, 11/5/2015
CR01AP, New, 11/5/2015
CR01B, New, 11/5/2015
CR01BP, New, 11/5/2015

Iteration 3 (11/7/2015) should return, in a recordset:
CR01A, New, 11/5/2015
CR01AP, Chg, 11/7/2015
CR01B, New, 11/5/2015
CR01BP, New, 11/5/2015

And so on...

Basically what we are doing here is determining if there is a newer entry to analyze for each iteration (day) of the invoice period, and return a set, in this case of route CR01, that is the newest version for that given day of the loop.

This is the code that I thought would work to accomplish this, but there is a caveat:


Set rsTemp = invoicing.OpenRecordset("SELECT * FROM tripslog WHERE [tripName] LIKE '" & shortTripName1 & "%' AND [lastUpdate] = (Select Max([lastUpdate]) from tripslog WHERE [lastUpdate] <= #" _
& invoiceDateArray(i) & "# AND [tripName] LIKE '" & shortTripName1 & "%')") 'find the most recent set of trips that have the same form 'cr01..' from trip table

(shortTripName1 in this case is CR01)

It works when all of the dates are the same, but on iteration 3 (11/7) it is returning just CR01AP into the recordset. This has to do with the subquery lastupdate = select max(lastupdate).

Perhaps just a simple MySQL query will not accomplish what I hope (to grab all the trips from the table with the same form 'CR01' that are most recent to the date in the invoiceDateArray on each iteration. Maybe I need to post process in the recordset? I'm kind of stumped.

 

[jdraw]

I'd like to see a plain English description of what you are trying to do. It seems (to me) you haven't really described WHAT you are trying to do, you have shown us HOW you have tried to do something. And it is the something we need to understand.

 

[Kheribus]

Sure. I'll be back at my computer in ten and I'll type it out.

 

[Kheribus]

Ok. So I am doing an invoice generation from a "trip table" for transit.

Each vehicle has a name of the order CR01, CR02, etc... They are associated with schools and those schools have vendors, contracts, pricing, base rates, etc... associated with them. The trip table metrics can change on any day, so I have a subroutine to determine when metrics change, and if they do, to add a CHG record to the audit table with a change date.

So, my program will evaluate each 'route,' CR01 in this instance, and pull up all movements on a given day, where I will then calculate total time, cost, etc, for every day for every route.

That's why it's important for me to pull into a temp recordset all information for route CR01 on each day of the invoice cycle, so I can do calculations on the route time, loads, mileage, etc.. as that can change on a daily basis.

I have all the internal calculations worked out, all I need to figure out how to do is pull the relevant data (most recent entries from the audit table) on each invoice day to do calcs on it and spit it out to the invoice statement.

 

[Kheribus]

Ok. So since I can't figure out how to do this with a single query, I am trying to create a temp table that will store all the trip data of type 'CR01' with dates <= my invoicedatearray(i):

invoicing.Execute ("SELECT * INTO temptable FROM tripslog WHERE [tripName] LIKE '" & shortTripName1 & "%' AND [lastUpdate] <= #" & invoiceDateArray(i) & "#")

Now, I'm going to try to write a recordset query to store only the rows with distinct tripname and the greatest date. That will satisfy what I've been asking for in the original post.

 

[jdraw]

From your post #4, it appears that

You generate invoices related to transit based on trips.
The trips involve vehicles and related metrics which change frequently.
You calculate total time, cost, etc, for every day for every route.

You say

I have all the internal calculations worked out

.

I think we're still waiting to hear more about:

all I need to figure out how to do is pull the relevant data (most recent entries from the audit table) on each invoice day to do calcs on it and spit it out to the invoice statement.

Good luck with your project.

 

[Kheribus]

Sigh. I sense a hint of frustration coming from your post (maybe?), which I can absolutely understand, as it is quite complicated and time consuming for a poster articulate the specifics of a project. Yet, that is what you should expect, as you are the helper, and trying to fix something that you don't quite understand is frustrating at best.

Let me try to re-explain:

I have a tripslog table, my audit table, that checks a hosted file and automatically adds a new line with a 'chg' changeType field when one of the columns in the hosted table changes, adding a timestamp. I generate invoices over a range of dates by checking what the columns are in the tripslog (audit table) on every single day of the invoice window.

So, I know the background math that I have to operate (it's rather simple) to generate a dollar value for every trip on every day. All i'm trying to do here is to distinguish which were the active trips in my tripslog on each day of the invoice cycle.

So, I intended to accomplish this by cycling through each unique name identifier (of type 'CR01%' in this first example, and loading the 'current' metrics for each trip associated with CR01 on the invoice date I am evaluating.

As such, I should only return one line per unique trip id of form 'CR01%' on each day. I feel like that should be explained kind of clearly in the first post.

I threw this together and it seems to be working now:

Step 1: Create a temp table with values from 'CR01' trip on or before the invoice date I'm looking at.

invoicing.Execute ("SELECT * INTO temptable FROM tripslog WHERE [tripName] LIKE '" & shortTripName1 & "%' AND [lastUpdate] <= #" & invoiceDateArray(i) & "#")

Step 2: Create a recordset from this temporary table that grabs the tripname with the highest date.

Set rsTemp = invoicing.OpenRecordset("SELECT a.* FROM temptable a LEFT OUTER JOIN temptable b ON a.tripName = b.tripName AND a.lastUpdate < b.lastUpdate WHERE b.tripName IS NULL;", dbOpenSnapshot)

I'm still testing, but I think this accomplishes what I want, in only returning one line per unique trip ID associated with the pattern 'CR01%' with the highest date that is <= my invoiceDateArray(i).

Sorry for being vague and trying to cut corners by not completely explaining my project.

 

[jdraw]

Sigh. I sense a hint of frustration coming from your post (maybe?), which I can absolutely understand, as it is quite complicated and time consuming for a poster articulate the specifics of a project. Yet, that is what you should expect, as you are the helper, and trying to fix something that you don't quite understand is frustrating at best.

Yes, a little.
If you read through posts in most threads, you'll find it takes about 6-10 posts before there is a real sense of communication. We often see a neophyte trying to impress us with quasi-vba or quasi-Access SQL syntax to prove or show something.Usually there are some obvious errors in terminology or feature use so as to totally confuse most readers.

In your case, and in many others, it would be good to just describe the problem/opportunity in plain English. No ACCESS, minimal jargon. I often say, "If you met someone in a McDonalds line up who you do not know, who does not know database, nor Access, nor your business, and he asked you -- so what's the current problem?" What would you say? That's the level to get readers familiar with your issue.
Then, tell us what you have done -- tables, relationships..... What worked or what failed, now some detail. Perhaps some jpgs of screens, some code..... in due course.

Glad to see your efforts and tests are accomplishing what you need.

Good luck.

 

[Kheribus]

I get it man. I really do. I think in a lot of instances people come here in desperation, so deliberate and well thought out questions may be rarer than everyone would hope. I will only get better, and really appreciate your and all the other vets' patience in matters like this.

I thought that I could circumvent the large picture explanation by explaining exactly what I needed to do in this step of the project in post 1, but I realize that is a bit flawed and at the very least people don't appreciate that kind of questioning around here.

In summation - thanks again man. Next time (which I hope I won't have to come here begging for help quite _that_ much), I will frame my question with a backstory/purpose.

 

[jdraw]

Thanks for responding. It wasn't meant to be negative, but more to show some experiences and to put your post into some context. Don't be a stranger to this forum. We all can learn.