Access Query Nightmare

marleyuk

Registered User.
Local time
Today, 16:48
Joined
Feb 8, 2006
Messages
54
Hello, im having a real problem running a certain query in access. My database is used to monitor drain spillages, and the query im trying to build needed to look down a column and show descrite water spills. A descrite spill is when water spills for a bit then stops. I need to show how many times this happens in a column.

The complexity of the problem (comparing one value in a column to other values in that column) coupled with the size of the dataset (600K+ rows) just baffles everything I throw at it.

I had two stabs at it. One involved a recordset - looping through and updating a new field. Recordsets can cause database bloat - in this case it bloated the db to 2GB (Accesses maximum size causing the code to crash) - this is something I've never managed before. The other involved a function that left access frozen.

I was really stuck on this until a crazy idea came to mind, i want to know if this would work.

This might sound stupid but how about some crazy thing like adding a new column and numbering the fields, then adding some kind of query that says if the value is over 0.001 then add a 1, if not leave blank. Then have something like if the column has a 1 in it then check the next, if this one has a 1 in it then repeat this step, if not then this is 1 spill. Store the amount it starts and stops to a variable and just use that number as the Number of descrite spills.

Would this work?

Thanks,
Marley.
 
More info please...

Can you post a few records, just to get an idea? If so, include a few that you'd consider a discrete spill and a few that you wouldn't.

This is what I understand so far:

You have one field with a decimal value and you want to count how many records have a value greater than .001 ?

Answer this too: Is there only one field you'd use to decide whether a record constitutes a discrete spill?
 

Users who are viewing this thread

Back
Top Bottom