Results 1 to 9 of 9
  1. #1
    cwhite is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    4

    Novice's question: Not getting desired results from query accessing multiple tables

    For simplicity's sake, let's say I have a club membership database with 2 tables, tblMember and tblOfficer. tblMember contains the fields memID (key), memActive, memFirstName, and memLastName. tblOfficer contains the fields memID, ofcOffice, and ofcYear. They are joined by memID.



    My problem is creating a query which will include in its dynaset all active members and show their membership number and name for all and their office if they are currently holding office.

    When I create the query using only the tblMember table with the memActive criteria of '=Yes', I get the desired results of all active members. But when I modify the query to add the tblOfficer table without specifying any criteria for the field ofcOffice, the dynaset only shows the active members who have ever held an office. It does not show active members who have never held an office. Why? I suspect it has to do with the tblOfficer table only containing the membership numbers of current or past officers and the conditions of 'active members' and 'all officers'.

    How do I structure my query to produce a dynaset of all active members, whether or not they have held office, and their office if they are currently serving?

    Chuck
    Last edited by cwhite; 02-23-2021 at 04:10 PM. Reason: To correct spelling in Title.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are using an equal join in this query? Double click on the join; you'll see you have 3 options which are described in normal English. You probably want a Left join on members to officer tables but that dialog will present equal, left and right joins as 1,2 or 3.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    cwhite is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    4
    Thanks, Micron. When I double-click the join, I do get 3 options (1, 2, and 3) with the first radio button active. I think your reply suggests I use option #2 (Include ALL records from 'tblMember' and only those records from 'tblOfficer' where the join fields are equal). Is that correct? This database was set up several years ago by another person. I'm trying to help out the current membership person who has much less computer experience than me. Somehow, she has managed to lose all the structures except the tables. It's those missing structures I'm trying to recreate in order reproduce the Membership Directory again. Thanks for your help!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are you sure that this isn't a split database, where the tables are in one file and the forms/reports/queries are in another file?
    If I understand what records you're looking for, yes, that is the join I'm suggesting.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    cwhite is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    4
    I'm not sure about the split database but that sounds very likely. Except that there is a form for entry data into the database within this database (file?). The current Membership person mentioned that she had a problem with her computer and that's when the database got messed up. If it was/is a split database what would she be looking for that would contain the forms/queries/reports (i.e., file extension). It's possible she didn't know about the other file and only retrieved the *.accdb file.

    Chuck

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    They could have the same file extension. The usual way would be to make one mdb/accdb and the other mde/accde. Research 'ms access split database' if this isn't familiar to you. Perhaps there is a front end (fe) file that has the forms/reports/queries/macros, or something went wrong with the db you have. One thing you can try is to go to options > navigation options and select to see all hidden objects as they might actually be there. Or create another db and see if you can import everything (forms, etc) from the current file as they may be there but something corrupted the nav pane (where the objects usually show). Also make sure that in the nav pane, the option to show only a type of object is the issue (select to show all objects).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    cwhite is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2021
    Posts
    4
    After discussing your comments with the current Membership person, we definitely have (had?) a split database. She provided the following additional information. When she tried to open the *.accde file, she got the following error message: "The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. You should backup your database before attempting to open the database and delete the VBA project." Additionally, when she tries to execute the front-end, she gets a graphic image with 2 buttons, 'Add/Update members' and 'View/Print reports', and a message that says, "The expression you entered has a function name that MVMC Membership can't find." Also, a 'Macro Single Step' window shows with 'Macro Name: AutoExec', 'Action Name: RunCode', and 'Error Number: 2425'. Could all this be caused by the *.accdb and *.accde files not being in the expected locations?

    As you have suggested, I have started researching 'MS Access split database'. I've got a lot to learn! I have also checked and the 'Access All Objects' option is selected. Additionally, I have updated the navigation option to see all the hidden objects but nothing additional showed up. I have not yet created another database and tried to import everything.

    Does any of this help clarify the mess we are in and a possible route out of it?

    Thanks for your patience with us and the help you are providing.

    Chuck

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As the article says, you cannot edit mde or accde databases, nor can you import all objects from it into another one. The new error may be due to missing references, which I don't think you can alter in accde files either because you can't open the project editor. You need to find the accdb or mdb file that made your working database. If you cannot, all you can do is start over with that one. At least your data is safe - or is it? Do you do routine backups of that file?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-28-2018, 04:01 PM
  2. Replies: 6
    Last Post: 03-01-2018, 03:55 AM
  3. Replies: 5
    Last Post: 12-02-2017, 06:54 PM
  4. LIKE query does not give desired results
    By MarksinMO in forum Queries
    Replies: 3
    Last Post: 09-03-2015, 06:58 AM
  5. Replies: 4
    Last Post: 10-03-2010, 09:54 PM

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