Compare a Field to a Field in a Prior Record with Large Datasets (1 Viewer)

aaron_a · Feb 26, 2009

Referencing this example from Microsoft Support:

ACC2000: How to Compare a Field to a Field in a Prior Record

(If the link does not work because I am a new member it is from support.microsoft.com/?id=208953)

This example works with the limited dataset provided but if a new dataset is created which is much larger it randomly deletes, or doesn't delete, a record(s).

Does anyone have any experience using this type of querying within a query using large datasets and/or why it is causing this problem.

Attached is the example from Microsoft support with the added table and query that simulates a "large" dataset.

I could do it with a script/batch file but if it can be done in access it would be better.

(This same question was posted in another forum but have not had any responses.)

Thanks,
Aaron

Banana · Feb 26, 2009

How big it is shouldn't really make it go around deleting info randomly. I'm thinking that there may be more than meets the eye or more likely corruption.

If you import everything into a new blank database, does this behavior persist?

gemma-the-husky · Feb 26, 2009

the problem is always inherent in the concept of a "prior" record - because dbs deal with sets of data, and therefore a prior record is undefined.

so in order to find some related record, we need to impose an arbitrary order, and then you need to deal with the possibility that there is no prior record on the index you are using. You also have to use dlookups etc, or at least multiple reads to locate the "prior" record.

An alternative is to store whatever information you need (ie from the prior record) at the time you store the current record. However this may become an issue subsequently if you then need to interleave a record in the middle of a sequence.

If it is doable in this way, i would go for this solution - ie try to make each row independent of other rows

aaron_a · Feb 26, 2009

Banana:

I opened a new .mdb file, imported a different dataset, and just copied the logic from the Microsoft support example. The problem still occurs.

gemma-the-husky:

Do you have any examples of the possible solutions you are posing or sites where I can find them? My Access experience is limited. I don't have any experience with dlookup or the other solutions you are proposing.

Thank you for both of your advice

gemma-the-husky · Feb 26, 2009

no

but if you have a record, and you want to find the prior record which do you mean?

the one you inserted last?
the one with the date nearest to the current one?
one for the same customer?

etc etc

so if you HAVE a current record, you need to decide what you mean by a prior record, and then see if there is a record that meets this criteria, and then get the nifo you need from that record. The idea being that data shouldnt depend on previous rows, which implies a normalisation problem

so, if you know what info you will need in advance, just save it when you save the record in the first place, so you dont need to look it up.

Banana · Feb 26, 2009

Could you please post the full SQL of the query you're using?

aaron_a · Feb 26, 2009

Attached is an example of what I am trying to accomplish.

It is a list of numbers increasing in value, 1 to 10,000. The actual list is the same as what is provided except they are floating point numbers (Field Size = Double), both numeric types produce the undesired results.

The query points to the original table and an alias of the original table to compare the current record with an exact list of the original table. When at a specific record in the alias table it looks to all of the values that are less than it in the original table and picks the largest of those values. The value retrieved should be the previous record since the column is in ascending order.

For most cases, 99.99%, the correct value is provided in the new query column, but instead it will, what appears to be, "randomly" drop a logically valid record from the output of the query.

If you run the query in the attached example multiple times you should see that one record is dropped from the list of 10,000 to 9,999. When inspecting which records is dropped it is not because it did not fit the criteria but rather it drops it because.... I don't know.

This is not logic I have created but rather what Microsoft's support suggested for this type of operation (same logic).

aaron_a · Mar 2, 2009

Bump

Any ideas?

aaron_a · Mar 18, 2009

Update on the issue:

I think I solved the issue:

When I was running the query it was in "Select-Query" mode. If instead I create a table to append to using "Append-Query" the query works every time.

It seems as though the "Select-Query" mode may have some memory issues.

Note: I am using MS Access 2003

boblarson · Mar 18, 2009

aaron_a said:
The value retrieved should be the previous record since the column is in ascending order.

I don't see anything that sets these numbers to ascending order. How do you figure that they are in Ascending order?

boblarson · Mar 18, 2009

aaron_a said:
It seems as though the "Select-Query" mode may have some memory issues.

No, the Select Query will work fine as long as you make sure to set the Ascending order on the initial field (IN THE QUERY), which you didn't have in your sample.

boblarson · Mar 18, 2009

The thing you have to realize is that tables don't necessarily store data in the order you think it does. Tables, when you view them, are really only queries themselves, but the data is really stored in the file and it isn't necessarily in a particular order.

So, you must set your sort in a query (or report) to guarantee the order you want.

aaron_a · Mar 18, 2009

boblarson said:
I don't see anything that sets these numbers to ascending order. How do you figure that they are in Ascending order?

The table I am querying from is already in ascending order.

Are you saying I should sort the column again in the query, because the "NUM" column is set to sort ascending?

Attached is an updated Access file to illustrate the changes I made. The "TableX" is now 10,000 rows instead of the approximately 2000 rows from the previous file I uploaded.

boblarson · Mar 18, 2009

aaron_a said:
The table I am querying from is already in ascending order.

And I'm saying, no it isn't. It may APPEAR to be in Ascending order, but tables are not stored in any special order in the background.

Are you saying I should sort the column again in the query, because the "NUM" column is set to sort ascending?

You set the NUM column as sorted in Access in the QUERY.

If you set the sort order in the select query, it works just fine.

aaron_a · Mar 18, 2009

Can you either upload the updated Access file or a screenshot of the changes you make to the select query?

I ask because I am unclear what you mean by setting "the sort order."

boblarson · Mar 18, 2009

aaron_a said:
Can you either upload the updated Access file or a screenshot of the changes you make to the select query?

I ask because I am unclear what you mean by setting "the sort order."

see attached (I deleted record 230 and 998 as tests)

aaron_a · Mar 18, 2009

Running the query five times, it failed to produce a complete output twice (9997 records instead of the original 9998).

On the last time it did not show the row containing "478" in the "NUM" column.

Attached is a screenshot of 'TableX' and the select query.

Compare a Field to a Field in a Prior Record with Large Datasets (1 Viewer)

New member

Attachments

split with a cherry atop.

Super Moderator

New member

Super Moderator

split with a cherry atop.

New member

Attachments

New member

New member

Smeghead

Smeghead

Smeghead

New member

Attachments

Smeghead

New member

Smeghead

Attachments

New member

Attachments

Similar threads

Users who are viewing this thread