Query to populate a field in an exists table (1 Viewer)

[Richard-B]

I have a table containing 1.6 million reacts.
I want to take the date in the table and populate another field in the same table.
The table is index.
I was going to use an append query but I can not get it working

This is what I want to do.
Date field example 12/10/2021 = DayMonth field 12/10

 

[theDBguy]

Hi. Welcome to AWF!

Have you tried using a Calculated Column?

 

[arnelgp]

use query and add a Calculated column.
the query is more versatile and you don't need
to do an Update everytime there is new Date or new record
being added.

 

[plog]

To your question, you should not use an APPEND query but a SELECT query to calculate the total. Then in that SELECT query you would use the Day() and Month() functions:

MS Access: Day Function

This MSAccess tutorial explains how to use the Access Day function with syntax and examples. The Microsoft Access Day function returns the day of the month (a number from 1 to 31) given a date value.

www.techonthenet.com

MS Access: Month Function

This MSAccess tutorial explains how to use the Access Month function with syntax and examples. The Microsoft Access Month function returns the month (a number from 1 to 12) given a date value.

www.techonthenet.com

Additionally, you have some bad names for fields:

1. Spaces should not be in names. Makes coding and querying harder. Instead use an underscore: [Obs N] -> [Obs_N], [Site quoted] -> [Site_quoted]

2. Don't use reserve words as names. [Date] should not be a field name because its a built in function name. Instead name it for what that date represents (e.g. SurveyDate). You can find of all reserved words here:

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com

 

[Pat Hartman]

Just a little more clarification.
Append queries add NEW rows
Update queries update EXISTING rows.

Your question is about populating an empty column.
1. the query itself cannot add a new column to a table so you would need to do that manually and first.
2. you would need to use an update query to update the new column with calculated data from a different column.

Do NOT take that explanation as support for your "solution". The others are correct. It is far better to create a calculate column in the query than to save calculated data. There are cases that can justify saving a calculated value but this is not one of them.