Query question of beginner

[Testar]

Hello,



I am beginner to MsAccess and I have to solve a problem of query in the geological database.
I have got a stratigraphic columns in the wells with units of different orders which are indexed in the hierarchy field:

WellID depth unit hierarchy
1 0 Kenozoic 100
1 0 Quaternary 110
1 0 Holocene 111
1 110 Pleistocene 112
1 225 Neogene 120
1 300 Jurassic 220
2 0 Triassic 230
2 500 Permian 310
2 800 Devonian 330
2 800 Franian 332

I need to build a query to get a file with records of top of selected order, for instance the first digit of the index:

WellID depth unit hierarchy
1 0 Kenozoic 100
1 300 Mesozoic 200
2 0 Mesozoic 200
2 500 Paleozoic 300

I need to eliminate all units of the same order below the top one, so I believe some dupicate removal will be involved. However, I have little/no idea how to replace unit for instance 332 with 300?

Any hint will be appreciated!!!

Regards
Z

[ranman256]

select * from tbl orderby [wellid], [heirarchy]

[June7]

Or maybe: http://allenbrowne.com/subquery-01.html#TopN

Need a unique ID in table, autonumber will do.

SELECT Table1.wellid, Table1.depth, Table1.unit, Left([hierarchy],1) & "00" AS Expr1
FROM Table1
WHERE (((Table1.[ID]) In (SELECT TOP 1 ID FROM Table1 As Dupe WHERE Dupe.wellid=Table1.wellID AND Left(Dupe.hierarchy,1)=Left(Table1.hierarchy,1) ORDER BY Dupe.wellid,Dupe.hierarchy)));

I don't know how you got Mesozoic and Paleozoic in your output example from the raw sample.

[Testar]

I don't know how you got Mesozoic and Paleozoic in your output example from the raw sample.

Thanks for response. Answering to your question: I have got in the db an additional "stratigraphic table" for all geological ages where all units are listed with the hierarchy:

Kenozoic 100 (Era)
...
Mesozoic 200 (Era)
Cretaceous 210 (Period)
Late Cretaceous 211 (Epoch)
....
Paleozoic 300 (Era)
....

In raw data in well 2 I have the following units:
2 0 Triassic 230
2 500 Permian 310
2 800 Devonian 330
2 800 Franian 332
which should be "translated" in the output to Eras on the base of hierarchy index:
2 0 Mesozoic 200
2 500 Paleozoic 300

[June7]

Then leave unit out of the suggested query. Join stratigraphic table to the suggested query.

[Testar]

Then leave unit out of the suggested query. Join stratigraphic table to the suggested query.

OK, I can do it after your hint, but then I get duplicates:
WellID depth unit hierarchy
1 0 Kenozoic 100
1 0 Kenozoic 100
1 0 Kenozoic 100
1 110 Kenozoic 100
1 225 Kenozoic 100
1 300 Mesozoic 200
2 0 Mesozoic 200
2 500 Paleozoic 300
2 800 Paleozoic 300
2 800 Paleozoic 300

I see now that I have to sort data by WellID and then by depth. And now I need to leave only uppermost records for each Era and delete all duplicates below:
WellID depth unit hierarchy
1 0 Kenozoic 100
1 300 Mesozoic 200
2 0 Mesozoic 200
2 500 Paleozoic 300

Any idea how to do it?

Regards

[June7]

Did you link the query and table on the common era fields? The query should be like:

SELECT Query1.wellid, Query1.depth, strata.PeriodName, Query1.Expr1
FROM strata INNER JOIN Query1 ON strata.Period = Query1.Expr1
ORDER BY Query1.wellid, Query1.depth;

All works for me.

[Testar]

Did you link the query and table on the common era fields? The query should be like:

SELECT Query1.wellid, Query1.depth, strata.PeriodName, Query1.Expr1
FROM strata INNER JOIN Query1 ON strata.Period = Query1.Expr1
ORDER BY Query1.wellid, Query1.depth;

All works for me.

Thanks for help. Unfortunately it does not work for me as the output consist of every line from input table. I linked Query1.Expr1 with strata.hierarchy. Is it OK?

[June7]

Post the exact queries you attempted. Did you follow the structure I suggested?