Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

need help on SQL query or a schema to display data on the datagrid using MS Access and VS.net 2003

Hi,

I have a table in MS Access with 3 fields:

Applications (x, y, z…)
Ticket Status (open, closed, outstanding)
Ticket Number (numbers…)

I want to count each status based on application and then display the number in a datagrid using VS.net 2003..

In the datagrid, I have four columns:

Applications        OpenTickets      ClosedTickets            OutstandingTickets

I tried "count" and datareader method to loop through the table but it ididn't work.  I need some help on  the query or a method to display the data.

thanks a lot.

k
0
kate_y
Asked:
kate_y
  • 8
  • 6
  • 2
1 Solution
 
Jeff CertainCommented:
You'll need to post exact table and column names, but this is about what you want:

SELECT Application,
SUM(CASE Status WHEN 'Open' THEN 1 ELSE 0 END) AS [Open],
SUM(CASE Status WHEN 'Closed' THEN 1 ELSE 0 END) AS Closed,
SUM(CASE Status WHEN Outstanding THEN 1 ELSE 0 END) AS Outstanding
FROM Applications a INNER JOIN Status s ON a.Status=s.Status
GROUP BY a.Application
0
 
kate_yAuthor Commented:
Hey, thanks, but i still have questions:

There is only one table called Applications. 3 fields are

Applications (x, y, z…)
Ticket Status (open, closed, outstanding)
Ticket Number (numbers…)

Does the SUM function act like counting? Did you mean Ticket Status when u said CASE Status? What is the INNER JOIN clause for?

thanks.

k
0
 
Jeff CertainCommented:
Sorry... should have read the question better.

SELECT Applications,
SUM(CASE [Ticket Status] WHEN 'open' THEN 1 ELSE 0 END) AS [Open],
SUM(CASE [Ticket Status] WHEN 'closed' THEN 1 ELSE 0 END) AS Closed,
SUM(CASE [Ticket Status] WHEN 'outstanding' THEN 1 ELSE 0 END) AS Outstanding
FROM Applications
GROUP BY Applications

1. In this case, SUM acts like COUNT becuase we've used the CASE statement to add 1 only when the status is waht we're looking for.
2. Field names with a space in them (like 'Ticket Status') are a BAD BAD BAD idea. You'll have to enclose them in [] every time you use them. The only thing worse is using the default 50-character text fields in Access. ;)
3. SUM(CASE Status WHEN 'Open' THEN 1 ELSE 0 END) means "sum a column that contains one if the status is 'open' and zero otherwise"... basically the same as DCount("[Ticket Status]='open'") except that a) I remember SQL syntax better than Access syntax, b) won't break when the next version of Access is released and c) more efficient.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
RamuncikasCommented:
Try MS Access's Pivot Table wizard :)

Ramuncikas
0
 
RamuncikasCommented:
Oops :) That's from Excel..

In Ms Access.... Go to queries, start new one in design view, choose your table. Then use menu Query > Crosstab query

Set "Applications" field to be a "Row Heading", "Ticket status" to be a "Column Heading" and "Ticket Number" to be a "Value". Also set "Ticket number" column a "Count" agregate function.

Hope this will help
Ramuncikas
0
 
kate_yAuthor Commented:
Hey, thanks a lot. I was trying to test the SELECT string. But it didn't work with datagrid directly. So i thought i could dump the results into a new table. It gave me INSERT statement syntax error. Here is what I did.

Sub Button_Click()

Dim strSQLnewtable As String = "INSERT INTO NewTable(Applications, Open, Closed, Outstanding) SELECT Product as Applications,SUM(CASE Status WHEN 'Open' THEN 1 ELSE 0 END) AS Open,SUM(CASE Status WHEN 'Resolved' THEN 1 ELSE 0 END) AS Closed,SUM(CASE Status WHEN 'Outstanding' THEN 1 ELSE 0 END) AS Outstanding FROM Application GROUP BY Product"

objCommand = New OleDbCommand(strSQLnewtable, objConnection)
objCommand.ExecuteNonQuery()

...............

End Sub
0
 
kate_yAuthor Commented:
By the way,I am building a asp.net application. So i can't use Access directly.  Thanks
0
 
Jeff CertainCommented:
1. You're asking for a field called "Products" -- this doesn't appear in your original table definition.
2. You need to change "Status" to "[Ticket Status]" unless you changed the field name.
3. If your status is text, and is lower-case, you need to replace 'Open' with 'open' etc.

This code should work by itself:
SELECT Applications,
SUM(CASE [Ticket Status] WHEN 'open' THEN 1 ELSE 0 END) AS [Open],
SUM(CASE [Ticket Status] WHEN 'closed' THEN 1 ELSE 0 END) AS Closed,
SUM(CASE [Ticket Status] WHEN 'outstanding' THEN 1 ELSE 0 END) AS Outstanding
FROM Applications
GROUP BY Applications

You should test the SQL code in the Access query builder (cut-and-paste into the SQL view) before trying to use it from ASP .Net.
0
 
kate_yAuthor Commented:
i changed the field name based on your query. Let me try to test it in Access. thanks
0
 
kate_yAuthor Commented:
I changed field names Applications to Product, Status to Condition, and table name Applications to BIDJoe. I pasted the string to SQL view. It gave me syntax error (missing operator). Sorry if i missed anything. thanks

SELECT Product,
SUM(CASE [Condition] WHEN 'Open' THEN 1 ELSE 0 END AS [Open],
SUM(CASE [Condition] WHEN 'Resolved' THEN 1 ELSE 0 END) AS [Closed],
SUM(CASE [Condition] WHEN 'Outstanding' THEN 1 ELSE 0 END) AS [Outstanding]
FROM BIDJoe GROUP BY Product;
0
 
kate_yAuthor Commented:
I added ")". it still saying missing operator
0
 
Jeff CertainCommented:
SUM(CASE [Condition] WHEN 'Open' THEN 1 ELSE 0 END AS [Open],

should be

SUM(CASE [Condition] WHEN 'Open' THEN 1 ELSE 0 END) AS [Open],
0
 
Jeff CertainCommented:
Hmmm... guess Access didn't like the SQL syntax I was using (SQL Server 2000). Try this instead (it should be functionally equivalent)

SELECT Product,
SUM(IIF(Condition='open',1,0)) AS [Open],
SUM(IIF(Condition='closed',1,0)) AS [Closed],
SUM(IIF(Condition='outstanding',1,0)) AS [Outstanding]
FROM BIDJoe GROUP BY Product

To explain, the syntax is: IIF(expression, what to return if true, what to return if false)
0
 
kate_yAuthor Commented:
Wow, it worked!!! Thank you so much...You are wonderful!  I am gonna post another silly question on UPDATE query later on...thanks...:)....

k
0
 
Jeff CertainCommented:
Glad we finally got it. Sorry for the confusion. It's been a while since I used Access, so when people say "SQL" I immediately think "T-SQL/SQL server"...
0
 
kate_yAuthor Commented:
i am such a beginner...hopefully i am going to use SQL sever soon....:)...
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now