Solved

Creating a two table Query that is linked and show all data from one table and linked data from second table.

Posted on 2008-06-24
4
210 Views
Last Modified: 2013-11-27
I have created a database for tracking medical visits to a first aid center on a project site.  The database has a few tables that are linked to make the database more user friendly because it pulls data from different locations to autofill fields when someone comes to the first aid center for a consultation.  The problem I have is this:

I have a Query that has two tables.  Table one is "Patient Information" and Table two is "Daily Consultation."  When I created the query I linked two fields "Project ID Number" from the patient information table and "Compnay Project ID Number" from the Daily Consultation table.  I used the Join Properties to link the two fields.  I also selected the third radial button so that (in theory) all records from the Patient Information table would appear and any Daily Consultation data would appear that was linked to a patient (all data is linked, so all data from both tables should appear).  The problem is that only the data from the Daily Consultation appears when the query is opened.

The reason I need the data from both tables is that if it is a new patient (never had a consultation), then when you select the patient I can auto fill the daily consultation with information from his or her information located in the Patient Information table.  If it is a patient that has a history of consultations, I pull the last record and copy selected data from that consultation and autofill the fields in a new consultation.

Any suggestion on how to get get the data from both tables to appear would be appreciated.
0
Comment
Question by:csall
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 21855630
the query should be Patient LEFT JOIN to daily consultation as the patient must exist before you can have a consultation with that patient. ie the Patient is primary table, the consultation the secondary
so eg (replace any field names accordingly)
select p.*, d.*
from  [tblPatient]  p LEFT OUTER JOIN to [tblDailyConsultation] d
on p.[Project ID Number] = d.[Company Project ID Number]
order by p.[Patient ID]

this will return all patients regardless if they have a consultation or not
0
 

Author Comment

by:csall
ID: 21862673
Attached is a screen print of the solution I entered that seemed to work.  Please let me know if this is what you were telling me to do?  I also created a realationship that is the same setup for the database as a whole. (used the relationship button on the main screen)

If you believe it could cause problems in the future, please let me know also.  I am trying to make the database as stable as possible so if I leave the project it will run without problems.

One thing that is important is I cannot directly link the daily consultation to the patient information.  I need to save daily consultations as its own record because information can change like patient's vital signs and weight along with other information like supervisor.  I am trying to keep a historical record, so once data is put it I do not want it changed unless done specifically by going into the daily consultation record and making the change.

Query-View.doc
0
 
LVL 19

Accepted Solution

by:
frankytee earned 250 total points
ID: 21872366
I dont know your db design but it appears there is fundamental flaw in your physical db design.
Why is there a Project ID and Company ID in your patients table but a CompanyProjectID in the related DailyConsultation table?
You cannot create a consultation for a patient that does not exist.

Is this db to cater for multiple projects or just one project site?
I'll assume that former.

In that case I would design the db as:
tblCompany (required if a company has multiple projects and you need to handle multiple companies)
      CompanyID      PK
      CompanyName
      Address
      etc

tblProjects
                     ProjectID PK
      ProjectName
      CompanyID      FK (if applicable)
etc

tblPatient      
                     PatientID PK
      FirstName
      Surname
      InitialWeight      
      Etc

tblDailyConsultation      
                     ConsultationID      PK
      PatientID            FK
                     ProjectID            FK (assuming that you have patients working on
            multiple projects)
      DateVisit
      Symptoms
      Outcome
      Weight
      Supervisor
      etc
            
your daily consultation is your historical table. With each new visit by the patient you add a new record, do not overwrite the previous record otherwise you'll lose your historical data.

if the above is correct then you'll need to change your query accordingly
0
 

Author Comment

by:csall
ID: 21872981
There is actually two ID numbers.  The project just started issuing ID cards that have a uniquie ID number on the (project ID number), but we have multiple companys working on the project, so employees will have a company specific ID.  The problem we had is that many people did not know their company ID, so when the project started issuing ID cards, we decided to use that number to track employees.

You are correct that a consultation record cannot be created until basic employee information is entered into the database.  The catch is that when a consultation is made, we need to capture all the employee data into the consultation record.  That way if the employee changes companies, but remains on the project, we can look back at their record and know who they were working for at the time of the consultation.  (It is a very complicated database, hence the reason it was to work without flaw and have lots of checks and balances so if I leave the project it will run with little or no mantenance.)

If you have not guessed, I am not a programmer (but the experience I have gained over that last 6 months may say otherwise).  I run the medical program for the project, but a medical record database was not budgeted for in the project scope.  It was needed, so I created it.   Your help is much appreciated.  
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now