Results 1 to 7 of 7
  1. #1
    Wahid is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4

    Show only fields in a query related to already filtered fields?

    Hope everyone will be fine. This is my first post.


    I have database linked as in clip1.
    I have made a query as in clip2 , clip3 which is working fine.
    I wanted to filter the data and used the filter max as in clip4.
    but I see some irrelevant data is shown.
    when I remove 3 fields on right side the result is ok in clip5.
    I want the result like in clip5 (only 24 results) which also includes the remaining 3 relevant fields and want to drop other values from 3 right wards fields.
    but when I add these fields query begins to show all the fields(37 fields) as in clip 3.
    How I can get the goal?
    Attached Thumbnails Attached Thumbnails Clip1.jpg   Clip2.jpg   Clip3.jpg   Clip4.jpg   Clip5.jpg  


  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Have you tried setting PrchDate to Max instead of Group By in the query with all six columns
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Wahid is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Yes Bob I tried it but the results are disappointing and I attached the images .
    Only one record is shown.
    Actually I don't need the max value from rightward 3 fields. I need only records relevant to the date field which are sorted by max.


    Click image for larger version. 

Name:	Clip8.jpg 
Views:	12 
Size:	192.1 KB 
ID:	36955

    Click image for larger version. 

Name:	Clip9.jpg 
Views:	11 
Size:	162.9 KB 
ID:	36956
    Last edited by Wahid; 01-15-2019 at 08:29 AM. Reason: want to add a file

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    That's not quite what I meant
    Have you tried with the following field settings for "Total" row:
    ItemName: Group By
    VendNAme: Group By
    PrchDate: Max
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Wahid is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Yes Bob.

    I tried
    ItemName: Group By
    VendName: Group By
    PrchDate: Max

    It is ok as I already said in my original post. but what about the remaining 3 fields I also want to show them for the relevant records.

    I tried
    ItemName: Group By
    VendName: Group By
    PrchDate: Max
    InvPrice: Group By
    Bilty: Group By
    PrchPrice: Group By

    But no success.

    I also tried

    ItemName: Group By
    VendName: Group By
    PrchDate: Max
    InvPrice: Max
    Bilty: Max
    PrchPrice: Max

    but still no success all the results are shown rather than selected results relevant to PrchDate.

    I tried to upload access file but an error occurs while uploading!!

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Compact and then zip the db file before uploading
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Wahid is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Here is my file. Please solve my problem.
    Attached Files Attached Files

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

Similar Threads

  1. Report does not show all fields from query.
    By PATRICKPBME in forum Reports
    Replies: 3
    Last Post: 01-03-2018, 03:25 PM
  2. Replies: 3
    Last Post: 07-20-2014, 08:56 PM
  3. Replies: 1
    Last Post: 03-15-2014, 06:38 PM
  4. Replies: 6
    Last Post: 07-12-2013, 01:23 PM
  5. Replies: 1
    Last Post: 07-30-2011, 03:21 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