Looking for idea how to find out definition of Key values.

Without access to the parent tables where the id is likely the Primary Key, no.

What you are asking is the same as a table I have where a number foreign key is 123, what does it mean?

Why do you think you don't have access to those tables?  If the columns you are interested in should contain foreign keys then you must have access to those tables otherwise you could not create a new records or check the data integrity etc.

Do you have SQL Server Management Studio?
Are you able to backup the database and restore it in development environment?

Are you able to execute e.g.
SELECT * FROM INFORMATION_SCHEMA.TABLES
or
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Frequency' -- try it for some existing table

How did you retrieve the data into Access? You should know connection string and be able to connect into the database.

@slightwv,
As mentioned, I have access to the site where I can see the meaning of individual patients records.
So if for example every patient would have only one record, it would be easy to figure out...
So perhaps similar to this, we can have other ideas on how to figure out...
Thanks,
Ben

@pcelba,
We use a web bases software called HHAExchange, and they provide access to GUI only, no tables whatsoever.
While they have an FTP site where they put some tables upon request, including Patients Medications.
However not all tables are being transferred to the FTP, and therefore the related info is missing.
Thanks,
Ben

Just remember one thing: Patient data are highly sensitive stuff. You are either approved and you have granted access or not. If the data owner restricted the access then the reason is moreless clear.

I am just hesitating whether this is hacking or development. If it would have been development then you should know the data model, you should have testing environment where all these lookup tables are copied from the production and patient data are anonymized.

OK, it seems the HHAExchange owners are just keeping their secrets in house. Why should they provide all the values (all the know how)?
You may create a number of testing patients and investigate the data meaning (if the license allows it) or possibly pay for the internal data or ask for meaningful data etc. etc.

OTOH, I would not buy the system where the vendor does not guarantee data export for possible porting such data into a different system.

What kind of prove are you expecting to get in order to verify we are the legitimate owner of this data and have the full access to all patients data-:)

Thanks,
Ben

You may create a number of testing patients and investigate the data meaning

This would require creating a record for each option of each of those 3 drop down menus...
And we can only get the data transferred after 15 minutes.
looking for a shortcut...
Thanks,
Ben

OTOH, I would not buy the system where the vendor does not guarantee data export for possible porting such data into a different system.

Agree, and they promised us to export all data requested, and now on this (not sensitive data, that we can probably figure out ourselves) they are refusing...
Searching for the email what kind of excuse they gave on this...
Thanks,
Ben

I don't need the proof. You can be the owner of the patient data but that's not sufficient for easy data porting outside the application into a different environment. The app owner allows to export the patient data and then he can import them back into the same application but the owner did not promise to provide all internal details like Frequencies, Routes etc. etc.

To use the application requires some agreement. What everything grants or allows this agreement? Does it say "All lookup tables are available for download by app users"? You may obtain all data entered by you, the app owner has the rest.

Are you able to define your own Frequencies or Routes and use them in your Patient records? If yes then you should be able to obtain such data via FTP because they are yours. If no possibility of custom Freqs and Routes definition exists then there is no reason to provide such data as a part of the standard pricing plan.

See attached what they send about the definition of values requested.
I believe we are not violating any laws by figuring out this info, just they happen to have an issue handling over those tables...
Thanks,
Ben
Capture.PNG

If no possibility of custom Freqs and Routes definition exists then there is no reason to provide such data as a part of the standard pricing plan

OTOH, I would not buy the system where the vendor does not guarantee data export for possible porting such data into a different system.

Aren't these two contradicting comments-:)

Anyways back to our main Q, what are the alternatives?

Thanks,
Ben

If they are exporting the data then they have queries prepared for this purpose. To update these queries would just mean a few more JOINs.

Agree, however it will cost some fortune (as everything they customize for us...) besides the time it will take for them to complete...
If I have the option of figuring out w/o their involvement, why not go for it?

Thanks,
Ben

Yes, almost nothing is for a free today... And you have to pay for the stability and reliability of the application vendor.

Cheaper option could be to outsource the investigation into India but it also has significant drawbacks...

And the two contradicting comments - the first one was from the vendor point of the view the second is rather focused on customer. Each one requests maximum advantages... and it could be the contradiction.

Hi,
Thinking of an idea, not the best but at least some kind of a shortcut, instead of entering all options as separate records.
Will add all options selected as a description, see attached.
Still open for better ideas...
Thanks,
Ben
Untitled.png

Yes, even when TY TAB sounds cryptic this seems to be doable if you don't have hundreds of options.

Hopefully they don't change IDs of the data in option lists.

Hi Experts,
The situation here is as follows,
when entering a new med for a patient (thru the GUI), we must type in the first two letters then a list pops up to select a medication, once selected and returning to first screen, the dosage drop down shows you the options avail for that med...
When queering our table, I can see over about 360 options of dosages ID's.
firstly I'm not familiar with all types of medication names, wouldn't even know what to start typing...
then to know the different dosage avail for each med would need a doctor or a pharmacist...(as you might guess I am a programmer-:)
and lastly if I have to create a new patient for each entry, filling in all requested fields and then the med, it would take forever...
Second, as far as better ideas, if you have access to the web interface and can enter data, then enter record(s) for everything you can think of, then see what the values are that are returned for the known records.
Having 360 dosage options, 27 Frequencies and 19 Routes, what would be the total of records I would have to add in order to get my results?

Hopefully they don't change IDs of the data in option lists.

Good point, have to verify that with them as well...

Thanks,
Ben

IDs change is just a defense against data miners I would not expect they are doing something like this…

Having 360 dosage options, 27 Frequencies and 19 Routes, what would be the total of records I would have to add in order to get my results?

The answer is 360.

Hi Experts,

Just thinking of another idea...

Will create a file containing all dosage ID's, all under one patient, and have the dosage value in comment field as well.
Then will ask them (HHAExchange) to import this file into our DB.

Just emailed them asking if they would this...

PS- As mentioned, they have built in functionality only to display dosage applicable to particular Med, so in order to create that file will have to run a query that will give me the Med for each DosageID.

What do you say for this..?

Thanks,
Ben

<<What do you say for this..?>>

Won't work unless you can see the ID's in the web interface.

 I would look at the data you already have for patients with singular values (one medication/dosage).   Then look them up in the web interface and get the descriptive text for the med and dosage.

 Build some local lookup tables based on that.

 After your done with that process, then you'd need to enter one patient record with a single medication for each one that you don't have.  The dosages will be easy because they span medications, but you might need to enter hundreds of records to get all the medications.

 My hunch though is that you'll be able to get the majority fairly quickly and it will be the last 10-20% that will take some time.   So I'd probably attack it piece meal and have some scrubbing built in at import that flags records with no match in your local lookup table.

 Then as they occur, I would add some patient records to get those.

 This way, you'd be entering records a few at a time rather than trying to account for every possibility, some of which may never get used.

Jim.

Hi Jim,

Won't work unless you can see the ID's in the web interface.

Well this is why I mentioned the following

...and have the dosage value in comment field as well.

So this will show up there.

I would look at the data you already have for patients with singular values (one medication/dosage).  

I ran a query and almost no patient have only one medication. (guess if they need only one medication then he's not a patient with a nurse..-:)

@pcelba,

But you don't need complete Medication database just the data export containing data from your patients in a form meaningful for human. Explain them you need export containing no IDs but texts visible in the Web app.

Will try that option as well.

Thanks,
Ben

They finally agreed to send over the description of those ID fields (luckily I didnt start the other route, as would never been able to figure out, as they have multiple descriptions per ID).
Thanks to all participants!