Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating Report From Make-Table Query (XP SP-2)

    I need to create a report based on a make-table query and that doesn't seem to be an option. When I select create a report based on table/query, my make-table query isn't listed. What do I need to do to accomplish this?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Report From Make-Table Query (XP SP-2)

    Like other action queries, a make-table query does not return records; it performs an action instead. Because it does not return records, you cannot use it as the basis for a form or report.
    When you run the query, a table is created. You should use this table as record source for your report.

    If you want to run the make-table query each time you open the report, I have to ask why? Can't you change the query back to a selection query, and use that as record source?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Report From Make-Table Query (XP SP-2

    Hans, perhaps I'm not using the right method to accomplish my goal. My make-table query returns a list of customers who buy a particular product. I want to edit that list and telemarket to just the selected ones. Now that I think on it, a make-table won't work since it re-does itself each time its opened.

    So now my question is how to do it. I need to create a table with my selected customers that I can edit to trim down the list.

    We are manufacturers reps and the customers buy one of our lines. The sales information I'm using to select the customers is in another program completely so the info is not in Access. I am manually choosing the customers.

    Is there a simple way to accomplish this?

    Thanks again. I really appreciate your fast responses!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Report From Make-Table Query (XP SP-2

    I'm not sure I understand completely.

    You could keep the make-table query and run it to create a new table. You can then edit the records of this new table without disturbing the original data. The table should be the record source for a report.
    Note: running an action query means double clicking it from the database window, or opening it in design view, then selecting Query | Run. Switching to datasheet view does not really run the query.
    Warning: the make-table query will overwrite the table if you run it again, so if you want to keep the previous version, you must rename the table.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Report From Make-Table Query (XP SP-2

    You solved my problem. I thought that when I ran the make-table query from the design window that it actually was making the table. No wonder I couldn't find it in my table list. Is this true of all queries or just the make-table (that they don't "actually" do the query when run from design window)?

    Thank you for straightening me out. Now I can proceed!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Report From Make-Table Query (XP SP-2

    There are (roughly) two types of queries:
    <UL><LI>Selection queries (including the subtypes union queries and crosstab queries) display records; they don't perform an action. You can use these as record source for a form or report. "Running" a selection query is the same as switching to datasheet view.
    <LI>Action queries (update queries, append queries, delete queries and make-table queries) perform an action that modifies the database. You cannot use them as record source for a form or report. "Running" an action query is different from switching to datasheet view.[/list]You can "run" a query in different ways:
    - Double click it from the database window.
    - Open it in design view and click the "Run" button on the toolbar.
    - Open it in design view and select Query | Run.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Report From Make-Table Query (XP SP-2

    Thank you for the clarification. I'm still quite new to Access and I can see that I'll have to go back and re-read and really learn the section on different types of queries until I get it right.

    BYW do you have a recommendation on a book to learn Access? I have 2 but I think I could use something else. I have "Special Edition Using MS Access 2002" by Roger Jennings and "The Complete Reference Access 2002" by Virginia Andersen. I use the SE mostly.

    Thanks for all your help. It has made learning Access much easier.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Report From Make-Table Query (XP SP-2

    The Access 2002 Developer's handbook by Litwin et. al. has a very good reputation.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Report From Make-Table Query (XP SP-2

    At the moment I think that may be a little too advanced for me but it sounds like a book I'll use after I get some more experience. So I'll definitely pick it up. Thanks for the recommendation.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Creating Report From Make-Table Query (XP SP-2

    Another book that I like that is largely at the intermediate level is Alison Balters "Mastering Access 2002 Desktop Development" - it has a companion Enterprise volume that looks at multi-user issues and larger database design issues.
    Wendell

  11. #11
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Report From Make-Table Query (XP SP-2

    Wendell, thanks to everyone here at WOPR I feel I am one baby step above a beginner (my database does work and I can get what I want out of it). I will put Alison Balter's book on my list. Probably the next book I get. Then when I am more knowledgeable I'll get the Litwin book and maybe become an expert like you and Hans <img src=/S/yep.gif border=0 alt=yep width=15 height=15> <img src=/S/yep.gif border=0 alt=yep width=15 height=15> <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •