Multi-table queries not displaying any data

[Gicu]

Hi Michael,

Please have a look at the new form. Basically the foreign keys controls need to be replaced with combo's as @orange explained. As for the 1-to-many relationships those are handled with form\subform setup as shown in frmSites.

Cheers,

[mike60smart]

Hi

In tblDeposits you have the field InhumationID_FK which is linked to tblInhumationInfo

What value are you trying to store in this field?

Currently tblInhumation does not have any records ?

Usually when you have a FK in a table, when you create the Form for Data Input, a Combobox is created which looks up a value from the related table, which normally contains just a List of Values to select from.

[Michael91]

Hi

In tblDeposits you have the field InhumationID_FK which is linked to tblInhumationInfo

What value are you trying to store in this field?

Currently tblInhumation does not have any records ?

Usually when you have a FK in a table, when you create the Form for Data Input, a Combobox is created which looks up a value from the related table, which normally contains just a List of Values to select from.

Hi Mike,

I see where I have gone wrong here. I was trying to create a Type hierarchy - tblDeposits being the supertype, and these other tables (tblInhumationInfo, tblCremationInfo, tblComingledInfo, tblPartialSecondaryInfo, and tblDisarticulatedInfo) being the subtype - Every inhumation is a deposit, but not every deposit is an inhumation - does that make sense? In this case, they should be linked to tblDeposits by a one-to-one relationship right?

[mike60smart]

Hi Michael

When you add a new record into tblDeposits I am assuming that you then want to add related data into tblInhumationsInfo.

Can you give me an example of the data you would enter into tblInhumationsInfo please.

[Bob Fitz]

Have you seen the redesigned db that Vald attached in post #46

[Michael91]

Hi Michael

When you add a new record into tblDeposits I am assuming that you then want to add related data into tblInhumationsInfo.

Can you give me an example of the data you would enter into tblInhumationsInfo please.

Hi Mike, that is correct - or rather I want to enter a new record in a form that adds info to both tables (but I may be going about this wrong). For tblInhumationsInfo there are 6 fields which are for data specific to one type of deposit (inhumations) but not relevant to all other deposit types:

InhumationID - the AutoNumber ID
DepositID_FK - The Foreign key for the deposits table that it turns out I don't need?
Position - The position the deposit (skeleton in this case) is in - they could be lying on their back (supine), on their front (prone), or crouched on their left or right side, or "dumped in", or several other options really.
Bound - Do they appear to have been wrapped, bound, or tied up before burial (like in a shroud, or with ropes) - YES/NO
Orientation - Which compass points are they aligned to (e.g. most Christian burials are placed East-West) - There are basically 9 main options N-S, NE-SW, E-W, SW-NW, S-N, SW-NE, W-E, NW-S, plus NotRecorded.
FacingDirection - Which compass direction is the head facing - There are basically 11 main options - N, NE, E, SE, S, SW, W, NW, plus Upwards, Downwards, and NotRecorded

I hope that is clear?

[Gicu]

Michael,

You do not (usually) add records in two tables at once in the same form; as I was explaining in post #46 you do that in a (main) form\subform setup where you link the form to the subform via the PK\FK fields (so the FK get populated automatically when you enter records in the subform).

Cheers,

[Michael91]

Hi Michael,

Please have a look at the new form. Basically the foreign keys controls need to be replaced with combo's as @orange explained. As for the 1-to-many relationships those are handled with form\subform setup as shown in frmSites.

Cheers,

Hi again Vlad! Thank you for this! I see where I have been going wrong. I will get redesigning.

In the frmSites you made - You have the subform sfrmSiteTimeFrameLink embedded - I think I have built these tables wrong? I want it to display/allow me to enter the TimeFrame values (e.g. 1300-1201 BC, 1200-1101 BC, 1100-1001 BC) not their IDs - or allow for a multi-entry box like you made for frmDeposits. How do I fix this? Additionally, can multiple subforms be added to a form?

I will start working on redesigning the FK tables so they have combos

I really thought i was starting to get it, ah well!

[Michael91]

Michael,

You do not (usually) add records in two tables at once in the same form; as I was explaining in post #46 you do that in a (main) form\subform setup where you link the form to the subform via the PK\FK fields (so the FK get populated automatically when you enter records in the subform).

Cheers,

Thanks Vlad, apologies for not grasping this sooner.

[mike60smart]

Hi Michael

Yes very clear.

Study the Relationship Diagram dealing with tblDeposits and the relationships between tblInhumationInfo and related - Positions, Orientation and Facing Direction.

Note that each of the tables tblPositions, tblOrientation and tblFacingDirection and all Lists of Values you would be able to add to if necessary.

I created a Main Form based on tblDeposits and a Subform based on tblInhumationInfo.

I set the Subform as Continuous Form View - it can be set as Single Form View if required.

[Michael91]

Hi Michael

Yes very clear.

Study the Relationship Diagram dealing with tblDeposits and the relationships between tblInhumationInfo and related - Positions, Orientation and Facing Direction.

Note that each of the tables tblPositions, tblOrientation and tblFacingDirection and all Lists of Values you would be able to add to if necessary.

I created a Main Form based on tblDeposits and a Subform based on tblInhumationInfo.

I set the Subform as Continuous Form View - it can be set as Single Form View if required.

Hi Mike

Thank you very much! This, combined with what @Orange and Vlad said, is making more sense I think. I will redraw another logic model, redesign my tables around what you have done for inhumations, and test it again.

One thing - If I am have inhumationsinfo as a subform in frmDeposits, can I build four more forms the same, but switching out the subform, for the other deposit types? e.g. frmDeposits with subform frmCremationsInfosubform or will this cause issues in the data that is going into tblDeposits?

Thanks again!

[Gicu]

Michael,

I will start working on redesigning the FK tables so they have combos

don't do that (if you mean adding lookups to the tables directly)! See this link for why you should avoid that: http://access.mvps.org/access/lookupfields.htm
Instead do what I just did, right-click the textboxes bound to the FKs and choose Change To and select combo box; now set the column number to 2 (or more if you need to show more info like I did for SiteID), set the column widths to 0';x' (0 to hide the first bound numeric column and x being whatever size you need to show the descriptive value).

As for the timeframe ID vs values the same Idea applies; just change that into a combo as described above and it will display the values but store the ID (there are many advantages to that as it saves space, faster searches and allows for editing of the description without having to update all previous records).
EDIT: forgot to mention, I never use multi-value fields, that is what the join tables are doing.
Cheers,

[Michael91]

Michael,

don't do that (if you mean adding lookups to the tables directly)! See this link for why you should avoid that: http://access.mvps.org/access/lookupfields.htm
Instead do what I just did, right-click the textboxes bound to the FKs and choose Change To and select combo box; now set the column number to 2 (or more if you need to show more info like I did for SiteID), set the column widths to 0';x' (0 to hide the first bound numeric column and x being whatever size you need to show the descriptive value).

As for the timeframe ID vs values the same Idea applies; just change that into a combo as described above and it will display the values but store the ID (there are many advantages to that as it saves space, faster searches and allows for editing of the description without having to update all previous records).
EDIT: forgot to mention, I never use multi-value fields, that is what the join tables are doing.
Cheers,

Ah thanks! I think I know what you mean, I will give that a go for one or two tables and send it back tomorrow to see, if that’s okay? I will implement Mike's changes at the same time.
Also just so I am sure, for the tables where I have made a DepositID_FK field (like in tblCremationsInfo, see posts #47-8), these FK fields should be removed yes?

[mike60smart]

Hi Michael

Same question regarding tblCremations.

What data would you store in this table assuming that tblCremationInfor is related to tblDesigns

[Michael91]

Hi Michael

Same question regarding tblCremations.

What data would you store in this table assuming that tblCremationInfor is related to tblDesigns

Hi,

tblCremationInfo serves the same purpose as tblInhumationsInfo, but for a different deposit type (Cremations not inhumations), so:

CremationID - the AutoNumber ID
DepositID_FK - The Foreign key for the deposits table that it turns out I don't need
Urned - Are they in an urn/vessel or not - YES/NO (Quite often archaeological cremations were just put straight in a hole, or in a bag made of material that does not survive, like cloth)
Weight - Number field - How much cremated bone is present, in grams

The reason there are different fields for cremations, and inhumations, is that different data can be gleaned from them - for example, you can't get a skeletal position if the skeleton is cremated.

I hope that helps?

I can do the same for the other deposit types too?