Microsoft Access lookup and calc on date field

[Melbourne Guy]

TL;DR Summary

I'm using the latest version of Access on Windows 10 and want to calculate age from a date of birth field in one table and an event date in another and it's not working ☹

Having exceeded the limits of Excel to track the names, rank, attributes, etc. of a list of ship's crew, I have tried to use Access.

First up...

I'm not a database expert. So, I'm muddling along with help from YouTube, but either I can't frame a correct search for this problem or it's not commonly asked and so I'm stuck. Basically, what I want to do is calculate the age of crew by taking the date of a known event in one table, and their date of birth in another, and subtracting the years.

The two tables are:

- Timeline, which has one entry of 'date' type.

- Crew, which has one entry with multiple attributes that include DOB of 'date' type.

As a test, in the 'Crew' table I have used the Year() function to confirm that I can access the DOB field and that the correct year of birth is extracted. However, when I try and create a calculate relationship that uses the Year() function from the Timeline table, it returns the wrong value:

I have reached the limit of attaching screenshots, but the Wizrad results in an 'Event_Date' field in the Crew table that shows the correct 1970 date. But if I now apply the Year() calculation to the 'Event_Date' field, it results in the number 1899.

It is not obvious what I am doing wrong, but any thoughts would be appreciated!

 

[jedishrfu]

Are the data types the same like is one a text field and the other is a date time field?

 

[phinds]

Having exceeded the limits of Excel to track the names, rank, attributes, etc. of a list of ship's crew, I have tried to use Access.

? How many people are IN that crew?

 

[Melbourne Guy]

Are the data types the same like is one a text field and the other is a date time field?

Hmmm. I'm not sure, @jedishrfu. The two fields with the dates are 'date' type, but whatever type the relationship field creates, that is done by Access. I've just checked and I cannot see if that aspect can be modified.

? How many people are IN that crew?

It's not the amount, @phinds, it is the relationships between the various attributes - fleets, battles, commendations, and the like - and ability to create reports based on these different attributes.

 

[pasmith]

There is no obvious relationship between a crew member and an event, so adding an "AgeAtEvent" column to the Crew table makes no sense.

Instead you should create a new query which at a minimum contains Crew.ID, Crew.DOB, TimeLine.ID and TimeLine.EventDate for all combinations of crew members and events. You can then add a calculated field to that query which contains the crew member's age as of the event date.

 

[phinds]

It's not the amount, @phinds, it is the relationships between the various attributes - fleets, battles, commendations, and the like - and ability to create reports based on these different attributes.

I still don't understand how you ran out of capability in Excel. Are you fully conversant with its use? What am I missing?

 

[pbuk]

There is no obvious relationship between a crew member and an event, so adding an "AgeAtEvent" column to the Crew table makes no sense.

Yes indeed.

Instead you should create a new query which at a minimum contains Crew.ID, Crew.DOB, TimeLine.ID and TimeLine.EventDate for all combinations of crew members and events. You can then add a calculated field to that query which contains the crew member's age as of the event date.

Yes, this is the way to go. I don't think you can add a calculated field using the simple query wizard so you will have to use the query builder.

I still don't understand how you ran out of capability in Excel. Are you fully conversant with its use? What am I missing?

Yes, this kind of thing is often done in Excel without much difficulty. Access is a rabbit hole which IMHO you should avoid.

 

[phinds]

Yes, this kind of thing is often done in Excel without much difficulty. Access is a rabbit hole which IMHO you should avoid.

Exactly

 

[Melbourne Guy]

There is no obvious relationship between a crew member and an event, so adding an "AgeAtEvent" column to the Crew table makes no sense.

Took me a bit of thinking to see what you are driving at, @pasmith, but you're correct. I'll look at adding the query, thanks.

I still don't understand how you ran out of capability in Excel.

I've been using Excel up to now, it's unwieldy. The crew of a large ship can run into thousands of people and when you add attributes like dimensions, weapons, top speed, commissioning / decommissioning, and events like battles, ship locations, onshore command hierarchy, and associated characteristics you need a database, not a spreadsheet.

 

[jedishrfu]

When using a database it’s good to design your schema of tables around a star schema ie a central fact table and several ancillary dimension tables referenced by the fact table.

If that is insufficient or query-wise inefficient then consider a more complex arrangement but please plan it out And think through the kinds of queries you’ll likely make.

https://en.wikipedia.org/wiki/Star_schema

 

[Melbourne Guy]

When using a database it’s good to design your schema of tables around a star schema

I've been working off an ERD I drew up, @jedishrfu, as I did not expect this to be too complicated (and I already have the Excel spreadsheet as a basis for 'table' design), but I'll look at the star schema, thanks.