Help With Query Syntax (1 Viewer)

GC2010

Registered User.
Local time
Today, 07:14
Joined
Jun 3, 2019
Messages
120
I need to query my access table to determine which review each employee needs completed. We have 6 fields
Empreview1start
Empreview1finish
Empreview2start
Empreview2finish
Empreview3start
Empreview3finish

What I am after is a query that will show this:
If empreview1start is not null and empreview1finish is null then show Review 1
If empreview2start is not null and empreview2finish is null then show Review 2
If empreview3start is not null and empteview3finish is null then show review 3

What would my access query be to achieve this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:14
Joined
Oct 29, 2018
Messages
21,358
Is there any way you can modify the table structure so you won't have this problem? What I think you're showing is a "repeating group." If you can get rid of it, it would be easier to manage your database and simpler to get out the data you want from it.
 

GC2010

Registered User.
Local time
Today, 07:14
Joined
Jun 3, 2019
Messages
120
Is there any way you can modify the table structure so you won't have this problem? What I think you're showing is a "repeating group." If you can get rid of it, it would be easier to manage your database and simpler to get out the data you want from it.

Unfortunately not. It’s a linked sql server table into access and I am unable to modify the source table. I could create a local access table and put the data into a more friendly format if that would help.
 

June7

AWF VIP
Local time
Today, 06:14
Joined
Mar 9, 2014
Messages
5,425
Maybe:
SELECT *, Switch(NOT empreview3start IS NULL AND empreview3finish IS NULL, "Review 3", NOT empreview2start IS NULL AND empreview2finish IS NULL, "Review 2", NOT empreview1start IS NULL AND empreview1finish IS NULL, "Review 1") AS Review FROM table;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:14
Joined
Oct 29, 2018
Messages
21,358
Unfortunately not. It’s a linked sql server table into access and I am unable to modify the source table. I could create a local access table and put the data into a more friendly format if that would help.
No, not needed. Instead, what you could do is create a UNION query to "normalize" the data. You can then use the UNION query to do your SUM.
 

GC2010

Registered User.
Local time
Today, 07:14
Joined
Jun 3, 2019
Messages
120
Maybe:
SELECT *, Switch(NOT empreview3start IS NULL AND empreview3finish IS NULL, "Review 3", NOT empreview2start IS NULL AND empreview2finish IS NULL, "Review 2", NOT empreview1start IS NULL AND empreview1finish IS NULL, "Review 1") AS Review FROM table;

That was perfect! Thank you kindly!
 

Users who are viewing this thread

Top Bottom