Simple calculated field question

[N20GF]

Relatively new Access user because I am tired of using Excel as a psuedo-database. :-)

Q: Given one field with a date entry, I need a calculated field that generates a date that is the last day of the sixth calendar month following from the manual date entry. For instance, today's date of January 25, 2024, the calculated field results in July 31, 2024. For the life of me I can't figure out what the syntax is to do this. I've had plenty of caffeine today, so that's not my problem.



Forgive the newbie question; I checked answers already here and cruised a few other forums. Even the Microsoft help pages don't seem to .. umm, help.

[orange]

This may help to get started
?Date()
25-Jan-24
?DateSerial(year(Date()), month( Date() ) +6 +1, 0)
31-Jul-24

DateSerial gives a Date where:

Year(date()) Gives current year
month( Date() ) +6 +1) gives the Month that is 6 months from Date, and the +1 adds 1 month (July)
, 0 gives the day before or gives the last day of the previous month

[Micron]

You're not storing this calculation in a table, I hope?

[N20GF]

At the risk of sounding like a complete Access rookie, well wouldn't the calculations be kept within the table?

Here's what I am trying to do, and as I mentioned I'm using Access for the first time - and essentially have no other db building experience - ha ha Microsoft Works does not count:

Keep a list of employees ... easy enough to build.
Enter certain training qualifications, with dates completed. Also simple.
Have Access calculate when the requalification is due to be completed again. There is a drop-dead date for each that is the final day of x months after the qualification was completed initially.
Then be able to generate a report that gives me a list of employees a b and c that are expiring with qualification x or y or whatever in a specific month in which I'm interested. For instance, hey Access tell me all the employees that expire in the month of August this year so I can anticipate and plan for training events required several months in advance.

Sounds simple .. and the basic concept is. Can it be done on Excel? Yup. But my dataset of employees who have multiple training quals, with different expiration dates, who build different widgets with that have different machine quals, is getting too cumbersome to be tracked with Excel. So this is a good project for me to work with and self-educate on Access in the meantime.

[madpiet]

Oh, I see your problem. In Excel, there are "tables" (sort of) and there are calculations/expressions/whatever, where you use date etc functions to do this kind of thing. Access is a little bit different. Basically, tables are for storing data. Anything that you can derive, you can basically create queries for. (For example, the date you're talking about).

SELECT col1, col2, SomeDate, DATEADD("m",3,SomeDate) AS ThreeMonthsAway
FROM Mytable

and then ThreeMonthsAway gets calculated when you open the query. (So you could do something like base a report on that query, or base another query on it.)

[NoellaG]

There is also the possibility of adding a calculated field to the table. Doing so will make the field available for every query, form or report. In most databases you can make those fields persistent and index them, but I don't think that's possible working with native Access tabels. So the general rule is: if it has to be available in a lot of objects: create a calculated field in in the table. If the field is only used in 1 or 2 reports/forms: calculate it in a query/view.

[CJ_London]

wouldn't the calculations be kept within the table

in theory they can be although you say

Enter certain training qualifications, with dates completed. Also simple.
Have Access calculate when the requalification is due to be completed again. There is a drop-dead date for each that is the final day of x months after the qualification was completed initially.

the problem here is that you also say

employees who have multiple training quals, with different expiration dates

I'm reading that as different periods (i.e. not all qualifications have a 6 month expiry date, some will last for 6 months , some for 12, etc) so I would expect the expiration period to be related to the qualification, not the individual - so that value is in a different table - and table calculations can only reference values in the same record

The other issue is that calculated fields cannot be indexed, so if you have large data sets the query will be slow.

I would expect you to have 3 tables

tblEmployees
employeePK
employeeName

tblQualifications
qualificationPK
qualificationName
expiryPeriod

tblQualified
QualifiedPK
employeeFK
qualificationFK
qualifiedDate

and the query would be

SELECT employeeName, qualificationName
FROM (tblEmployees E INNER JOIN tblQualifications Q ON E.employeePK=Q.employeeFK) INNER JOIN tblQuailified R ON Q.qualificationPK=R.qualificationFK
WHERE Format(DateSerial(year(qualifiedDate), month(qualifiedDate) +expiryPeriod +1, 0),'yyyymm')=[enterDate (yyyymm)]

[NoellaG]

The other issue is that calculated fields cannot be indexed, so if you have large data sets the query will be slow.

Indexes only make a difference when you put a selection criterium on the indexed field, and doing the calculations in queries is certainly not faster than doing them in the table.
But if the value is in another table, you need to do the calculations in a query. But NEVER do the calculation on the left side of the equality. That is one of the worst things you can do in a query. No index can be used and the calculation has to be made for every record.

Always do something like

WHERE [MyField] = calculatedValue

The value has to be calculated once and, if there exists an index on [MyField], the engine can use it.

[Micron]

wouldn't the calculations be kept within the table?

Personally, I would steer away from calculated table fields for the most part.
http://allenbrowne.com/casu-14.html

[CJ_London]

Indexes only make a difference when you put a selection criterium on the indexed field

good point - in the query I posted below, if the qualifiedDate is indexed then I would modify the where clause to

WHERE qualifiedDate Between dateadd("m",-expiryPeriod -1,datevalue([enterDate (yyyy-mm)] & "-01")) AND dateadd("m",-expiryPeriod,datevalue([enterDate (yyyy-mm)] & "-01"))-1