Sum Query.

[jaygoodwin77]

Hi there.

Can anyone help me with a question about queries.

I have a table that records all sessions, those sessions would either be delivered or Pending, and would be attached to region

Is there a way of having one query tell me how many of each there are in each region?

Jay

 

[KenHigg]

Have you tried doing a 'totals' query?

 

[MSAccessRookie]

If I understand you correctly, then something like this should work

SELECT Region, Sessions, Count(*)
FROM TableofSessions
GROUP BY Region, Sessions

 

[KenHigg]

Yes - How'd it work?

 

[jal]

Yes - How'd it work?

The general idea behind Group By is that it "makes a mental note" of each set of rows that follow a pattern. So if I have

Group By City, State

and have five rows
Jackson, Mississippi
and 10 rows with
Tulsa, Oklahoma
The engine has now made two menal notes (it remembers those five rows for instance). Then if you do, say Count(* ) it's going to report "5".

 

[jaygoodwin77]

Here is my Db

Here you can see the table i am using.

It is fairly long winded due to all the data inputs.

However i want a query that will look into the delivery status column and give me 2 results in one query, so I can see in one place how many pending ILT days and how many consumed ILT Days have been used in a demand quarter

Can you help

Jay

 

[jal]

Here you can see the table i am using.

It is fairly long winded due to all the data inputs.

However i want a query that will look into the delivery status column and give me 2 results in one query, so I can see in one place how many pending ILT days and how many consumed ILT Days have been used in a demand quarter

Can you help

Jay

Your file had this query:



SELECT qryDatabaseExportMain.Demand_Quarter, qryDatabaseExportMain.Geography AS Region, Sum(qryDatabaseExportMain.ILT_Days) AS [Forecast Days]
FROM qryDatabaseExportMain
GROUP BY qryDatabaseExportMain.Demand_Quarter, qryDatabaseExportMain.Geography, qryDatabaseExportMain.DeliveryStatus
HAVING (((qryDatabaseExportMain.DeliveryStatus)="Pending"));

So I would just add these words to the SELECT like this (creates another column)

"Pending" as DeliveryStatus

in other words:

SELECT qryDatabaseExportMain.Demand_Quarter, qryDatabaseExportMain.Geography AS Region, Sum(qryDatabaseExportMain.ILT_Days) AS [Forecast Days], "Pending" as DeliveryStatus

FROM qryDatabaseExportMain
GROUP BY qryDatabaseExportMain.Demand_Quarter, qryDatabaseExportMain.Geography, qryDatabaseExportMain.DeliveryStatus
HAVING (((qryDatabaseExportMain.DeliveryStatus)="Pending"));

Then write your second query - and put the word UNION between the two queries. With the 2nd query, add these words (again, for a new column)
"Delivered" as DeliveryStatus

Remember, a UNION only works if the two queries have pretty much the same column names, the same number of columns, and the same column order.