Microsoft Access lookup and calc on date field

  • Thread starter Melbourne Guy
  • Start date
  • Tags Tags
    Field
10 replies · 2K views · last reply
Melbourne Guy
Messages
463
Reaction score
315
TL;DR
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.
Microsoft Access Timeline table in datasheet view with columns ID, Event and EventDate; one row shows ID 1, Event Starts, EventDate 25/06/1970 12:00:00 PM.

- Crew, which has one entry with multiple attributes that include DOB of 'date' type.
Screenshot of a Microsoft Access table named Crew with columns ID, Given Name, Family Name, Sex, Rank, Role, DOB and YOB; the single record shows DOB 1/12/1956 12:00:00 PM and YOB 1956.


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:

Microsoft Access datasheet of the Crew table with fields ID, Given Name, Family Name, Sex, Rank, Role, DOB and YOB, one row showing DOB 1/12/1956 12:00:00 PM. The Click to Add menu is open listing field types such as Short Text, Number, Date & Time and Lookup & Relationship.


Lookup Wizard dialog with the first option selected: I want the lookup field to get the values from another table or query.


Lookup Wizard dialog asking which table or query should provide values for the lookup field, listing Class, Crew, Fleet, Navy, Ship and Timeline with Timeline selected.


Lookup Wizard dialog in Microsoft Access asking which fields of Timeline to include; Available Fields list shows ID, Event and EventDate, with Selected Fields empty.


Screenshot of the Access Lookup Wizard asking which fields of Timeline to include; EventDate is the only selected field, with ID and Event listed as available.


Access Lookup Wizard step asking sort order for list box items; first field dropdown open showing (None), ID and EventDate options, with Ascending buttons beside four sort rows.


Access Lookup Wizard step showing a single column named EventDate with the value 25/06/1970 12:00 and a calendar icon, with the Hide key column option ticked.


Microsoft Access Lookup Wizard dialog asking for a label for the lookup field, with Event_Date entered, Enable Data Integrity unchecked, Allow Multiple Values unchecked, and Finish button selected.


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!
 
Melbourne Guy said:
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?
 
jedishrfu said:
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.

phinds said:
? 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.
 
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.
 
  • Like
Likes   Reactions: hutchphd
Melbourne Guy said:
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?
 
  • Like
Likes   Reactions: hutchphd
pasmith said:
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.

pasmith said:
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.

phinds said:
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.
 
  • Like
Likes   Reactions: hutchphd and anorlunda
pbuk said:
Yes, this kind of thing is often done in Excel without much difficulty. Access is a rabbit hole which IMHO you should avoid.
Exactly
 
  • Like
Likes   Reactions: hutchphd
pasmith said:
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.

phinds said:
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.
 
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
 
  • Like
Likes   Reactions: pbuk
jedishrfu said:
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.