Counting: multiple date fields, counting most recent (1 Viewer)

[Tim L]

I have a table in which there are four data fields; date 1 ('lowest') to date 4 ('highest'). The purpose of each field is to record when a particular state has been entered. It is possible for the states to occur in the orders as follows:

1, 2, 3, 4
1, 2, 3, 2, 1
1, 2, 3, 4, 3, 2, 1
1, 2, 1, 2, 3, 2​

Apart from the rule that states occur from 1 up and from high to low in sequential order, the newest data could occur in any field. As can be seen, it is possible that not all fields contain dates at any one time (but hey, this is the easy part, I think).

I need to create a query to count the quantity of each field that has been completed but only including the most recent in the count. Currently I count all date entries, so for a table with 6 records in I could end up with a query that produces

State 1: 5
State 2: 4
State 3: 2
State 4: 0

Which, of course, adds up to more than 6 records total. I can assume that there are 5 occurences of state 1, of which 4 also have occurences of state 2 of which 2 also have occurences of state 3, but I cannot determine which was the most recent and it may be the case that for one of the records state 3 was entered but state 2 has been entered again since then.

It is necessary to track when each state occurred, so using a single date field with a state indicator is not appropriate, also there are only 4 possible states; a linked table to record the state changes has been considered but would, in the long run, result in a larger database as more than 4 states could be recorded, plus there's the link field overhead.

Can anyone suggest how I may go about creating a query to get accurate counts?

Tim

 

[neileg]

Your design is wrong. You have one field per state, you should use a table with one record per state. It then becomes easy to select the most recent entries using Max in the query.

 

[Tim L]

Neileg,

I see where you are coming from. The design is something that I am currently lumbered with and there is an issue with using one record per state, in that this will create 4 times as many records. I perhaps need to explain further:

Each of the state records is linked to a single record elsewhere, say an item, device or person. For each state a comment perhaps, but not necessarily, about the most recent entry may be recorded, along with another entry that links the state to one of (currently) up to 60 possible situations (this is likely to change and not all items will have the same combination of situations). Hence, changing to 1 record per state the table will blossom by a factor of 4; in reality the number of items will break the 1,000 barrier and may even exceed 10,000. All of the states, for each of the 10,000 items and 60 situations, are recorded in a single states table. There is therefore the potential for a table with 240,000 entries, rather than 60,000 entries. Additionally, this would only be the case if there were a maximum of 4 changes per item, if there were more changes the situation would be worse, hence the concern about bloat.

I've got a theory of a work around but until I have tried it out I won't muddy the water here.

If such bloat is not likely to be a problem though I'd certainly like to consider the alternative you suggest. How easy would it be to remove entries in excess of the last 4 for any particular item? (This would need to be done on creation of each fifth entry.) That aside, any other ideas?

Tim

 

[neileg]

But if you have an empty field, it still takes up space, a non-existent record takes up no space, so your bloat calculations aren't the real world.
In any event, the record will only have 4 fields, an ID, a foreign key to link it to your other data, a field to hold the state and a field to hold the date. This isn't a huge record, especially when you would be holding the date, anyway.

I'm not a trained programmer, just a hack. When I was first introduced to the concept of normalisation (which is what we are talking about) I thought it was a lot of purist nonsense. I now realise that it's a key component of proper design and it just makes everything work easier and better.

 

[Tim L]

Neileg,

Thanks again for your comment.

I fully agree that normalisation is the 'right' way to go and, when time permits, will modify to comply. I have, for now, employed a single-byte field that records the most recently set field. Not really ideal but at least it prevents a need to significantly modify several tables, queries and forms at a time when I need to concentrate on other more critical things.

Tim

"Heath Robinson, who's he? (1872-1944)"

 

[neileg]

Yeah, been there. Do you know the qulaity triangle in project management? Imagine timescale, resources and quality as three sides of a triangle. As you alter one side, the others have to alter, too. So as time and resources reduce, you have to accept a more 'pragmatic' quality level.

Hope it works out.

 

[neileg]

Yeah, been there. Do you know the quality triangle in project management? Imagine timescale, resources and quality as three sides of a triangle. As you alter one side, the others have to alter, too. So as time and resources reduce, you have to accept a more 'pragmatic' quality level.

Hope it works out.