Page 1 of 11 12345678910 ... LastLast
Results 1 to 15 of 165
  1. #1
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76

    Multi-table queries not displaying any data

    Hi,


    I am new to access, and building a fairly large researchdatabase. So far I have one 'master' table and five related tables that each linkto it. I can get single table queries to run, and multi-table queries betweenthe master table and one other related table, but that is it. I need to createqueries using data from two or more of the other tables, but every time I dothe results is blank. I have tried many videos and online tips but everyexample seems too unrelated to be helpful to me. For information:


    I am working with archaeological data
    The 'Master' table is a list of archaeological sites,their location, overall details about the site and the kinds of things foundthere.
    Tables 2-6 are each a category of data (e.g. burials,structures, metalwork) - these tables each have relationship connections to themaster table
    Tables 2-6 are not mutually exclusive - each site may havedata of all types, or some, or just one.
    Many categories in Tables 2-6 are exactly the same, toensure Access knows to connect them (e.g. site ID, individual ID, site name,depositional context) but some are not, out of necessity, because the data ineach is different.
    Each site has an ID number that is consistent acrosstables 2-6, and each entry in tables 2-6 has its own unique ID number.


    For example:
    Site ID 62 (Tower of London) may have three recordedburials (Table 2, IDs 233-235), and five metalwork finds (Table 4, IDs 41-6).


    How do I get queries to work containing multiple tables?For example, if I wanted to know if cremations and other burials were oftenfound in the same contexts (e.g. graves, pits, ditches)?
    Would I create a query using the master table - Site ID,Site name and then burials table - Site ID, site name, Individual ID,depositional context and then the cremations table - Site ID, Site name,Individual ID, depositional context?

    I have tried adding relationships between tables 2-6 butit would get VERY complicated if each was related to each other, in multiplecategories.

    Sorry if I have been too vague, I can provide more data asneeded, but obviously no data specifics as the information is sensitive. Also, this question is cross-posted to the Microsoft website (here - Access - Multi-table queries - Cannot get data to display - Microsoft Community) - apologies for this, I found that before this site, but this seems like a better place to ask the question.

    Sorry again for my lack of knowledge, as I said Access is new to me.
    Thank you!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    With your query in design view, right or double click on the join line between tables to edit it. You want the option that retrieves all values from the one table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Hi, thanks for replying.

    I tried what you suggested - ran a test query with ONLY two of the tables (burials and cremations), these were connected to one another by Site ID. It definitely produced results (thank you) but I think there is still an issue - I want each of the tables to be considered equally and show me all pertinent data, whereas I think your suggestion gives one table preference over another? And then finds only the data from the second table with info in common to the first (in this case Site ID as that is their relationship).

    How do I avoid this? Make my relationships elsewhere?

    If I included the master table it didn't work at all but that's okay.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of the database or post a screen shot of your Relationship Window, showing your tables and their relationships.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think the design of your db could be improved; instead of five tables for categories have one table only with one extra field to hold the category (burial, cremation, etc.). Add all possible columns to this table and only the SiteID_FK (foreign key) from the main SiteInfo table. You don't need to duplicate the site name, description in this table as you already have it in the main one. Now simply link the main table to this new and improved categories table joining them by SiteId -->SiteId_FK and bring your desired fields in.

    To use your original design you would need to always have the master table with outer joins towards the five category tables (arrow pointing towards the category tables).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Michael91 View Post
    I am new to access
    ...
    Many categories in Tables 2-6 are exactly the same
    ...
    I have tried adding relationships between tables 2-6 but it would get VERY complicated if each was related to each other, in multiple categories.
    ...
    Sorry if I have been too vague, I can provide more data asneeded, but obviously no data specifics!

    It sounds to me like we better make sure your table structure is good first. Can you post a screen shot of your 'relationship view' window? Or post a copy of your database with the sensitive data removed.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @Michael91
    I agree with the good advice offered by the other members. You table designs could be improved as Vlad suggested. I think it would be helpful to you and readers if you could describe in simple plain English 'a day at the site' or " here's what we found at siteA and at siteB". I want to understand your "archaeological" business as it pertains to you and what your database will support.

    You know your business better than readers, so it is dependent on you to bring us up to speed.

  8. #8
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Quote Originally Posted by Bob Fitz View Post
    Can you post a copy of the database or post a screen shot of your Relationship Window, showing your tables and their relationships.
    Hi Bob,

    Unfortunately I don't think it would be right to post the data itself, given that it deals with sensitive subjects (human remains) and it is part of ongoing research, so not entirely mine to share publicly.

    I have attached my (messy) relationships window though. The site list (Master) is in the centre, with the other tables at the sides (five main tables currently), and the tables with AMS on them are subtables - they are working as needed and not related to the queries. I have deleted all relationships between the five tables in question, as it may be better to start from scratch.

    I am certain I am doing things wrong and I appreciate all the help


    Click image for larger version. 

Name:	relationships.jpg 
Views:	70 
Size:	65.2 KB 
ID:	47744

  9. #9
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    @Gicu

    This seems like a good suggestion but I am not sure I know how to make that work for my data - each table has different data, and so different columns - are you suggesting I add ALL columns from all tables into one big table? Won't it make data entry completely unweildy? I am entering a lot of data, and not all of it fits into neat boxes, so I created five main tables to help me better subdivide the data and enter it more easily.

    Am I misunderstanding?

  10. #10
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    @kd2017
    This has now been done

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am certain I am doing things wrong and I appreciate all the help
    Let's start with a "business description". And then, develop a model.
    Also, for reference, Access does not play well with names with embedded spaces or special characters (/? ). The underscore "_" is accepted.

  12. #12
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Quote Originally Posted by orange View Post
    @Michael91
    I agree with the good advice offered by the other members. You table designs could be improved as Vlad suggested. I think it would be helpful to you and readers if you could describe in simple plain English 'a day at the site' or " here's what we found at siteA and at siteB". I want to understand your "archaeological" business as it pertains to you and what your database will support.

    You know your business better than readers, so it is dependent on you to bring us up to speed.
    Okay - this is research data for a large project, looking mainly at burial trends over an extended period. I am collecting data from multiple sites and sources (several hundred sites at least), so I can answer broad questions like - "what things were people buried with? Did this change over time?" or "Are people in eastern Scotland treated differently than people from Northern England?" or "where do cremations occur, and how frequently?" etc etc. These are example questions, the data should be presented in a flexible way so I can ask many questions of it, as it develops. The data will drive some of the questions so I can't design the database with one or two specific questions in mind.

    So if I am collecting data - I record the site name, its location (county, country, grid reference), the type of site it was (settlement, cemetery, industrial, defensive, etc), the period it dates to (multiple categories, one for each 100 years, in a yes/no format) and the material found there as an overview (e.g. 3x cremations). If the site has 3 cremations I go to the cremations table, I enter the broad site info, so it is linked to the main table (site name, county, site type), and then all relevant data for each cremation (age, sex, weight of material, grave goods, depositional context, urn type etc). I have one entry per cremation.

    Does that help?

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It's a start.
    In order to answer questions, you have to have data recorded that is relevant to the question.
    Eastern Scotland..Northern England sets some broad geog scope.
    sites and sources
    "what things were people buried with" ---some representative data would help

    I record the site name, its location (county, country, grid reference), the type of site it was (settlement, cemetery, industrial, defensive, etc), the period it dates to (multiple categories, one for each 100 years, in a yes/no format) and the material found there as an overview (e.g. 3x cremations). If the site has 3 cremations I go to the cremations table, I enter the broad site info, so it is linked to the main table (site name, county, site type), and then all relevant data for each cremation (age, sex, weight of material, grave goods, depositional context, urn type etc). I have one entry per cremation.

    Note: It might be helpful if you could tell us about each of the tables in your graphic. You could describe things in terms of John Doe without revealing any specifics. Mr X or Ms Y.... It would show us some of your thinking regarding relating topics and the level of detail involved.



    How readily available is the detail info given that you're dealing with 100 yr periods for some things.

    Do you have a list of questions you want to be able to ask your data/database? This can help qualify the ifo to be collected and at what level.
    Do you have a form for manual collection of data? ??Has someone attempted similar study previously??
    Do you have access to automated records?


    You may find the dialog/interaction in this thread helpful - at least some concepts.

    Below are a few things I found related to archaeological database




  14. #14
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    It might be helpful if you could tell us about each of the tables in your graphic. You could describe things in terms of John Doe without revealing any specifics. Mr X or Ms Y.... It would show us some of your thinking regarding relating topics and the level of detail involved.

    Okay - the "Inhumations" table deals with the buried (inhumed) dead - each has an individual ID number, and then the site ID and name linked to the site table, to help identify each individual. They then have a burial number (this is short text, the original number given by their excavator), then a YES/NO column for if they have been carbon dated (this links to the AMS subtables), and a short text column for the period they date to (Bronze Age, Iron Age etc). Then there is contextual data columns, all short text also - site context (cemetery, settlement etc), depositional context (grave, pit, ditch, cist, barrow etc), grave shape, and dimensions. None of these can be easily divided neatly into preset categories, as while there are often common types, there will always be variation. Then there are more short text categories about the skeletal material - burial position, orientation, associated material (grave goods), state of preservation. Then there are osteological data categories (age, sex, stature etc) - these could be easily categorised but are currently short text. After this there are a couple of categories relating to scientific analysis of the skeletal remains (isotopes, aDNA) which are yes/no columns. Then finally a long text notes column for everything else.

    The other tables follow a similar format, though some columns are different because the data that can be recorded is different. For example, one table deals with loose fragments of human bone. In most cases age/sex/stature are not identifiable, and skeletal position is irrelevant, but they will have a depositional context, so there is overlap. I could lay out each category in each table for you but it seems redundant?

    Currently I am just working on data from Scotland, I am estimating there will be c.200 sites, and around 1000 individuals/deposits from them, divided amongst the other tables. When this is on I will gather the data from England, which will be much larger

    How readily available is the detail info given that you're dealing with 100 yr periods for some things.

    There is a huge amount of precise data available about each individual/find/deposit, its just that precise dating is not always possible. Where it is, the exact dates are in the AMS subtables

    Do you have a list of questions you want to be able to ask your data/database? This can help qualify the ifo to be collected and at what level.

    As I said there are broad questions of burial treatment and how this relates to society over time, but on a detailed level the data results will drive the questioning.

    Do you have a form for manual collection of data? ??Has someone attempted similar study previously??

    This is a follow-on from earlier research I have done - but I used excel up until now. This project is bigger so it seemed appropriate to use actual database software, which excel obviously is not. I think the issue is I have been working with Access as if it worked like excel, which I am guessing it does not? All data is collected by me, through my research.

    Do you have access to automated records?

    Such as? There are no existing similar access databases I could use if that is what you mean?


  15. #15
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Thanks again for your responses everyone, all the help is appreciated

Page 1 of 11 12345678910 ... LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-15-2017, 03:00 PM
  2. Replies: 4
    Last Post: 05-03-2017, 10:04 PM
  3. multi table queries
    By omid116200@gmail.com in forum Queries
    Replies: 2
    Last Post: 07-19-2016, 09:10 AM
  4. Multi-Table Queries (Where to Start?)
    By dccjr in forum Queries
    Replies: 1
    Last Post: 04-23-2013, 06:34 PM
  5. Replies: 6
    Last Post: 12-30-2011, 09:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums