Hi all,
I have to create a query using MS Query, and what I need is beyond my
knowledge.
I am querying from a list of invoices, and each invoice can have several
delivery dates (possibly a different date for each line item on the
invoice).
I need to retrieve the largest delivery date for each invoice that does not
have a NULL value for a delivery date. Meaning, I only want invoices that
have all line items delivered. Here is an example
Invoice | Delivery date
553 | jan 3
553 | jan 7
553 | NULL
554 | jan 15
555 | jan 8
555 | jan 7
So, here are the query results I am looking for
Invoice | Delivery date
554 | jan 15
555 | jan 8
Invoice 553 would not be returned because one line item has a NULL delivery
date (meaning not all items have been delivered). How can I do this in MS
Query?
Thanks in advance,
Sincerely,
Steve
4  6980 
Steve Oster wrote: Hi all,
I have to create a query using MS Query, and what I need is beyond my
knowledge.
I am querying from a list of invoices, and each invoice can have several
delivery dates (possibly a different date for each line item on the
invoice).
I need to retrieve the largest delivery date for each invoice that does not
have a NULL value for a delivery date. Meaning, I only want invoices that
have all line items delivered. Here is an example
Invoice | Delivery date
553 | jan 3
553 | jan 7
553 | NULL
554 | jan 15
555 | jan 8
555 | jan 7
So, here are the query results I am looking for
Invoice | Delivery date
554 | jan 15
555 | jan 8
Invoice 553 would not be returned because one line item has a NULL delivery
date (meaning not all items have been delivered). How can I do this in MS
Query?
Thanks in advance,
Sincerely,
Steve
You could create another query that selects all records with invoice
dates equal to null. Then in your current query add this new query and
link to the invoice number, dbl-click on the relationship line and set
it to All in current and only matching in New query, and drag the
InvoiceID from the new query with the criteria IS Null.
Try this:
SELECT YourTable.invoi ce, Max(YourTable.[delivery date]) AS [Delivery Date]
FROM YourTable
GROUP BY YourTable.invoi ce
HAVING (((YourTable.in voice) Not In (SELECT YourTable.invoi ce FROM YourTable
WHERE (((YourTable.[delivery date]) Is Null));)));
Mark
"Steve Oster" <os************ *******@amerite ch.net> wrote in message
news:oU******** *********@newss vr19.news.prodi gy.com... Hi all,
I have to create a query using MS Query, and what I need is beyond my
knowledge.
I am querying from a list of invoices, and each invoice can have several
delivery dates (possibly a different date for each line item on the
invoice).
I need to retrieve the largest delivery date for each invoice that does
not
have a NULL value for a delivery date. Meaning, I only want invoices that
have all line items delivered. Here is an example
Invoice | Delivery date
553 | jan 3
553 | jan 7
553 | NULL
554 | jan 15
555 | jan 8
555 | jan 7
So, here are the query results I am looking for
Invoice | Delivery date
554 | jan 15
555 | jan 8
Invoice 553 would not be returned because one line item has a NULL
delivery
date (meaning not all items have been delivered). How can I do this in MS
Query?
Thanks in advance,
Sincerely,
Steve
Mark wrote: Try this:
SELECT YourTable.invoi ce, Max(YourTable.[delivery date]) AS [Delivery Date]
FROM YourTable
GROUP BY YourTable.invoi ce
HAVING (((YourTable.in voice) Not In (SELECT YourTable.invoi ce FROM YourTable
WHERE (((YourTable.[delivery date]) Is Null));)));
Mark
That would work. However, sub-selects in Access are nortoriously slow.
I'd use them if I wasn't concerned about speed.
"Steve Oster" <os************ *******@amerite ch.net> wrote in message
news:oU******** *********@newss vr19.news.prodi gy.com...
Hi all,
I have to create a query using MS Query, and what I need is beyond my
knowledge.
I am querying from a list of invoices, and each invoice can have several
delivery dates (possibly a different date for each line item on the
invoice).
I need to retrieve the largest delivery date for each invoice that does
not
have a NULL value for a delivery date. Meaning, I only want invoices that
have all line items delivered. Here is an example
Invoice | Delivery date
553 | jan 3
553 | jan 7
553 | NULL
554 | jan 15
555 | jan 8
555 | jan 7
So, here are the query results I am looking for
Invoice | Delivery date
554 | jan 15
555 | jan 8
Invoice 553 would not be returned because one line item has a NULL
delivery
date (meaning not all items have been delivered). How can I do this in MS
Query?
Thanks in advance,
Sincerely,
Steve
Obviously not that notorious :o) Cheers for the info
Mark
"Salad" <oi*@vinegar.co m> wrote in message
news:el******** ********@newsre ad1.news.pas.ea rthlink.net... Mark wrote:
Try this:
SELECT YourTable.invoi ce, Max(YourTable.[delivery date]) AS [Delivery
Date]
FROM YourTable
GROUP BY YourTable.invoi ce
HAVING (((YourTable.in voice) Not In (SELECT YourTable.invoi ce FROM
YourTable WHERE (((YourTable.[delivery date]) Is Null));)));
Mark
That would work. However, sub-selects in Access are nortoriously slow.
I'd use them if I wasn't concerned about speed.
"Steve Oster" <os************ *******@amerite ch.net> wrote in message
news:oU******** *********@newss vr19.news.prodi gy.com...
Hi all,
I have to create a query using MS Query, and what I need is beyond my
knowledge.
I am querying from a list of invoices, and each invoice can have several
delivery dates (possibly a different date for each line item on the
invoice).
I need to retrieve the largest delivery date for each invoice that does
not
have a NULL value for a delivery date. Meaning, I only want invoices
that
have all line items delivered. Here is an example
Invoice | Delivery date
553 | jan 3
553 | jan 7
553 | NULL
554 | jan 15
555 | jan 8
555 | jan 7
So, here are the query results I am looking for
Invoice | Delivery date
554 | jan 15
555 | jan 8
Invoice 553 would not be returned because one line item has a NULL
delivery
date (meaning not all items have been delivered). How can I do this in
MS
Query?
Thanks in advance,
Sincerely,
Steve
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Joshua Ruppert |
last post by:
A section of the documentation for the isSet() function states:
Also note that a NULL byte ("\0") is not equivalent to the PHP NULL
constant.
Where would you encounter a NULL byte? Is a null byte what is returned
from a field in a database?
Josh
|
by: scott |
last post by:
i've come across a real head-hurter. I'm looping through a recordset and
response.writing it's rows out with no problem except 1 field. The field
type is varchar and contains words like meeting, holiday, etc.
Problem is, I'm trying to render a "n/a" when the field is null as in
LISTING 1 below. My code isn't catching the null values. How can I test for
null values? I could swear I've successfully used the Len() test like below...
|
by: JOEP |
last post by:
What do I need to do to allow an append query to post null values to
records in a field of the destination table? Basically I want to allow
records with null values to post to the table. The append query will
not work unless there are values in the data i am attempting to send.
I want the fields in the destination table to accept null and
populated values. Any help here would be gladly accepted.
thanks
|
by: mar10 |
last post by:
I have a form that has a drop down box on it which allows the user to
select a YEAR. If the user makes a selection I have a subform that is
driven by a query that will select the YEAR that the user picked. If
the user does not select a year I want to find all items on the
underlying subform. There are Null YEARs in the data base.
I thought if I had my query either select the YEAR picked or "*" it
would find everything, but I guess "*"...
|
by: Jonas Hansson |
last post by:
Hi,
I have a problem regarding NULL values in a database when I retrieve the
data with the MySqlDataAdapter fill a DataSet with the returned value.
Whenever the SQL statement returns data that has a column of which the
first row has the value NULL, that whole column will be NULL for all
retrieved rows. Do I have to set a specific value in the
DataSet/MySqlDataAdapter for it to work as expected or is it a bug or is
it somehow supposed to...
| | |
by: Rodrigo C. Souza |
last post by:
I need to insert a null value in a table on MS SQL Server 2000 using a stored
procedure.
When I send no data to the stored procedure, I get an error saying that the
value cannot be null, but I did the table to receive null values.
I tried DBNull.Value but didn't work.
Thanks, Rodrigo.
|
by: nkumarin001 |
last post by:
Hi,
I have some doubts regarding NULL values. Can any one help me in clearing my doubts.
I have created a table:-
create table suppliers
(
supplier_id number,
supplier_name varchar2(10)
);
|
by: Nobby |
last post by:
On a crosstab query, I often produce reports that show Counts of
values. the problem that I have is that where there are no counts, no
values are returned i.e. there are blank cells.
Is there an easy way in which to automatically replace these null
values with a zero?
Many thanks.
|
by: Toby Gallier |
last post by:
Hello!
I have a form that is calculating averages as follows:
" =(NZ()+Nz()+Nz())/3 "
However I need to now adjust for null values , so for example if
value2 is null I would then need to base my average on just 2 values
instead of 3 i am currently using in my string. How can i have the
form update the "3" based on the number of values that are populated?
|
by: ndeeley |
last post by:
Hi,
My database user has just decided that a recent text field in my database needs to be numeric so that some calculations can be run. I've changed it to a double so I can keep the point formatting (ie. 4.1).
I have changed the data types and altered my variables to insert numbers. I have added a default value of 0 to new records added to the database.
Howevern, some of the existing records have a null field in this changed column.
CF...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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...
|
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...
|
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...
|
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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
