Results 1 to 9 of 9
  1. #1
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82

    Filter a Report?

    Ok this ones gonna be fun I think. I'm trying to figure out how to put data in a field based on it's value...

    Used Breaker | Phase | Grid | Plug Type | SW | Description | Description | SW | Plug Type | Grid | Phase | Used Breaker

    That's the header across the top... now I know I can filter each record based on the ID of the SW... Each SW will have it's own ID I can pull from and will always contain a value of 1 - 42.

    What I'm trying to achieve is something like this...


    Used Breaker | Phase | Grid | Plug Type | SW | Description | Description | SW | Plug Type | Grid | Phase | Used Breaker
    --------------Data From SW ID where Value = 1 -------------|--------------Data From SW ID where Value = 2 -------------
    --------------Data From SW ID where Value = 3 -------------|--------------Data From SW ID where Value = 4 -------------



    Etc etc? Does that make sense? I think I can do this if I make a Form with this stuff then a query off that form but I was hoping to just do the filtering on the Report itself. Is this possible or do I need to make another form and query?

    Thanks all.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    So grouping on [SW ID] does not provide the results you want? What is Value? Is that the name of a field in a table?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not making much sense to me.

    Why is there two sets of field names in mirrored order?

    What is this db for? What is SW? What do you mean by each SW has its own ID? Show some actual raw data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by June7 View Post
    Not making much sense to me.

    Why is there two sets of field names in mirrored order?

    What is this db for? What is SW? What do you mean by each SW has its own ID? Show some actual raw data.
    This is actually to print off a sheet with breaker information to a breaker panel.
    I have the tables split properly and joined properly in the relationships so they get tied to the right things... anyways SW stands for the breaker number on the panel. Each breaker or SW has it's own unique ID that is a autonumber. The SW will always be a number, on the breaker panel itself the breakers on the left side are odd numbers the ones on the right are even numbers.

    When I say data from SW it has other information with it on the same autonumber that it belongs to. Let me see if I can find a example to post.

  5. #5
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    This is my Datasheet
    Click image for larger version. 

Name:	datasheet.jpg 
Views:	19 
Size:	67.6 KB 
ID:	15279
    This is what I'm trying to achieve on a Report
    Click image for larger version. 

Name:	FDP-DC_closeup_breakers1.jpg 
Views:	19 
Size:	134.7 KB 
ID:	15278

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Making sense now.

    Consider the following, although it won't mirror the fields.

    Calculate an odd/even field in query (or does the Breaker field provide that criteria?).

    Set report for 2 columns.

    Set report sort by PanelIDFK, OddEven (ASCEN or DESC depending on value assignment).

    Will probably need some code to force column break between even and odd sets.

    Producing the mirrored display is more involved, options:

    1. two subreports

    2. more code and temp table
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    The SW field will fit the Odd/Even criteria, I tried to create two queries and that did work but when I tried to combine them into the report I would get duplicates. When I do the OddEven thing I can only filter odds or evens, but trying to stuff them both on the same page is seeming troublesome, I might need to dig around for some code on it, you say two sub reports? Is that doable? And how? Sorry I'm still new to all this, but you guys have been great help! Thanks again!

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    My primary suggestion did not require filtering on Odd/Even, it was to use that field for sort criteria. Review my previous post again. What about the instructions are not clear?

    Two subreports would simply be filtering each subreport to either even or odd and apply same grouping and sort criteria to each as well as any other filter criteria.

    But I think column report should be possible.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by June7 View Post
    My primary suggestion did not require filtering on Odd/Even, it was to use that field for sort criteria. Review my previous post again. What about the instructions are not clear?

    Two subreports would simply be filtering each subreport to either even or odd and apply same grouping and sort criteria to each as well as any other filter criteria.

    But I think column report should be possible.
    Sorry you made sense I was just tired when I read it, I didn't know about subreports, there just like subforms, I didn't think about trying that, that will work out perfectly for what I'm trying to do thanks again!

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

Similar Threads

  1. Filter a Report
    By cnunez in forum Reports
    Replies: 5
    Last Post: 01-24-2014, 03:01 PM
  2. Replies: 1
    Last Post: 07-25-2013, 01:20 PM
  3. Replies: 2
    Last Post: 05-10-2013, 03:37 PM
  4. Report Filter
    By SFC in forum Reports
    Replies: 4
    Last Post: 01-11-2012, 04:13 PM
  5. Filter Report
    By BorisGomel in forum Reports
    Replies: 2
    Last Post: 06-09-2011, 03:22 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