Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Identical records, different dates (Access 2000)

    I am an Army officer working on a database for data reconciliation between the various ones we use. I have a table (thru an ODBC link to our server) that shows personnel authorized strength by effective date. The table contains records for all personnel authorized in each unit by Grade, Military Occupation Specialty (MOS), etc., by effective date of the authorization document. As a result, I have multiple records which are identical except for the effective date. I need to query the table to find records with the most recent effective date for each unit's authorization document. To make matters worse, the data field for the effective date is not formatted as a date -- text only -- and since the server is not mine, I have no control over the properties of the table and can't change them. Can anyone out there help me out with this?

    "Taking care of America's sons and daughters..."

    Captain D

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Identical records, different dates (Access 2000)

    You can use a Group By query for this. The exact way to set it up depends on the way the date is stored as text.

    If the dates are stored as mm/dd/yy or something like that, you can create a calculated field using the DateValue function; if they are stored in the form mmddyy, you'll need to parse the month, day and year parts and use DateSerial to create a date value.

    The query should contain all identifying fields with Group By, and the calculated date field with Max.

    See attached picture (the example uses DateValue).
    Attached Images Attached Images

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identical records, different dates (Access 2000)

    The dates are stored in yyyymmdd format. I tried to change the regional settings of my computer to follow this format, but it requires a separator placeholder, like "/" or "-". When I tried to create a setValue table, setting the field format to yyyymmdd, it would not convert the data to the new format. I do not want to use separator placeholders, as this will only further confuse the users this database is intended for.

    If I create a macro to run the setValue query to create a source table for the database, someone could really screw it up if they accessed the database on a computer that did not have the regional setting set to yyyymmdd. I have had this nightmarish problem before.

    Can I parse the date value into yyyy and mm and dd and use a DateSerial to create a date value without placeholders? If so, then I can probably use the GroupBy to retrieve only the records I want.

    Right now, I have 6 queries executed by a macro to extract the records I need from the base table to create a table I am using to retrieve my data. It works, but it ain't exactly user friendly.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Identical records, different dates (Access 2000)

    I also work with military personnel records (among other things) & some of the data systems we work with (mainframe systems) also store dates as text fields in YYYYMMDD format. I use a simple user-defined function using DateSerial function to convert these text dates to "real" dates when importing records into Access:

    Function ConvertDate(ByVal strDate As String)

    'Convert date text field (YYYYMMDD) to valid date
    'Sample data: 20020630 = 30 JUN 2002
    ConvertDate = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))

    End Function

    If using this function to convert dates, be sure to exclude records where date is blank. Once converted to valid date values, you can use Format function to display date in just about any desired format (just keep in mind that Format function will always return a text string, not a date or numerical value, so you would not use it when performing calculations). Examples:

    ? ConvertDate("20030109")
    01/09/2003

    ? Format(#1/09/2003#,"yyyymmdd")
    20030109

    As illustrated, you can use ConvertDate function to convert the text string to valid date, and then use Format function to display date in original format, if desired. The difference is, if saved as date in a table you can perform date-related calculations, which cannot be done if saved as text.

    HTH

  5. #5
    New Lounger
    Join Date
    Jan 2003
    Location
    Fort Hood, Texas, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identical records, different dates (Access 2000)

    Thanks, Mark. I tried it and it worked perfectly. This is gonna save me some real time and effort.

    Captain D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •