Results 1 to 2 of 2

Thread: Complex SQL for Access resource needed

  1. #1
    Join Date
    Oct 2019
    Posts
    1

    Complex SQL for Access resource needed

    First, I'll let you know the type of work I'm doing and what I need. I pull 835 insurance files into an Access database to manage my denials. So each row has a uniqueID, amtbilled, amtpaid, etc... One service can have many lines and I'm trying to automate the "status" field so that I know whether I need to still follow up to get paid or mark it off the follow up list if it's paid correctly.

    So I pull in the files and I need a good query to say "of these particular uniqueIDs, pull ALL those that match from the entire 835 table so that what I have is a subquery of "uniqueids with the checkdate of xdate" plus all other rows in the the table that match the uniqueid."

    I think I'm going it ok for now. I have basically

    select [835].field1, field2...

    from (select uniqueid from [835] where checkdate = "x date") as b

    join on [835].uniqueid = b.uniquid

    It seems to pull the correct group.

    But now I want to start figuring out how to automate changing the status. I'll need to use aggregate data like min(claimed) to compare against sum(paid) to see if what was billed is what was paid. And I need to update all the rows with that uniqueid when we've been paid what we billed.

    I don't expect one of you to help me with all of this but I need a really good SQL resource for Access - a really good book, training, website, etc... way more detailed and complex than what I've found.

  2. #2
    Join Date
    Nov 2020
    Posts
    36
    Saving data especially aggregate calc is usually a bad idea.

    If it can be calculated for an UPDATE, it can be calculated when needed.

Tags for this Thread

Posting Permissions

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