Select Record Based On The Time Of Another Record

[bazzason]

hi guys,

some background on my data:

i have a table that holds Electronic Gift Card details (those plastic store gift voucher cards). it holds all details of every transaction for every card, so the card number, activations, redemptions, dates, times, transaction values etc. are all recorded in my table.

i have attached a small extraction in a spreadsheet.

a single gift card can have multiple transactions against it throughout its lifespan. there are sometimes error transactions, and i need to write a query that finds these error transactions (they are all flagged with a "1" in the [reversal flag] field, so that bit is easy) but then also then the next transaction that occurs on that card. this subsequent transaction will not be flagged with a "1", but is a reversal of the error and will have an "802" flag in the [transaction type] field.

each transaction is date stamped hh:mm:ss, but i don't know how to write a query that will

1) find gift card number that has a reversal code of "1"
2) find next transaction made on that gift card based on transaction time
3) check that subsequent transaction type is "802"
4) select both transactions
5) repeat for entire table

i can't seem to get the desired result just using a straightforward query, and i don't know much (or any!) VBA in access.

does that make any sense at all???

cheers
bazzason

 

[Jon K]

See the query in the database.

Hope it's what you needed.
.

 

[bazzason]

thanks jon, but in the full database there are lots of other "802" transactions that are perfectly valid, and this query would select those too.

that is why i need to select based on time, as every "1" transaction would be immediately followed by an "802" that needs to be removed (but only those particular "802"s, no others).

thanks again for your help.

 

[The_Doc_Man]

bazzason, first let me throw a monkey wrench into the works. Then I'll tell you approaches that might help you. But you need to understand why what you are doing is so hard.

You see, Access and all other databases are deeply rooted in SET theory, for which the word "Order" has no particular meaning. Your records will appear in no particular order when you open the table.

Queries, having the ability to impose order, can help. BUT - set theory jumps in again. Queries, which are merely recordsets (note ... record SETS) have no inherent order either, despite the ability to use keywords like ORDER BY (in the SQL query) and ASCENDING or DESCENDING (in the query grid). Therefore, you have a significant absence in the syntax of tables and queries - keywords NEXT and PREVIOUS don't exist because (due to rooting in set theory) neither do the concepts of 'next record' and 'previous record.'

So... how do you get there from here? You have to build a structure that allows you to manipulate records in a way that lets you store and later use data from prior records. VBA can do this. Forms can also do this if you include some VBA underneath the form. The table or query will not have syntax to remember NEXT and PREVIOUS but VBA allows you to pick values out of a record and hold them for consideration at a later time.

Therefore, to do this right and infallibly, study your VBA. Look at recordset manipulation. Think about ways to trap the condition you seek. What you describe is not at all impossible - but it is infernally difficult if you don't go the way of VBA code that manipulates recordsets.

 

[bazzason]

ok, i can just about get my head around that! the problem i face is having no VBA skills at this point in time...

i have an awful lot of data in each record, including a time stamp. i keep thinking there must be a way of applying order in a seperate field using the existing data, but of course that wouldn't solve the problem as 'order' doesn't exist.

so frustrating...! i'll definitely look into Recordset Manipulation.

out of interest Mr Doc Man, do you have a real idea of how this could work using VBA, or were you just thinking 'out loud'? i know some people would be reluctant to just hand me information without making me think about it first (which i respect)...

 

[The_Doc_Man]

Well, first things first. There are same databases posted on this forum and (depending on how you installed it) with Access that manipulate recordsets.

Get ready to browse the Access help files a lot so you can look up the terms I mention. I'll drop (q.v.) markers where a keyword is used you can look up. In particular, where there is an example link in the help files, follow it. Read it. You will be surprised at how simple some of this really is. The problem, of course, is that it is a new environment for you.

Here is a POSSIBLE approach to consider.

Consider the order of processing something. As long as you don't say SQL, you can say NEXT/PREVIOUS. (Once you say VBA and SQL, you can also say NEXT/PREVIOUS. Just SQL by itself is the bugaboo.)

You define the order so you can build the SQL query with appropriate sort order indication. If you did something like GROUP BY (q.v.) credit card number, ORDER BY (q.v.) date/time stamp, that might help. Build the query to hold what you will need for your processing and to show it in the order that you need. Do the GROUP BY first, ORDER BY second. Also, note that a GROUP BY implies an ORDER BY.

OK, you will do this in a module. You have two choices. I don't know your preferences, so I'll give you the overview. You can either build this in a module designed to be run from a form or from a macro. From the form, you would trigger your code based on some form event (q.v.), such as a button-click. From a macro, you would use the RunCode (q.v.) action. I would use a form, but that is just my preference. The form would be unbound (q.v.) so there would be no chance of me changing the recordset by a clumsy hand operation through it. I HAVE been known to fat-finger forms.

Inside this code, you would define a DAO (q.v.) recordset (q.v) and open it using the OpenRecordset (q.v.) method on the query you defined earlier. Since you are opening a query and might wish to update something, open it as a DynaSet. (q.v.) Before you do much else, reset the recordset to the beginning via the recordsetvariable.MoveFirst method. (q.v.) Later, let me call the recordset variable RSV. Just a name.

OK, now you are ready to start a loop. So at the top of the loop, before you begin iteration, reset some variables local to the module so you can remember some things from one record to the next. You know how the data elements are stored in your table so make the temporary holders in the module have the same data type. For TEXT fields, the corresponding VBA type is STRING. For Yes/No fields, the corresponding VBA type is Boolean.

Reset the variables to a default state. Now we start the loop.

You earlier did a RSV.MoveFirst outside the loop, so there should be a record already selected. You can get the fields from that record either of two ways. RSV.Fields("FieldName") gets the named field. So does RSV!FieldName.

Compare your stored credit card number (CCN) to the recordset's value. If they are not the same, reset the values before proceeding because you have changed to a new sequence.

If the stored CCN and record's CCN match, you are on the same card's history so can proceed. Now see if you made a mark for the previous record. If not, look at this code flag that can be 1 or 802 or whatever. You will have several cases and a few sub-cases.

If the previous record (for which you stored the code) was nothing special, you can test whether this record is marked with a 1. If so, you mark the start of a sequence of interest. Store whatever else you need to store and drop to the bottom of the loop.

If the previous record was marked with code 1, test whether this one was marked with code 802. If so, you now know the previous contents (you DID store them, didn't you?) and and the current contents. If not, you have found a card for which a correction has not yet been entered. (Error sequence?)

If neither is the case, you have an ordinary transaction. Do what you need for that one, too.

At the bottom of the loop, you would check for (q.v.) RSV.EOF = TRUE and, if not, do an RSV.MoveNext (q.v.) and go to the top of the loop again.

ALWAYS, ALWAYS, ALWAYS when done with a recordset (as in, .EOF was TRUE), close what you open. Finish by setting the recordset to Nothing (a keyword, q.v.) You are done.

Now, frills and embellishments? You can open a second recordset or an ordinary file to store error messages such as finding a 1 record not followed by an 802 record. And other errors you might find. Close those when done, too.

 

[bazzason]

oh....




my....




god!!!

thank you so much for that. it'll take me a while to go through it all and if i have any other questions - highly likely - i'll post them (it just shows how steep the learning curve can be)...

 

[The_Doc_Man]

Here, learning curves can be multi-dimensional.

Learning VBA, learning RECORDSET operations, trying to coordinate SQL queries to feed the recordsets properly, learning how to whip up a quick design, think it through a few iterations, ... these are things you eventually learn as you get more experienced. And in this environment, experience is the best teacher. Unfortunately, Professor Experience works for the College of Hard Knocks. But hey, the harder the knock, the better the lesson.

I learned problem analysis a long time ago. The key is to break things down into steps until each step is small enough that you understand it. Which is why I put paragraph breaks in the previous part between major step divisions.

Once you've seen it done a few times - and have done it yourself successfully a few times - it becomes second nature.

Just remember, politically, Julius Caesar was a overly trusting idiot who hung around too much with guys who liked to play with sharp objects. But with respect to war, he was a blinkin' genius. "Divide and conquer" works as well today as it did 2000 years ago.