Results 1 to 11 of 11
  1. #1
    phil123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    22

    Sql help!

    Hi,



    I have a form, frmMainScreenView, and a query, qrySearchBySeniorAdmin. On a separate form I use a combo box to select a Senior Administrator, then on pressing the command button, the following expression is actioned:

    DoCmd.OpenForm "frmMainScreenView",,"qrySearchBySeniorAdmin"

    This works fine. However, the frmMainScreenView has a number of subforms which are viewed via a navigation which I want to include in the query.

    For example, one of the subforms is "frmMembershipCompleted", and within this are two fields I want to filter on: "DateMembershipCompleted" and "DateInvoiceCompleted". If these two fields are left blank, it means they are yet to be actioned, and I want the above query to include this criteria when it opens the form, and only open the records with outstanding actions (or blanks).

    I feel like this it is possible to add some extra code to the DoCmd....thread above, such as an Is Null expression, button I cannot fathom this.

    I also have a few extra subforms which will need to be brought in to the query/SQL, but as long as I can get one to work I'm sure I can keep replicating.

    Thanks,
    Phil

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the subforms have their own query bound to the master form.
    so filtering the master form, will only show data in the subform for those.

    if you want different data, make a different subform.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There should be no need to create a new subform as previously suggested
    Yes you can do this with the same subform if you get your filtering correct.

    The only complication may be if you are using a built in navigation form as these can be difficult to modify (and can't have multiple subforms)
    You may therefore need to build a form & subform yourself

    If you post your existing subform record source and details of what you want, one of us should be able to provide amended code for you.
    Also please tell us the field(s) used for linking the form & subform - master & child fields
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    phil123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    22
    ridders52 - you are a star. Thanks very much for your help.

    In answer to your question regarding the navigation form, to build this I just used the Navigation control function and added a form (which I had already built) to each of the tabs. I'm crossing fingers (and everything) this will be ok.

    So this tab is called Student Membership and is populated using the table 'tblStudentMembership'. Each form is linked together by the same ID number.

    I want to be able to select a Senior Administrator - which will match to the frmMainScreenView, which is populated by the table 'tblMainScreen'. I then want to display the records which have outstanding tasks. So where the value is null in the fields 'DateRegisteringComplete' and 'DateInvoiceComplete' of the 'tblStudentMembership'. I don't know whether this will make it more complicated; but in some cases there may be a value in one of these two fields but not the other, but I still want that record to be displayed. This will all be actioned via a command button which will open the frmMainScreenView and populate each of the tabs.

    There are then a few other subforms in the tabs which I will need to apply the same ruling too, but hopefully I'll be able to work this out from the code you can provide.

    Many thanks,

    Phil

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Suggest you post a stripped down copy of your database or at least the parts described in your last post.
    As I mentioned, I don't use anything to do with built in navigation forms/controls so can't offer code without looking at it.
    If that's a problem, someone else may be able to assist with some 'air code'
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    phil123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    22
    Hi,

    I've attached a stripped down version of the database for you. Obviously all sensitive data and logos have been removed (and a few unnecessary forms for this purpose).

    Hopefully you can see all the forms and tables in question. The table with the command button in called frmSearchBySeniorAdmin (which isn't fully designed yet). The button is 'Search for Outstanding Tasks'. Database Stripped Down.zip I will also be developing the ability to search by other staff types after I've got this sussed!

    I've added a couple of test records as well for you.

    Many thanks,

    Phil

  7. #7
    phil123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    22
    Hi,

    Just wondering if anyone is able to provide any code for this?

    Many thanks,
    Phil

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I looked at you dB and, to me, you have some table design issues that need to be fixed.

    You have what appears to be 6 main table that all have "ID" as the PK field name and they are all in 1-to-1 relationships. To me, this is a major flaw in your design.


    On a lesser note, the table "tblAdditionalCosts" is not normalized. There are 6 fields that are repeating. There should only be the 6 fields with an additional field to select the "Group". (Centre, PGGrad, Other1 and Other2)



    Good luck with your project..........

  9. #9
    phil123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    22
    Steve, thanks very much for taking the time to look at this. I appreciate your advice as I'm still fairly new to building databases.

    What would you recommend with regards to the 6 tables which have ID as the PK? The information in each of the tables all come together on one form to make up one record. I linked these together using the ID field to ensure the correct information loads for every record. They are 1-to-1 relationships as the information will only relate to other records with the same ID number.

    Thanks
    Phil

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The following are my observations. This is no reflection on you.

    It doesn't look like there was much time put in to design the tables. Maybe the tables were copied over from a spreadsheet design.

    In your posts you have said what you are doing and what you want to happen, but nothing about the process/purpose of the dB.
    How would you describe what the dB is for to a 9 year old that knows nothing about computers? In 5 - to 10 sentences.


    Looking at the relationship window, it is hard to understand what the tables are for by their names. It is very rare that there are 1-to-1 relationships in table designs, let alone 6 of them. It appears that table "tblMainScreen" is the main table because the PK field is an autonumber and, in the other 5 tables, the PK field is a number (Long Int). But what kind of data is stored in "tblMainScreen"?
    Click image for larger version. 

Name:	6tables.png 
Views:	16 
Size:	183.1 KB 
ID:	34445
    I see a couple if fields named "FullName". It is better to use "FirstName" and "LastName"; much easier to combine fields that split them.





    Lets look at 2 of the tables: "tblStudentMembership" and "tblHigherApprent".
    "tblStudentMembership" does not appear to be about students (no student named or info) and I have no idea what "tblHigherApprent" is about. But they each have the same 15 fields. If the relationships for the 6 tables are truly 1-to-1, why the duplicate fields?
    Click image for larger version. 

Name:	2Tables.png 
Views:	16 
Size:	66.5 KB 
ID:	34444
    Also in "tblStudentMembership", what are Level4, Level5, Level6, Level7 and Placement?


    Then look at the table "tblAdditionalCosts". It has repeating fields. (I've numbered them.)
    Click image for larger version. 

Name:	DupFields.png 
Views:	16 
Size:	60.5 KB 
ID:	34446
    What would happen if you needed to add another group, say Doctorate (yes, I'm reaching ). You would have to add 6 more fields to the table, then modify any queries, the forms involved, any reports and don't forget the VBA code.
    All you should have is 5 fields (I would not have the "TickComplete" field - if you have an entry in the "DateComplete" field, it is complete), plus a field for the "GroupType" (Centre, PGGrad, Other1, Other2, etc)... so 6 fields, plus the PK field, plus the FK field to link the record to the main table. (8 fields total)

    tblAdditionalCosts
    --------------------------
    AddCostsID_PK - Autonumber
    MainTableID_FK - Number (Long) (link to maintable)
    GroupTypeID_FK - Number (Long) (link to GroupType table)
    LicenseFee -
    DueDate - Date
    Responsible - Text
    ResponsibleName - Text
    DateCpmplete - Date

    And the new group type table:

    tblGroupTypes
    -------------------------
    GroupTypeID_PK - Autonumber
    GroupTypeName - Text (Centre, PGGrad, Other1, Other2, etc)

    (Note: I use "ID" to denote the field is numeric, "_PK" and "_FK" suffixes for primary and foreign key fields)


    Other things:
    tblHigherApprent.Company/cohort - should not have a special character ("/") in the name.
    I would remove all look up fields in the tables. See The Evils of Lookup Fields in Tables


    I would suggest going back to the drawing board... literally.
    Think "A Beautiful Mind" with Russel Crowe. I frequently use dry markers on by office windows when design/modifying tables, before getting on the computer.



    Again, there are my observations and thoughts. No offense meant.

  11. #11
    phil123 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    22
    Sorry for the delay, not had chance to read through this properly over the last few days!

    Thanks very much for this advice, I'm still new to database building so this is all helpful stuff.

    Phil

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

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