Results 1 to 13 of 13
  1. #1
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44

    Filtering/removing/excluding the total (Flat) member and post it as comparison value

    Hi,



    How would I exclude the highlighted record from the detail and put it under the calculated members as illustrated?
    Click image for larger version. 

Name:	report.png 
Views:	19 
Size:	38.7 KB 
ID:	48246

    Not sure if it is relevant resp. of help, but here is the design view:
    Click image for larger version. 

Name:	design.png 
Views:	17 
Size:	29.2 KB 
ID:	48247

    I have a rough idea, however lack the knowledge to create this, that an if/sumif approach could lead to the expected result.

    edit: thinking it further through another approach could be to exclude the 'flat' member out of the total calculation and apply a conditional format to that member to distinguish from the others (if this is possible).

    Cheers & thanks,
    Daniel
    Last edited by daredan; 07-07-2022 at 07:04 AM. Reason: another possible approach

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    provide some example data and the sql to your report recordsource.

    At the moment looks like you are storing calculated values (not recommended) but suggest modify your report recordsource to exclude records where entity='flat'

    alternatively since you already have the total value, why not just exclude the total calculations

  3. #3
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44
    Good morning,

    Here is the query I use for the report:
    Code:
    SELECT tblSource.Time, 
        tblSource.Entity, 
        tblMasterData.SortID, 
        tblSource.Acct, 
        tblMasterData.Description, 
        tblSource.ActDKK, 
        tblSource.FcDKK, 
        [ActDKK]-[FcDKK] AS dAbsDKK, 
        IIf([FcDKK]<>0,[dAbsDKK]/[FcDKK],9.99) AS dRelDKK, /*to avoid Div!0 error*/
        tblSource.ActLocCur, /*not being used in this, but a related report*/
        tblSource.FcLocCur, /*not being used in this, but a related report*/
        tblSource.Comment
    FROM tblMasterData RIGHT JOIN tblSource ON tblMasterData.Name = tblSource.Acct
    WHERE (((tblSource.Time) Between [Choose start period YYYYMM:] And [Choose end period YYYYMM]))
    ORDER BY tblMasterData.SortID;
    And here is a small sample dataset for it.
    Click image for larger version. 

Name:	sample_dataset.png 
Views:	14 
Size:	59.0 KB 
ID:	48266

    Maybe I should elaborate briefly on the background on why I want to isolate the 'Flat' member from the others. The sum of all REG_* entities can but don't necessarily have to be the same number as the 'Flat' (=Total) member. In my first post it is the case that all REG_ members equal the number I have on the related 'Flat' transaction. However it can easily look like this...

    Attachment 48263
    ...which would then indicate, that the missing variances need to be investigated. For the first column we're missing ~338k, which is the 866k from the 'Flat' member minus all listed 'REG_*' entities.

    Solutions I've been thinking about could look like, that I either conditional format and remove the 'Flat' member from the total calculation (below left) or remove it from the detail query, however show it below the total comparison (below right)
    Click image for larger version. 

Name:	Options.png 
Views:	14 
Size:	27.1 KB 
ID:	48264
    Hope this makes sense.
    Attached Thumbnails Attached Thumbnails sample_dataset.png  

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    general observation - no point in ordering in your query as reports will ignore it.

    think you need to exclude Flat from your dataset and use some unbound controls in your time footer using a dlookup to populate the the Flat values

    Note that Time and Description are reserved words and should not be used as field descriptions

  5. #5
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44
    Thanks for the advice and inspiration CJ_London.
    I will proceed as recommended and revert with hopefully final results.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    another method would be to modify your query to link to the Flat record and bring the flat data down as separate fields - then you would group on that data instead. Not tested but something like



    Qry2:
    SELECT * FROM
    Qry1 A INNER JOIN Qry1 B ON A.Time=B.Time
    WHERE A.Entity<>'Flat' AND B.Entity='Flat'

    Qry1 is your original query

  7. #7
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44
    I will also look at that proposal. Thank you.

    I tried the variant with the DLOOKUP, but am not sure, why the following is creating an error (also tried with [] on fields/query but result stayed the same):

    Code:
    =DLookUp("ActDKK","qryTransaction","Entity ='Flat'")
    Click image for larger version. 

Name:	dlookup.png 
Views:	14 
Size:	12.8 KB 
ID:	48268

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You also need to include Time otherwise you will just get the first 'Flat' record it finds

    "Time=" & [Time] & " AND Entity ='Flat'"

    Also just realised your query is prompting for criteria - You need to enter this on a form and modify the query to reference that instead - otherwise I suspect dlookup won't work because it needs to provide those parameters, and can't.

    You could try having the query open before running the report but besides being inconvenient, no guarantee it will work

  9. #9
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44
    Alright, had a few shots with your solution proposals. Unfortunately none successful yet.

    FYI - I have 'TIME' to 'YearMo' to not potentially interfere with Access.

    I also temporary removed the from/to 'YearMo' criteria from the initial query to simply see if the overall functionality would work.

    Approach #1: DLOOKUP with the query excluding entity = 'flat'
    No more error, however no value in the respective unbound control box

    Approach #2: DLOOKUP with full query (incl. 'flat', which naturally is listed again in the detailed section, where I want to exclude it)
    Code:
    =DLookUp("ActDKK","qryTransaction","YearMo=" & [YearMo] & " AND Entity ='Flat'")
    Click image for larger version. 

Name:	2022-07-08_12-15-14.png 
Views:	14 
Size:	31.0 KB 
ID:	48269

    Plots a value for 'flat', however in all fields it keeps the initial value it finds

    Approach #3: Modified inner join query
    Code:
    SELECT * 
    FROM qryTransaction A 
    INNER JOIN qryTransaction B 
    ON A.YearMo=B.YearMo
    WHERE A.Entity<>'Flat' AND B.Entity='Flat';
    Click image for larger version. 

Name:	qry1+2.png 
Views:	14 
Size:	25.5 KB 
ID:	48270
    I guess this doesn't work as A.Entity and B.Entity have no overlap, so the query is empty?

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    approach #3 should return records, 202204 for example. If you can have 'non flat' records and no 'flat' records, they you need to use a left join (include all from A and those from B where there is a match - but that would then group them on a 'null' flat

    Not sure why the dlookup does not refresh for each group, is there some other field involved to identify a specific 'flat'? for example Acct?

  11. #11
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44
    Confirm. Approach #3 works. Not sure why it didn't work before (I didn't make any changes, only reopened it after my lunch break) and I got ~4m records, which is quite a bit more compared to the 9k records I started out with.

    Small extract of the 4m:
    A.YearMo A.Entity A.SortID A.Acct A.AName A.ActDKK A.FcDKK A.dAbsDKK A.dRelDKK A.ActLocCur A.FcLocCur A.Comment B.YearMo B.Entity B.SortID B.Acct B.AName B.ActDKK B.FcDKK B.dAbsDKK B.dRelDKK B.ActLocCur B.FcLocCur B.Comment
    202204 REG_CLU_SOUEU 3680 4700102 Travel -23 -145 122 -84.14% -23 -145
    202204 Flat 2135 1000000 Nr 1839375 1883412 -44037 -2.34% 1839375 1883412
    202204 REG_CLU_SOUEU 3835 4800301 Internet -232 -205 -27 13.17% -232 -205
    202204 Flat 2135 1000000 Nr 1839375 1883412 -44037 -2.34% 1839375 1883412

    Also considering the fact, that the DB will grow over time, I'd rather not further bloat it that way.

    Not sure why the dlookup does not refresh for each group, is there some other field involved to identify a specific 'flat'? for example Acct?
    Yes, indeed. In order to distinguish between the different 'flat's the SortID/Acct would need to be taken into consideration. The SortID is simply a substitution for the acct structure, as the account numbers do not follow a reliable structure than can be sorted.
    Click image for larger version. 

Name:	flat.png 
Views:	13 
Size:	33.6 KB 
ID:	48272

  12. #12
    daredan is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    44
    I think I have now a fairly straightforward, not ideal, but slim solution:
    Click image for larger version. 

Name:	solution.png 
Views:	12 
Size:	23.5 KB 
ID:	48273

    Summary: I'll keep 'Flat' in the detail overview, however conditional format it, to highlight its position. And in the sum I'll exclude it, so we'll still be able to compare all regions vs. the total (flat), so everyone should be happy.

    Really appreciate your inspirational inputs here. Wouldn't have been able to finalize it without you. Thank you a lot. Have a nice weekend!

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You know your data it may be the there is another field you need to join on or set a criteria to

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

Similar Threads

  1. Replies: 2
    Last Post: 06-19-2018, 12:13 PM
  2. Replies: 3
    Last Post: 05-21-2014, 10:15 AM
  3. Replies: 4
    Last Post: 03-12-2014, 06:56 PM
  4. Flat File Query
    By mma3824 in forum Queries
    Replies: 1
    Last Post: 10-09-2012, 08:54 AM
  5. Replies: 8
    Last Post: 08-24-2012, 01:54 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