?
Solved

Need SQL statement to read data from database

Posted on 2009-04-16
8
Medium Priority
?
179 Views
Last Modified: 2013-11-29
I have an access database which holds soccer statistics for certain matches like so: (with sample data)
 "Event"             "Name"      "Time"
  PassGood       Player 1       0001
  PassBad          Player 2      0002
  HeadBall          Player 3      0003
  BlockBall          Player 6      0004
  GoodTackle      Player 1     0006
.......

I want to count all events from the "Event" column for each player and show this information in a variour text boxs for each player on a C# form.

For example, the C# form would be laid out like so:
Player 1:
Good Passes : 2
Bad Passes : 3
Headers: 1

Player 2:
Good Passes : 1
Bad Passes : 3
Headers: 1
Tackles: 6

etc...

I need an SQL statement to count this information, I am new to sql so need help.
It would be like e.g. Select Column EVENT, COUNT all GOOD PASSES for PLAYER 1 ..


Thanks
0
Comment
Question by:Wanting2LearnMan
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 1000 total points
ID: 24159472
SELECT Name, Event, COUNT(*) EvCnt
FROM YourTable
GROUP BY Name, Event
0
 
LVL 12

Accepted Solution

by:
Nathan Riley earned 400 total points
ID: 24159475
Try this:
SELECT Name, Event, Count(Event)
FROM Table
GROUP BY Name, Event

Open in new window

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 600 total points
ID: 24159491
Is this what you want?

select event,name,count(*) from YourTable
group by event,name
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.

 

Author Comment

by:Wanting2LearnMan
ID: 24159624
Yes I think the above is what I want but I do not know how to write this properly in c#.  Is it like so:
String sql = "SELECT [Event],[Name], Count(*) EvCnt FROM MatchStats GROUP BY [Event],[Name], ";

If the above is correct, how do I use this sql statement to bind/insert a value into a textbox?

Thanks
0
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 1000 total points
ID: 24159856
We solved the SQL part and now you have to wait for somebody from C# zone, sorry.

Basically you have to establish connection from C# to Access database and execute SQL command. It will create some data set which you have to process. I would help if you decide to use Visual FoxPro.

It would be better to ask again just in C# zone with above select command.

Another question is why not to do it all in Access? It has sufficient resources for your needs. But I have to agree this task is good to learn C#.
0
 

Author Comment

by:Wanting2LearnMan
ID: 24159895
Thanks, I'll ask in the C# zone.

I have to do it in C# as this is only part of my program. I have to add this part into an existing application.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 600 total points
ID: 24161093
This is in the C# zone.  So the question has only been partially answered.
0
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 1000 total points
ID: 24162222
To be accurate, the original question was "Need SQL statement..." It has 8 responses already and the chance it will be completed by somebody from C# zone is low.

So, my recommendation is to ask again the C# specific part of this question in C# programming AND in Microsoft Visual C#.Net zones. This approach probably better folows EE rules.

Additional question should point to Access zone and its subject could probably be "How to connect Access database from C#".
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question