Solved

Make access query to show full table results

Posted on 2014-02-14
4
548 Views
Last Modified: 2014-03-28
Hi,
    I have two tables, one is a list of hospitals, and the other table is a main table which has entries, which are produced from a simple form.  So user can enter details on form and they appear in table - main table.

What i need to do is generate a query which shows ALL hospitals from the hospital table, but then also shows any entries from main table, against any of those hospitals.  There will only be one entry per hospital.

Can anyone help

Cheers
0
Comment
Question by:damianb123
  • 2
4 Comments
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 334 total points
Comment Utility
In your Hospitals table, you should have an Autonumber field with a field name of Hospital_ID.  This should be the primary key of that table (although some database purists would argue against making this the primary key).

Then, in your "main table", you would have this field too.  On the form, I would have combo box bound to this field, with a RowSource that is a query that looks something like:

SELECT Hospital_ID, Hospital_Name FROM tbl_Hospitals ORDER BY Hospital_Name

If you do that, then you can create a query that lists all of the hospitals and the corresponding record from the "main table" like:

SELECT tbl_Hospitals.Hospital_ID, tbl_Hospitals.Hospital_Name, tbl_Main.*
FROM tbl_Hospitals
LEFT JOIN tbl_Main
ON tbl_Hospitals.Hospital_ID = tbl_Main.Hospital_ID

This would list all of the hospitals, and the corresponding records in tbl_Main, but would contain blanks for the tbl_Main data if there was no data in that table related to a particular hospital.

To get just the hospitals that have data in tbl_Main, you would either change the type of join:

SELECT tbl_Hospitals.Hospital_ID, tbl_Hospitals.Hospital_Name, tbl_Main.*
FROM tbl_Hospitals
INNER JOIN tbl_Main
ON tbl_Hospitals.Hospital_ID = tbl_Main.Hospital_ID

or add a WHERE clause:

SELECT tbl_Hospitals.Hospital_ID, tbl_Hospitals.Hospital_Name, tbl_Main.*
FROM tbl_Hospitals
LEFT JOIN tbl_Main
ON tbl_Hospitals.Hospital_ID = tbl_Main.Hospital_ID
WHERE tbl_Main.PKFieldName IS NOT NULL

in this example, you could replace "PKFieldName" with any field in tbl_Main, but the best one to use is a field that is required.
0
 

Author Comment

by:damianb123
Comment Utility
Hi,
    I tried the above, but it was pulling out the values by the ID on both tables, which won't work as they're not commonly linked, however what is linked are the Hospital names, so I wrote this:

SELECT [Hospital's].FIELD1, [Main Table].*
FROM [hospital's] LEFT JOIN [Main Table] ON [hospital's].FIELD1 = [Main Table].hospital's
ORDER BY [hospital's].FIELD1;

So it should (I think) select field1 - hospital name from hospital's table, and everything from main table....

Then we join the hospital's table and main table where hospital's table.field1 (hospital name) = main table hospital name....

and order by the hospital's - but it doesn't work....  I get:

Syntax Error (missing operator) in query expression

Any ideas?
0
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 166 total points
Comment Utility
fyed's query assumes that the tables have a defined relationship using a primary key common to both tables and that is the 'correct' way to setup the date. If you are joining on hospital name and the users are not 100% accurate in the way they type them (Nuffield, nuffield, nufield for example) then over time your query will be less and less useful.

However this is the modification of his query to do a join on hospital name:-

SELECT tbl_Hospitals.Hospital_Name, tbl_Main.*
FROM tbl_Hospitals
LEFT JOIN tbl_Main
ON tbl_Hospitals.Hospital_Name = tbl_Main.Hospital_name
ORDER by tbl_Hospital_name

The 'Left join' indicates that you want all the rows from the Left side of the 'on' condition - ie: all hospitals,  the On clause is then used to return all the Main.Hospital_name that match the Hospital name. If the data entry has resulted in a hospital name in Main that doesn't appear in hospital then  that row won't appear (because it has no corresponding Hospital.name). a Right join would return all the Main rows and just the hospitals which match and a FULL JOIN would return all rows from both tables

You don't need a where clause unless you want to filter the JOINED results - eg: Where Hospital.name = "Nuffield"

If you want a more complete answer it might be sensible to add some background -
- what are you trying to achieve with this - just a report, use it for input or form validation?
- is this an existing application that you are modifying or starting from scratch?
-what is the Schema for this part of the application and how much control do you have over it
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 334 total points
Comment Utility
If you could provide some sample data in a database (remove anything that might look like patient information), it would be easier for us make meaningful responses.

Another think you need to do is get rid of all of the spaces and special characters in your table and field names.  If you only have a couple of these, and you are not too far into your development, you can do that manually.  But if you have a bunch of tables, queries, and forms, it will be easier and save you time and money to purchase Rick Fishers Find & Replace utility.  It is cheap, extremely easy to use, and will save you hours, maybe days of searching when trying to change the names of fields, tables, and reports.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 40
MS Access 2010 Form Building 3 22
putting an icon in a form 13 20
Test a query 23 11
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Viewers will learn how the fundamental information of how to create a table.
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…

743 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

15 Experts available now in Live!

Get 1:1 Help Now