Solved

Make access query to show full table results

Posted on 2014-02-14
4
561 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
[X]
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
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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