Click here to Skip to main content
15,570,202 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
SELECT        
  colldet.college,    
  COUNT(DISTINCT manuscript.p_name) AS A,
  COUNT(DISTINCT CASE WHEN s_p = 'منجز' THEN p_name END) AS B,
  COUNT(DISTINCT CASE WHEN s_p = 'منجز منشور' THEN p_name END) AS C, 
  COUNT(DISTINCT CASE WHEN s_p = 'مخطط' THEN p_name END) AS D
FROM manuscript 
RIGHT OUTER JOIN colldet 
ON manuscript.coll_name = colldet.college
GROUP BY colldet.college


What I have tried:

I'm trying to convert SQL query to work in MS-Access, is there any suggested way? thank you
Posted
Updated 21-Apr-22 6:52am
Comments
OriginalGriff 21-Apr-22 2:17am    
And?
What have you tried?
Where are you stuck?
What help do you need?

Use the "Improve question" widget to edit your question and provide better information.

Conversions of anything are rarely the way to approach a problem. And your problem statement is not entirely clear - if you plan to ask more questions always supply some sample data and the results you expect from that sample data.

In this case it looks as if you are trying to get a count of data based on column s_p - so start off with a query that does that e.g.
SQL
SELECT colldet.college, S_P, Count(*) AS Expr1
FROM manuscript RIGHT JOIN colldet ON manuscript.coll_name = colldet.college
GROUP BY colldet.college, S_p;
You can then use the Access equivalent of SQL's PIVOT - TRANSFORM statement (Microsoft Access SQL) | Microsoft Docs[^]
 
Share this answer
 
Comments
Maciej Los 21-Apr-22 11:21am    
5ed!
MS Access engine does NOT know CASE WHEN... END statement. Equivalent statement is: MS Access: Switch Function[^]

Usage:
SQL
SELECT
    Switch(
        Price < 5001, "A",
        Price < 10001, "B",
        Price < 20001, "C",
        Price >= 20001, "D"
        ) AS PriceType
FROM YourTable;


I'd also suggest using TRANSFORM statement - as suggested by @CHill.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900