Total of many queries into one report

GiannisGalatsis

New member
Local time
Today, 16:46
Joined
Jun 18, 2016
Messages
4
Hi,
I want to set the totals of many queries into one report
I have an ecxel for the total selling of books of a bookshop(per month). I was import the excel to access into a table. The books are from about 30 differend publishers. The records from the selling have the fields code, title, quantity, prise and vat. The code is for example Publisher1.book1, Publisher1.book2, Publisher2.book1, etc. I was create a query with creteria Like "Publisher1.*" for the first publisher. I was doing this for each publisher and i have about 30 queries with total quantity and total turnover per publisher. I want to set all the totals into a report.
Thanks for your time and sorry about my English.:)
 
Your table is most likely wrong and your set of queries is definitely wrong. You shouldn't have a query for each publisher, instead you should have a row for each publisher. Like so:

Publisher, SalesMonth, Sales
House of books, January, 56
Booktopia, January, 77
Total publishing, January, 19
...
...

That's how your query should be. Also, you need a table to support that query. Like the query, you wouldn't have a column for each publisher, but a row for each h publisher.

What's your table look like?
 
Thanks for sesponding
For examble the excel file is like this table:
Code | Title | Quantity | Price
Publisher1.00001 | 50 shades of grey | 5 | 15 €
Publisher2.00132 | The island | 1 | 13 €
Publisher1.00044 | Mr. Mercendes |3 | 9 €
Publisher3.00044 | Tuesday's gone | 2 | 16 €
Publisher2.00026 | Little prince | 4 | 12 €
Publisher2.00033 | Game of thrones 1 | 6 | 16 €
Publisher1.00101 | The crow | 1 | 9 €
Publisher6.00019 | History of rock | 2 | 12 €
Publisher3.00117 | Blue Mondey | 3 | 16 €

Each line for one title
In one query set the creteria on Code field: Like “Publisher1.*”
Give me the result :
Code | Title | Quantity | Price
Publisher1.00001 | 50 shades of grey | 5 | 15 €
Publisher1.00044 | Mr. Mercendes | 3 | 9 €
Publisher1.00101 | The crow | 1 | 9 €
Total | 9 | 33 €

After the query I have about 30 tables one per publisher
I want only the line with total for every table into one report or table like this
Publisher Quantity Price
Publisher1 9 33 €
Publisher2 12 50 €
Publisher3 5 26 €
Publisher4 16 10 €
 
Your problem is you've the Publisher and the Code in one field, they need to be in their own fields. Use an Update query to put them in 2 fields, like I've below:
The table and the result from the query:
attachment.php


The query:
SELECT Publisher, Sum(Quantity) AS SumOfQuantity, Sum(Price) AS SumOfPrice
FROM Books
GROUP BY Publisher;
Ps: When you display data examples and results, so be aware that there is consistency between them.
 

Attachments

  • Books.jpg
    Books.jpg
    72 KB · Views: 278
Thanks a lot,
I was use an update query to remove the last 5 digit, the number of code ( I was find this there: "access-programmers.co.uk/forums/showthread.php?t=166486" ). I don't need it and then I use your query and it work.
:):):)
 

Users who are viewing this thread

Back
Top Bottom