Results 1 to 7 of 7
  1. #1
    Mr.Kaja is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    2

    Row filtering in reports

    Hello everyone, as a greenhorn, I have probably a stupid question, but I will be really grateful for the answer.

    I have source table with data in the format:

    YEAR SUBJECT DATA_A DATA_B DATA_C

    2017 Joe xyz1 abc1 dlc1


    2018 Joe xyz2 abc2 dlc2
    2019 Joe xyz3 abc3 dlc3


    And I would like report for Joe, looking like:


    2017 2018 2019
    DATA_A xyz1 xyz2 xyz3
    DATA_B abc1 abc2 abc3
    DATA_C dlc1 dlc2 dlc3

    Is it possible in a easy way? Report shows only values from first (alt. last) row from source table. My only idea is separating source table into multiple tables (for each year) and merge into one table with column for each year (2017_DATA_A; 2018_DATA_A), which doesn't sound very elegant nor clever.

    Thank you for anwers and sorry for my english.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The filter should be in the query via criteria (on a form )
    arrangement & sort of the data is done in the report via the GROUP & SORT banding.

    the database shows data top/down , so reporting data left to right wont work well.
    Making a crosstab query WILL show your years as columns (left to right) but you cannot make reports work that way since they dont know what column fields will be in the report.
    Its best to deliver crosstab data as a query.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your desired result sample doesn't make sense - there are 3 fields (columns) but each row has 4 data.
    The short answer to your question is to take a look at cross tab queries in Access.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Mr.Kaja is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    2
    Thank you for your help. I will try to be more specific. I have data from a large number of companies from multiple completed forms. Output are reports looking like another filled form. Data are in cloud and usually, multiple forms for one company (ID) and one year are downloaded and filled by queries into one source table (including one row). And voila - report is filled. No problem at all.
    The problem is year-on-year comparison for some company (ID). Final source table has multiple rows (for each year one). So in report I have problem to specify different years and it use information just from first row (year 2017, not 2018 or 2019). In case, I would use criteria in last query, I can filter one year (I can choose which one, but problem is quite the same). Grouping wont help (one company ID, but different years and indicators with values). I tried even iif (in case year is 20xx, proper value for indicator is filled, otherwise null), but still - i got the same number of rows...each for one year...not bad ... for example year 2017 row has null value, year 2018 row value is Ok filled, year 2019 row is null. So I can use sum values in one row. But company ID are number saved as text, starting with 000 and it is quite mess.

    Ok, it's hard to describe, especially I'm not allowed to publish any of the data. I understand that it is not easy to understand me a give me some advice. But I still appreciate your efforts and thank you.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Could you not make a duplicate of the dB, change the companies to made up names (Dunkin of Flordia, Krougers of Main, etc), use Propane, Bread, coffee for data and add in made up data for the years?
    Delete tables, queries, reports that do not affect this printing problem, then post the dB?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    or consider randomizing the data so it can't be interpreted: https://www.accessforums.net/showthread.php?t=77482

    I guess it's not a popular suggestion because I always seem to be the only one suggesting it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Since you are storing the data in an excel type structure and not a database type structure, suggest you use excel to transpose the data.

    If you want to stick with a database, as others have said - normalise the data and use a cross tab

    You can fake a normalised structure by using a union query to which you can then apply a cross tab query. However this will no doubt need to be rewritten every time you add or delete a column in your source data . Potentially it will also be slow as union queries do not have any indexing capabilities

    A normalised structure, based on your pseudo data would look something like

    Year..subject..datatype..data value
    2017..joe.......dataA.......xyz1
    2017..joe.......dataB.......abc1
    2017..joe.......dataC.......dic1
    2018..joe.......dataA.......xyz2
    2018..joe.......dataB.......abc2
    2018..joe.......dataC.......dic2

    Also recommend you provide pseudo data that at least reflects reality to some extent - is joe supposed to be a company?are the values all text?numeric? A mixture?

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

Similar Threads

  1. Replies: 3
    Last Post: 02-10-2019, 02:03 PM
  2. Replies: 13
    Last Post: 02-08-2017, 04:11 PM
  3. Reports - filtering revision fields
    By Nubia in forum General Chat
    Replies: 1
    Last Post: 03-06-2015, 09:46 AM
  4. filtering data for reports
    By mejia.j88 in forum Reports
    Replies: 7
    Last Post: 12-27-2011, 12:19 PM
  5. Filtering Reports
    By JackA in forum Reports
    Replies: 2
    Last Post: 07-19-2011, 10:22 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