Patient Management Database, Help Needed For Tables And Relationships
Jan 16, 2008
I am new to this post. I am a physician with interest in database designing. I have been trying to design a database for my clinic for few months but am unable to make one. I have been searching/ reading alot of info and came across this thread. Maybe someone can help me.
Actually, I want to make a database regarding ultrsound scan examinations of patients.
I have five tables.
1. Patients. (patinetid*, patientname, age, sex, address, contact no)
2. Physicians. (physicianid*, physicianname, speciality, address, contact no)
3. Scans. (Scanid*, scanname, charges)
4. Scan orders.(scanorderid*, patientid*, physicianid, scanordernumber, scandiscount, totalcharges)
5. Scanorder details.(scanorderdetailid*, scanorderid, scanid, charges, discount)
I want to have primary key for scanordernumber which wil be the patient number and should this be placed in patient table??
All the ids have been linked with one to many reltionships. Actually I am unable to set proper relationship.
So when the patient arrives he is registered with a unique number, a physian name with date added and scan ordered is entered. Sum calculated. I have done the later part with the form all designed but the relationships and primary key are all messed up.
I can post an image of relationships or blank database.
Patient demographic info (Patient ID, First Name, Last Name, Address, date of birth, etc...) Physician demographic info (Physician ID, Name, Last Name, Specialty, Phone, Fax
I also have another table with patients that are being case managed. I have those ID#'s. However, I want to be able to auto-populate a form with the patient's demographic info by only typing the patient ID. Also, there are cases when the patient ID will not be in the patient demographic info table. Therefore, I will need to enter all their demographic info manually.
Same with the physician. I want to be able to select the physician and auto-populate the demographic info. However, there are occassions where the physician will not be in the main file...therefore, I'd like to add that info manually.
Hi, I would like to get your advice on my table setup and relationships for this payroll project. The company is an engineering company with Projects (or construction sites) around the world.
The 'Candidates' are current or potential employees and contractors. There are three main pay categories:
1.Shift-workers All shift workers doing a particular job on a particular project are paid the same rates e.g. all welders on a particular project or site in England are paid the same as each other. For that reason I want to link the pay rates with the job description for these workers. This avoides creating 50 records for 50 welders on the site in England to say that they make £10 an hour normal time (or whatever it is) etc.
2. Contract Contract workers usually get paid a flat rate per hour. As these are negociated on an individual basis I would need to have this information linked to each individuals job (M_CandidateJobDetails).
3. Salary Again this information needs to be input for each individuals job.
For the contract and salary people the pay frequency can vary (weekly, bi-weekly or monthly). So can the currency they are paid in. I haven't got as far as the currency issue yet.
The reason for the one-to-many relationship between M_JobClassifaction and M_CandidateJobDetails is that many candidates can have the same type of job e.g. there can be many employees that in the job classifaction of 'Electrician'. For many of the jobs at managerial level e.g. 'site manager' there will only be one.
I will have a table with the hours worked by each person per week. I can use this for those on shift work or contract to calculate what they will be paid.
One of the main reasons for this database is so that the company can print reports to see what is paid out in payroll for each site and in total (in euros). These will be gross figures and I don't need to take expenses, vacations, bonuses or taxes into account. They other thing we will need to be able to do is assign candidates to vacant positions and change them from one position to another - possibly between different projects.
So basically does anyone have any comments on the relationships, normalisation or anything else. Is this the best way to do it?
Hi, I am a newb at databases and Access, I work in a hospital that does not yet have an electronic medical record, so I am trying to make a simple database for our trainees to input patient information and print out daily progress notes. There is no money to buy one that is already made, and I'm very much interested in learning the basics of database design.
Our system currently has 12 teams defined by colors (red, blue, green etc.), each with 2 interns (intern 1 and intern 2).
Patients: Patient_Id (autonumber, PK) Intern_Id (number) Last Name: First Name: . . . etc.
Teams are set up 1 to many with interns which is set up 1 to many with patients. I've already inputted all the team colors (red, blue, green etc.) and the interns (since there is a set number of these)
I'm having a few problems
1) on a basic note, how would you go about assigning a patient first a team color, then an intern. The way it is set up now I can assign them an intern who is already assigned a team. But then I have to pick through 24 interns (12 teams x 2 interns) to select the intern. Ideally I want to first select a team, which then narrows it down to only 2 interns.
2) is there a way on a form to display text in a combo box, but have the database enter a number in the actual database. The way I have it set up above, everything is assigned an autonumber. I want to set up an easy to use form for interns to enter patient info. If I want to assign a patient an intern I would like the combo box to say (intern 1, intern 2). However each intern is assigned an autonumber so in the form it lists autonumbers 1-24 (since there are 24 interns overall)
Sorry if I am not explaining this very well. Any help would be greatly appreciated!
My db is split into front-end and back-end. In the f-e's Relationships schematic, I can see the relationships as they were defined at the time when the db was split, complete with the one-to-many symbology.
I can add a new table to the b-e and set its relationship as one-to-many, enforcing referential integrity and cascading as I wish - and the schematic (in the b-e) reflects that.
In the f-e, I can then use the <Get External Data - Access Database> function to link to the new table, and I can add the new table to the relationships diagram in the f-e. I can also drag and drop to link primary and foreign keys (within the f-e), but cannot select one-to-many. I'm OK with that, as I understand that the relationship is within the b-e, and this is just a diagrammatic representation.
But I can see the one-to-many relationships between the tables which existed when the db was split, and I would like to be able to see the new table's relationships in a consistent fashion. Updating the linked tables via the Linked Table Manager does not do the trick.
Surely I don't have to split the database again in order to achieve a consistent diagram - do I ?
There is no clear relationship between the variables - ie they can all be 1 to 1 or 1 to many or do not have to exist at all. So I realise now (after 14 years of working with this data), that I need to have what I would think of as holding tables between the primary tables that hold combinations of the variables. Do I do this in one table that holds all 3 variables in non-repeating combinations (although this would need to allow nulls) or do I do it in 3 separate holding tables?
I am trying to create a database for a clinic, and am severely stuck on how to input appointment dates for individual patients.
I have been using the 'student' database from office.com as a template for how to save the dates (given that appointments and attendance are exactly the same!), however, even after following what has been set up in the 'student' template database, I can't seem to replicate it.
Every time I add multiple visit dates for a specific patient, these exact dates show up for every other patient in the database. I need to be able to add different dates for all the different patients.
I am trying to create a access database system which will show classes, students, attendance, progress report, teachers, departments etc...
i have repeatedly failed in accomplishing this database as i dont have the knowledge.
the place that i get messed up is for example the attendance, i want it so that i can select a date and then all the students associated with that class showup. so 1 date and a list of student only on that class.
another problem was adding a progress report for every class and individual students.
this is for a community based school which teachings on saturdays, there are over 15 volunters who do not get paid to teach but are still willing to give something back to the society.
i beg you guys out there, you all are geniuses!!! please help us design this simple system. please if somebody already knows where i can get a system like this free of charge then let me know ASAP!
I would like to thankyou in advance for reading thins messge.
I'm new to this forum so if I ask something stupid please forgive :)
I want to do something like the contact management database which is offered by microsoft for download.
I played a little with it and my problem is I copy-pasted the "calls" tab and on the pasted tab when I browse throug the different calls the call notes field below wont change / update. I thought there is maybe a macro behind it but I looked at every macro and did not find anything appropriate in my opinion.
It is maybe only a minor but I can not figure it out,
Thanks for any help in advance,
PS.: I uploaded the database to rapidshare : http://rapidshare.com/files/1305209/Contact_management_database.mdb
Hi all, I need your help. I am in process of designing a Contract Management database. I have three tables The first one is Contracts Details table, with these columns: Contract ID, Contract Name, Vendor ID, Vendor Name, Start Date, End Date, Type, and Description. The second table is Vendors table, with these columns: Vendor ID, and Vendor Name. The third table is VendorContact table, with these columns: Vendor ID, Contact Person, Phone, Mobile, Fax, Email, and id. I want you to help me to construct the relations? Kindly, see the attached database.
I am a newbie, I am create Incident Management Database for our office. I have sorted relationships & a form in the database but i need more help with a form.
The main table in the database is INCIDENT and there are lots of lookups and 3 tables with one to many relationships. Now in my database, i must save the Incident entry first and only then i can add info in witness table. Is there any way that the data about the incident gets saved as soon as users try to enter witness info regarding that incident?
Please see attached two images to get better idea.
Any help is appreciated. See the link below (copy n paste in web browser) docs.google.com/Doc?id=df4ks7hh_21gxccsng4
I am trying to design and build an orders management database for a clothing wholesaler.
Has anyone got any ideas on what entities i should have.
Clients (Customer Details) Orders (Inc. order date, ship via etc) Order Details (sizes, quantity of orders per size) Product Details (Product Code, colour, size, stock numbers per size) Catergories (Shorts, Jackets, Jeans etc) Suppliers (Supplier Information) Delivery (Delivery Method etc)
However, loads of problems when i come to planning the sizes part. I have different product catergories with different sizes
And when i do the order details table, i need to enter order quantities for each different size for one clothing unit.
I would also ideally want to link the order details and products tables by the product code number. I.e. When filling out an order In the order details table i want to type in the product code then have information from the product table automatcially fill in the gaps in the order details table, such as unit price, colour etc.
I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).
The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.
I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.
I need to write a db for my boss where he can access his patient treatment information. How can I make the db (Access '03) to be able to add new treatment to the same patient and to display all treatments seperately on a form? There are about 500 patients. Appreciate any assistance.
I have constructed a seven table database for dealing with holiday cottages.
The seven tables are:
Bookings Cottages Customers Ratings Owners Regions Sales Reps
I have created the 7 tables and created the primary keys (BookingID,CottageID,CustomerID,RatingsID,OwnersID ,RegionsID,RepID)
The ratings table because cottages come under different ratings (Family, Sporting etc..).
I need to create a form which
*Adds a new booking, and also adds the new customers details *Checks the avalibility of the cottage for the chosen week *allows the user to choose a rating from a combo box, which then filters the cottages availible for selection in that category.
If anyone could help me with this I would be very grateful, I have emailed my lecturer but he is not replying and the assignment needs to be in on the 12th!
While searching for Access help through Google, I found your forum which has greatly helped me in building my database in Access. I got all answers through your forum for many of my doubts and ‘How To’s. I thank all the members for their excellent and simple way of explaining for a novice like me.
As to the Database I built, it is almost complete and working fine. I have built a switchboard with menu choices. I have distributed this to all my colleagues and they are using fine.
I have three problems still to be addressed. I have tried to protect my database tables, forms etc through the security wizard, user permissions etc. But nothing is working. I have hidden all tables, forms etc. and unclicked the ‘Display database window’ in Startup so that only the switchboard is visible. I have also changed the switchboard properties to cover the entire screen and no maximize/minimize/close button. When a user double-clicks the desktop icon the database file opens with the switchboard menu. There is an Exit button in the switchboard which the user can click to he can click the close button of the Access window. Apart from this, the user can do nothing to corrupt the tables, forms etc., so I believed. However, one colleague clicked the ‘View – Design View’ in the Access menu bar and the Switchboard became ready for modification. Is there a method to block the View – Design View’ option in the Access menu bar?
My second problem is that we want a new database file first of January every year. Is there a method to create a new DB file with the same table, form and settings from within Switchboard? (The only option I found was to copy the DB file to another location, delete all records and rename it for the new year). Is there any other procedure?
My third problem is that I designed a Crosstab Query as given in the Help menu with criteria LIKE “*” & [Enter any character to search by: ] & “*” but when I execute the query it displays all records containing the character entered. This is not what I want. My search criteria everytime changes (it is text based). So I am unable to save Query form with a specific criteria as it will execute only defined parameters. Is there a way to make Query form where any user input (i.e. any word or number) is taken for search?
Hi, All I am designing a database to keep track of part prices and their trends, the part numbers and all their related information are stored in a "Unified Parts Database" containing all pertinent information about the parts, their vendor, current price, material, sub-material etc...Now in the new database I am making I wanted to have one table that has these field.
The tables name currently is "Main", fields are as follows:
and then I have a "Prices" table which stores the different price changes for each part number:
PriceID (PKey) PartNumber Price PriceChanged
now I what I am trying to do is have the "Main" table pull all the part numbers and their descriptions from the Unified Parts Table which is a linked table and I am not sure how to get it to fill all this stuff automatically inside a table not a form, would a "Main" Query be the correct way to go and completely skip the "Main" table??
Hey guys , I was wondering if you can help me with this one cos I am clueless. I am in charge of updating this website which sells stationeries, office equipments etc. Well the catalogue in our website is kinda messy since there are lot of the product items inside the catalogue page which has no images.It is embarassing for those customers who wants to buy a product but couldnt see the picture of the items. For example , http://www.mellon.com.sg/catalogue/s_searchresult.asp?searchby=category2&searchfield=laminating%20materials.
The product items are generated from the Access database where I also included a column field in the database called "Picture" where those product items with images have checked(tick) in the box and those without images are unchecked(no tick) in the box I am not sure if I should filter anything or set up any criteria for the pictures from the database. I need help as i am not a total expert in Access
I'm an audio/video technician at a college and wish to use Access for a simple camera booking system, but do not have time to go through all the extensive help menus and tutorials about this program, and none I have viewed seemed to help.
I want four columns in the table. First, the user inputs a student ID number. Now my first problem is that I want the student name to appear in the 2nd column after entering their number. I don't know where to create the list of names or how to link them to their student ID numbers.
My second problem is I would like the 4th column simply to display the date seven days from the current one (as in whenever the db is used) for the return date without having to input it manually.
Any help towards these 2 problems would be most appreciated, and thanks in advance.
I want to make an access database but Im struggling with the relations a bit. I understand the one-to-many and many-to-many concept but seems like there is more then that
I want to make a database that keeps track of things we lend.
I have several items with different properties we lend out:
TANK -ID -Volume -Weight
MACHINE -ID -hours -fueltype
I made the following tables to keep track of it:
RENT -ID -Customer -...
One-to- many relationship between these
RENTLINE -ID -RENT_ID -Item
My problem lies with this Item. I want this Item to have a relation with the ID of the different items we lend out. there needs to be a one-to-many relation here. one rentline can exist of one TANK OR of one MACHINE. A TANK or a MACHINE can exist on many orderlines. So I made 2 one to many relationships from Rentline to the id's of Tank and Machine
the problem is that Access does not let me fill in the rentline with the id of a item. If I fill in the ID of a TANK it says a ID of a machine is needed and vice-versa.
I tried to solve it with an intermediate ITEM table that looks like this : ITEM -ID -Sort ( Tank or Machine) -ItemID (Id of the tank OR the Machine)
But this generates the same issue as directly in the rentline...
I guess this is pretty common stuff Im asking here but its confusing me like hell :(