create sequence from table of relationships

[matt beamish]

Hi people.
I am writing a deposit recording database for archaeological layers. My units are recorded in one table, and then the relationships are recorded in a separate table. I have limited the recording of relationships to be either "same_as" or "below".

So in tblContexts is have say: 1,2,3,4,5,6
in tblcontextrels I have say : 2 is below 1; 3 is below 1; 4 is below 3; 1 is below 5; 6 is the same as 5

I want to view the deposits on screen in their stratigraphic sequence. So I think I need to write a piece of code to run through "tblContextrels" which will then create a hierarchy attribute. This attribute can then be used to sequence the context deposits I am looking at.

Might anyone be kind enough help me with this?
thanks
Matt

 

[CJ_London]

I'm not getting it from your description. Can you translate that to what it looks like in the table and what you would like it to look like on the screen - use Excel if you like and post a screenshot.

I suspect the solution will be a simple sorting requirement but may extend to the world of binary trees

 

[matt beamish]

Thanks for your reply.
Attached a gif showing a section through some simplified deposits and the 'matrix' that I would like to create. I have also uploaded an mdb that contains the 'contexts' and 'contextrelationships' that are recorded in the section.
Also a screenshot of a form from some real data which shows a group of contexts in a 'subgroup'. I have used the recorded relationships table to show the relationships with each context using the AllenBrowne concatrelated function, and have simply inverted the 'belows' to show as 'aboves'.
Here I would like to use the recorded relationships for the contexts to be used to show the deposits in their matrix sequence.
thanks for your interest.
Matt

 

[CJ_London]

urgh, not quite as straightforward as I thought

With regards the data, I don't understand why 7-9 is below 8 (shouldn't they be a 3rd branch off 12-2?

And what is the relevance of 6?

And what is the significant of 12-2? is that a single record or two?

You are going to need something more sophisticated than binary trees where an object can only have one parent

 

[matt beamish]

8 is a cut - this has been dug through 7 (i.e. 7 had to exist before 8 was cut through it).
6 is the layer that precedes everything else.
12 - 2 is a single deposit. Sometimes single deposits get recorded twice by say different recorders on different occassions. These numbers get recorded as "same_as".
thanks

 

[jdraw]

Matt,
I think it would be helpful to readers, and useful to you, if you were to give us an overview of WHAT you are trying to do in plain English.(who,what,where, when, how, how often sort of thing).
I know it may be clear to you, but telling us about codes you have in tables before we really understand the proposed database, the entities involved, the relationships between entities is not necessary yet.

We don't know archaeological dig terminology or procedures. Context and relationships have a certain meaning in relational database, but you are using them in your "dig business" terminology.

Again for your benefit and for readers, it would be good for you to define/describe
- archaeological layers
-deposits
-cut
-stratigraphic sequence
-Harris matrix
.....

I do not immediately see the difference, if any, between 11,6,8,9.

 

[matt beamish]

Hi Jon.
I will be giving this database to colleagues to use. The database is quite developed already and I started the access version in the late 1990s. This element of trying to bind in some functionality from the recorded archaeological relationships is a new departure for me.
The database will be the basis for the records for many different sites. Each database may hold up to 5000 individual 'context' records. People will use this database on a daily basis to understand the site they are working on, and share their information with specialists using the same database. Eg. Pottery specialists, flint tool specialists, environmental specialists.

A context is an archaeological unit. A context may be a layer/deposit/wall or a cut. Each context is an archaeological event of unknown duration. This event in the past may be the cutting of a posthole or the dumping of a waste into a ditch. If we as archaeologists can define a unit as something defined and different, it will generally be recorded as a context. These contexts appear in sequences. They overlie one another etc. The basis of archaeological recording and interpretation is on the basis that something below something else, predated it.
Each context has attributes that help define it, and also may contain finds of pottery, bone, charred material etc.

The stratigraphy of the site is critical in interpreting how the site formed. We draw an archaeological harris matrix of our deposits to show how they relate on a relative chronological basis. On urban sites the stratigrpahy can be very deep as people have built on the remains from their predecessors. In rural situations, most sites are shallow and spread out and not as complicated.

11 is the cut of a small pit/posthole into layer 6.
9 is the cut of a small pit/posthole into layer 6.
8 is the cut of a slightly larger pit/posthole. Although this does cut layer 6, it also cuts layer 7 - and must therefore post-date 7.

We know that becuase part of 7 has been removed that is has been truncated by pit cut 8 and this is reflected in the Matrix that we would derive from these archaeological relationships .

The Harris Matrix has become a standard of recording, certainly in the UK. For a Wiki, please see herehttp://en.wikipedia.org/wiki/Harris_matrix

thanks for your interest.
Matt

 

[CJ_London]

I don't believe there is an easy answer. You have two things to consider

1. How to get the data into a relevant dataset
2. How to display the data

The dataset you will require as a minimum will look something like this

The records that may cause a problem are the ones highlighted in yellow (because they are duplicates of ChildPK), but if you sort by level it will give you an approximation of what you are looking for.

This really needs a recursive query to order the data (available in SQL server but not Access) but you can mimic this using VBA to create a new table.

If the number of levels are fixed you can do this in a query by linking the table to itself on ParentFK=ChildPK - repeating as many times as required. Since the number of levels will vary from site to site, you would need to build this in VBA

The nearest I can think of to your requirement is a production control algorithm perhaps displayed in a Gantt chart. You might want to investigate Visio as a way of doing this.

A fully written algorithm for this sort of thing has commercial value so your are unlikely to find a free example. This link may help to explain and give you the maths behind it. There are hundreds of different algorythms, you just need to find the right one for you

http://en.wikipedia.org/wiki/Tree_(data_structure)

Sorry I can't be of more help

 

[CJ_London]

See there have been further postings. Wouldn't it be easier for you to use one of the products suggested by your link - passing the data through from Access or linking to the db?

 

[matt beamish]

Thanks very much for your help. I am attaching another database I had a go with last night, using queries to ( I think) generate what I was after. I just realised that the queries were clunky and I would need to guess the depth of the site, and that would hopefully more an elegant VBA way of writing this.

Re 3rd party add ons. Yes, this is a possibility, but for most sites would be overkill. A simple sort of the contexts is what is needed, even if there are problems with duplications.

 

[CJ_London]

Had a quick look at your db and can see you have a similar table to the one I proposed, but you need to be able to identify the top layer - i.e. it is not 'below' anything

 

[jdraw]

It seems you can get a free trial version that supports up to 50 stratification units.

It would seem a worthwhile effort to download and review to see some of the concepts and interfaces used in this commercial product to help you with the hierarchical structure involved. It doesn't mention the cost of the full licence purchase or build/refine/design your own.

The ArchEd program is access based and is/was distributed free.

 

[matt beamish]

So I have a query that in a very unperfect way produces a result that for say 90% of my data will be satisfactory.

I now need to take the results of this query, and search for any given context, and then output that value into a text box with whichever column name the value was found in (ie 'OrderTop', 'Order-1' etc). I can then use those values to structure my data display in various places. I understand that this will give misleading results where there are multiple relationships.

My question is, what would the VBA to do that?

My recordset is Q_Contextbase
I have a value on a form of say SF_Contexts.Contextcntrl

thanks