Query with distinct count on 2 separate columns + summation takes a very long time

[ElPedroMagico]

Hi,


I have a query based on a single table of items in invoices (approx. 30,000 records; named X) with subqueries performing distinct counts and summation.
The table looks something like this (one record = one item):

Document_ID	Item_No	Item_Class	Amount
1	1	X	10
1	2	X	20
1	3	Y	25
2	1	Y	16
2	2	Y	22
2	3	Z	34
2	4	X	40

The code (see below) is a reworked version of what I found on a blog.

SELECT Main.Document_ID,
              (SELECT Count(Tmp1.Class) FROM (SELECT DISTINCT Document_ID, Class FROM X GROUP BY Document_ID, Class ORDER BY Document_ID )
              AS Tmp1

              WHERE Main.Document_ID = Tmp1.Document_ID
              GROUP BY Tmp1.Document_ID ORDER BY Tmp1.Document_ID) AS Count_of_Class, 

              Count(Main.Item_No) AS Count_of_items,
              Sum(Main.Amount) AS Amnt 

INTO TableXYZ
FROM X AS Main
GROUP BY Main.Document_ID
ORDER BY Main.Document_ID;

My questions:

1. The query takes a really long time to execute. How do I speed it up?
I have seen people suggesting INNER JOINs into queries to speed them up, however, I am not really sure how I would do that or in what way this would work in this case. Maybe someone knows how I would go about doing this or even has a completely different suggestion?


2. The query won't run unless I assign the original table an alias ("Main" in the code above). Can someone explain why this is so essential?

I'd really appreciate any help since I'm totaly new to Access and SQL. I've been progressing at a snail's pace with this and seem to be finally stuck after days of scrutinizing forums all over the place.

Sorry if this has been discussed before or if I left out any essential context...

Regards,
Peter

EDIT: One more question: I have been able to run the above query without the WHERE clause and am not entirely sure how that is possible. I have also tried substituting the WHERE clause FOR

INNER JOIN X Main ON Main.Document_ID = Tmp1.Document_ID

but got an error saying "At most one record can be returned by this subquery."
Why does this happen?

Thanks in advance, again, for ANY help!

[rpeare]

if the example table is your original data, what are the query results you want to end up with based on your sample data?

your query is actually three queries, every time you have a SELECT statement you are running a separate query. and you are running that query for each record of the main query which explains why it would run slowly.

It looks like you are trying to sum the AMOUNT field for each ITEM_CLASS on a particular DOCUMENT_ID but without rebuilding tables (I can't parse SQL visually very well) I can't check for sure. Especially considering you don't have the same table/field names in your example data as you do in your SQL statement.

If you can give an example of what you want your output to be based on your example data it'd be far easier to assist.

[June7]

Why saving calculated aggregate data into a table?

Instead of aggregate calcs in query, did you consider building a report and using Sorting & Grouping features with aggregate calcs in footer sections?