Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    72

    I'm frustrated since I have all the others working and this one doesn't seem to want to play despite me doing exactly the same thing. It appears to be a quirk of Access and I haven't got the experience or knowledge to solve it which is a real pain considering it looks like I've actually written the query correctly!

  2. #17
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First, your profile says you use 2007 but the attached is a later version. You should update your profile since some of us don't download later versions because we sometimes can't work with them. If one problem, after the point where you provided the attachment, is still that you don't understand why you get too many records if you provide an Outcome as criteria when using the query Analysis Query ActionJHB then the explanation would be this:
    - you have fields a (date), b and c and you ask for records where
    - Date is some value and b is some value OR
    date is same value and c is some value OR
    date is same value and b is some value AND c is some value
    and so on. All or many of these OR conditions are being met. In other words, you get what you ask for (Referral) plus you get outcomes for any other outcomes that satisfy the other criteria.

    So now I'm confused because when I open the report in question (I think) your attached version seems to work. If you want me to check it out further, provide step by step instructions as to what I need to do to replicate the problem. Clarify what the problem currently is and what the outcome should be.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #18
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2013 64bit
    Join Date
    Oct 2012
    Posts
    72
    Thank you Micron. I've changed my profile to reflect using Access 2013. I click Analyse data and then specify this academic year, e.g. since 01/09/2017 till today then I try and specify "Parents Informed" for instance but it still returns all records, e.g. Parents Informed, Referral, Internal Monitoring. Whereas if I specify a field in any of the other boxes it only returns those records, e.g. if I select Year 2 in Year group I get a report analysing the data but only the Year 2 records. Does that make sense? I want to be able to specify a data range but if necessary only analyse data for a particular Outcome so I could specify "Parents Informed" and the rest of the charts would only show information relating to records where the parents were informed. Thank you!

  4. #19
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your design is confusing because you give different forms similar names and captions. Not being the designer and dealing with forms that open automatically, in order to identify what's what it's necessary to see tab views of the forms so I can distinguish between them. You give one form a name, and another form a caption that's almost the same as the other form's name. One other thing you must do is turn on "require variable declaration" in the vbe so that each module has Option Explicit at the top along with Option Compare Database. One more design tip: research naming conventions and pick one. AnalysisQueryActionMainReport should be (using my method) rptAnalysisQueryActionMain. I know what the object is by its prefix. No need to use long words at the end to tell you what it is. Going further, I'd shorten words in a consistent manner, using camel case, so your report name would be rptAnalysisQryActionMain at most. If you're using the name to define it's data source as well, personally I would not but that's me. My associations are decipherable in the nav pane as in frmCust; sbfmCust, rptCust. By this I can quickly identify what's related. To elaborate, Cust is Customer, frm is form, rpt is report and sbfm is subform. Then there's normalization. I'm composing in NotePad at the moment so I'm not certain if anyone has pointed this out yet, but you should read up on the subject. Aspects like names, Issues, Consequence, Outcomes etc. should not be in the behaviour table. You have one table for this whole db whereas you should have at least 7 or more.

    Ok, here's what I see re: your problem. You're constructing a WHERE clause for a report ("AnalysisQueryActionMainReport", acViewPreview, , sWhere) that has no controls on it other than subreport controls, thus any WHERE clause or filter for that report is useless. Not only that, each sub report has a grouping and a count by for it's related aspect, but none of them have any criteria applied. A grouping is not a type of filter. Without criteria, grouping returns all categories/aspects/values over a group but arranges them together (in groups). To demonstrate, copy the record source for the behaviour sub report:
    Code:
    SELECT [Analysis Query ActionJHB].[Behaviour Type], Count([Analysis Query ActionJHB].[Behaviour Type]) AS [CountOfBehaviour Type] FROM 
    [Analysis Query ActionJHB] GROUP BY [Analysis Query ActionJHB].[Behaviour Type];
    and open a new query in sql view. Dump it in and with the analysis builder form open with dates and "parents informed' selected, run this test query and you'll find you get everything. This can happen for 2 reasons in your case. 1) The sub report record source has no criteria in it. 2) Your sub report record source runs the query that has multiple OR statements in it, which I covered earlier.

    Thus,
    - your code to build a WHERE clause is written for a report (main) that has no data controls.
    - your sub reports record souces have no criteria (which may be what you want) BUT the underlying query has multiple OR statements, causing too much data to be returned
    The second issue overrides your attempt to filter the subreports due to the many OR statements. Likely this would be the case even if you try to apply a coded record source for the subreports.

    Your code needs to handle the possibility that certain form controls are empty as you have done. However, you have to replicate the effort for each sub report, not for the main (when the main has no data controls). If you take that route, I wouldn't base any report on a query and attempt to apply the WHERE clause for the sake of clarity. I'd build the whole sql in code and open the report while passing the sql as its record source. Then you have it all in one place, and if you need to see the sql, you Debug.Print it. You could also copy/paste this output into a new query if you ever need to troubleshoot or modify. That is done like:

    Dim sql As String
    Dim strCrit As String <-- Crit is criteria. I'm too lazy to type unnecessary characters.

    sql = "build all the sql up to before the word WHERE" - this would allow you to run that portion without criteria. In this case, maybe never, but it's a habit I would adopt.
    Now build the WHERE part usiing IF's or a Select Case block (If's probably simpler here).
    Then open the report hidden, then apply the recordsource, then make the report visible.
    HOWEVER, you have a major obstacle with respect to applying any filter, WHERE clause or recordsource to your subreports. You used a period in the subreport control name(s) "Report.AnalysisQueryActionBehaviour". Using different syntaxes, I tried to get around that and was not successful. This proves the importance of following naming rules.

    In conclusion, (you're not going to like this) I suggest you read up on normalization and naming conventions and start over with a new db. You can import what you really need from this one into the new one, but I'd do that after studying (you should come to the conclusion that you don't need your table) thus the queries are not useful beyond refreshing your memory as to what they're for. You also appear to be using lookup fields in tables as though they were for data entry - also not advised.

    You can ignore this advice of course, but I truly think you'll be back to this forum several times, trying to solve issues caused by your design. Should you decide to research, here's a list I often recommend:

    Normalization is paramount. Diagramming maybe not so much for some people.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

    Important for success:
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html

    Good luck!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #20
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2013 64bit
    Join Date
    Oct 2012
    Posts
    72
    Thank you for taking the time to give me that detailed explanation. I had (sadly) come to the same conclusion that it is probably better to start over and have a fresh bash at this.

    Thank you for all your time and effort!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-11-2016, 02:34 AM
  2. Replies: 4
    Last Post: 10-13-2015, 02:29 PM
  3. Replies: 5
    Last Post: 07-10-2015, 08:30 AM
  4. Query not returning all values
    By whitelexi in forum Queries
    Replies: 12
    Last Post: 09-06-2014, 11:40 PM
  5. Query not returning null values
    By janelgirl in forum Access
    Replies: 5
    Last Post: 10-11-2011, 10:31 AM

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