I recently lost my practice manager, and I am no sure how to do certain things. Our service organization wants a report of our Medicare patients' name and address so the patients can be aware of our Medicare Advantage offerings. For the life of me, I cannot figure out how to include address in the report. I chose demographics-> insurance-> = -> Medicare, and the report comes out. However, it seems that report gives phone number, but I cannot add address as an output field. Is there some trick to that?
I am now on 11.1.1. Alternatively, is there a way to query the SQL database directly? I seem to remember there was some guest read-only way to do this. Maybe do a ODBC connection with some read only name and password?
Thank you for your reply. Someone hinted me that before, but for some reason adding any of the above fields ends up with timing out and the server slows way down and has to be rebooted. Adding just insurance is ok. My field names for address is also different.
Are you implying there is an actual way to input a formula, as in an SQL statement?
I think its just taking longer to run the report with more query criteria. Try running it overnight or on weekend? Dont' know how to run a direct sql query ask JamesNT
I use:
demographics / inactive = false notes & encoutners / encounter date > 01/01/2021 street address <> xxx state <>hi zip <> 11111 city <> xxx
the report spits out the appropriate matches with address fields attached
While this can be a DIY project by using queries, it is likely much simpler with someone familiar with SQL. If you can't find JamesNT, then my friend Raja could do it.
I love database projects, and would love to be able to query the AC database itself. However, I do not have username/password access to it. There must be a read-only way for practices to access their own data.
Because any AC report involving address seems to time-out for me (possibly due to some corrupt field), I could not use Beagle's method. However, I was able to run a unique household's report, and inner join that to the medicare report (after some name manipulation) and get the report I wanted. So for now the needs are satisfied.
This should get your started. Reach out if you require further assistance.
SELECT [First] as FirstName, [Last] as LastName, PatientAddress as AddressOne, PatientAddress2 as AddressTwo, Demographics.City, State, PostalCode FROM Demographics INNER JOIN ListPayors ON Demographics.PatientID = ListPayors.PatientID INNER JOIN PayorsEdited ON ListPayors.PayorsID = PayorsEdited.PayorsID WHERE PayorsEdited.PayorName = 'Your Medicare Name Here'
I am probably wrong, but I believe these queries will have to be run in SSMS. Not sure if the OP will know how to download and install that although it is fairly straightforward. You also need the right version for the current SQL version. If you install SQL yourself, then there is a setting where you can choose to log into SQL using Windows Authetnication. However, if AC does it, you will need the sa username and password. This won't be a problem for James, but it will be a showstopper for the OP. ANd, the queries, while written out step by step can be screwed up.
It would just be simper if James remoted in, installed SSMS and ran the query. Just my guess.
Thanks for the kind words, but I will have to give James all the credit for running the queries. In his business, he can do that with his eyes shut.
Some basic info. A long time ago one could use Sql Server Management Studio (the program which allows you to manage the SQL database) had a system admin and password that many people knew. Now when AC installs SQL the sa username is different for every user, so it is nearly impossible to use SSMS (***********************************). If AC installs the entire v11 with SQL 2016, the only login will be with sa and password. (*******************************)
Using SSMS (v18) https://support.microsoft.com/en-us...8-8-093287d3-7aa8-f13d-22af-eda7aea16665 you can read and write to SQL, therefore to AC. So, it sets up an ethical situation. "Patient comes in with a 104 febrile...." and ends up with meningits, that note could be changed to "Patient comes in afebrile,...." now changes are logged and Indy and I disagreed on whether the logging could be turned off. (James can break the tie). I always look at EMRs and compare them to paper as the standard. So, if you had 104 written in the note, you would have to strike it out with a line and initial it. It would be hard to change. So that leads to do you have a right to your data or just read and not write. Of course, you could in parentheses write {edit pt not 104 was afebrile date and time and initial or sign] ______________
The person I use (before I knew James) works on all versions of SQL. His only job is to work with companies (say Quick Books) where they may be using it in a company or companies with hundreds of users. A user would select something which would ask for information with a query and the time would be recorded. So, say it took 3 seconds. The SQL Server tech would then monitor the query for time. If the query took longer than 3 seconds, then the bottleneck was with SQL and that could be looked at, i.e. how the query used the tables, etc. If the query took 2 microseconds, then the bottleneck was with Quick Books.
So, when we ran a query to pull medications over and it took 5 seconds but SQL took 15 ms, then we need it was with the program. That doesn't mean the program is written wrong, just the path it has to navigate the data.
Last edited by Bert; 10/15/20224:25 PM. Reason: tmi
Re: "So that leads to do you have a right to your data or just read and not write. "
At the risk of hijacking my own thread, I would of course have to vote 'yes' to clinics having read-only access. Is there a technical reason we cannot (if AC so agrees)?
I'll address two things here. First, auditing in Amazing Chats. Second, clinics having read-write access to their data outside of Amazing Charts such as through SQL Server Management Studio.
Amazing Charts handles auditing the way may softwares do, by placing "triggers" on various tables to record changes to records in the table they are on. Take the Demographics table, for example. The first attachment is its trigger. Note the trigger "fires" after any INSERT, UPDATE, or DELETE statement is run against the demographics table and records the results in the AuditLog table. If you want to disable auditing, then disable the triggers. Indy and I used to argue about AC auditing as well. He always seemed to think they had some "super secret super evil M$ thing" that allowed them to always know what was going on in any database at any time. Not true, I'm afraid.
As for clinics having read-write access outside of Amazing Charts, I am generally against it. I say generally because I do assist people with things like data clean up, deduplication, and so on. There are some things that just cannot be done easily within AC. And, using SQL Server Management Studio, you can really screw up your data in a hurry. So, yes, I will help people access their data but either A: I run the queries for them or B: I seek certain assurances they aren't about to shoot themselves in the foot AND I always ask if they have good backups.
My saying that James is spot on is kinda like a weekend Cessna pilot saying that a NASA astronaut has it spot on. Yes, a SQL Server Specialist can disable the triggers and no one would know.
As far as read only, it is crucial that it be read only (although it is read/write). One, if you had a big lime lawsuit and they subpoenaed your SQL Server Data, it would be good to know that there was no way you could change the data. Also, as James said, if you could write, you could really screw things up that could be fixed with a backup, but it would be rather difficult to explain to AC support that you need help because you changed the data in an entire table.
The irony is the username for Jon's SQL Server 2005 Express database for the sa was sa, and the password was one that would be hard to guess but was a little like using your cat's name, your birthday and the all too often used !$ or 1$ at the end.
Certainly, AC support already has enough issues troubleshooting a problem when 5,000 users have 5,000 slightly different network configurations. But, if they had altered the database, that could make things unfixable without a backup.
It would kind of be like asking Microsoft support for help after deleting 100 registry keys without backing up the registry. Of course, there are many times that changing the registry is helpful, but as James would tell you, making a backup of that is pretty simple.
James will also tell you, especially with backups of your VMs or server, backing it up and then seeing there is a backup is not really testing your backup. You have to actually test if you can restore from that backup (of course, not restoring over your production data).
And, I am putting words in James' mouth so hopefully he will correct everything I am positing he would say. (Did I use that word correctly)
Bert -- How can you really test a backup without screwing up your current data? We aren't allowed to have a second "test" or "backup" server so far as I know. I only had to do a full backup a long time ago when I messed something up, and the backup worked fine (but that was version 6.* and we didn't have to get permission from AC to set up a new server.
You test backups by having good IT like me that provides both onsite and offsite backups for you and randomly chooses a client every now and then and restores that client's backup to a test server.
In other words, it just gets done. And, you and request a backup restore test. I have a server laying around just for testing backups.
Yes, the confusion is I wasn't referring to the .enc AC backup. Ironically, it is pretty solid. I have found 95% of its main purpose is to restore Amazing Charts after an INSTALL. There is a way to test it, but you are correct, you would never want to restore it to your production AC.
In fact, there is never really a good reason to test any backup to a production environment. James is talking about restoring to a test server. Many of the 3rd party backup programs allow one to restore a bare metal backup to another computer with different hardware.
I was referring mostly to 3rd party software such as Veeam or Altaro or Acronis backing up Virtual Machines. You can restore to a different area and most of them have test environments which mimic your network environment.
I have three backups. All of them are restored to a non-production computer or to the setup the backup software creates.
Veeam even has SureBackup, where it creates a backup and tests it with certain scripts, checks it, verifies files and can even do a complete antivirus on it. You can spin it up and browse it and run programs, etc.
To clarify, I never use Amazing Charts backup. And I don't do bare-metal installs. Everything is virtualized and I back up the entire virtual machine. The reason for this is there are always other things you care about on the server besides Amazing Charts:
* Updox installation * Interfaces that themselves may have programs installed to facilitate * Practice Management software.
And so on. With the AC backup you have your Amazing Charts databases and imported items, but you lose all the stuff I mentioned above, and it could take hours/days to get all that stuff re-installed. You have to coordinate with vendors (e.g. LabCorps), for them to get their stuff back on, so forth and so on and so on and so forth. What a nightmare.
When I restore the VM, everything including other vendor installs is back the way it was and we are done, Done, DONE.
To reclarify, while VMs are considered best practice for many reasons, not everyone has VMs. I do have VMs, and I do back them up every night with FF incremental backups, but I also back up AC each night as well. I sort of look at it like a quick modular backup. If something happened to AC, I would much rather re-install it and restore it in minutes with a backup that is made specifically for AC. Then I don't have to restore an entire VM and restore over other perfectly good programs and other data when I can just restore AC. The other reason to back up AC is it is the only way to install the new version (yes, you can do the backup specifically for that purpose just prior -- and you should).
We just talked about testing backups which is not done every day so it is possible that the entire VM backup is corrupted or not being done for whatever reason. Then you at least have your AC backup to keep you from practically losing your practice. Not sure which is more important -- AC or your PM -- but you can continue practicing even if you lose everything else. Or if you don't have a good backup after 10/12/2022, you can restore up to there but still have a nearly uptodate AC and PM and imported items. You can never have too many backups, and we can agree to disagree on this one.
Now I am with you on the fact that you should back up your entire VMs or entire server or entire workstation if using peer-to-peer. I even run the AC backup and our Medware backup manually and watch it complete. I would highly recommend that everyone do a full backup as well as the AC backup and your PM backup if it allows you.
Back in the days before computers I knew of practices that were destoyed by fire or flood and lost all their records. In those times there was no practical way to back up all the paper files, so the catastrophe victims just started over from new blank records. Office business insurance ususally had a benefit for this eventuality.
It's probably more common to lose a server through random error, vandalism, accident, etc -- but those things seem to be amazingly tough, and it doesn't happen very often in small practices so far as I know.
Backups seem to be relatively easy. However there appear to be a number of ways to do it. I wonder about the corporate environment -- seems like data loss can take a major corporation several days or longer to get straight. James mentioned something about a "test server" Does AC allow us to have a backup "test" server? I never asked them.
Since I'm backing up the entire virtual machine as-is, I can do as many test restores as I want with no involvement with AC. If you want to test AC backup, you have to call AC and set aside time/money/your own test server/etc.
When I worked at General Electric Global Nuclear Fuels, and also at Corning Fiber Optic, we had lots of things in place to prevent data loss besides just backups.
* Hyper-V Failover Clusters * SQL Server in-guest clusters (a SQL Server cluster within the afore mentioned Hyper-V cluster) * Backups that includes nightly full backups with hourly incrementals. * RAID 6 or RAID10 arrays * Storage arrays with multiple back-planes and multiple controllers.
Some of the setups I did you could lose 4 SAS cables, 2 disks, an entire server, and an array controller all at once and you would have no data loss and roughly 4 minutes of downtime as the surviving server took over the cluster and powered your VMs back on. That setup was also north of $90k.
Add that on top of my C#/VB.Net/SQL language programming skillset, SQL Server Administration, Group Policy/Active Directory, and all the other IT stuff I know including Azure, pfSense, and Juniper Administration.
.png "ACUF Donor I")
