Microsoft Access lookup and calc on date field

  • Thread starter Melbourne Guy
  • Start date
  • Tags
    Field
In summary: 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.? How many people are IN that crew?There is no obvious relationship between a crew member and an event, so adding an "AgeAtEvent" column to the Crew table makes no sense.There is no obvious relationship between a crew member and an event, so adding an "AgeAtEvent" column to the Crew table makes no sense.
  • #1
Melbourne Guy
462
315
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.
TimeLineTable.png

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


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:

CreateRelationshipInCrewTable.png


FirstStepofWizard.png


1641016098685.png


1641016116617.png


1641016128236.png


1641016141013.png


1641016152257.png


1641016175915.png


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!
 
Computer science news on Phys.org
  • #2
Are the data types the same like is one a text field and the other is a date time field?
 
  • #3
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?
 
  • #4
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.
 
  • #5
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 hutchphd
  • #6
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 hutchphd
  • #7
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 hutchphd and anorlunda
  • #8
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 hutchphd
  • #9
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.
 
  • #10
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 pbuk
  • #11
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.
 

FAQ: Microsoft Access lookup and calc on date field

What is a lookup in Microsoft Access?

A lookup in Microsoft Access allows you to retrieve information from a related table or query based on a common field or key. This can be useful for creating a more efficient and organized database.

How do I perform a lookup in Microsoft Access?

To perform a lookup in Microsoft Access, you can use the "Lookup Wizard" which will guide you through the process of selecting the table or query to lookup from, the fields to retrieve, and the key to match on. Alternatively, you can use the "Lookup" function in a query or form to manually specify the table, fields, and key.

What is a calculated field in Microsoft Access?

A calculated field in Microsoft Access is a field that is created by performing a calculation on other fields in the database. This can be useful for creating dynamic and customized data in a table or form.

How do I create a calculated field in Microsoft Access?

To create a calculated field in Microsoft Access, you can use the "Expression Builder" which allows you to select fields, functions, and operators to create a formula for the calculated field. You can also directly type in the formula in the field properties or in a query.

Can I perform calculations on date fields in Microsoft Access?

Yes, you can perform calculations on date fields in Microsoft Access. The "DateDiff" function can be used to calculate the difference between two dates, and the "DateAdd" function can be used to add or subtract a specific time interval from a date. These functions can be used in calculated fields or in queries to retrieve data based on date calculations.

Back
Top