Results 1 to 10 of 10
  1. #1
    gemadan96 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    110

    Question Using a date from a specific table record to calculate age.

    I have SQL Server backend and Access frontend. Three of my tables are Contacts, Settings, and Tournaments. Contacts is pretty obvious and includes a DOB field. Settings is used for setting default values for specific functions including identifying the current tournament. Tournaments list all the tournaments and details, including the tournament date.



    In SQL server I have a scalar function for calculating date difference in years. I use it in the Contacts table for a calculated field for Age based on the current date - ([dbo].[GetDateDiffY]([DOB],getdate())). Works as I want it to.

    I also need to calculate the contact's age, in years, on the date of the tournament. I need to be able to have SQL server identify the current tournament set in Settings and get the current tournament's date from the Tournaments table. I can create a calculated field using something similar to - ([dbo].[GetDateDiffY]([DOB],tournamentDate())). I need your assistance to define tournamentDate() in SQL server.

    I know I can do this in Access. I have functions in Access called GetDefTournament() and GetDefTournamentDate(). I want to do this in SQL and let SQL do the work.

    Any suggestions/assistance are appreciated.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,223
    use the datediff function - something like datediff("yyyy",Date1, Date2)

  3. #3
    gemadan96 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    110
    That is basically what I'm doing. Date1 it gets from the DOB field in the Contacts table. Date2 I want it to get automatically from the the tournamentDate field in the tournaments table based on the current tournament set in the settings table. The idea is when I set a new tournament as default the contact's tournament age will be recalculated based on that new date.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,223
    so what is your problem?

  5. #5
    gemadan96 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    110
    Not to seem like a jerk, but did you read it? In SQL Server I want to create a function that sets the tournament date to use in calculating the contact's tournament age. The function will look at the Settings table and find which tournament is the current one. Then using that information look up the date of that tournament and set it as the current tournament date. Then using that set tournament date calculate the contact's tournament age in the Contacts table something like this ([dbo].[GetDateDiffY]([DOB],tournamentDate())). I need to be able to tell SQL server how set/define tournamentDate(). Over time there will be many tournaments listed, but only one will be the current tournament at any given time. The idea is to change one record, they only record, in settings and the Contact's table will display in that calculated field there correct tournament age, which can be different from their current age at the moment the contact is entered or viewed. For instance Someone's birthday is 8/31, the event is 9/7, and today is 8/20. There age at this moment is 10, but their age on 9/7 will be 11.

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,223
    sorry, you've lost me. Your thread title is about using a date to calculate an age. The body of the text is explaining how a function is intended to work.

    The function will look at the Settings table and find which tournament is the current one.
    . Is that a sql server function? or a VBA one? what does the setting table look like? define current - is it the tournement running now? One you have an interest in from the past? something else?. Either way, you have a settings table and it presumably contains something to identify 'the current tournament'. maybe a name, maybe a numeric key

    Then using that information look up the date of that tournament and set it as the current tournament date
    why? why not just use the date in the tournament table to compare with the contacts table?
    But OK so you update the settings table - is this where your problem is?

    I don't even see why you need a function, just needs a query

    join settings to tournaments and cartesian to contacts gives you all the data you need to calculate the contact age for any tournament

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,223
    looks like you have cross posted here https://www.access-programmers.co.uk...d.php?t=306443. So since I am struggling to understand your requirement, I'll leave it to the other forum

  8. #8
    gemadan96 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    110
    All of the data is in SQL Server. In SQL Server I currently have a scalar function that calculates the date difference in years between two date. In the Contacts table is a field DOB that stores the contact's birthday. using the function in SQL Server I am able to calculate the contact's age in years using the DOB field and getdate(). What I want to do, in SQL server, is to also calculate the contact's age on the date of the tournament. In time the tournaments table will list many different tournaments, all with different dates. Only one tournament can be the current tournament. So I want to use my scalar function to calculate the date difference between the DOB and the current tournament date. I don't want to store the current tournament date with each contact, because every contact will need to be update each time there's a new current tournament. So I'm hoping to create a function in SQL server that calls the current tournament date from the tournaments table based on the current tournament defined in the settings table. Hopefully the image helps to understand what I want.



    Click image for larger version. 

Name:	DBdiagram.jpg 
Views:	17 
Size:	35.7 KB 
ID:	39524

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,223
    looks to me like the solution proposed on the other forum should meet you needs, just need to change the sql to reference your settings and tournaments table to pick up the date

  10. #10
    gemadan96 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    110
    This has been solved. Solution available at https://www.access-programmers.co.uk...d.php?t=306443

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-24-2019, 02:35 PM
  2. Replies: 6
    Last Post: 05-10-2018, 10:55 AM
  3. Replies: 3
    Last Post: 03-24-2017, 12:34 PM
  4. Replies: 7
    Last Post: 01-05-2016, 11:23 AM
  5. Using VB to calculate a specific date
    By barryg80 in forum Programming
    Replies: 8
    Last Post: 12-05-2013, 09:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums