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

[Benno2805]

I have 2 tables.

Table one [Person_Details]

Person ID	Date of Birth	Age in Days	Age Range
KL123	26/07/1994	10254	26-40
DJ348	24/01/2000	8346	18-25

Table two [Age_Range]

Min Age Days	Max Age Days	Age Range
0	6570	<18
6570	9490	18-25
9490	14965	26-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]

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:

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.

 

[ebs17]

Instead of constantly updating tables, one would rather calculate variable data in queries. Table Person_Details gets by with the first two fields.

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]

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:

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