SQL Query - Help please

[tanya74]

I need to produce a report listing how much users created a case note per month per ethincity and sex. So something like this

Year Month User Ethnicity1 Ethinicity2

Male Female Male Female
​

2010 Jan Tanya 2 3 1 6
2010 Jan Therese 1 4 4 4
2010 Feb Tanya 6 7 3 2
2010 Feb Therese 9 2 7 4

So far I have the sql

SELECT [Year], [Month], User , Count(Description) AS Total
FROM (

SELECT year(DateTime) AS [Year], month(DateTime) AS [Month], User, Description, Patient.PracSoftId, Patient.Sex AS Sex, Patient.Ethnicity AS Ethnicity
FROM CaseNote
INNER JOIN Patient

ON CaseNote.PatientId = Patient.PracSoftId) AS [%$##@_Alias]
​

GROUP BY [year], [month], [user];​

​

Year Month User Total
2010 1 Tanya 12
2010 1 Therese 13
2010 2 Tanya 18
2010 2 Therese 22

I just have no idea how to convert to the desired result. Can anyone help??

thanks in advance
Tanya

 

[thingssocomplex]

I suggest you don't use Access reserved words such as 'Month'; in reference to your query I don't fully understand what you are trying to achieve if you want to format month to change from the numbers 1 and 2 you can convert your date to a string using the format option to assign a month and year

 

[tanya74]

Thank you for your reply. Sorry if I confused you with the formatting of results. Hopefully today it will be clearer.

I have changed my query after your suggestion so now the sql looks like:

SELECT [myDateTime], User AS CaseOfficer, Count(Description) AS Total
FROM (
SELECT format(DateTime,"yyyy-mm") AS [myDateTime], User, Description, Patient.PracSoftId, Patient.Sex AS Sex, Patient.Ethnicity AS Ethnicity FROM CaseNote INNER JOIN Patient 
ON CaseNote.PatientId = Patient.PracSoftId
)  
GROUP BY [myDateTime], [user]
order by [myDateTime]

Result:
myDateTime CaseOfficer Total 2010-01 Tanya 1 2010-01 Therese 1 2010-02 Tanya 1 2010-03 Tanya 2 2010-04 Tanya 2 2010-05 Fred 1 2010-05 Tanya 3 2010-06 Fred 1 2010-06 Tanya 3 2010-10 Jerry 5 2010-10 John 1 2010-10 Tanya 8 2010-11 Miranda 3 2010-11 Tanya 4 2011-01 Tanya 0



This is the result I would like:

myDateTime CaseOfficer Total Ethnicity1 Ethnicity2 etc… Male Female Unknown Male Female Unknown 2010-01 Tanya 1 1 0 0 0 0 0 2010-01 Therese 1 0 1 0 0 0 0 2010-02 Tanya 1 0 0 1 0 0 0 2010-03 Tanya 2 0 0 0 1 1 0 2010-04 Tanya 2 0 1 0 0 0 1 2010-05 Fred 1 0 1 0 0 0 0 2010-05 Tanya 3 0 1 1 1 0 0 2010-06 Fred 1 0 0 0 0 1 0 2010-06 Tanya 3 0 0 0 1 1 1 2010-10 Jerry 5 1 1 1 1 1 0 2010-10 John 1 0 0 0 0 1 0 2010-10 Tanya 8 2 2 2 2 0 0 2010-11 Miranda 3 0 0 0 1 1 1 2010-11 Tanya 4 0 1 2 1 0 0 2011-01 Tanya 0 0 0 0 0 0 0
I would like to break the Total column up into the different ethnicities and the different sexes. There are 4 different ethnicities and three sexes (male, female and unknown).

Is this possible?

Thanks
Tanya

 

[tanya74]

oh dear... the formatting is even worse then before...

 

[tanya74]

So I have attached image files...

thanks

 

[vbaInet]

How is Ethnicity1 and 2 determined per user? Plus, how do you determine Unknown?

 

[tanya74]

Both fields are set values by a third party product. Data is imported into this Access database.

Ethnicity options are
Y = Aboriginal,
T = Torres Strait Islander,
B = Aboriginal and Torres Strait Islander
[FONT=&quot]N = neither or unknown.

Sex:[/FONT]
unknown or blank. I would be happy just to just male or female at the moment.

 

[vbaInet]

I've had a look at your desired outcome and it isn't possible in a query. You can probably do it in a Pivot Table but you will have to fiddle with it to get what you want.

However, I would say that having a an ethnicity/gender data to the right of your set of results is meaningless. If each record was unique, then I would agree with your design, but since you have a set of data that is repeated, you don't need the ethnicity/gender comparisons.