Need to create new column in access table that looks at two other columns (If, then, else)

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
Hello, and thank you in advance if you can help,
I think what I am trying to do should be pretty easy, and I have tried several things, but I keep getting syntax errors. I want to create a column for NEW_EVENT_ID that is If Group_ID is blank then Sampling_EVENT_ID else Group_ID. I have tried so many times, and searched on line, but I can't make it happen. I am trying to do this in a table created from a query, but I could do it in the query if it would work better. I could do this in Power Query, which is where I am headed with the data, but it was running so slow in the past that I am trying to do as much data manipulation in another format.
Any advice would be greatly appreciated,
Maggie Barr
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,514
Office Version
  1. 365
Platform
  1. Windows
It is always recommended to do Calculated Fields in a Query rather than a Table.
Recent versions of Access allow you to do limited calculations at the table level, but it really isn't advised. It is better to do them at the Query level.
 

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
Joe4,
Thank you for that, and I had tried some, but whatever I was doing was not working. Could you please let me know how to get started on the right path to performing this. I am very new to Access, and the language/syntax is not like excel or Power Query.
Thank you,
Maggie
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,514
Office Version
  1. 365
Platform
  1. Windows
Here is one way, using the Nz function (which tells it what to return instead of a field if that field is null/empty):
Code:
NEW_EVENT_ID: Nz([Group_ID],[Sampling_EVENT_ID])
 

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
Joe4,
THANK YOU SO MUCH! Yes, that worked great, I really appreciate your help.
If you have any advice on the best books to learn Access & syntax, please let me know. I am trying to learn to fish good enough to feed myself...
Best Wishes,
Maggie
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,514
Office Version
  1. 365
Platform
  1. Windows
If you just want to learn how to use Access, I used the Microsoft Step-by-Step series as a good introduction (though that was many years ago, and there are many other good books and tutorials).
If you are going to be creating database applications with Access, it is important to have a good understanding of Relational Database Theory and Design, especially the Rules of Normalization.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,176
Members
409,854
Latest member
rickcoba
Top