Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2012
    Posts
    29

    Question Unanswered: creating 1 report from 2 queries

    I need to create monthly quarterly and yearly reports. I have 2 queries one with the quantity of goods imported and the other the amount of revenue collected.
    the quantity report runs on the day it arrives into the country, while the revenue query runs on the day the goods were paid. I need to run the reports similar to that of the Sales report in Northwind. how do i create the reports with the both queries?

  2. #2
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I don't think you're going to be able to accomplish this within one report in Access. I would create two separate reports then set up a macro to run both to PDFs then merge the two into a single PDF.

    Joshua

  3. #3
    Join Date
    Aug 2012
    Posts
    29
    Joshua thanks but I don't know how to do what you are suggesting Can you help
    Last edited by kim40; 09-21-12 at 20:35. Reason: This was not the reply I meant to type

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You can create a report from multiple queries in the same way that you can create a report from multiple tables. As long as there are defined relationships between the tables used in the queries, the Jet engine will collate and join the records correctly.

    Your problem will be trying to run part of a report:
    the quantity report runs on the day it arrives into the country, while the revenue query runs on the day the goods were paid.
    Your report will run on the day that it is run, and it will try to show all relevant information. As you imply the need for date parameters in the queries, and the date value for one will not be the same as for the other, you might need to build a third query and use your existing queries as the sources for subreports instead.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Aug 2012
    Posts
    29
    What do I put in the 3rd query I do have one but I not dure how to build the report around it . do I create parameters for the quantity and revenue?

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Without knowing the structure of your database, it's impossible to say with certainty what should be included in the third query. All I can advise is you include some fields that will assist the database engine in linking records together.

    The report should consist of a group header that holds the records from the above query, and displays information that links the quantity and revenue reports. The group's detail section should hold the quantity and revenue reports as subreports. If you apply parameters to the queries that underlie the subreports, you will be prompted for them whenever you open the main report.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Kim,

    I'm not sure if you deleted your reply with the table. I received an email with it but when I went to this thread it was not visible. Anyway, I was going to ask you, are you entering data directly into these Access tables or are you importing or linking from an external spreadsheet? In both of your tables, are the "Fuel Type" names the same?

    Weejas' third query recommendation is a good one if you plan on only having a summary by "Fuel Type". i.e. if you plan on having just one total line for each Fuel type including total quantity and total revenue collected. However, if you are wanting to show each date and each "Fuel Type" I'm afraid you're not going to be able to do this. You would need something unique like an invoice number to be able to do this. You could use a combination of the date and the fuel type except that it sounds like the dates will differ. If it is the latter of the two above, you might be better off creating and maintaining a Microsoft Excel workbook and then set up a linked table to that workbook. In that workbook, I would enter everything you showed in your table and then when you get the other report data, fill it in on the same row in your Excel Spreadsheet with the corresponding quantity. Then you could just run a report in Access from a single table.

    Joshua

  8. #8
    Join Date
    Aug 2012
    Posts
    29
    The summary is by fuel type. My end report should reflect the quantity and revenue of the fuel by month, quarter and year can I send you part of the spreadsheet just to have a look of the end result

  9. #9
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Kim,

    It would probably be better if you sent me a spreadsheet with a sample of the two datasets as well as a third document showing how you want it displayed on the report.

  10. #10
    Join Date
    Aug 2012
    Posts
    29
    I am sending the spreadsheet I have done a sample of the first quarter (july to sep) The sheets have been labeled and the summary sheet is what I expect to see. the summary sheet is linked to the individual sheets.
    Thanks
    I cannot upload the excel sheet I am going to try to send as a word document

  11. #11
    Join Date
    Aug 2012
    Posts
    29
    I zipped the file the excel spreadsheet reflects a quarter, and the summary sheet is my end result

    thanks
    Attached Files Attached Files

  12. #12
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    It looks like that file is corrupt. I could not open. Joshua

  13. #13
    Join Date
    Aug 2012
    Posts
    29
    sorry about that I made the tables and queries out of the sample The import table is what I expect. I just uploaded them from the excel sheet. there is a payment and quantity table and I also did a query for both the payment and quantity

    Thanks for the help
    Attached Files Attached Files

  14. #14
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Sorry Kim, I still can't open it. Same problem

  15. #15
    Join Date
    Aug 2012
    Posts
    29
    thanks for trying guess I'll have to battle this one on my own

Posting Permissions

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