Case Statement to get value in Access Query

[hrdpgajjar]

Hi all,
I have table name Application which consists of following columns,
1. WO Date
2. TPA Date
3. TR Date
4. Invoice Date
5. Current Stage

Now i want to use Case statement to get Current Stage value based on all above 4 columns,

For Example,
If Invoice Date is null and all above 3 fields have dates entered then current stage is "TR DONE"
if invoice Date and TR Date fields are null and rest of above 2 fields have dates then current stage is "TPA Done" so on...

I have tried to enter query in "Onload " of form but not able to do it.

how can i do this?

thanks

 

[arnelgp]

i don't think you need Current Stage field on your table. you can create a Query and use the query on your form:

SELECT [WO Date], [TPA Date], [TR Date], [Invoice Date], Switch(IsNull([Invoice Date]) And 
Not IsNull([WO Date]) And Not IsNull([TPA Date]) And Not IsNull([TR Date]), "TR DONE", 
IsNull([Invoice Date]) And IsNull([TR Date]) And Not IsNull([WO Date]) And Not IsNull([TPA Date]), "TPA DONE", 
True, Null) As [Current Stage] FROM YourTableName;

 

[cheekybuddha]

Arnel has given you a "quick'n'dirty" solution.

If you are interested in a better solution (that requires a bit more effort on your part) then consider normalising your table properly.

This would require a table for holding all your different possible stages, and then a junction table to link applications to their stages.

Something like:

Applications          ApplicationStages           Stages
------------          -----------------           -------
ID (PK)      >--|     ID (PK)                |--< ID (PK)
...             |---< ApplicationFK          |    Stage
...                   StageFK           >----|
                      Date

 

[DHookom]

I’m with David regarding normalizing although I prefer a naming convention that would not have multiple “ID” fields and “Date” as a field name.

 

[mikenyby]

I agree with the normalization method listed above, but if you don't want to go through that trouble you can also make current stage a calculated field in the table. You can nest a few IIF statements together to get the desired result.
If your data is stored in back end tables on SQL Server or some other more competent database engine, you can create a computed field in the back end and use the SQL CASE statement to do what you're looking to do.

If this database is not going to have a lot of production utility, this may be an adequate method. If it's going to get more mileage, definitely look at normalizing.

I'm also on team @DHookom regarding naming conventions. ID fields without the table names can be a little confusing when writing joins.

 

[Gasman]

Confuses Access as well.

 

[mikenyby]

Confuses Access as well.

Joins in Access are obnoxious to begin with. The only place you'll ever see so many RIGHT JOINs in the wild.

 

[GPGeorge]

Joins in Access are obnoxious to begin with. The only place you'll ever see so many RIGHT JOINs in the wild.

The query designer in Access is a graphical UI, which has to be translated into SQL. That does often result in awkward SQL, to be sure.

For someone who is serious about writing good, clean SQL statements, there's always the SQL editor. And in the very near future, the Monaco SQL Editor will be available.

 

[cheekybuddha]

I prefer a naming convention that would not have multiple “ID” fields and “Date” as a field name.

I couldn't agree more about naming a field 'Date' - never do it!

However, whilst everyone is free to have their opinions, I feel that the objection to 'ID' is misguided and usually a bit cargo-cultist.

If you are SELECT'ing from a single table, then 'ID' is more than adequate, and actually has the effect of giving the field no more meaning than it needs as a unique identifier of a record/row:

SELECT
  ID,
  SomeField
FROM YourTable;

... and if you use the Query Builder that insists on qualifying everything even in queries based on a single table, you will get:

SELECT
  YourTable.ID,
  ...
 
-- instead of:
SELECT
  YourTable.YourTableID,
  ...

which just makes you think you've been drinking too much and are seeing double

If you are using it in a query with a join, it will always be qualified:

SELECT
  t.ID,
  t.SomeField
  l.LookupValue
FROM YourTable t
INNER JOIN LookupTable l
        ON t.LookupFK = l.ID
;

I find it more irritating to have to read/write:

SELECT
  Applications.ApplicationID
  Applications.SomeField,
  ...
FROM Applications
INNER JOIN ...

Naming the field ApplicationID serves no benefit here - it's obvious which table it's from or, if not, then you are using an alias that is not expressive enough.

Confuses Access as well.

Only when you try and use a field that could be from more than one table in an expression, since the stupid query builder decides that's the time when it can't be bothered to qualify a selected field.

Joins in Access are obnoxious to begin with.

The most obnoxious thing about Access joins is its requirement to nest each join in brackets, often leading to the join order being reversed or jumbled up completely

 

[cheekybuddha]

And in the very near future, the Monaco SQL Editor will be available.

But you will still have to use Jet/ACE stupid join syntax, no?

 

[GPGeorge]

But you will still have to use Jet/ACE stupid join syntax, no?

I prefer not to use pejoratives to describe behavior in software, but yes, Access SQL requires Access syntax, with superfluous parentheses.

 

[Pat Hartman]

The only place you'll ever see so many RIGHT JOINs in the wild.

Access doesn't force you to use Right Joins. It uses the order in which you add tables to a query to interpret whether you want a left or a right join. So, add the tables in a logical order or switch them later.