Hello,

I am new to sql and would greatly appreciate any help you guys could give me.

I am setting up a database to compare my companies software with a competitors software.

I have two tables

the first table is named "issue list" and has 2 collumns 1) id 2) issue.
this table is for creating lists known issues with both software

the second table is named "deal issues" and has 3 collumns 1) deal 2) issue 3) type
the deal field is a scenario where an issue is found
the issue field is the issue that was found
and the type field is which software it belongs to ("1" for our software "2" for our competitors

What i am trying to do is create a query that has 3 collumns 1) all of the issues on the issue list 2) a count (of deals) of that issue that are from our software and 3) a count of that issue that is from my competitors software

Some issues are unique to each software and some issues are contained in both software.

If this is not clear please let me know and i will try and clarify.

If anybody could point me in the right direction I would greatly appreciate it.

Thanks,

Dan

Have a look at a Totals Query (http://office.microsoft.com/en-us/access/HA100963101033.aspx).

Thanks for the link.

Here is the code that i am trying to use:

SELECT IssueList.issue, Count([deal issues].deal) AS '1 issues'
FROM IssueList left JOIN [deal issues] ON IssueList.issue = [deal issues].issue
where [deal issues].type = '1'
GROUP BY IssueList.issue;

this creates 2 collumns with the first collumn being the list of issues (that are in software 1) and 2nd colloumn with the count of the issues in each category that (that are in software 1).

My issue is that I need a third colloumn that contains the same results as the first query but with software 2 selected (same query just changing type =2). I cant seem to get this to work.


Any help is greatly appreciated.

Hopefully this will help you guys visualize what i am looking to do.

Issue List Table
ID issue
2 a
3 b
4 c
5 d
6 e
7 f

deal issues talbe
deal issue type
1 a 1
1 b 1
1 c 2
2 a 1
2 d 2
3 d 2
3 d 1
4 a 2
4 a 1
5 d 1
6 c 2

desired output

issue software 1 software 2
a 3 1
b 1 0
c 0 2
d 2 2
e 0 0
f 0 0

I am making progress with this one I was able to make a query that outputs the correct result for the first two collumns 1) the issue id collumn and 2) the count of issues for software 1.

If anyone could point me in the right direction in adding the third collumn(count of issues with software 2) i would really appreciate it.


select issliss.issue, sfw.CountOfdeal
from [issuelist] as issliss left join (SELECT il.issue as issues, Count(di.deal) AS CountOfdeal
FROM IssueList AS il LEFT JOIN [deal issues] AS di ON il.issue = di.issue
WHERE di.type = '1'
GROUP BY il.issue) as sfw
on issliss.issue = sfw.issues

Thanks in advance for your help.

-Dan