Convert SQL query to work in MS access SQL

Question

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 20-Apr-22 20:04pm

Member 15525054

Updated 21-Apr-22 5:52am

Add a Solution

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.

2 solutions

Solution 1

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[^]

Posted 20-Apr-22 23:01pm

CHill60

Comments

Maciej Los 21-Apr-22 11:21am

5ed!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2022-04-21T05:52:00

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.