Page 11 of 11 FirstFirst ... 234567891011
Results 151 to 164 of 164
  1. #151
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,171
    Michael,

    For the first query.


    Starting in query Design view with tblDeposits;
    Select and drag tblDepositContexts to the query area
    Select and drag tblPeriods to the query area

    tblDeposit and tblPeriod have a direct link via your relationships, so you'll see that "join" automatically
    tblDepositContexts can be "joined" to tblDeposits, as shown in the video. Drag the field to the corresponding field in the other table and the "join" will appear.

    Go to Run and you'll get text based on the related tables.

    It appears that the tblDepositContextsLink is not required. There my be other link tables that prove to be redundant/not required. We'll have to review as project progresses.


    I'll look at the others, but I'm sure they are similar.

    If there is a FK in tbldeposit that relates to another table directly, then the Link tables may not be needed.


    Update: Michael, it appears the gif file was converted to jpg--so is not animated. I'll try again.
    Just confirmed it is converting gif to jpg.

    I put the gif inside the zip. I hope you can view it.
    Attached Files Attached Files

  2. #152
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,171
    RE second query. Same issue.

    Here is SQL for a query which should give you the texts.
    Open a query design, select SQL view and paste in the SQL below.
    Once it is saved you can open the query in Design view to see the tables involved.
    Run the query and see your result.

    Code:
    SELECT tblDeposits.DepositID, tblDepositTypes.DepositType, tblPeriod.Period
    FROM tblPeriod INNER JOIN (tblDepositTypes INNER JOIN tblDeposits ON 
    tblDepositTypes.DepositTypeID = tblDeposits.DepositTypeID_FK) ON
     tblPeriod.PeriodID = tblDeposits.PeriodID_FK
    WHERE (((tblDeposits.DepositTypeID_FK)=1));

    Here is the basic part of query 3. Copy and paste into the query design "sql view". Then view in Query Design to see tables involved and "joins".

    Code:
    SELECT tblAssociatedGoods.AssociatedGoodsType, tblDeposits.AssociatedGoodsDetail, tblOsteoSex.OsteoSex
    FROM (tblAssociatedGoods INNER JOIN 
    (tblDeposits INNER JOIN tblAssociatedGoodsLink ON
     tblDeposits.DepositID = tblAssociatedGoodsLink.DepositID_FK) ON 
    tblAssociatedGoods.AssociatedGoodsID = tblAssociatedGoodsLink.AssociatedGoodsID_FK)
     INNER JOIN (tblOsteoSex INNER JOIN tblOsteoInfo ON
     tblOsteoSex.OsteoSexID = tblOsteoInfo.OsteoSexID_FK) ON
     tblDeposits.DepositID = tblOsteoInfo.DepositID_FK;
    Good luck.

  3. #153
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Thanks Orange!

    I copied your GIF for Query 1 and 2, they work great! I copied your SQL for query 3 and that works too

    To test things I made more queries, and added criteria to Query 3 - for AssociatedGoodsType I have excluded the term "None" so it only returns results with Associated goods.

    The four new queries I made are:

    qryAdultGraveGoods - this is a query to see which deposits were of adult age, and were placed with associated goods. For this I created criteria in AgeCategory that selected only adult ages (e.g. Young adult, Middle adult). This seems to work fine, but occasionally when I close and open it, it asks for a parameter value? Is this an issue?

    qryUrnedCremations - This is a query to show which cremation deposits were urned (in a vessel of some kind)

    qrySiteTypes - This is a query to show what site type each site is, e.g. cemetery

    qryDepositAges - This is a query to show the age of each deposit, so I can later create a chart showing age distribution.

    All of these appear to be working great now! Attached is the latest database version for clarity - Database v 13.2 - New queries.zip

    What should my next step be?

  4. #154
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,053
    Hi Michael

    See if your qryAdultGraveGoods should be more on the lines of only showing those records which have associated goods.

    If there are None then the result is as shown :-

    Code:
    SELECT tblDeposits.DepositID, tblSites.SiteName, tblAssociatedGoods.AssociatedGoodsType, tblAgeCategories.AgeCategory
    FROM ((tblSites INNER JOIN tblDeposits ON tblSites.SiteID = tblDeposits.SiteID_FK) INNER JOIN (tblAssociatedGoods 
    INNER JOIN tblAssociatedGoodsLink ON tblAssociatedGoods.AssociatedGoodsID = tblAssociatedGoodsLink.AssociatedGoodsID_FK) 
    ON tblDeposits.DepositID = tblAssociatedGoodsLink.DepositID_FK) INNER JOIN (tblAgeCategories INNER JOIN tblOsteoInfo 
    ON tblAgeCategories.AgeCategoryID = tblOsteoInfo.AgeCategoryID_FK) ON tblDeposits.DepositID = tblOsteoInfo.DepositID_FK
    WHERE (((tblAssociatedGoods.AssociatedGoodsType)<>"None") AND ((tblAgeCategories.AgeCategory)="Young adult (18/21-25)" 
    Or (tblAgeCategories.AgeCategory)="Young to Middle adult" Or (tblAgeCategories.AgeCategory)="Middle adult (25-45)" 
    Or (tblAgeCategories.AgeCategory)="Middle to Old adult" Or (tblAgeCategories.AgeCategory)="Old adult (46+)" Or (tblAgeCategories.AgeCategory)="Adult"));

  5. #155
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,053
    You can also simplify the query by using In() vice all of the Text Values for Age

    Code:
    SELECT tblDeposits.DepositID, tblSites.SiteName, tblAssociatedGoods.AssociatedGoodsType, tblOsteoInfo.AgeCategoryID_FK, 
    tblAgeCategories.AgeCategory 
    FROM ((tblSites INNER JOIN tblDeposits ON tblSites.SiteID = tblDeposits.SiteID_FK) INNER JOIN 
    (tblAssociatedGoods INNER JOIN tblAssociatedGoodsLink ON tblAssociatedGoods.AssociatedGoodsID 
    = tblAssociatedGoodsLink.AssociatedGoodsID_FK) ON tblDeposits.DepositID = tblAssociatedGoodsLink.DepositID_FK) 
    INNER JOIN (tblAgeCategories INNER JOIN tblOsteoInfo ON tblAgeCategories.AgeCategoryID 
    = tblOsteoInfo.AgeCategoryID_FK) ON tblDeposits.DepositID = tblOsteoInfo.DepositID_FK
    WHERE (((tblAssociatedGoods.AssociatedGoodsType)<>"None") AND ((tblOsteoInfo.AgeCategoryID_FK) In (7,8,9,12,16)));

  6. #156
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,171
    Hi Michael,

    Things look good. There are some variations in code as Mike points out. If you are getting the results you expect from a given query, then I'd refrain from trying to alter the code. There's lots to learn for sure, but at this point making sure/confirming the structure of the database (tables and relationships) support your requirement is key.

    One thing you may find is that when creating queries in the query design, try to arrange the tables such that there are few or no crossed lines. This for clarity and communication. The database doesn't care, in fact that interface is for the human(user/designer/programmer). Some will work directly with SQL, but as you are progressing, I suggest staying with query design and organizing the tables so you can see the tables, the fields and the relationships. If and when you collaborate with colleagues the "visuals" in query designs may support a discussion point.

    As I mentioned, you may find that some tables and/or fields may not be required. If you encounter this, the point is to confirm that it is or is not required before removing same from your database.

    The most common cause for getting a message such as Please enter parameter is a misspelling of a field name. I do not get the error when running the query.

    As far as next steps, I suggest that depends on any issues you are/are not finding during data entry. You may find you have to enter some fields in a specific order and that may an issue to investigate. You may not. If you have 5 or 10 questions that you want to have answered by the data, you can work on those, again to confirm the data exists and that it is retrievable.

    I mentioned that tblDepositContextsLink may not be required. If there is a FK in tbldeposit that relates to another table directly, then the Link tables may not be needed. You could investigate your relationships to see if similar set up exists. I'd suggest looking at tables (suffixed ..Link) and see if there is a direct link between the tables where this link table was "intermediary".

    These are the tables to review:
    tblAssociatedGoodsLink
    tblCutshapeLink
    tblDatedByLink
    tblDepositContextLink
    tblDepositSiteContextLink
    tblOsteoLink
    tblPartialElementsLink
    tblScienceLink
    tblSiteTimeFrameLink
    tblSiteTypeLink
    tblTaphonomyLink

    Things are progressing!

  7. #157
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Hi both,

    @Mike60Smart – Thanks for the info, using the numerical codes for the ages is a great idea especially (post #155) but as Orange says, it works currently so I am not going to risk fiddling with it. I will remember this for the next queries though 😊

    @Orange – I have rearranged all the tables as you suggest, and I will do the same with the overall database structure eventually too.
    The “Please enter parameter” problem does seem to have fixed itself, it must have been a spelling issue as you said
    I will create a backup today and explore the possibly redundant tables, but again, everything seems to work so I don’t want to prod at it too much 😊

    I will write some more queries and enter some more data and figure out what else needs tweaking.

    The only issue I am finding at the moment is that when entering a deposit or site I get an error with every button, unless I first click the relevant subform (I assume this “connects” them?). I have attached a video to show what I mean: Button error.zip

    This error happens with every listbox button, but it is not really a problem as the data does seem to be recorded fine? This issue should be present on the last version I sent through.
    Should I be worried or is this working as it should?

  8. #158
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,053
    Hi Michael

    This is the standard error when you are entering a New Record but at the moment you Click the Command Button to run the Event
    the data has not yet been saved to the underlying table.

    To rectify this in the On Click Event for the Button enter the following line of Code which will Force a Save of the data on the Form.

    If Me.Dirty Then Me.Dirty = False

    Place the line of code before the line :-

    Dim itm As Variant

  9. #159
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Quote Originally Posted by mike60smart View Post
    Hi Michael

    This is the standard error when you are entering a New Record but at the moment you Click the Command Button to run the Event
    the data has not yet been saved to the underlying table.

    To rectify this in the On Click Event for the Button enter the following line of Code which will Force a Save of the data on the Form.

    If Me.Dirty Then Me.Dirty = False

    Place the line of code before the line :-

    Dim itm As Variant
    Thanks Mike! I applied this to every button and it seems to work I assume the expression "Dirty" in this context is just something Access understands? It has a specific meaning/use?

  10. #160
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,053
    Hi Michael

    Yes Data just entered in a Form is classed as Dirty until saved.

  11. #161
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,053

  12. #162
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    That makes perfect sense, thank you

  13. #163
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,171
    Michael/Mike,

    This error happens with every listbox button, but it is not really a problem as the data does seem to be recorded fine? This issue should be present on the last version I sent through.
    Should I be worried or is this working as it should?
    As Mike said, there is an attempt to add record(s) to tblAssociatedGoods. And Associated Goods is in a 1:M relationship with tblDeposits. In the video when you start to enter data for the new tblDeposits record 102, and then go to the listbox for Associated Goods, Record 102 has not been saved into tblDeposits yet. The new Deposits record has some fields with data, so it is "dirty". When you attempt to add related record(s) to tblAssociatedGoods , there is no corresponding record in tblDeposits yet--> that's why you get the 3201 error.

    FYI:
    When you click on the subform, you are moving off the deposit record and that is forcing the tblDeposits record to be saved.
    When you have unsaved changes in the tblDeposits record, you'll notice a "pencil icon" in the left margin. Once the record is saved, the "pencil" is replaced with and "arrowhead/triangle". That's a visual cue.

    It appears that Mike has offered code to resolve the issue.

    Also, it may not be possible to display every table and relationship in your relationships window without crossed lines --physical space limits. You can adjust individual table "boxes" if you go to the bottom right corner of the box and click, it will resize the box. It is good if you can show tables such that all fields are visible --again you may be constrained by physical screen size. BUT, in your queries where there are fewer tables and relationships, I suggest you expand the tables and try to minimize the number of crossed lines.

    Things seem to be progressing well!!

  14. #164
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Hi all,

    I thought I would post an update:

    - The database seems to be working well, I have every table I need, and I have made around 20 queries now, which all work and are updating as I enter more data. There are now around 500 deposits in the database (couldn't put off data entry forever) and I have not had any issues in storing the information I need to.

    I looked through Orange's post #156 and went through every link table to see if they were superfluous:

    Did not delete because they store data
    tblAssociatedGoodsLink
    tblSiteTypeLink
    tblScienceLink
    tblOsteoLink
    tblTaphonomyLink
    tblSiteTimeFrameLink
    tblPartialElementsLink



    Did not delete because I couldn’t create a valid relationship without them
    tblCutshapeLink
    tblDatedByLink

    Deleted
    tblDepositContextLink

    Seeing as everything functions, I see no reason to keep messing with things now, but if you want to see the current version or have any more thoughts/advice/criticisms I would be more than happy to hear them

    I wanted to say another thank you to everyone that helped me, especially Mike60Smart and Orange, you have been amazing! I am going to stay on the forum but probably won't add much to this thread now.

Page 11 of 11 FirstFirst ... 234567891011
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