473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

SQL Query Problem

Hi:

I have a joined query from two tables. I want ro preserve ALL the data from the RIGHT table while summing the data from a field in the LEFT table having a specific criteria from a third field. Here's the code:

Expand|Select|Wrap|Line Numbers
  1. SELECT ActionType.[Action Type], Sum([6 Export OwnOcc Loan Activity].[Action Type]) AS [SumOfAction Type]
  2. FROM [6 Export OwnOcc Loan Activity] 
  3. RIGHT JOIN ActionType 
  4. ON [6 Export OwnOcc Loan Activity].[Action Type] = ActionType.[Action Type]
  5. GROUP BY ActionType.[Action Type], [6 Export OwnOcc Loan Activity].Occupancy
  6. HAVING ((([6 Export OwnOcc Loan Activity].Occupancy)="1"));

The query runs fine (showing all data from the "ActionType" table) when I remove the "HAVING ((([6 Export OwnOcc Loan Activity].Occupancy)="1"))" criteia. It does not show all the data from the "ActionType" table with that criteria in place.

Can someone tell me how to fix it to show ALL data from the "ActionType" table?
May 28 '12 #1
3 1166
Rabbit
12,516 Expert Mod 8TB
Please use code tags when posting code.

That's because there's no record in the left table. So the occupancy field isn't going to have any data. So when you say it has to have "1" in there, it's not going to return since it's not "1". You'll want to return the "1"s or where it's null.
May 29 '12 #2
Rabbit:

Thanks. I think that makes sense. The key issue here is that we need the occupancy to only be the "1" values. I was thinking about that after I posted and thought it might work to do a pre-query filtering out the "1s" then do the joined query without the criteria.

I bet that would work...
May 29 '12 #3
Rabbit
12,516 Expert Mod 8TB
Returning ones and nulls is no different than what you're trying to accomplish with a second query.
May 29 '12 #4

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

Similar topics

7
by: YoBro | last post by:
Hi, I am stuck and cannot think my way out of this small problem. I am coding in PHP but trying to write a MySql query. I have db that stores vehicles. I am trying to SELECT vehicles where...
8
by: Dave | last post by:
Hi all, I've been trying to figure this out for the last day and a half and it has me stumped. I've got a web application that I wrote that keeps track of trading cards I own, and I'm moving it...
6
by: John Baker | last post by:
Hi: As those who have looked at this newsgroup recently will realize, I am a neophyte with Access, although I have experienced with Approach (the Lotus product). There are things I could easily...
1
by: Mark Reed | last post by:
Hi All, I have a table with a date field and 6 number fields. The number fields are not in numerical order across the fields and I want number 1 to be the lowest number and number 6 to be the...
4
by: Apple | last post by:
1. I want to create an autonumber, my requirement is : 2005/0001 (Year/autonumber), which year & autonumber no. both can auto run. 2. I had create a query by making relation to a table & query,...
2
by: Bernd Hohmann | last post by:
Dear collegues, small query problem. A table (simplified example)... customer as char(5) inv_date as date amount as double
3
by: luis.c.torres | last post by:
Hello. I have and unbound form with a subform. The form has 4 controls (a combo, two txt boxes and a button). The combo has the filter criteria (Equal to, newer than, older than and between)...
1
by: hardik | last post by:
hi friends i need help in this sql query i have table like, id fid __ _____ autonumber text and i am storing values like
29
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where...
4
trueadvantage
by: trueadvantage | last post by:
Hi... I have 1 little but critical (for me) problem regarding access query. Problem Details: - I have one table which includes Employee Name and Employee ID Table:- EmpName EmpID A ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.