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

[Maggie Barr]

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

 

[Joe4]

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]

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]

Here is one way, using the Nz function (which tells it what to return instead of a field if that field is null/empty):

NEW_EVENT_ID: Nz([Group_ID],[Sampling_EVENT_ID])

 

[Maggie Barr]

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]

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.