Multi-table queries not displaying any data

[Michael91]

@Gicu and @Orange thank you both so much! Vlad, your database structure makes much more sense, but I do have questions if that's okay?



1 - Should I remove "site name" from each of the tables (inhumations, cremations, bog bodies etc) because it is redundant information?

2 - How do the junction tables work? I did not like my multivalue fields but I couldn't think of a better solution. I had not explained this but the dating for this table (sites) indicates when the human remains from each site date to. So - a site may have been in use for 500 years, but if I have one skeleton, dated 300-100 BC from it, then in the sites table I want it recorded that the relevant period is 300-100 BC, hence the columns divided per 100 years. Now, if there is skeleton dated 300-100 BC and a cremation dated 200-0 BC, I used the multivalue fields to show when each is known to be from (300-100 and 200-0), while also giving a total range for the site (300-0). Do your junction tables allow for this? How do I enter the data correctly?

3 - Would this new layout allow me to run multi-table queries between the tables that are not directly linked? e.g. if I wanted to know whether cremations and inhumations were often both found in pits?

4 - Someone else had suggested making a "burials" overview table containing all fields common to each current table (Context number, period, depositional context etc), and then making sub-type tables for each burial type (cremation, inhumation, bog body etc) to hold the unique fields. Would this be a better solution? If so, how would I go about it, and how would i integrate the site data?

I will start changing my category titles to things Access doesn't hate

Happy digging Vlad - that is something I actually understand! I am much more competent with a shovel

[Gicu]

Hi Michael,
1. Yes, as you can always get it from the SiteList table via the SiteID join

2. Yes, the junction table would allow that. Junction (or linking) tables (one to many) are usually implemented with a form\subform design where the (main) form is bound to the one side (SiteID in your case) and the subform is bound to the many (100 years long timeframes in your case); the third table tblTimeframes is just a lookup table to be used in the subform as the row source for a combo in which you select the timeframe (and it actually saves the timeframeID). You would also use a combo for the actual value, you can use a list like you had or create another lookup table in case you want to add some more in the future.

3. Yes, but you need to include the main SiteList table in your queries and make sure you have outer joins from it towards the other

4. Would probably be better as you would avoid duplicating fields, but it would be close to the current design as you do not duplicate data (the same fields are in separate tables at the same "level" so they are mutually exclusive.

Cheers,

[orange]

Here is a draft logical model based on your posts.

[Michael91]

Hi both!

Sorry for the slow reply, I have been reading my way through Roger's Access Blog, I think things are starting to make sense. I really appreciate all the help, and no doubt I will run into roadblocks pretty soon so if you don't mind I may post more in this thread?

One thing before I go - I have been entering data directly into the tables so far - is this wrong? Should I build a form and enter the info into that so it populates the tables?

@Orange - your logic model is very impressive, what is the purpose of the IndividualArtefact table? Is it just a linking table for all the others? If so what fields should it contain and should it have a multi-field primary key. Also, do I need IDs for so many of those tables? Would they not get confusing? I was hoping I could have one ID per burial/individual/skeleton, which was carried over to all relevant tables so they were identifiable? In your system what are the other IDs for?

Once again thanks to everyone who replied, this is a very helpful forum

Michael

[orange]

Glad you are finding the forum useful. I will answer each of your questions below.

I have been reading my way through Roger's Access Blog, I think things are starting to make sense.
>> Good stuff. You should work through 1 or 2 of the tutorials. You will experience the process of table design and relationships. What you learn can be used with any database. I recommend ZYX Laboratories and/or
Class info system.

One thing before I go - I have been entering data directly into the tables so far - is this wrong?
>>Absolutely. It may be OK for some initial testing/debugging; but data entry should be via Form.

Should I build a form and enter the info into that so it populates the tables?
>> That is how it works when following best practices. There may be some exceptions, but they would be rare-few and far between.

@Orange - your logic model is very impressive, what is the purpose of the IndividualArtefact table?
>> Individual artefact was my abstraction of your burial/individual/skeleton/fragment/metalpiece. From your posts, in a "dig" when/if you find something worthy of recording, that is an individual artefact. Perhaps, you mean something different. It represents "a thing you want to record info about in your database".

Is it just a linking table for all the others?
>> Basically yes. It is abstracted enough to identify- a person, a burial, a piece of metal, a shin bone, an unknown chunk of X.

If so what fields should it contain
>> It contains a unique identifier, foreign keys to the other relevant tables as you see fit, any comments or references that are specific to that individual artefact. For example, if you photograph/draw a picture of the artefact, then a link to that would go in the table.
and should it have a multi-field primary key.
>>No. Unless there is something that hasn't discussed. I see no reason for multifield PK.

Also, do I need IDs for so many of those tables?
>> Every table in a relational database should have an identifier (PK) to uniquely identify every record in that table.

Would they not get confusing?
>>Possibly, but it's the name I've used in the model. You could call it whatever you want. If you name them well, it shouldn't be confusing.

I was hoping I could have one ID per burial/individual/skeleton, which was carried over to all relevant tables so they were identifiable?
>>Whatever it is that you want to record information about -that is how I interpreted "individual artefact". Again this is the sort of detail that evolves as you "test/vet" your data model with meaningful test data and scenarios. You know the subject matter, the jargon and the reality of archaeological projects, I don't and most readers do not.

In your system what are the other IDs for?
>>Not sure I understand which "IDs" you mean. The purpose of the ID field (PK) is to uniquely identify records within a table.

Other thoughts:

I think you may be adding data to a physical database whose design has not been thoroughly tested. I created a draft "conceptual/logical" model to try to identify the subjects involved and some linkages based on the thread dialog. My interpretation may be completely off-base in some areas. I really don't know if you have data and are trying to record it, or if you are designing a database for use with other archaeological projects.

I could not find an existing model for archaeological artefacts that had been tested and generally available. To me, that indicates you may be in new territory. All the more reason to test your model with realistic sample data and scenarios. It is so much easier to change the design at the model level than a physical, populated physical database. The analogy: modifying a blueprint vs changing the layout of your occupied house.

You may want to use the database where you have entered data, and prepare some queries and forms/reports to see that your database structure allows proper access and retrieval. Don't get yourself into a position that doesn't work; where you have entered so much data you can't possibly change course. Make sure it works before you finalize design. We have all heard the developer who says "I've spent so much time and effort inputting data that I can't change things now. It doesn't do what I need, but it's too late to adjust".

All that to say -test your set up; adjust as/if needed; then add the real data.

Good luck with your project.


Update: Just found these on youtube

I recommend you start with this video on Archaeological Databases by Ted Banning UofT. Good info for anyone starting an archaeological database design for recording their findings.
Key points ~6 minute mark, and the last few minutes of the video.

Archaeological Sites Working Group Geneva 2020 has info relevant to data model and mapping. I have not reviewed each in any detail. There is sufficient info in the videos to flesh out a model for your project. This area seems to be a work in progress.

https://www.youtube.com/watch?v=zbcDDI4qD_o

Related:
https://www.youtube.com/watch?v=fmQFkgp_3EI

[Michael91]

Hi again,

I have now started from scratch and built a new database – 21 tables including some link tables.
I have made sure all relationships are 1-1 or 1-many, and all have “enforced referential integrity” on, and cascade update fields up and down. I have also removed all my bad field names.
I planned it out and I thought it should work, but when I created a form for data entry it is completely blank.

I googled this issue and apparently this can happen when there is no data already in the tables. I made sure the form was set to accept data and edit it also, so this isn’t the issue.

I then tried to enter some example data in the tables but I keep getting this error:
“You cannot add or change a record because a related record is required in table ‘tblPeriod’”
It is not just this table that causes the issue, there are several, I assume it is because it will not allow a null value? But some of the tables need to, because they aren’t all relevant to all others.
I can attach my new file? Or a relationship screenshot?

[Michael91]

For clarity, I created a form to add inhumation (buried dead) data. I want to record where they were found (site), when they date to (period, dating), how they were buried (depositcontext, cutshape, position, orientation, grave goods etc), who they were (age/sex/stature/trauma) and what scientific analysis they have been subject to (aDNA, isotopes).
For this I added the fields:
DepositID – The unique ID for each individual in the database
DepositType – From a table containing 5 deposit type options (inhumation, cremation, partial, disarticulated, comingled)
Period – from a table containing several preset time period options
SiteID_FK – The foreign key for the site this individual is from
DatingID_FK – The foreign key for the radiocarbon date for this individual
RelativeDate – a short text field where I can enter a more general date for this individual
DepositContextType – From a table containing 17 preset burial contexts to select from (e.g. grave, pit, ditch) – it is possible for an individual to have more than one of these (e.g. if they were in a grave that had been dug through a house floor). There is a linking table.
OriginalNumber – A short text field for the identifying number the individual was originally given by its excavator
CutShape – A short text field so I can enter the shape of the grave/pit/ditch (Should be a table with preset values?)
Dimensions – Short text
GraveFurniture – Short text field for grave/pit/ditch characteristics (e.g. stone lining)
Position – Short text for skeletal position (should be a table with preset values?)
Bound – Y/N field for if they appear to have been buried wrapped up
Orientation – Short text for skeletal orientation (should be a table with preset values?)
FacingDirection – Short text for direction of the head (should be a table with preset values?)
ArtefactType - from a table containing several preset grave good options, with a linking table, as each deposit may have multiple goods
PreservationCompleteness – Short text so I can say how much of the individual survives
OsteoDate – Number field so I can enter the date they were examined by their excavator
AgeRangeLow – Number field for the lowest age estimation of the individual
AgeRangeHigh – Number field for the highest age range estimation (a range may be something like 35-44)
AgeCategory – Short text field for the age category they fall into (e.g. Young adult) - (should be a table with preset values?)
OsteoSex – Short text field for the sex of the individual as determined by skeletal evidence (should be a table with preset values?)
Stature – Number field for the estimated stature of the individual
GeneticSex – Short text field for the genetic sex of the individual (should be a table with preset values?)
aDNA – Y/N field to say whether they have been DNA tested by my project
aDNAOtherProject – Y/N field to say whether they have been DNA tested by anyone else
SrIsotopes – Y/N field to say whether they have had strontium isotope data taken
OIsotopes – Y/N field to say whether they have had oxygen isotope data taken
CIsotopes – Y/N field to say whether they have had carbon isotope data taken
NIsotopes – Y/N field to say whether they have had nitrogen isotope data taken
(Different isotopes are present in the bones and teeth, and values taken from them can give archaeologists an idea of where people were born, where they moved to in their live, and what they ate)
PotentialaDNA – Y/N field to say if this individual should be analysed in future
PotentialIsotopes - Y/N field to say if this individual should be analysed in future
Notes – Long text field
These fields are drawn from multiple tables and linking tables, and I may have misunderstood how to connect and select the right ones.

I also do not understand how preset values work in a form – will they appear in a drop-down or will I have to type the numerical ID because I would rather avoid the latter.

[Michael91]

@Orange –
Thanks for your last reply too – I have not had tome to go through those youtube videos but I will try to do so today. I have tried to take on board and implement everything else you suggested though – see the attached file 😊

Scotland database redesign.zip

[orange]

Haven't looked at you database in #38, nor read carefully the detail in #37.
"relationships are 1-1 or 1-many," There typically won't be a lot of 1-1.

“You cannot add or change a record because a related record is required in table ‘tblPeriod’” This means you are trying to add a record to the many side of a relationship when there was not related record in the 1 side. The analogy here is attempting to add a "child record" before you have identified the "parent".

I also do not understand how preset values work in a form – will they appear in a drop-down or will I have to type the numerical ID because I would rather avoid the latter. Not sure I understand this statement. Drop downs(combobox) are usually set up to show the "numeric key", and the related text value. The "secret" is in the format of the combo. You set the widths for display as 0;1 (or some non zero width for field2). The "numeric value" is hidden from the user, but is stored and used to relate records in the table you are populating.

Perhaps you could describe a sample of your preset values in a form.


Have you created some test data and scenarios to test your model?? See stump the model.

All good stuff.

[Michael91]

Haven't looked at you database in #38, nor read carefully the detail in #37.
"relationships are 1-1 or 1-many," There typically won't be a lot of 1-1.

“You cannot add or change a record because a related record is required in table ‘tblPeriod’” This means you are trying to add a record to the many side of a relationship when there was not related record in the 1 side. The analogy here is attempting to add a "child record" before you have identified the "parent".

All the relationships are "One to Many" actually - I just double-checked

Regarding the parent/child issue - That is what I thought it was, but many of the "child" tables have entered preset values (in period, for example, they are period acroyms such as LBA meaning Late Bronze Age), and for some I want the value to be blank - for example, the "Deposits" table links to one for inhumation-specific fields and one for cremation-specific fields; I was entering data for a deposit that was an inhumation, so all cremation-specific fields are not relevant.

Should I alter the relationships? I thought they should all be connected to the "Deposits" table as that is the main table they all relate to - the one containing largely foreign keys (you called it an IndividualArtefact table in your explanation)

[orange]

Should I alter the relationships? I thought they should all be connected to the "Deposits" table as that is the main table they all relate to - the one containing largely foreign keys (you called it an IndividualArtefact table in your explanation) Not yet.

Let's work through an example and see how the data/requirement and model fit.

"I was entering data for a deposit that was an inhumation, so all cremation-specific fields are not relevant. "
How exactly did you enter the data?

For whatever reason, the inhumations form is blank(all I see is the title??) when I try to open it. I can see it in design view???

I looked at you tables and recordcounts.

TableName RecordCount
Deposits 0
tblAMS_C14_Dates 1
tblAssociatedGoods 10
tblAssociatedGoodsLink 0
tblComingledInfo 0
tblContextInfo 0
tblCremationInfo 0
tblDepositContextLink 0
tblDepositContexts 17
tblDepositTypes 5
tblDisarticulatedInfo 0
tblInhumationInfo 0
tblOsteoInfo 0
tblPartialSecondaryInfo 0
tblPeriod 9
tblScienceInfo 1
tblSites 0
tblSiteTimeFrameLink 0
tblSiteTypeLink 0
tblSiteTypes 13
tblTimeFrames 22

[Michael91]

Should I alter the relationships? I thought they should all be connected to the "Deposits" table as that is the main table they all relate to - the one containing largely foreign keys (you called it an IndividualArtefact table in your explanation) Not yet.

Let's work through an example and see how the data/requirement and model fit.

"I was entering data for a deposit that was an inhumation, so all cremation-specific fields are not relevant. "
How exactly did you enter the data?

For whatever reason, the inhumations form is blank(all I see is the title??) when I try to open it. I can see it in design view???

I looked at you tables and recordcounts.

I mentioned in post #36 that the form appears completely blank - I don't understand it either, but some googling told me it can happen when there are no records in the tables yet (which for some tables is not true but for most it is). I then tried entering example data into the tables directly, which is when I got the error I mentioned.

"Let's work through an example and see how the data/requirement and model fit."

An example individual? Okay I will make one up --- An inhumation burial from the Early Iron Age settlement at Bornais. They have no radiocarbon date but are thought to be from 700-500 BC. They were placed in a circular pit, 1m x 1m x 0.5m. They were crouched on their left side, aligned N-S, and placed with a copper bead. They are a young adult male, aged 20-25. They have not been subject to isotopic or aDNA testing but would be a good candidate.

So to enter this individual properly in a form- Should I have a separate form for "Sites", and have the info for Bornais already entered beforehand?

[mike60smart]

The Form that you have created is wrong.

You have based it on a Select statement which includes 11 Tables in Total.

What you need initially is a Main Form based on Deposits (Needs to be renamed tblDeposits) then a Subform based on tblSites

[orange]

An inhumation(deposittype) burial from the Early Iron Age(TimePeriod) settlement(siteType) at Bornais(Site).
They have no radiocarbon date() but are thought to be from 700-500 BC(). They were placed in a circular pit(DepositContext), 1m x 1m x 0.5m.(OsteoInfo) They were crouched on their left side, aligned N-S(orientation??), and placed with a copper bead(). They are a young adult() male(), aged 20-25(). They have not been subject to isotopic(ScienceInfo) or aDNA(ScienceInfo) testing but would be a good candidate.

I have read through your sample record(s). I don't have enough familiarity with the tables/fields to assign the data. I have attempted to mark in RED where I think the data would reside. Some are just brackets since I'm not sure of proper storage location. There are different areas for time/Dates I find confusing, but I'm not familiar with archaeology jargon.

I think I see issues with your tables/linkages.
For example, Table tblAMS_C14_Dates has a field DepositID_FK as do tblContextInfo, tblOsteoInfo, tblScienceInfo -- what does it represent/mean?
Can you identify the steps in your proposed data entry of this sample record? That will help with "business rules" - I think.

Update: Just noticed mike has responded re: the Form. I did not look at the form other than it had a design view, but did not display in form view. He is probably correct in his findings. Mike is from Scotland and may have an interest in the thread.

You should try to place your sample info in tblDeposits and related tables as per attached png. Thaty will help test/vet your model.

[Michael91]

@Orange I have fleshed out the brackets below: An inhumation(deposittype) burial from the Early Iron Age(TimePeriod) settlement(siteType) at Bornais(Site).
They have no radiocarbon date(AMS_C14_Dates TABLE) but are thought to be from 700-500 BC(RelativeDate FIELD). They were placed in a circular pit(DepositContext), 1m x 1m x 0.5m (Dimensions) They were crouched on their left side (Position), aligned N-S(orientation), and placed with a copper bead(ArtefactType FIELD, AssociatedGoods TABLE). They are a young adult(AgeCategory FIELD, OsteoInfo TABLE) male(OsteoSex FIELD, OsteoInfo TABLE), aged 20-25(AgeRangeLow and AgeRangeHigh FIELDS, OsteoInfo TABLE). They have not been subject to isotopic(ScienceInfo) or aDNA(ScienceInfo) testing but would be a good candidate.

The reason for multiple dating types is some deposits will have been dated using scientific means (radiocarbon (C14) or AMS dating), giving them very precise date ranges and thus specific data to add, while others will only be dated relative to other known information, like associated material or site details – for example if they were buried with pottery that we can date to 800-500 BC, we know the skeleton should be no older than 800 BC, but could be more recent.

I think I see issues with your tables/linkages.
For example, Table tblAMS_C14_Dates has a field DepositID_FK as do tblContextInfo, tblOsteoInfo, tblScienceInfo -- what does it represent/mean?

As these tables all link to the deposit table, I thought they needed foreign keys to it, to connect the data? Am I over-complicating it?

Can you identify the steps in your proposed data entry of this sample record? That will help with "business rules" - I think. I made a form, using fields from multiple tables, to enter the sample record. All records are deposits, so all will get a deposit ID. The next most important criteria is deposit type (in this case inhumation). I made one form containing all fields that are useful for recording data about inhumations - but as Mike says, this form is obviously not constructed right.

You should try to place your sample info in tblDeposits and related tables as per attached png. Thaty will help test/vet your model.
I don’t understand – isn’t this just showing the table in Design view? What difference will entering data there make? I know what each field should do, and apologies if I am not explaining myself well, I am trying to be clear (see also my full list of form fields in #37, where I have tried to explain what each field is for)

The Form that you have created is wrong.

You have based it on a Select statement which includes 11 Tables in Total.

What you need initially is a Main Form based on Deposits (Needs to be renamed tblDeposits) then a Subform based on tblSites

Hi @mike60smart, thanks for responding! So are you saying I will eventually need one form for every table? I thought the purpose of forms was to allow for easier data entry, to populate multiple related tables? How can I make a form based on the "Deposits" table, it mostly just contains Foreign Keys. Again, sorry if I am misunderstanding. What I want to do is to be able to enter data about individual deposits, of which there are five main types (inhumations, cremations, partial burials, disarticulated burials, and comingled burials), so I was assuming I would need five forms. I will also need to enter site data, so one more form? And ideally the deposits from each site would appear in the sites form, as subform(s). For the deposits with radiocarbon dates, I was hoping the data stored in this table (tblAMS_C14_Dates) could be entered as part of the five deposit forms.

Thank you both again, and I appreciate your patience with this