Multi-table queries not displaying any data

[mike60smart]

Hi Michael

See the attached

I created a subform for tblCremationInfo

I added a Tab Control and added a Subform to each of the Tabs.



You can create as many tabs as required.

[mike60smart]

Hi Michael

You said "DepositID_FK - The Foreign key for the deposits table that it turns out I don't need"

You DO need to keep this field in the table as it is the FK which is linked to the PK in tblDeposits.

[mike60smart]

Hi Michael

I take it you go to a specific Site and each Site can have Many Deposits?

[Michael91]

Hi Michael

See the attached

I created a subform for tblCremationInfo

I added a Tab Control and added a Subform to each of the Tabs.

You can create as many tabs as required.

This is really cool! I will take a look at how you made this and try to replicate it for the other deposit types! I will need to fix a lot of the other tables still but thank you, I like this format

[Michael91]

Hi Michael

You said "DepositID_FK - The Foreign key for the deposits table that it turns out I don't need"

You DO need to keep this field in the table as it is the FK which is linked to the PK in tblDeposits.

Ah okay, I got confused - before when you asked what they were for, I thought the implication was that they shouldn't exist.

[Michael91]

Hi Michael

I take it you go to a specific Site and each Site can have Many Deposits?

Absolutely, yes. A site could be something like a settlement, with one burial (deposit) in it, or it could be a large cemetery with several hundred burials (deposits).

One deposit will normally consist of a singe individual, either complete (an inhumation), or burnt (cremation), or buried incomplete (partial/Secondary - sometimes this could be an upper half, or they could be missing some limbs for example), or a deposit could be a single bone (disarticulated - like a decapitated head). The final category (comingled) is more complicated - this is where pieces/bones from multiple individuals are deposited together, and you can't really identify which bones belong to which person, so they are considered together as a single deposit.

[mike60smart]

Hi Michael

If the Main Focus is the Site and all related data then the attached is one way you can do this.

[Michael91]

Hi Michael

If the Main Focus is the Site and all related data then the attached is one way you can do this.

Hi,

Its a tricky one because I originally had my "main" table as Sites for this exact reason (see the start of this thread) - but people here rightly pointed out that my data (and the subject of all subsequent analysis) is the deposits (burials), not the sites so much. You are also right though, they are all contained within, and originate from, the sites.

Thank you for the new version - it is excellent!

I can't believe how helpful everyone on this forum is - its really nice to see, and as a complete beginner it is hugely appreciated

[orange]

I've been away most of the day. Just looking at your latest post.
You have identified intended storage location--great.

Normally you have 1 table per form. If the table is related 1:M with another(s), then that/those on the many side could become subform(s) on the Main form.
You can populate your Lookup /reference tables. Those tables that contain an ID and a constant text. eg tblDepositContexts , tblPeriod.

Update: 6:30 PM EDT

I just looked at Mike's version of the database and the form he suggests.
I agree that that is the type of form conducive to data entry.

You start with a site and identify a "deposit"-- and all of the relevant lookup values(Ids/PKs) are assigned to the proper fields in the deposit record. Record is validated either passes or fails. You may have some conditions for accepting a record that has not met all required values (you know the data and requirement better than readers).

The provisos:
The form should have all required lookup table values as appropriate rowsource to appropriate combos or listboxes
The form should have a validation procedure in the BeforeUpdateEvent
Every required control should have an acceptable value that can be validated with some vba code
Only after all validation has occurred should the Deposits record or a related record be saved.

You will have to confirm that all required attributes for a deposit record are available and accessible on/from the form.
(There may be some form control value that will cause some event to fire that may calculate some necessary value to be saved in the record)


You may have a form(s) for updating each of the Lookup tables, or additional tables if needed as project progresses

Next steps may be:
-identify every field in the deposit table that must be valued for an "acceptable/complete" record.
-are those fields represented by controls on the data entry form or able to be retrieved from existing subform(s)
-adjust/populate control tags to indicate mandatory value for validation routine
-create validation routine with appropriate messages
- you may want to log who added what, when for audit purposes. Such a log may be useful if, for some reasons, incomplete records have to be saved.

I was not aware what Canmore was :Dive in and explore hidden treasures from Scotland's archaeological past. Discover twelves ages, starting in deep prehistory and journeying through time to ....

Canmore is a city in Alberta Canada.


You are getting good info from Mike and Vlad!! (and Ken and Scott)

[Michael91]

I was not aware what Canmore was ive in and explore hidden treasures from Scotland's archaeological past. Discover twelves ages, starting in deep prehistory and journeying through time to ....

Canmore is a city in Alberta Canada.

I will reply properly later but yeah! Canmore is an incredible resource if you are interested in the history, archaeology and heritage of Scotland - it holds freely accessible records of almost every known discovery, and it is fully searchable, so you can see what is in your local area

I actually drove through the Canadian Canmore back in 2018 - I spent a month driving across Canada and cannot wait to go back, its a truly beautiful place

[orange]

Hi again Michael,
On reviewing the thread, I went to the Microsoft forum and saw the responses. Ken and Scott are long time players in the forums and are well respected. Ken's background (archaeology) gives him some personal and technical experiences that the rest of us don't have. I go to his site to get sample databases from time to time. He is suggesting supertype/subtype which in effect is saying "Burial" is the subject of your project, and all burials have some basic data to be recorded, AND burials can be divided into specific "sub groups" each with their specific attributes.

My responses have focused on Deposit or artefact since that was my understanding of your requirement. In this line of thinking deposit could be a person or part or a copper pot or a piece of furniture. The related tables contain values to define the who, what, where, when, how much and how often aspects of the "deposit/artefact".

This has some implications on your data structure (tables and relationships). Ken has real experience with the subject matter, his suggested approach bears weight.

As all responders have advised, get a data model and test it with sample data and scenarios to confirm the structure matches your needs.

Can you describe deposit/artefact and burial now, after having received many comments?

[Michael91]

Hi again Michael,
On reviewing the thread, I went to the Microsoft forum and saw the responses. Ken and Scott are long time players in the forums and are well respected. Ken's background (archaeology) gives him some personal and technical experiences that the rest of us don't have. I go to his site to get sample databases from time to time. He is suggesting supertype/subtype which in effect is saying "Burial" is the subject of your project, and all burials have some basic data to be recorded, AND burials can be divided into specific "sub groups" each with their specific attributes.

My responses have focused on Deposit or artefact since that was my understanding of your requirement. In this line of thinking deposit could be a person or part or a copper pot or a piece of furniture. The related tables contain values to define the who, what, where, when, how much and how often aspects of the "deposit/artefact".

This has some implications on your data structure (tables and relationships). Ken has real experience with the subject matter, his suggested approach bears weight.

As all responders have advised, get a data model and test it with sample data and scenarios to confirm the structure matches your needs.

Can you describe deposit/artefact and burial now, after having received many comments?

Hi @Orange

Ken and Scott have been very helpful too – Ken especially because of his archaeology background.

I apologise for the confusion over burial/deposit/artefact here – this is a terminology issue mostly, but entirely my fault. I am dealing primarily with the treatment of the dead, and archaeologists sometimes try to avoid using “burial” for this, because it doesn’t describe all treatments (not everyone is buried), so when I talk about “deposits”, I mean a single instance of human remains, as I wrote in #66:

“One deposit will normally consist of a singe individual, either complete (an inhumation), or burnt (cremation), or buried incomplete (partial/Secondary - sometimes this could be an upper half, or they could be missing some limbs for example), or a deposit could be a single bone (disarticulated - like a decapitated head). The final category (comingled) is more complicated - this is where pieces/bones from multiple individuals are deposited together, and you can't really identify which bones belong to which person, so they are considered together as a single deposit.”

The term “deposit” could definitely apply to other things – pottery, animal bone, anything really, but I am focussed on human remains, how they are treated, and the things (artefacts) they are deposited with.

"He is suggesting supertype/subtype which in effect is saying "Burial" is the subject of your project, and all burials have some basic data to be recorded, AND burials can be divided into specific "sub groups" each with their specific attributes."

This is absolutely correct, and I did build my last model on this premise (when I couldn’t get the form to work), only I exchanged the term “Burial” for “Deposit” for the reason I just gave above. The issue arises when bringing in the site information also, as multiple deposits (burials, skeletons, human remains, whatever word you choose) can come from one site, which is where Mike’s layout makes more sense.

[Michael91]

I've been away most of the day. Just looking at your latest post.
You have identified intended storage location--great.

Normally you have 1 table per form. If the table is related 1:M with another(s), then that/those on the many side could become subform(s) on the Main form.
You can populate your Lookup /reference tables. Those tables that contain an ID and a constant text. eg tblDepositContexts , tblPeriod.

Update: 6:30 PM EDT

I just looked at Mike's version of the database and the form he suggests.
I agree that that is the type of form conducive to data entry.

You start with a site and identify a "deposit"-- and all of the relevant lookup values(Ids/PKs) are assigned to the proper fields in the deposit record. Record is validated either passes or fails. You may have some conditions for accepting a record that has not met all required values (you know the data and requirement better than readers).

The provisos:
The form should have all required lookup table values as appropriate rowsource to appropriate combos or listboxes
The form should have a validation procedure in the BeforeUpdateEvent
Every required control should have an acceptable value that can be validated with some vba code
Only after all validation has occurred should the Deposits record or a related record be saved.

You will have to confirm that all required attributes for a deposit record are available and accessible on/from the form.
(There may be some form control value that will cause some event to fire that may calculate some necessary value to be saved in the record)


You may have a form(s) for updating each of the Lookup tables, or additional tables if needed as project progresses

Next steps may be:
-identify every field in the deposit table that must be valued for an "acceptable/complete" record.
-are those fields represented by controls on the data entry form or able to be retrieved from existing subform(s)
-adjust/populate control tags to indicate mandatory value for validation routine
-create validation routine with appropriate messages
- you may want to log who added what, when for audit purposes. Such a log may be useful if, for some reasons, incomplete records have to be saved.

I was not aware what Canmore was :Dive in and explore hidden treasures from Scotland's archaeological past. Discover twelves ages, starting in deep prehistory and journeying through time to ....

Canmore is a city in Alberta Canada.


You are getting good info from Mike and Vlad!! (and Ken and Scott)

Thank you for al this – I am still figuring out how Access actually works, I had a lot of wrong assumptions about how I thought it did, and I appreciate the help and clarity.


What do “rowsource” and “BeforeUpdateEvent” mean?
Am I expected to learn “vba code”? I have zero coding experience
Regarding incomplete records – Archaeological data is never perfect, in very few cases will all relevant fields be filled for every record. How much of an issue is this? Should I simply make a value in every combo/listbox that says “unknown” “unrecorded” or similar?

[mike60smart]

Hi Michael

A lot of your problems stem from the fact that your tables contain Fields that should be Records in a Related Table.

Take for example the relationship between tblDeposits and tblDisarticulatedInfo.

Attachment 47792

All of the fields which are Yes/No should be in a separate table.

[mike60smart]

Hi Michael

When entering data about Disarticulated Info would you enter details in the following fields:-

ElementPresent and Side and then choose 1 value for TaphKnown, Cut, Burnt, Gnawed, Polished, Perforated or Other

Or would you want to enter more than 1 value from TaphKnown, Cut, Burnt, Gnawed, Polished, Perforated or Other