Results 1 to 5 of 5

Thread: Query (ACC2002)

  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query (ACC2002)

    I am trying to create a catalog merge doc in word using data from my Access database. There are 2 queries which provide the data and I need to figure out if I can put this into one query somehow. This is the scenario:
    My database tracks 2 types of requests: best practices and call avoidance.
    We are having a contest that whoever submits the most requests in the quarter gets a prize.
    I'm creating a monthly newsletter to communicate the requests (seperating them by type) and give an update to the contest.
    So, right now I have 2 queries:

    One tracking the type of request and a brief summary.
    The second tracking requestor name and calculating the number of submissions per requestor.

    How can I get this all into one query? I can see how the Requestor/ type/ summary can work in one query, but how do I implement the count part? There are over 50 potential requestors.

    let me know if more information is needed

    Any ideas suggestions would be appreciated!

    Thanks!

    Helen

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

    Re: Query (ACC2002)

    Hello Helen,

    Welcome to Woody's Lounge!

    If the data are going to end up in one list or table in the Word document, it should be possible to create one query that combines the results of the two separate queries into one. The layout of the list/table should suggest how to do this - can you join the queries on request type, for example?

    If the data are going to end up in two separate lists or tables, you should probably perform two separate mail merges, and then create one document in Word from the resulting documents.

    If this is not enough to get you started, feel free to post back with some details of the queries and the tables involved.

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (ACC2002)

    hmmm, not sure if I could join them. Each query simply has 2 fields: the first consists of RequestorName and Count of Requestor (which is a calculated field). The other query has Type of Request and Summary. I understand how the requestor, type and summary could be joined but using that calculated field is what trips me up.

    However, maybe the 2 documents idea is feasible. The question would then be how to automate the joining of the 2 documents. The original plan was to have a button on my switchboard that would create a newsletter.

    Thanks for your quick response.

    Helen

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

    Re: Query (ACC2002)

    You can control Word from VBA; this is called Automation. You can create the two documents using mail merge, then fuse them into one. Another approach would be to skip mail merge, and build the document entirely in code.

    Access moderator <!profile=WendellB>WendellB<!/profile> has several tutorials on his website that you may find useful:
    Automation 101
    Managing Merges With Access
    Both contain lots of links to other sources of information.

    Take a look at the tutorials, and come back here if you have questions.

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

    Re: Query (ACC2002)

    <font color=blue>I knew I was taking a risk when I began responding to a message that was more than 10 minutes old, but at least Hans and I came up with similar ideas.</font color=blue>

    Based on the info you've provided, I don't think you can create a single query. But if I understand correctly, both queries operate on the same table, so the calculated data is simply a count of the number of requests. Access reports do this sort of thing regularly, and put a count at the end of a detail section, but in this case I believe you want to have a listing of the requestors and their counts, and then a listing of each request with the summary by requestor. It sounds like that may create a fairly lengthy newsletter. That aside, my take would be that you might be better off to use Automation and throw the results of each query over the wall to Word so that you end up with a single document. You could execute two separate mail merges and then combine them using Automation, but that seems pretty kludgey. If you want to explore Automation a bit, check out our Automation Tutorial.
    Wendell

Posting Permissions

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