Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have two tables.
    One has patient name, account number, their insurance name, and a field that signifies if it is taxable, or not.
    The other table has the account number, and payment received from their insurance.
    I need to be able to update the "Taxable" field with a "Y" or "N" based on the name of their insurance in the second table.

    I have tried several methods, and other than doing it manually, I am unable to come up with a workable plan.

    If anyone can help, with either a solution, or just a suggestion to point me in the right direction, I would be greatful. The tax man has visited our company & I need to get answer questions, I didn't know needed to be answered.

    Thanks in advance, Michael

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MHadden' post='764612' date='10-Mar-2009 16:16'][/quote]
    I don't understand what "based on the name of their insurance in the second table" refers to - according to your description, the Taxable field and the insurance name are both in the first table.

  3. #3
    New Lounger
    Join Date
    Sep 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow, I guess I messed that one up right out of the gate...

    What I meant to say is that the "Taxable" field is in table two.

    In effect, the patient has a primary & a secondary insurance. If their primary insurance is Medicare (from table number 1), then the part that is paid by their secondary insurance (in table number 2) is considered to be a part of the same transaction which is then non taxable.
    I got caught up in trying to simplify a very confusing situation.
    I'm sure there's an easy way to handle it, but I'm stumped.

    Our problem is that we have to link the customer number with their primary insurance, so that even when their secondary pays, it shows as non taxable.

    I'm sorry for the confusion, but glad you caught it. The "Taxable" field is in the second table, which contains all of the payments from secondary insurance companies.

    Thanks again,
    Michael

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MHadden' post='764627' date='10-Mar-2009 16:52'][/quote]
    I feel rather stupid today, I still don't understand. If secondary payments are never taxable, it would seem to apply to all records in the second table. Under what circumstances would a record in the second table have "Y" in the Taxable field?

  5. #5
    New Lounger
    Join Date
    Sep 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Examples:

    Mr. Smith has Medicare as his primary insurance, and Blue Cross as his secondary insurance.
    The 80% that Medicare pays is non taxable. The remaining 20% that Blue Cross pays is therefore non taxable as well.

    Mrs. Jones has Blue Cross as her primary insurance, and Aetna as her secondary insurance.
    Both the 80% from Blue Cross as well as the 20% that Aetna pays is taxable.

    Mr. Doe has United Mineworkers as his primary insurance, and Medicare as his secondary insurance.
    The 80% that United Mineworkers pays is taxable, and the 20% from Medicare is non taxable.

    We did not have a great way to prove who had what as their insurance, so the Department of Revenue in our state, while reviewing our records are now asking for a payment of taxes on revenue received from patients like Mr. Smith, because he has Blue Cross insurance, or proof of his Medicare primary coverage.

    I am trying to determine which patients have Medicare as their primary insurer, so that I can show which ones we do not have to pay taxes for their secondary payments, from companies like Blue Cross, Aetna, etc.

    I apologize for the misunderstandings, but since I'm in the industry, I sometimes ass/ume that others understand certain things without my going into detail.

    I really do appreciate you looking at this to see if you can offer any help.

    Thanks, Michael

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your description still doesn't fit with what you told us about the tables. It would help if you could post a stripped down and zipped copy of your database with some dummy data.

  7. #7
    New Lounger
    Join Date
    Sep 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,
    Thanks for being patient as I have tried to explain my problem.
    I am attaching a copy of the database with names I copied out of the "Northwind" database in order to protect my patient's privacy.
    All the remaining data is what I am currently working with.

    Thanks Again, Michael
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MHadden' post='764667' date='10-Mar-2009 19:32'][/quote]
    You mentioned that "Medicare" played an important role. I don't see "Medicare" anywhere in the tables though.

  9. #9
    New Lounger
    Join Date
    Sep 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry...

    Medicare has Cigna handle the paperwork for them.
    The ins code is 9003 & the name is "Cigna Government Services"

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MHadden' post='764692' date='10-Mar-2009 21:53'][/quote]
    You can use a query with the following SQL:

    UPDATE CustomerInfo INNER JOIN [RT-PI_Oxygen_Leases] ON CustomerInfo.[CUSTOMER #] = [RT-PI_Oxygen_Leases].CustNo SET [RT-PI_Oxygen_Leases].Taxable = IIf([insCode]=9003 Or [INS]=9003,"N","Y");

    Note that the majority of records in RT-PI_Oxygen_Leases doesn't have a matching record in CustomerInfo; these records will not be updated since there is not enough information. I have removed most of them to reduce the size of the database.

    I have attached the database with the query: [attachment=82775:Copy_of_AL_DOR.zip]

    Note: I'd make Taxable a Yes/No field.
    Attached Files Attached Files

  11. #11
    New Lounger
    Join Date
    Sep 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,
    I cannot thank you enough!
    This has been a fantastic help to me.

    I hate that so many of the things I said were confusing, but I very much appreciate you sticking with me through it.

    I have taken your advice & changed the taxable field to a Yes/No field.

    Thanks again,
    Michael

Posting Permissions

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