Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Make access query to show full table results

Posted on 2014-02-14
4
559 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
ID: 39858803
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
ID: 39862421
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
ID: 39862440
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
ID: 39862547
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

840 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