Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Max Date (Access2000)

    Is there a way to sort for Max Date of Service if there is not primary key. I have a table with Account#, Name, Date of Service. I tried a query with a sort by acct# and max Date of Service but almost half of the records still show duplicates.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Max Date (Access2000)

    Doesn't that mean that you have two (or more) records with the same Date Of Service for the same Account#? If the Date Of Service is actually a timestamp as well, you should no longer get that, but if it's not, then you probably need to do a GroupBy on the Date Of Service field to start with and then build a second query on that that gets the Max of the field. BTW, if you field name is really Account#, that's not a real good idea - special symbols in field names can lead to all sorts of odd behavior when you least expect it.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Max Date (Access2000)

    I'm having trouble understanding your request. It would seem to indicate that 1 account can have multiple records? Given that Name is one of the fields, this doesn't seem too normalized to me. Exactly what is it you are trying to find? If each account can have multiple records, are you trying to find the record with the most recent date for each account. Or just the single account with the oldest date?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max Date (Access2000)

    Sorry for the confusion. I posted rather hastily and really should have figured out what I was looking for myself first. I have a table that contains fields like Account_No, FirstName, LastName, Drug, DateofService, Qty. There are over 4,000 records but there is no primary key. At least half of the patients have had their prescription filled more than once and possibly more than one prescription. I may not be able to do what I want because the table is far from normalized. What I am seeking is the most recent DateofService for each Account_No. Could I possibly split the table and put the Date of Service in a separate table? Then remerge the information? I realize I'm asking alot here but I'm very confused myself with so many different fields.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Max Date (Access2000)

    Is there some compelling reason you can't normalize this table? As it is, you might as well put it in an Excel spreadsheet because you are not making use of any of the built in advantages to Access. Your question about splitting the table is confusing. Why would you split the table and then put all the same data back into this monster? Wasn't there another thread where some suggestions were made as to a better table structure, or am I confusing this with someone else's questions?

    You should be able to query the table using a Group By query. If you group by the Account_no and use a Max value of DateOf Service, that would give you the latest date of service by account_no. However, those are the only two fields you can use or you're likely to get incorrect results. For instance, if the names were entered differently, you would get one record grouping for each version of the name. I'm sure that isn't what you want and I suspect you don't want just the account_no and date either, but the method is constrained by the poor design of the table.

    Overall, this is a bad design and you're going to keep having problems until you fix it.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max Date (Access2000)

    I don't have much experience with normalizing tables but as you suggest, I think it would take just as long to do it in Excel as it would to straighten out this mess. Guess I'll just spend a few hours deleting records after I sort it. Thanks for all your suggestions though.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Max Date (Access2000)

    If you want the history, you can't delete records. Do you understand the principles of normalizing the table and splitting the data into appropriate tables so you can easily get at the information you need? If not, there are several people who have web sites with information on the process and it has been discussed extensively in this forum. If you need help, feel free to ask for it.
    Charlotte

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Max Date (Access2000)

    I took a look at an earlier version of your database you posted in February, and it appears to be reasonably normalized. Have things changed significantly since then? Your T_Demographics appears to be a patient record, so you should be able to link that to your Service records and get a query that does what you want. If you haven't added lots of data, you could post your database as you did before and we can help you sort out the problem you are having. But it would help if the real problem can be defined more precisely - do you want the most recent service record for each patient?
    Wendell

  9. #9
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max Date (Access2000)

    Sorry Wendell, but this is a different database. Rather, not a database at all. It is just a spreadsheet I pulled using Crystal Reports. I dumped the table into Access to see if there is an easier way to find the most recent service date. I'm going to look at my Crystal book tommorrow at work. There may be a way to just pull the most recent date but I've mostly just used it to pull info from the ** Possible SPAM post - please alert a Moderator (2)** system which is an Oracle database. Thanks for your help though.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Max Date (Access2000)

    Surely a normal GROUP BY should be sufficient to get what you want, something like:

    SELECT Account_No, Max(DateofService) as ServiceDate
    FROM Table
    GROUP BY Account_No

    Pat

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Max Date (Access2000)

    I doubt Crystal will help you much - you talking about the way queries work and how you select certain data. Are you aware that you can connect to an Oracle database and run queries directly from it? It requires setting up an ODBC driver on your workstation - your system people should be able to help you do that. Then it works just like any other linked Access table.
    Wendell

Posting Permissions

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