Access SQL Statement to sum based on criteria

Hi Experts,

I would like to sum a database column based on certain criteria using SQL.
The data looks like the attached file.QueryExample.png

I would like to sum columns "DN Quantity" and "Quantity Delivered" (independently) where the "DN Number" is the same, however where the phrase "Awaiting Data" is present in the "Quantity Delivered" column, I would like it to return the sum of "DN Quantity" Column instead.

So for "DN Number" 10003 the DN Quantity would be returned as 768,000 and as the "Quantity Delivered" Column contains "Awaiting Data", this would be returned as 768,000 also.

"DN Number" 10004 however would be returned with "DN Quantity" of 66,000 and a "Quantity Delivered" of 68,500

I hope this is clear.

Many Thanks
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

In order to put "awaiting data" in what should be a numeric column you had to make it text.  BAD!!!  In a relational database we have a concept for "unknown" and that is the Null.  So a numeric field that is null has an unknown value and that is what you should be using.  If on a report, you want to display "awaiting data", that is a completely different issue.  In a spreadsheet, the presentation and data layers are merged.  In a relational database, the data is in tables and the presentation is handled in forms and reports.

Relational databases are not spreadsheets.  Tables and queries are not spreadsheets.  In a relational database, all the rows of a query are "equal".  They have the same level of detail.  You can't naturally have subtotals interspersed with un-aggregated data.  To do what you are asking, you should be using a report.
martinibboAuthor Commented:
Ok - my data has originated from excel and I am looking at moving it to access to generate reports and gather large amounts of data together from different sources. I understand that there may be tidying up of the data to perform first. It would be very simple for me to remove the Awaiting Data flag, and replace it with Null as you mentioned.

As I am very new to access, SQL, queries and reports, could you please provide a simple example file of your solution using reports, else point me in the direction of online resources around creating reports.

Jim HornMicrosoft SQL Server Data DudeCommented:
In addition to Pat's correct comments...

>my data has originated from excel
Ok, but just so you know Excel is considered a poor data source for a normalized database, as users can edit an Excel doc in thousands of ways that would cause the import into the normalized database to fail.

>As I am very new to access, SQL, queries and reports, could you please provide a simple example file
Open Access
Hit Queries, New Query Editor
Add any table you want, then add a name and amount column
Hit the Sigma button (funky E) Group by the name, Sum by the amount.
In the Criteria row(s) you can add your filtering logic above.

>As I am very new to access, SQL, queries and reports
btw Seems that this is an Access SQL question, and not a SQL Server question, so if that's the case I propose we remove SQL Server as a zone.

If you're trying to learn I also recommend searching articles for Access Query and seeing what displays.  I have two Access articles, although neither addresses learning queries, and there are some excellent Access experts that have many other articles here:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

martinibboAuthor Commented:
Jim - your example is so close to what I want.

SELECT DN.[SO Number], DN.[DN Number], Sum(DN.[DN Quantity]) AS [SumOfDN Quantity], Sum(DN.[Quantity Delivered]) AS [SumOfQuantity Delivered]
GROUP BY DN.[SO Number], DN.[DN Number];

Open in new window

I would like it to sum (for [SumOfDN Quantity]) using the [DN Quantity] if the [Quantity Delivered] is Null. So in my example the [Quantity Delivered] figure for [DN Number] "10003" would be 528,000 not 288,000. Is this possible, how would you go about it?

Apologies for the incorrect zone, however I cannot see where to remove it.

Thanks for your help.
Jim HornMicrosoft SQL Server Data DudeCommented:
>if the [Quantity Delivered] is Null
Add to the Criteria row, [Quantity Delivered] column...    Is Null

Which would add this to the SQL
WHERE [Quantity Delivered] Is Null

Open in new window

Any questions, copy both query both design view and SQL view into this question.
martinibboAuthor Commented:
It didn't 100% answer my problem (the example given), however gave me a better understanding of SQL, and SQL is perhaps not the correct route to solving my problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.