• 3 rows of data into 1….

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » 3 rows of data into 1….

    Author
    Topic
    WSDeborah
    AskWoody Lounger
    July 1, 2009 at 8:14 am #460920

    Ok, here is the deal.
    I have a large spreadsheet that contains 1 row for charge, 1 row for insurance payment and 1 row for patient payment.
    I have imported into access.
    I want to have 1 row of data for this patient with the charge amt, insur payment amt and for the patient payment.

    I created a query and have been playing around in there but can’t seem to get it to work.

    Now, a patient can pay more so I might have more rows than just the 3 mentioned above or there could just be a charge.

    How can I get one row based of account number with all the fields popluated??

    Deb

    Reply | Quote
    Viewing 4 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      July 1, 2009 at 8:39 am #1167257

      I think you should leave the table as it is now, and use a query to display the data for a patient on one row. Depending on the setup of the table, you may be able to use a crosstab query. If you want more help, please provide more detailed information, or attach a stripped down and zipped copy of the database to a reply.

      Reply | Quote
      • WSDeborah
        AskWoody Lounger
        July 1, 2009 at 9:06 am #1167264

        Attached is a sample excel file.
        the first records are a prime example of my issue.

        I would like to have one record with all the data filled in…instead of three separate ones. I tried the cross tab but it will not allow me to have a column for charge, payment and adjusted. this is noted in gray at the bottom of the sheet.

        Thanks for your help

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          July 1, 2009 at 9:19 am #1167268

          Can you explain how I should read this table? There are dates with a diagnosis code, description and charge amount, but other dates without. How can we determine, for example, what the payments on December 11 and 14 refer to?

          Reply | Quote
          • WSDeborah
            AskWoody Lounger
            July 1, 2009 at 9:24 am #1167269

            Look at the dates for 12/25. There are 3 rows of data.

            There is a charge of 249 – this contains all the data for that initial visit.
            There is a Pmt of 156.20
            There is an adjust of 92.80

            Look at row 17. This is what I would like the final to appear as.

            Reply | Quote
            • WSHansV
              AskWoody Lounger
              July 1, 2009 at 9:26 am #1167272

              But what do I do with the other rows? Discard them? I would like to know the exact criteria to decide what to include and what to discard.

              Reply | Quote
    • WSDeborah
      AskWoody Lounger
      July 1, 2009 at 9:26 am #1167271

      Also, this is why I am trying to put them all together.

      I need to make sure there was an appropriate charge sent on that date of service.
      It appears that for this CPT code, there was not charge but payment was applied – 12/11 and 12/14 dates.

      Reply | Quote
      • WSHansV
        AskWoody Lounger
        July 1, 2009 at 9:28 am #1167273

        Also, this is why I am trying to put them all together.

        I need to make sure there was an appropriate charge sent on that date of service.
        It appears that for this CPT code, there was not charge but payment was applied – 12/11 and 12/14 dates.

        But ALL rows have the same CPT code. Does that mean there were FIVE payments of $156.20, and FIVE adjustment amounts of $92.80?

        Reply | Quote
        • WSDeborah
          AskWoody Lounger
          July 1, 2009 at 9:30 am #1167275

          Different dates of service……

          But ALL rows have the same CPT code. Does that mean there were FIVE payments of $156.20, and FIVE adjustment amounts of $92.80?

          Reply | Quote
          • WSHansV
            AskWoody Lounger
            July 1, 2009 at 9:31 am #1167277

            Different dates of service……

            I’m afraid I’ll have to leave this question for others, I’m not able to understand it. Sorry.

            Reply | Quote
    • WSDeborah
      AskWoody Lounger
      July 1, 2009 at 9:29 am #1167274

      I will need all data.

      I need to know based off of dates of service, was there a charge, if a charge, was there a payment from the insurance and was there an adjusted amount.

      I am thinking the office charged the wrong CPT code but applied the payment to the account.

      Reply | Quote
    • WSDeborah
      AskWoody Lounger
      July 1, 2009 at 9:32 am #1167278

      look at this attachment.
      I sorted by dates of service.
      Noticed that some do not have a charge amt or diag code.

      this is the problem…..I have to find these mistakes as well as make sure the charge was correct when there is one.

      Reply | Quote
      • WSHansV
        AskWoody Lounger
        July 1, 2009 at 9:38 am #1167280

        Let me try one last time. Should we only look at dates that have a Diagnosis code, and ignore the other dates?
        In your example, does that mean that the end result should have rows for December 18, 21 and 25? If not (you only indicate December 25), why should December 18 and 21 be ignored?

        Reply | Quote
        • WSDeborah
          AskWoody Lounger
          July 1, 2009 at 9:47 am #1167282

          Please do not ignore any dates.
          The 12/25 was a prime example of what should be there.
          A charge record, a payment record and an adjusted date record.
          I need to see all the data.
          I need to be able to look at one row….and see a charge, payment and adjusted amount. This is the correct. If I see a row with only a payment and adjusted then I know they charged to the wrong accout or applied monies to the wrong account…etc.

          Thanks for looking one last time.

          Reply | Quote
          • WSHansV
            AskWoody Lounger
            July 1, 2009 at 9:52 am #1167283

            You mentioned at the beginning that there could be more rows because of multiple payments. Should these payments be added together in the summary row? If not, how should extra rows be processed?

            Reply | Quote
            • WSDeborah
              AskWoody Lounger
              July 1, 2009 at 9:59 am #1167284

              The extra payments will come in an additional row of data.

              I have created 3 queries and then created another that combines them all together.
              This works only if there is 1 charge, 1 payment and 1 adjusted.

              Is there no way to look at the account # and date of service and if they are equal then create one record???

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              July 1, 2009 at 10:06 am #1167286

              In the attached, zipped database, I imported the worksheet into tblRaw.
              I then copied and pasted tblRaw with the option to paste the structure only, not the data. The result was an empty table tblProcessed with the same structure as tblRaw.
              I then created a totals query based on tblRaw that groups by the Date of service, and aggregates the other data: the sum for currency amounts, and the max for all other fields. This query returned a single row per date, regardless of how many rows there were originally.
              I changed it to an append query that adds the records to tblProcessed, then executed the append query.
              Does the result correspond to what you want?

              Reply | Quote
    • WSDeborah
      AskWoody Lounger
      July 1, 2009 at 10:10 am #1167288

      Yes, I think this is exactly what I need.
      I will study the database and see what I need to do on my end to get the same results.

      I do appreciate your help. I know it is difficult to follow the questions and statements without truly understanding where we are coming from. We see it clearly…typing it out is not so clear. I worked a helpdesk for years…LOL

      Thanks, you are my hero!

      Reply | Quote
      • WSHansV
        AskWoody Lounger
        July 1, 2009 at 10:11 am #1167289

        Glad we were able to work it out in the end. Post back if you have more questions.

        Reply | Quote
    Viewing 4 reply threads
    Reply To: 3 rows of data into 1….

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.