Results 1 to 10 of 10
  1. #1
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114

    Different queries, can I use the same report for each?

    Hi there,



    I created a query and a report for that query.


    My boss wants me to create several different queries, with the same report output.




    Instead of creating the same report for each query (and there are 26 queries) Is there anyway I can use the same report with each of the queries? The query set up (fields) would be the same for each query.


    Thank you,


    Rebecca Feinstein

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Assuming the fields in each query have the same name so the Report can find then I would think you could assign the RecordSource in the OnLoad event of the Report.

  3. #3
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    I thought so, but wanted some confirmation. Thank you.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I have done stuff like this before successfully.

    But I would question the 26 queries. What is the difference between all the queries? Are they subtle?
    If they are, I have used solutions to this that use one Form, two Queries, and one Report.

    Basically, I have a Query that has all possible records/fields that I may need for my Report.
    I then have a Form where users make criteria selections (for what they want to see on the Report).
    Then, when they click the button, I have VBA code that creates the SQL code it needs from the first query and applies it to the second Query (so the second Query is dynamic and built on-the-fly).
    Then the Report uses the second Query as its Record Source.

    The big advantage here is if you ever needed to update the underlying query for any reason, you only need to update one and not 26.

    And creating the SQL code for the query on-the-fly is really not as hard as it seems. If you can build an example using Query Builder, just switch to SQL View and you can see exactly what the SQL code you need to build looks like. And you are really only having to create the criteria part, as the first part of the SQL query is always the same, i.e.
    Code:
    SELECT [Query1].*
    FROM [Query1]
    WHERE ...

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    In addition to Joe's great suggestion I usually just use a "Select *" type query for the Record Source for a Report and add a WhereClause in the OpenReport command.

  6. #6
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    Thank you for the quick response.

    Basically, I've run one query/report with fields for manufacturer names, our p/n; the part description; the mfr's p/n and the status (which is active). The report ran fine; grouping by manufacturer and our part number.

    Now, I want to run a query for just one manufacturer at a time, using the same report. However, when I change just the manufacturer id that we have, I get nothing in the return of the query.

    And it just does NOT happen on one id; it happens with all the one's I've tried so far. And in checking the general report I ran with all of them; for one manufacturer, it duplicated all the part numbers for that one list (I checked all the tables used in the query and NONE of them have any duplicated records).

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are you using the OpenForm command to run the report from a form?

  8. #8
    rebfein's Avatar
    rebfein is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2015
    Location
    So Cal
    Posts
    114
    OpenForm command????

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How are you running the reports?

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by rebfein View Post
    OpenForm command????
    DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

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

Similar Threads

  1. Replies: 11
    Last Post: 04-07-2015, 09:15 AM
  2. Two Queries for One Report
    By lynnmc26 in forum Reports
    Replies: 5
    Last Post: 08-05-2011, 10:45 AM
  3. Two queries in one report
    By victor in forum Programming
    Replies: 1
    Last Post: 08-05-2010, 01:31 AM
  4. Replies: 1
    Last Post: 06-29-2010, 03:40 AM
  5. Replies: 3
    Last Post: 05-21-2010, 03:57 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