Advanced query

[Jamie Collins]

To answer your question field 1 is simply an autonumber and goes from 1 to
984k.

So that's not the key then i.e. 'field 1' = 1 does not identify a
single entity.

Jamie if you look at the example from garys post <<snipped>>

But Gary doesn't know the business, does he? OK, you won't reveal nor
analogize the nature of the business, fair enough. I hope you don't
really have data element names such as 'Field 6' and 'Field 7' ;-)

Can I instead ask you tell me what the relational key or business key
for this table is? What normal form is it in?

The point I'm trying to get at is SQL is designed to query SQL tables;
it's a set-based language. A set has no inherent order but your
'table' seems to rely heavily on sequential row order. I could be
wrong but I suspect you haven't got a SQL table therefore it could
well be the case that SQL is the wrong language to process it with.

From appearances, Michel would seem to have the correct approach i.e.

yours is a 'staging' table and you need to first process the data into
a 'SQL' table before it can be queried with SQL.

Jamie.

--

 

[Guest]

Apologies Jamie

It's not an issue with telling you what the business is I just didn't want
to bore anyone.

The data entries are relating to measurements on a railway line.
The native file format is a csv that has then been imported into an access
table and as a result, yes, those are the field names.

Field 1 is just an auto number, Field 6 is the section of track, field 7 is
the type of track and field 8 is a mileage point on the track.

A measurement is taken every so many seconds resulting in a record.
The result is several records all having the same mileage measurement, I
want to eliminate all entries with the exception of the highest and lowest
mileage measurement for each change in track section and track type.

If this section of track appears later in the table then it should not be
combined with the earlier entries.

Hope this helps.

 

[Michel Walsh]

Then the procedure described should work.

- Create table g1g2, two fields, g1, the primary key, autonumber, and g2, a
long integer.

- Insert records in g1g2 as mentionned.

- Run the Group By query that groups on the difference of the two ranking
numbers (autonumbers)

SELECT LAST(a.field6),
LAST(a.field7),
MIN(a.field1),
MAX(a.field1),
MAX(a.field8),
MIN(a.field8)
FROM myTable As a INNER JOIN g1g2 ON g1g2.g1 = a.field1
GROUP BY g1g2.g1-g1g2.g2
ORDER BY MIN(a.field1) ASC




If the initial table would have got hole in its sequence, we would have to
reestablish the 'rank', through an additional table, like g1g2.

The logic behind the process is that the difference in the two ranks will be
the same for two records that belong to the same 'group' (as long as there
is no duplicated values in the rank, neither hole). Indeed, while it appears
we subtract autonumbers (or a reference to an autonumber subtracted form an
autonumber), we effectively subtract RANKS.



Hoping it may help,
Vanderghast, Access MVP