Multi-table queries not displaying any data

[Michael91]

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.

Ah really? One table for all of them, or one table each? And how should they be linked to the DisarticulatedInfo table? (These fields are relevant only for recording data on disarticulated remains (DepositType Disarticulated))

[Michael91]

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

Sorry didn't see this before I replied to the last message

Each of these (TaphKnown, Cut, Burnt, Gnawed, Polished, Perforated or Other) is YES/NO only, but they are not mutually exclusive - a single disarticulated deposit could have all of these. (To explain the data, sometimes you find single bones on archaeological sites e.g. a skull fragment. These may have been shaped into amulets, or polished through handling, or decorated, or show signs of rodent gnawing, and more. They may have been kept as trophies, or as mementos of the deceased, or for other reasons)

[orange]

What do “rowsource” and “BeforeUpdateEvent” mean?
On Access forms, if you have a control/field that has a number of options and you have to choose 1, the combobox is the type of control to use. The data values that represent the options is the rowsource of that combobox.

When doing data entry, you type in or select values in the different controls. Sometimes a control is skipped, sometimes a "strange value" is inserted (number instead of text etc). The BeforeUpdate event is the last chance the user has to correct or to ensure all fields have valid values. This is the place to verify/validate appropriate entries have been recorded for all fields that require an entry. This is the place where you could Undo and invalid entry. The AfterUpdate event means the record has been recorded to the table.


Am I expected to learn “vba code”? I have zero coding experience
Not necessarily, but probably some depending on the requirement.

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?
Well that is difficult to quantify, but you have knowledge of the subject and can assess the impact and adjust collection/recording as you see fit.
Again, if you can differentiate between an "empty field" and "unknown", then it may not matter. If it makes sense to identify a value that has meaning to you and your project, then make it an option in the combo.

FYI: Fields/columns exist in tables. Controls exist on Forms. Forms can be considered a method of safely displaying data from underlying tables.Bound controls get there values from the underlying table. Bound indicates that the value assigned to the control happened by "Access magically". If you have a control on a form, say a combobox
that gets its values from a reference table, then that combo would be unbound, and you would identify the appropriate rowsource for that combobox. Bound-Access populates; unbound --you populate. Terminology/jargon not a roadblock.

[mike60smart]

Hi Michael

See the revised Tab Control where I have added a page for Disarticulated.

On this Tab I added a Subform which allows you to enter date for ElementPresent & Side together with a related Subform which allows you to add 1 or More items ie Cut, Burnt etc...

[Michael91]

What do “rowsource” and “BeforeUpdateEvent” mean?
On Access forms, if you have a control/field that has a number of options and you have to choose 1, the combobox is the type of control to use. The data values that represent the options is the rowsource of that combobox.

When doing data entry, you type in or select values in the different controls. Sometimes a control is skipped, sometimes a "strange value" is inserted (number instead of text etc). The BeforeUpdate event is the last chance the user has to correct or to ensure all fields have valid values. This is the place to verify/validate appropriate entries have been recorded for all fields that require an entry. This is the place where you could Undo and invalid entry. The AfterUpdate event means the record has been recorded to the table.


Am I expected to learn “vba code”? I have zero coding experience
Not necessarily, but probably some depending on the requirement.

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?
Well that is difficult to quantify, but you have knowledge of the subject and can assess the impact and adjust collection/recording as you see fit.
Again, if you can differentiate between an "empty field" and "unknown", then it may not matter. If it makes sense to identify a value that has meaning to you and your project, then make it an option in the combo.

FYI: Fields/columns exist in tables. Controls exist on Forms. Forms can be considered a method of safely displaying data from underlying tables.Bound controls get there values from the underlying table. Bound indicates that the value assigned to the control happened by "Access magically". If you have a control on a form, say a combobox
that gets its values from a reference table, then that combo would be unbound, and you would identify the appropriate rowsource for that combobox. Bound-Access populates; unbound --you populate. Terminology/jargon not a roadblock.

Thank you, that makes sense

[Michael91]

Hi Michael

See the revised Tab Control where I have added a page for Disarticulated.

On this Tab I added a Subform which allows you to enter date for ElementPresent & Side together with a related Subform which allows you to add 1 or More items ie Cut, Burnt etc...

This is excellent, thank you again! I am struggling to keep up with all the information and updates, I haven't had time to actually edit anything and try some test data but this looks a lot better than the model I was struggling with.

Once I have the data in, how can I actually analyse it now it is divided over so many tables? Just make queries that draw from multiple tables? I assume this will work given that the relationships should all be valid?

[mike60smart]

Hi Michael

Yes if you get all the Relationships set correctly then the last stages of creating queries and producing Reports is the easy part.

[orange]

Michael,

Once I have the data in, how can I actually analyse it now it is divided over so many tables? Just make queries that draw from multiple tables? I assume this will work given that the relationships should all be valid?

The secret is to not put the data into the tables first. The point we have all been making is "Get your tables and relationships set up and tested with a few records and some test scenarios ( analogy is designing a blueprint for your house; test it against your criteria; adjust the blueprint if necessary; then build the physical house").
You do not need a lot of test records to vet your model. You do need a few records that cover the project scope. The intent of the test records and scenarios is to ensure the model supports the "archaeological business" you are undertaking.

[mike60smart]

HI michael

Your tblPartialSecondaryInfo should be broken down into 3 tables as demonstrated in tblDisarticulatedInfo
The same applies to tblScienceInfo

[Michael91]

Hi Michael

Yes if you get all the Relationships set correctly then the last stages of creating queries and producing Reports is the easy part.

Perfect

[Michael91]

Michael,



The secret is to not put the data into the tables first. The point we have all been making is "Get your tables and relationships set up and tested with a few records and some test scenarios ( analogy is designing a blueprint for your house; test it against your criteria; adjust the blueprint if necessary; then build the physical house").
You do not need a lot of test records to vet your model. You do need a few records that cover the project scope. The intent of the test records and scenarios is to ensure the model supports the "archaeological business" you are undertaking.

Thank you, I do appreciate and understand this. I was not trying to jump the gun on data entry, or enter things into the tables, just asking how it would work in the end, before I do it, so I don't end up making the same mistakes as before.

[Michael91]

HI michael

Your tblPartialSecondaryInfo should be broken down into 3 tables as demonstrated in tblDisarticulatedInfo
The same applies to tblScienceInfo

Hi,

I thought that would be the case, thanks I had no time to go over any of it today but I'll start re-arranging them tomorrow

[orange]

Michael,

Can you post your latest tables and relationships, or a copy of the database you have at the moment?
Have you moved away from Ken's suggestion to go super/subtypes?

[Michael91]

Michael,

Can you post your latest tables and relationships, or a copy of the database you have at the moment?
Have you moved away from Ken's suggestion to go super/subtypes?

Michael,

Can you post your latest tables and relationships, or a copy of the database you have at the moment?
Have you moved away from Ken's suggestion to go super/subtypes?

I have had no time to change anything yet - I am to trying to keep up with all the suggestions/help/updates people are giving but the database is only one part of my work responsibilities at the moment. I will devote time to it today and send an updated version back to you either today or tomorrow?

As for super/subtypes - I have not moved away from anything, as I mentioned in post #72 both approaches make sense to me, each has its benefits. I think if I can add all relevant fields to Mike's system then that will work better, but I do not know if I will manage that yet. I will take a look at how he altered other ones so far, and try to replicate it first.

A super/subtype system makes sense as my main focus is deposits (supertype) and there are characteristics they all share, and some they don't (deposittype subtypes). I tried building a model based on this, the one I sent back in post #38, but it was wrong, and I did not know how to incorporate the sites data. The sites and deposits have a one to many relationship - many deposits can come from one site, so Mike's system makes sense here, providing it allows for enough detail to be entered in the form(s), but again this is something I will try to figure out today .

[Michael91]

Your tblPartialSecondaryInfo should be broken down into 3 tables as demonstrated in tblDisarticulatedInfo
The same applies to tblScienceInfo

@Mike60Smart I have now done this, and the same for tblOsteoInfo. I have connected them in the same way you connected tblDisarticulatedInfo. I have made a tab for PartialSecondaryInfo too, made up of two forms as you did for DisarticulatedInfo.
I also changed a few field names and then I tried to enter some test data.

HOWEVER
The biggest issue I now have is with the Deposit subform - When using the sites form, I added the info for the first site, then moved on adding the info for the first deposit from that site in the subform (Deposit 4 in the image attached). This first deposit is an inhumation so I went to the Inhumation Info tab to enter the rest of the info for this deposit.

BUT – entering new rows in this tab does not change the DepositID, even though each row here indicates info for a new deposit, and if I move to a different tab they all have the same deposit ID too (Deposit 4 – see attached image). I also cannot enter more deposits into the Deposit subform without moving to a new site.

I am absolutely not saying the database was built wrong, I appreciate all the work you put in, and sorry if I explained this wrong initially, but this will not work for what I am trying to do ☹ I am sure it is doing what it is built to though.

Just in case I am still not explaining myself properly:

Each site can contain many Deposits. A deposit here means one of five DepositTypes (Inhumation, cremation, partialorSecondary, and Comingled). Each deposit should be a single record, and some deposit types will have common fields (dating, associated material, depositionalcontext, notes etc.) but others that are unique to their depositType

I have other issues and questions too, but they may be irrelevant because of the bigger issue. I can’t devote any more time to this today but over the weekend I think I will use Vlad’s database and try to build from there, using the things I have learnt today from Mike’s one 😊

Today's work on the database is attached too - @Orange

Database - DepositType Subforms.zip