Home Forums MEDICATION DATABASE-- nuts and bolts

I have been trying to find what makes the medication database work. I have successfully DELETED ALL TABLES except for MEDNAMES, MED DETAILS, LISTING. Correct me if I am wrong but I have found that the function of these are:

MEDNAMES- function: List of medication for you to search.
MED DETAILS- function: Gives choices of medicaion dosages.
LISTING- I don't know why this is essential.

Seems like the other tables are superflurous. I believe Bert told me this before but I didn't have the guts to delete the table until know.

The next question: What does the Listing table do? If anything at all...

Roy:

I have been messing with the same thing for months, and I have an answer (sort of).

Apparently, Jon's starting place for his prescription writer program was the National Drug Code Directory database, which anyone can download for free here:

http://www.fda.gov/cder/ndc/

On this page, you will find a little rundown on what each part of the database was intended to do. As you will discover, the database was actually created for the purpose of this online drug search program:

http://www.fda.gov/cder/ndc/database/default.htm

For this reason, there are a lot of portions of the Amazing Meds database which are indeed superfluous, from our perspective, because they were never designed for Amazing Meds.

As far as I have as always ascertained:

Formulations is basically empty but essential
Listings is the main database -- where you make changes to the medications and where AC goes to get the information
MedNames - The medications themselves complete with their form, i.e. capsules, tablets, etc.
MedsDetails -- MedNames plus strengths

Never fear about deleting the last two. You can delete them until the cows come home or even longer (until the script writer is fixed, couldn't resist) and they will just keep coming back. Kind of like Herpes. In fact, they are the two tables that MUST be deleted after a change if you want the change to be recognized.

I did a lot of trial and error with the medication database when I rebuilt the pediatric one. A lot of columns can be deleted. Even more than I have taken out. I will change that in Version 2.0. They can also be moved left to right or right to left. As far as the tables, these four and only these four are essential. I do not believe you can take out formulations even though it seems useless.

Brian most likely has a better answer than I, because he has devoted a great deal of time to the study of Microsoft Access while I am only reading six hours per day on SQL Server J/K. But, in Access tables and databases are linked so that some tables are essential even though they seem to do nothing. All through the databases that you can access, there are tables that are empty.

Hope this helps.

Bert,

So far, the database is working without the FORMULATION TABLE. I'll let you know if it continues to work without the FORMULATION TABLE.

I guess I'm wondering if I can get rid of LISTING, provided that I make edits directly to MEDNAMES and MEDDETAILS. As long as Amazingcharts does not need to recompile the MEDDETAILS and MEDNAMES, then the LISTING is not necessary (right?).

That is cool that you got rid of formulations. I can't speak for certain, but my STRONG feeling is that you can't get rid of Listings. I believe it is the key table.

Well, I guess that shows how much I know.

Deleted both Formulations AND Listings table, and it worked fine. I did try deleting the MedNames, and it crashed like Toonces the driving cat.

The only thing I didn't try was the database tuner.

That's too cool. One thing's for sure. If I ever switch, I will have to have my Access guy come in and write a script to move everything. I'm not editing 1687 entries, lol.

Bert removes his AmazingMeds.mdb Database Sage medallion from his neck and puts it arounds Roy's.

It's a sad day for Riverview Pediatrics

If listing isn't needed (and I guess we'll find out the hard way), then it would really simplify manipulating the database.

Perhaps Brian can explain this.... is there a way to see how Excel distills the needed information to the MedList and MedDetail table? I don't know where the macros to create these tables are located.

Thanks for trying removing the two tables, Bert.

Roy:

I am stuck at the hospital today, so I can't open up the database and check the details, but links between tables are just one of the innate properties of an Access database. You can create and break links between shared data in different databases.

There is a way to display these links. Unfortunately, off the top of my head, I can't remember how you go to the view that shows the relationships between tables (that's how much of an Access expert I am).

The reasons why certain tables in our database are linked to each other are bound to be obscure, unless you understand the way they worked together in the program they were originally designed for.