Query to populate a field in an exists table

[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:

Avoid using reserved words and symbols in Access - Microsoft 365 Apps

Lists the reserved words that you shouldn't use in field, object, and variable names.

docs.microsoft.com