473,851 Members | 2,200 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do you show records with no matching values?

147 New Member
I am not sure if I worded my question correctly. But what I want to do is run a report from two related tables as follows:

The first table is called Category - it has one field, called Category, that is also the primary key. There are 18 categries listed in the table.

The second table is called CaseManagement, with no primary key, and multiple fields including Category which is taken from the related Category table.

They are related with a one to many link from Category to CaseManagement.

In my report I want to list all the categories from the Category table followed by the number of cases in each category from the CaseManagement table.

My problem is that if a categoy does not have any related data in the CaseManagement table, the category is not listed in my report. I want the report to include every category from the Cattegory table and show a 0 for it if it does not have any related data in the CaseManagement table.

Thanks for any help
Dan
Nov 22 '07 #1
16 1954
wvmitchell
42 New Member
SELECT Category.Catego ry, Count(CaseManag ement.Category) AS CountOfCategory
FROM Category LEFT JOIN CaseManagement ON Category.Catego ry = CaseManagement. Category
GROUP BY Category.Catego ry;
Nov 22 '07 #2
DAHMB
147 New Member
SELECT Category.Catego ry, Count(CaseManag ement.Category) AS CountOfCategory
FROM Category LEFT JOIN CaseManagement ON Category.Catego ry = CaseManagement. Category
GROUP BY Category.Catego ry;

Thankyou for your response but where do I place this code? Please excuse my ignorance.

Thank you for your help.
Dan
Nov 22 '07 #3
wvmitchell
42 New Member
From the Database Window, select the Queries tab.

Click New, and at the New Query dialog box select Design View and then click OK.

At the Show Table dialog box, click Close.

From the Access menu bar, click View ~ SQL View and paste the code into that window.

From the Access menu bar, click View ~ Design View and you'll see the query in the graphical design window. Save the query.

When you run the query you'll see the results.
Nov 22 '07 #4
DAHMB
147 New Member
From the Database Window, select the Queries tab.

Click New, and at the New Query dialog box select Design View and then click OK.

At the Show Table dialog box, click Close.

From the Access menu bar, click View ~ SQL View and paste the code into that window.

From the Access menu bar, click View ~ Design View and you'll see the query in the graphical design window. Save the query.

When you run the query you'll see the results.

Thank you very much for you reply but I am geting a Syntexc error in Join Operation. I have typed the code as follows the table names are different because I was trying to be as clear as possible on my original post but these are the actual names:

SELECT tblOffenses.Off enses, Count(tblCaseMa nagement.Offens e) AS CountOfOffense FROM Offense LEFT JOIN tblCaseManageme nt ON tblOffenses.Off enses = tblCaseManageme nt.Offense GROUP BY tblOffenses.Off enses;

Thank you again for your time and help.
Dan
Nov 23 '07 #5
wvmitchell
42 New Member
You have a typo - you wrote

SELECT tblOffenses.Off enses, Count(tblCaseMa nagement.Offens e) AS CountOfOffense FROM Offense LEFT JOIN tblCaseManageme nt ON tblOffenses.Off enses = tblCaseManageme nt.Offense GROUP BY tblOffenses.Off enses;

but it should be

SELECT tblOffenses.Off enses, Count(tblCaseMa nagement.Offens e) AS CountOfOffense FROM tblOffenses LEFT JOIN tblCaseManageme nt ON tblOffenses.Off enses = tblCaseManageme nt.Offense GROUP BY tblOffenses.Off enses;
Nov 23 '07 #6
DAHMB
147 New Member
You have a typo - you wrote

SELECT tblOffenses.Off enses, Count(tblCaseMa nagement.Offens e) AS CountOfOffense FROM Offense LEFT JOIN tblCaseManageme nt ON tblOffenses.Off enses = tblCaseManageme nt.Offense GROUP BY tblOffenses.Off enses;

but it should be

SELECT tblOffenses.Off enses, Count(tblCaseMa nagement.Offens e) AS CountOfOffense FROM tblOffenses LEFT JOIN tblCaseManageme nt ON tblOffenses.Off enses = tblCaseManageme nt.Offense GROUP BY tblOffenses.Off enses;

Thank you so very much!!! I have been racking my brains for a week on this! I reealy appreciate it. I will try it tommorrow when I go to work and let you know how I made out. Thank you for your help please check back for me in case I have any more issues.
Dan
Nov 23 '07 #7
DAHMB
147 New Member
OK Great now I understand how to do this. But now I have a form where I list twwo dates to limit a search between. Now if I add the search line it gives me a resuly with just the fields with data again. I need the fields from tblOffense to show with 0 in the limited search as well. my sql with the date search looks like this:

SELECT tblOffenses.Off enses, Count(tblCaseMa nagement.Offens e) AS CountOfOffense
FROM tblOffenses LEFT JOIN tblCaseManageme nt ON tblOffenses.Off enses = tblCaseManageme nt.Offense
GROUP BY tblOffenses.Off enses
Having (((Count(tblCas eManagement.Off ense)) Between [Forms]![frmIntakeSearch]![Date1] And [Forms]![frmIntakeSearch]![Date2]));


What am I doing wrong?
Thanks again
Dan
Nov 25 '07 #8
DAHMB
147 New Member
Sorry I posted the wrong sql it looks like this:

SELECT tblOffenses.Off enses, Count(tblCaseMa nagement.Offens e) AS CountOfOffense
FROM tblOffenses LEFT JOIN tblCaseManageme nt ON tblOffenses.Off enses = tblCaseManageme nt.Offense
WHERE (((tblCaseManag ement.DateOpene d) Between [Forms]![frmIntakeSearch]![Date1] And [Forms]![frmIntakeSearch]![Date2]))
GROUP BY tblOffenses.Off enses;
Nov 25 '07 #9
wvmitchell
42 New Member
Dan,

I don't fully understand the logical relationship between your two tables, but when you apply criteria to the tblCaseManageme nt table, the effect is that you're making it act like an INNER JOIN, because you are requiring a matching record in tblCaseManageme nt.

In any event, can you try this & see if it gives you the desired results:

SELECT tblOffenses.Off enses, Count(tblCaseMa nagement.Offens e) AS CountOfOffense
FROM tblOffenses LEFT JOIN tblCaseManageme nt ON tblOffenses.Off enses = tblCaseManageme nt.Offense
WHERE (((tblCaseManag ement.DateOpene d) Between [Forms]![frmIntakeSearch]![Date1] And [Forms]![frmIntakeSearch]![Date2]))
OR (tblCaseManagem ent.DateOpened) IS NULL
GROUP BY tblOffenses.Off enses;
Nov 26 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
2431
by: rowan[canspam] | last post by:
I have two tables ITEMS: items,description,mfg MANUFACTURER: mfg,full_name The items database has 230,000 records, the manufacturer, 493 records. I want to see how many records in the items table don't have a matching record in the
7
2987
by: Philip Mette | last post by:
I have been searching many postings and I cant seem to find anyone that has this answer so I decided to post. I am using SQL (Transact-SQL).If I have 2 tables with columns acct_num,activity_date,and pay_amt and I want to delete one instance of a record in table 1 for every instance of that record in table 2 how could I do that. For example. Table 1 ----------- acct activity_date pay_amt
4
2647
by: news.online.no | last post by:
In a query, I need too be able to show if a parent record has a child record. Using the query in a combo box too select record in a form. Thanks :)
1
2998
by: snOOp | last post by:
I am trying to combine the data from two similar tables into one query, but I need for all of the records from both tables to show up and I want the ones that have matching 'emplid' to be combined into one record showing both the 'empstatus' and 'strole' fields. The following query works, but does not combine the matching records: SELECT givenname, sn, empstatus, emplid, ssn FROM dbo_EmpData WHERE empstatus='A' UNION ALL SELECT...
2
15442
by: Lauren Quantrell | last post by:
I am using a stored procedure as the recordsource on an MS-Access2000 form: Forms!frmName.RecordSource = "dbo.myStoredProcedure" The stored procedure creates a temp table #Contacts and then inserts matching KeyID values contacts into it. Then I use a union query in the same stored procedure to create the actual recordset. What I want to know is how I can return the number of records in the temp table #Contacts back to MS Access and...
6
8170
by: Sven Pran | last post by:
Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join" in another table but I have not found what the field criteria should look like? 2: And if/when I succeed I should further like to build a new record (with
2
8089
by: Zeljko | last post by:
I'm creating Address book. Header of the main form (frmAddress) contains combo box (cboFilter) to filter records by Occupation on main Form(Ocupation1). That's working. Combo box also have "Show All" in one row. ==Combo box query: SELECT tblOCUPATION.id, tblOCUPATION.txtOcupation1 FROM tblOCUPATION UNION Select "(All)" as Bogus, Null as AllChoice From ; ==Combo box event: If IsNull(Me!) Then
4
1823
by: J | last post by:
I am editing a pre-existing view. This view is already bringing data from 40+ tables so I am to modify it without screwing with anything else that is already in there. I need to (left) join it with a new table that lists deposits and the dates they are due. What I need is to print, for each record in the view, the due date for the next deposit due and the total of all payments that they will have made by the next due date.
1
2201
by: Kev | last post by:
Hello I have a form (RosterForm) based on a table - RosterRange RosterRange has 4 fields: RosterRangeID Autonumber RosterStartDate Date RosterEndDate Date (probably unnecessary) Ward Text I have 2 unbound combo boxes looking up values - cmbDepartment and
0
11020
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10356
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9506
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7907
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7073
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5934
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4549
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4143
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.