Results 1 to 12 of 12
  1. #1
    tamijsmith@yahoo.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7

    I don't want all records to show in a report based on a query - only one record

    I am probably going about this the wrong way, but I have a report based on a query. I have calculated fields in the report detail section. I only want those results to show once but it's giving me all 4,000+ records. I am trying to recreate a report that was done with code only to calculate the results, but I do not have those skills. I have read that I need to remove the record source for the report, but I see the way I'm going about this will not work - it needs the record source. I will be adding a different sub-report later. Is there a way I can only show the first record? That would solve my problem in that each record detail is the same. Thanks in advance! - Tami



    Click image for larger version. 

Name:	design view 1.JPG 
Views:	23 
Size:	116.9 KB 
ID:	28030Click image for larger version. 

Name:	print preview 1.JPG 
Views:	23 
Size:	109.6 KB 
ID:	28031

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Can we see the query Sql or design view?


    Sent from my iPhone using Tapatalk

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does moving those to the report footer section resolve the problem?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    tamijsmith@yahoo.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7
    Yes, moving all those controls to the report footer now only shows the results once. But the report is still 51 pages long. I am showing the query/code in the reply above to Andy49.

  5. #5
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    With the controls in the footer section set the detail section's visible property to No that should resolve your problem

  6. #6
    tamijsmith@yahoo.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7
    Here's the SQL view. Also screen shots of query design below.

    SELECT DISTINCT tblClients.ClientID, tblClients.ZipCode, tblBillingInformation.BillingDate, tblClients.Member, tblClients.HonoraryMember, tblClients.Sex, tblClients.RaceID, tblRaces.Race, tblClients.BirthDate, DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(),"mmdd")) AS Age, IIf([Age]<55,"Under55",IIf([Age] Between 50 And 59,"55-59",IIf([Age] Between 60 And 69,"60-69",IIf([Age] Between 70 And 79,"70-79",IIf([Age] Between 80 And 89,"80-89",IIf([Age]>=90,"90+","Unknown")))))) AS AgeRange
    FROM (tblClients INNER JOIN tblBillingInformation ON tblClients.ClientID = tblBillingInformation.ClientID) INNER JOIN tblRaces ON tblClients.RaceID = tblRaces.RaceID
    WHERE (((tblClients.Member)=Yes) AND ((Year([BillingDate]))=2016)) OR (((tblClients.HonoraryMember)=Yes) AND ((Year([BillingDate]))=2016));

    Click image for larger version. 

Name:	qbd grid 1.JPG 
Views:	21 
Size:	82.6 KB 
ID:	28032Click image for larger version. 

Name:	qbd grid 2.JPG 
Views:	21 
Size:	66.8 KB 
ID:	28033

  7. #7
    tamijsmith@yahoo.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7
    Thanks, Ray. That worked! But I will still be needing to add a sub-report. Do I just add that to the report footer section also?

  8. #8
    tamijsmith@yahoo.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7

    Now the parameter is prompting me again and again

    Quote Originally Posted by RayMilhon View Post
    With the controls in the footer section set the detail section's visible property to No that should resolve your problem
    Ray,

    I worked putting it in the report footer. Now I added a subreport and I'm getting prompted many many times for start date and end date. Both the main report and subreport are in the report footer and are based on the same query. I tried removing the parameters from the second query and basing that query on the first query, but it's still prompting multiple times. Any thoughts?
    Thanks!
    Tami

  9. #9
    tamijsmith@yahoo.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7
    Well, I may have fixed my problem. Instead of prompting for dates, I just put criteria in to return the year 2016, which is what I need, and it worked. But in the future, is there some way to prompt for dates where it will only prompt once?
    Thanks!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Most of us would use a form to gather user input and let the queries get the values from there. You have more control that way anyway.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    tamijsmith@yahoo.com is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7
    Quote Originally Posted by pbaldy View Post
    Most of us would use a form to gather user input and let the queries get the values from there. You have more control that way anyway.
    Thank you for this comment. I studied how to do this and it makes perfect sense now!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 03-30-2014, 04:57 PM
  2. Replies: 5
    Last Post: 02-27-2014, 08:25 PM
  3. Replies: 1
    Last Post: 01-23-2013, 10:44 AM
  4. Count records based on report ,not in query
    By Abasalic4 in forum Reports
    Replies: 1
    Last Post: 04-13-2012, 12:46 PM
  5. Replies: 1
    Last Post: 02-02-2009, 05:52 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