I created this query in Access with the Design View.
SELECT Fiscal_Period.F iscalKey, Invoice.type, Invoice.accid,
Sum(Invoice.Tot al) AS Amount
FROM Fiscal_Period LEFT JOIN Invoice ON Fiscal_Period.F iscalKey =
Invoice.FiscalK ey
WHERE (((Invoice.acci d)=29))
GROUP BY Fiscal_Period.F iscalKey, Invoice.type, Invoice.accid
HAVING (((Invoice.type )=0))
ORDER BY Invoice.accid, Fiscal_Period.F iscalKey, Invoice.type;
I have 37 fiscal periods in the table from 200301 until 200601. I am
expecting to get 37 records, one for each record, but I am only
getting records for the ones in the Invoice table.
How do I get a record for the months in which there are no records in
the Invoice table.
In the query builder, I clicked on the join option thay says "Include
ALL records from Fiscal_Period and only those records from Invoice
where the joined fields are equal.
Thnks. 2 1288
On Mon, 19 Dec 2005 22:36:04 GMT, New Guy <ne****@yahoo.c om> wrote: I created this query in Access with the Design View.
SELECT Fiscal_Period.F iscalKey, Invoice.type, Invoice.accid, Sum(Invoice.To tal) AS Amount FROM Fiscal_Period LEFT JOIN Invoice ON Fiscal_Period.F iscalKey = Invoice.Fiscal Key WHERE (((Invoice.acci d)=29)) GROUP BY Fiscal_Period.F iscalKey, Invoice.type, Invoice.accid HAVING (((Invoice.type )=0)) ORDER BY Invoice.accid, Fiscal_Period.F iscalKey, Invoice.type;
I have 37 fiscal periods in the table from 200301 until 200601. I am expecting to get 37 records, one for each record, but I am only getting records for the ones in the Invoice table.
How do I get a record for the months in which there are no records in the Invoice table.
In the query builder, I clicked on the join option thay says "Include ALL records from Fiscal_Period and only those records from Invoice where the joined fields are equal.
I *think* that your Having clause is restricting the resultset to
those rows where Invoice.Type =0. With that said, if you have a row
that is built from a Fiscal_Period that has no matching Invoice, that
row will first be included (in the Left Join) and then, since it
doesn't "have" an Invoice, no longer be included.
I think the same thing goes for the Where clause.
So I think you will need to "OR" both of those with something that
will keep your Fiscal_Period rows where there are no Invoices.
Something like:
WHERE (((Invoice.acci d)=29) OR ISNULL(Invoice. accid))
and
HAVING (((Invoice.type )=0) OR ISNULL(Invoice. accid))
I'm not sure the ISNULL will work in both clauses (I know it will work
in the WHERE clause).
Try it and see what happens.
mike
On Mon, 19 Dec 2005 23:34:25 GMT, mb******@pacbel l.net.invalid (Mike
Preston) wrote: On Mon, 19 Dec 2005 22:36:04 GMT, New Guy <ne****@yahoo.c om> wrote:
I *think* that your Having clause is restricting the resultset to those rows where Invoice.Type =0. With that said, if you have a row that is built from a Fiscal_Period that has no matching Invoice, that row will first be included (in the Left Join) and then, since it doesn't "have" an Invoice, no longer be included.
I think the same thing goes for the Where clause.
Yes, that did it.
Thanks a lot. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Adam i Agnieszka Gasiorowski FNORD |
last post by:
I need help width formulating the most
effective (in terms of processing time)
SQL query to count all the "new"
documents in the repository, where "new" is
defined as "from 00:00:01 up to 23:59:59
today". My current query does not give me
satisfactory results, it creates a visible
delay in rendering of the main page of one of
the...
|
by: Geremy |
last post by:
Hi
Consider two tables
id1 code1
----------- -----
1 a
2 b
3 c
id2 code2 value
|
by: jbm05 |
last post by:
Hi,
I'm curious about the computational complexity of a query I have. The
query contains multiple nested self left joins, starting with a simple
select, then doing a self left join with the results, then doing a self
left join with those results, etc. What puzzles me is that the time
required for the query seems to grow exponentially as I...
|
by: bob |
last post by:
Could someone help me get the following SQL statement working?
SELECT
standardgame.gamename,
standardgame.rowteamname,
standardgame.colteamname,
standardgame.dollarvalue,
standardgame.gameid,
standardgame.cutoffdatetime,
standardgame.gametype,
|
by: Giordano |
last post by:
I am trying to construct a report menu that will allow the user to
select any combination of 3 variables from 3 combo boxes (Select
Subject/SelectCategory/Date Range). There are 2 possible options for
each combo ie <Allor a specific value . That means there are 8
possible outcomes from the user selection process.
The SQL for the report is...
| |
by: chadlupkes |
last post by:
I'm getting NULLs where there shouldn't be. Any help is appreciated.
Here are the tables:
precinct
Field Type Null Key Default Extra
id smallint(6) PRI NULL auto_increment
precinct_number int(11) 0
precinct_name varchar(20) MUL
|
by: cberthu |
last post by:
Hi all,
I have a strange (at least for me) things happening. I am save the
result of an outer join into a file and I read this file and put these
values into a table.
The first row written contains the code page... how comes did I do
something wrong or is it a normal behavior?
I have to say that I did not manage to insert the value...
|
by: execworks |
last post by:
I would appreciate help with the following Function.
The SQL statement below returns a Recordcount = 0, yet,
when it runs in the query designer it returns the correct count of 40
Private Function ApplyFilter()
Dim intCount As Single
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim strRange As String
|
by: matheussousuke |
last post by:
Hello, I'm using tiny MCE plugin on my oscommerce and it is inserting my website URL when I use insert image function in the emails.
The goal is: Make it send the email with the URL http://mghospedagem.com/images/controlpanel.jpg
instead of
http://mghospedagem.comhttp://mghospedagem.com/images/controlpanel.jpg
As u see, there's the...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
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...
|
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...
|
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...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
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
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |