Multi-table queries not displaying any data

[Michael91]

I do not have it - Mike recommended it but I haven't gotten round to it yet, and don't have much money spare at the moment

[Michael91]

I think I am at the point where I can add some sample data and try some queries (as well as checking that the model fits the data as needed) - I have created a new blank database, and imported all the tables, forms and relationships from Database 12.6, using the "new data source" option in the "import and link" section of the "external data" tab on the menu. I made sure to select "definition only" in the options for the import. It looks okay, but before I enter any data, have I gone about this correctly?

I have made sure that Option Compare Database and Option Explicit are at the start of each of the modules I think.

Latest version attached - the imported one with no data. Once I know this is okay I will start adding data to it. I have named this one V.13 Database v 13 - blank.zip

[orange]

No problem Mike. I just saw some line numbers and error procedure code that looked like MZTools. I find it easy to add indentation and line numbers with it.

[orange]

Michael,

Downloaded V13. Is there something specific to look at?

[Michael91]

Michael,

Downloaded V13. Is there something specific to look at?

Not specifically, I just wanted to check I went about moving all the tables etc to the new version correctly? If I am going to start putting real data in, I would prefer to start fresh so all the ID numbers are at 1 when I begin

[orange]

Good stuff. Autonumbers are really for the use of the database system. They are unique, not necessarily sequential nor positive.They aren't meant to be of concern to you. They are used by the system to keep track of records within tables. Once you start a record, even if you back out or cancel, that autonumber is gone/used.

Things seem to be going well.

[Michael91]

Hi,

I have been unable to do anything to the database the last few days, but I just tried to enter some data into V.13 and none of the drop-downs or listboxes work? They are all totally blank. I assume I did something wrong when I imported everything to the new database?
EDIT - I have figured it out - when importing without the data (definition only), I am removing all data in every table, including the preset data for combos and list boxes. Is there a way to import just these? Not the sites/deposit data entered through forms?

Good stuff. Autonumbers are really for the use of the database system. They are unique, not necessarily sequential nor positive.They aren't meant to be of concern to you. They are used by the system to keep track of records within tables. Once you start a record, even if you back out or cancel, that autonumber is gone/used.

I see your point, but if I can start fresh on ID1 with my new data it will be much less irritating for me personally, plus I will need to discuss and identify each deposit uniquely (in papers, written reports and other publications) so the Deposit IDs will serve this purpose? And again, it will make much more sense for publications if the data didnt start on ID 30 or 40, but on ID 1.

[orange]

Michael,

If having an identifier for your use or for others is required, then you can create a field in the table. You will be responsible for maintaining that number. This is a field whose value is important to you. That's a different purpose than an autonumber. Use an autonumber for the database to uniquely identify records in the table. Use you own identifier for your use

Tell me more about the list boxes and drop downs/combos and I'll take a look. Can you give me records or send me your database?

UPDATE:
Just reread your post #137
"I made sure to select "definition only" in the options for the import" That's saying--just the table structure--NO DATA

I'm sure that's the issue re no data in your rowsources fro combos and listboxes.

I haven't done anything more than adding my tables to your database. The tables beginning data_ are my tables and are the only tables with records as seen below.

Table................RecCount..RunDate
data_CommentsT 0017 01-Jun-22
data_dictionary 0166 01-Jun-22
Data_PropertiesAllControlsAllForms 14617 01-Jun-22
data_tblControlsOnForms 0264 01-Jun-22
tblAgeCategories 0000 01-Jun-22
tblAMS_C14_Dates 0000 01-Jun-22
tblAssociatedGoods 0000 01-Jun-22
tblAssociatedGoodsLink 0000 01-Jun-22
tblBound 0000 01-Jun-22
tblComingledInfo 0000 01-Jun-22
tblCountries 0000 01-Jun-22
tblCremationInfo 0000 01-Jun-22
tblCutshapeLink 0000 01-Jun-22
tblCutShapes 0000 01-Jun-22
tblDatedBy 0000 01-Jun-22
tblDatedByLink 0000 01-Jun-22
tblDepositContextLink 0000 01-Jun-22
tblDepositContexts 0000 01-Jun-22
tblDeposits 0000 01-Jun-22
tblDepositTypes 0000 01-Jun-22
tblDisarticulatedInfo 0000 01-Jun-22
tblDisarticulatedTaphonomyList 0000 01-Jun-22
tblElements 0000 01-Jun-22
tblFacingDirection 0000 01-Jun-22
tblInhumationInfo 0000 01-Jun-22
tblOrientations 0000 01-Jun-22
tblOsteoAnalysisList 0000 01-Jun-22
tblOsteoInfo 0000 01-Jun-22
tblOsteoLink 0000 01-Jun-22
tblOsteoSex 0000 01-Jun-22
tblPartialElementsLink 0000 01-Jun-22
tblPartialElementsList 0000 01-Jun-22
tblPartialSecondaryInfo 0000 01-Jun-22
tblPeriod 0000 01-Jun-22
tblPositions 0000 01-Jun-22
tblScienceAnalysisList 0000 01-Jun-22
tblScienceInfo 0000 01-Jun-22
tblScienceLink 0000 01-Jun-22
tblSides 0000 01-Jun-22
tblSites 0000 01-Jun-22
tblSiteTimeFrameLink 0000 01-Jun-22
tblSiteTypeLink 0000 01-Jun-22
tblSiteTypes 0000 01-Jun-22
tblTaphonomyLink 0000 01-Jun-22
tblTimeFrames 0000 01-Jun-22
Total tables (non system) count :45

[Michael91]

Michael,

If having an identifier for your use or for others is required, then you can create a field in the table. You will be responsible for maintaining that number. This is a field whose value is important to you. That's a different purpose than an autonumber. Use an autonumber for the database to uniquely identify records in the table. Use you own identifier for your use

Tell me more about the list boxes and drop downs/combos and I'll take a look. Can you give me records or send me your database?

See above (edited post) for the blank database issue - it is just V.13, the last one i sent through. As for the identifier, surely the DepositID will work fine? Its exactly what I want it to do, and an autonumber eliminates user error (accidentally adding the same identifier twice etc). Is there an issue with using the DepositID as a unique identifier?

[orange]

Michael,

No problem using the DepositID which is The unique ID for each individual in the database(for database use) BUT, it may not be sequential, could have gaps, and possibly even negative.
You can add a new field eg DepositNumber and value that with whatever makes sense to you.

DepositNumber - A number assigned by Michael to identify a Deposit for discussion with colleagues.

[orange]

Michael,These are the record numbers for tables in the C:\Users\JP\Documents\Database v 12.6_J.accdb

I'm having difficulty getting proper formatting from some debug.print

data_CommentsT...........................0017...01-Jun-22
data_dictionary..........................0166...01-Jun-22
Data_PropertiesAllControlsAllForms......14617...01-Jun-22
data_tblControlsOnForms..................0264...01-Jun-22
tblAgeCategories.........................0014...01-Jun-22
tblAMS_C14_Dates.........................0003...01-Jun-22
tblAssociatedGoods.......................0010...01-Jun-22
tblAssociatedGoodsLink...................0009...01-Jun-22
tblBound.................................0003...01-Jun-22
tblComingledInfo.........................0002...01-Jun-22
tblCountries.............................0003...01-Jun-22
tblCremationInfo.........................0002...01-Jun-22
tblCutshapeLink..........................0000...01-Jun-22
tblCutShapes.............................0010...01-Jun-22
tblDatedBy...............................0007...01-Jun-22
tblDatedByLink...........................0000...01-Jun-22
tblDepositContextLink....................0000...01-Jun-22
tblDepositContexts.......................0017...01-Jun-22
tblDeposits..............................0009...01-Jun-22
tblDepositTypes..........................0005...01-Jun-22
tblDisarticulatedInfo....................0004...01-Jun-22
tblDisarticulatedTaphonomyList...........0009...01-Jun-22
tblElements..............................0031...01-Jun-22
tblFacingDirection.......................0012...01-Jun-22
tblInhumationInfo........................0008...01-Jun-22
tblOrientations..........................0010...01-Jun-22
tblOsteoAnalysisList.....................0003...01-Jun-22
tblOsteoInfo.............................0005...01-Jun-22
tblOsteoLink.............................0002...01-Jun-22
tblOsteoSex..............................0009...01-Jun-22
tblPartialElementsLink...................0006...01-Jun-22
tblPartialElementsList...................0014...01-Jun-22
tblPartialSecondaryInfo..................0003...01-Jun-22
tblPeriod................................0011...01-Jun-22
tblPositions.............................0014...01-Jun-22
tblScienceAnalysisList...................0010...01-Jun-22
tblScienceInfo...........................0005...01-Jun-22
tblScienceLink...........................0011...01-Jun-22
tblSides.................................0004...01-Jun-22
tblSites.................................0003...01-Jun-22
tblSiteTimeFrameLink.....................0005...01-Jun-22
tblSiteTypeLink..........................0002...01-Jun-22
tblSiteTypes.............................0014...01-Jun-22
tblTaphonomyLink.........................0003...01-Jun-22
tblTimeFrames............................0022...01-Jun-22
Total tables (non system) count :45

[orange]

Michael
To get the data into the tables:
-you could create a new vXX, and import structure and data
-for the tables where you do not want "test records",
you can delete the records -then do a compact and repair and that will reset autonumbers on tables where there are no records.This would give all of the sources for combos and listboxes,and would empty tables with "test records".

[Michael91]

Hi everyone,
Apologies for the slow reply.

@Orange, thanks for your responses, I have added a DepositNumber field as you suggested. This is currently blank, I will fill it in right at the end of the project.
I also followed your instructions in post #147 and it worked perfectly, thank you!

I have now added a good chunk of real sample data in, and built three simple queries.

Two of them seem to work fine, but they are showing data in its numerical format (qryDepositContextsOverTime and qryInhumationsOverTime) – is there a way to get them to show this as text instead? Not a big deal if not, it would just save me having to check what each number represents.

My third query has a bigger issue (qrySexVsGraveGoods). The aim of this query is to see if there are patterns between the sex of the dead (deposits) and the things they were buried with (Associated Goods), e.g. were females more often given pottery, for example. I selected the fields DepositID, AssociatedGoodsID_FK, and AssociatedGoodsDetail from tblDeposits, and the field OsteoSexID_FK from tblOsteoInfo. The issue is that AssociatedGoodsID_FK is a multi-value field, one of the ones we made with a multi-select listbox and a button. This is not displaying any data at all in the query.

Have I built my query wrong? 😊

Latest database version attached - Database v 13.1 - Query tests.zip

[orange]

Michael,

Can you describe in simple terms what you intended these queries to show? I'm sure there is a need to include other tables to get the text. But for clarity, it you describe your need/intent, we can get things working.

qryDepositContextsOverTime and
qryInhumationsOverTime)
qrySexVsGraveGoods

The -FK fields in tblDeposits reference tables that should be included in your query. The 'Text' you want to display is in those related tables.

[Michael91]

Michael,

Can you describe in simple terms what you intended these queries to show? I'm sure there is a need to include other tables to get the text. But for clarity, it you describe your need/intent, we can get things working.

qryDepositContextsOverTime and
qryInhumationsOverTime)
qrySexVsGraveGoods

No worries

qryDepositContextsOverTime - I want this to show how depositional contexts change over time, e.g. are graves, or cists, or barrows etc more common in certain periods of time. I chose DepositContextID_FK and PeriodID_FK for this, both from tblDeposits.

qryInhumationsOverTime - this is very similar, I wanted to see how many inhumations (Deposit type) dated to each period of time. I chose DepositID, DepositTypeID_FK, and PeriodID_FK for this, all from tblDeposits, and I made sure that for DepositTypeID_FK, the criteria was set to "1" only, because this is the ID number for "inhumations" in tblDepositType.

qrySexVsGraveGoods - The aim of this query is to see if there are patterns between the sex of the dead (deposits) and the things they were buried with (Associated Goods), e.g. were females more often given pottery, for example. I selected the fields DepositID, AssociatedGoodsID_FK, and AssociatedGoodsDetail from tblDeposits, and the field OsteoSexID_FK from tblOsteoInfo.

I hope that is clear?