Solved Update Query Based on Columns in Another Table (1 Viewer)

Benno2805

New member
Local time
Today, 22:52
Joined
Jan 5, 2023
Messages
3
I have 2 tables.

Table one [Person_Details]
Person IDDate of BirthAge in DaysAge Range
KL12326/07/19941025426-40
DJ34824/01/2000834618-25


Table two [Age_Range]
Min Age DaysMax Age DaysAge Range
06570<18
6570949018-25
94901496526-40


I want to update Age Range in table 1 from table 2, based on the Age in Days value.

There might be an easier way of doing this, and i'd be grateful for any advice. But I can't figure this out at all.

thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:52
Joined
May 7, 2009
Messages
19,246
you can remove the [Age in Days] and [Age Range] fields from Person_Details table since you
can calculate it using Query:

here is your Query:

Code:
Select
    [Person ID],
    [Date of birth],
    Date() - [Date of birth] As [Age in days],
    (Select [Age Range] From [Age_Range]
                            Where (Date() - [Person_Details].[Date of birth]) Between [Min Age Days] And [Max Age Days]) As [Age Range]
From [Person_Details];

see Person_Details table on the demo db.
see how it is computed in the query, see Query1.
 

Attachments

  • dbAgeGroup.accdb
    480 KB · Views: 70
Last edited:

ebs17

Well-known member
Local time
Today, 23:52
Joined
Feb 7, 2020
Messages
1,991
Instead of constantly updating tables, one would rather calculate variable data in queries. Table Person_Details gets by with the first two fields.
SQL:
SELECT
   PD.PersonID,
   PD.DOB,
   AR.AgeRange
FROM
   Person_Details AS PD,
   Age_Range AS AR
WHERE
   DateDiff("d", PD.DOB, Date()) BETWEEN AR.MinAgeDays
      AND
   AR.MaxAgeDays
 

Benno2805

New member
Local time
Today, 22:52
Joined
Jan 5, 2023
Messages
3
you can remove the [Age in Days] and [Age Range] fields from Person_Details table since you
can calculate it using Query:

here is your Query:

Code:
Select
    [Person ID],
    [Date of birth],
    Date() - [Date of birth] As [Age in days],
    (Select [Age Range] From [Age_Range]
                            Where (Date() - [Person_Details].[Date of birth]) Between [Min Age Days] And [Max Age Days]) As [Age Range]
From [Person_Details];

see Person_Details table on the demo db.
see how it is computed in the query, see Query1.
Great, so much simpler, thank you
 

Users who are viewing this thread

Top Bottom