By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,731 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

invoices from 2002 & not from 2004

P: n/a
I stumped [this should be easy]
Can someone assist me with the query for "invoices from 2002 & not from 2004"
thanks
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
On 15 Jun 2004 10:08:15 -0700, StevenH wrote:
I stumped [this should be easy]
Can someone assist me with the query for "invoices from 2002 & not from 2004"
thanks
When you write "Invoices from 2002" do you want just that one year, or
from 2002 through the present?

Add a new column to the query:
InvoiceYear:Year([DateField])

As criteria for this column, write:
2002

To get records from 2002 and newer:=2002
You can change the criteria to
= [Enter Year]
or...= [Enter Year]


and it will prompt for the year. This gives you more flexibility to
get different years without changing the query.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #2

P: n/a

Sorry about the confusion, I only want a list of Inactive customers.

ie Customers who I invoiced in 2002.

I dont want included in this list customerw who I invoiced in 2002 &
2003 & 2004

Ideally I'd like a list of customer who I invoiced in 2002 and not in
2003

and another list of customers I invoiced in 2002 and not in 2004

tia
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a

I only want customers that had invoices dated in Only 2002 [not in 2003
or 2004] in one table in this query.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

P: n/a
How about a subquery for the criterias?

First create a query for all invoices in 2002 and another for invoices
in 2003. Copy the SQL statement.

For the criteria of the customerID put something like:

In (...SQL statement of the first query ...) And Not In (...SQL
statement for the second query...)

That should give you what you need.

Of course knowing nothing about your data structure I can't give you
the specifics for the SQL statements.

- Jim

On 15 Jun 2004 18:31:24 GMT, Steven Hellman
<st***@yourinternetinsuranceman.com> wrote:

Sorry about the confusion, I only want a list of Inactive customers.

ie Customers who I invoiced in 2002.

I dont want included in this list customerw who I invoiced in 2002 &
2003 & 2004

Ideally I'd like a list of customer who I invoiced in 2002 and not in
2003

and another list of customers I invoiced in 2002 and not in 2004

tia
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 13 '05 #5

P: n/a
thanks;but, Not a SQL guy, if you could, I'd appreciate some code..

Do you need me to post some/all of data structure, I can..

Just some code to follow will be fine

tia

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #6

P: n/a
fredg <fg******@example.invalid> wrote in
news:ky****************************@40tude.net:
On 15 Jun 2004 10:08:15 -0700, StevenH wrote:
I stumped [this should be easy]
Can someone assist me with the query for "invoices from 2002 &
not from 2004" thanks


When you write "Invoices from 2002" do you want just that one
year, or from 2002 through the present?

Add a new column to the query:
InvoiceYear:Year([DateField])

As criteria for this column, write:
2002

To get records from 2002 and newer:
=2002


You can change the criteria to
= [Enter Year]
or...
= [Enter Year]


and it will prompt for the year. This gives you more flexibility
to get different years without changing the query.


I would never do that, because using an expression means it won't
use the index that you presumably have for the invoice date.

Better would be:

Between #1/1/2002# and #12/31/2002#

This will use the index and will return the same result set as:

Year(InvoiceDate)=2002

I think it's always best to try to write your queries so that they
don't have criteria that run against expressions.

That's not always possible, but when it *is* possible, I think it's
inadvisable to use the expression.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #7

P: n/a
Steven Hellman <st***@yourinternetinsuranceman.com> wrote in
news:40**********************@news.newsgroups.ws:
I only want customers that had invoices dated in Only 2002 [not in
2003 or 2004] in one table in this query.


In the QBE, add your Customers table. Then add TWO copies of the
Invoice table. Join both Invoice tables to the Customers table
(presumably on a CustomerID). In one copy of the invoice table, you
want:

InvoiceDate Between #1/1/2002# And #12/31/2002#

The other table you want to limit to those invoices greater than
12/31/2002.

Then, you need to change the join between the second copy of the
Invoice table to include all records from Customers and only
matching records from Invoices.

Then your last criterion is that you want all those records where
the InvoiceID of the second invoice table is Null (because there
aren't any records in that table for the period you excluded).

Now, add in the fields you want from the Customer table, and set it
to Unique Records (SELECT DISTINCT), and you're done.

Another alternative to this would be to have your main query have
Customers and Invoices (with the criterion limiting it to the 2002
invoices) and then use a subquery in an IN clause.

This would look something like this:

SELECT DISTINCT Customers.FirstName, Customers.LastName
FROM Customers INNER JOIN Invoices ON Customers.CustomerID =
Invoices.CustomerID WHERE (((Invoices.InvoiceDate) Between
#1/1/2002# And #12/31/2002#) AND ((Customers.CustomerID) In (SELECT
Invoices.CustomerID FROM Invoices
WHERE (((Invoices.InvoiceDate)>#12/31/2002#));)));

The SELECT DISTINCT is required because you have a many-to-one join
between Customers and Invoices.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8

P: n/a
On 15 Jun 2004 21:11:11 GMT, stevenh <an*******@devdex.com> wrote:
thanks;but, Not a SQL guy, if you could, I'd appreciate some code..

Do you need me to post some/all of data structure, I can..

Just some code to follow will be fine

tia

Something like this: Open up a new query on the query tab. Add the
customer table. Add the customer ID plus any other fields that you
need for your purpose.

Now open a new query and add the Order Header table (assuming you have
one - Typically you have an order table that drives the invoices) and
the Invoice Header table. Add the Customer ID (probably from the order
header) and the Invoice Date from the invoice header. In the criteria
row for the Invoice Date enter: Between #1/1/2002# And #12/31/2002#

Now make this a totals query. Menu: [View -> Totals]
In the Totals row that is now available: Select the Total for Invoice
Date and select Where.

OK, now right click on the query and select SQL view. You should have
something like:

SELECT tblOrderHeader.CustomerID
FROM tblOrderHeader INNER JOIN tblInvoiceHeader ON
tblOrderHeader.OrderNum = tblInvoiceHeader.OrderNum
WHERE (((tblInvoiceHeader.InvoiceDate) Between #1/1/2002# And
#12/31/2002#))
GROUP BY tblOrderHeader.CustomerID;

Copy this [Ctrl] + [c] and paste it somewhere safe. I usually use
Notepad for this stuff.

Right click the top of the query and choose Query Design.

Now go to the Criteria row for the Invoice Date field and change it
to: >#12/31/2002#

You should now have SQL that looks like:

SELECT tblOrderHeader.CustomerID
FROM tblOrderHeader INNER JOIN tblInvoiceHeader ON
tblOrderHeader.OrderNum = tblInvoiceHeader.OrderNum
WHERE (((tblInvoiceHeader.InvoiceDate)>#12/31/2002#))
GROUP BY tblOrderHeader.CustomerID;

To wrap up; in the first query with the customers, select the criteria
row of the Customer ID field and using the In clause create something
like: (Shift + F2 will open a zoom window with more room)

In (SELECT tblOrderHeader.CustomerID
FROM tblOrderHeader INNER JOIN tblInvoiceHeader ON
tblOrderHeader.OrderNum = tblInvoiceHeader.OrderNum
WHERE (((tblInvoiceHeader.InvoiceDate) Between #1/1/2002# And
#12/31/2002#))
GROUP BY tblOrderHeader.CustomerID;) And Not In (SELECT
tblOrderHeader.CustomerID
FROM tblOrderHeader INNER JOIN tblInvoiceHeader ON
tblOrderHeader.OrderNum = tblInvoiceHeader.OrderNum
WHERE (((tblInvoiceHeader.InvoiceDate)>#12/31/2002#))

Note the first In clause holds the invoices created for 2002 and the
second In clause uses the Not operator to exclude invoices for the
period greater than 2002. Together they will produce what you require.

- Jim

Nov 13 '05 #9

P: n/a
"Steven Hellman" <st***@yourinternetinsuranceman.com> wrote in message
news:40**********************@news.newsgroups.ws.. .

I only want customers that had invoices dated in Only 2002 [not in 2003
or 2004] in one table in this query.


something like this, (please post you table structure if you need some more
help)

select * from customers as c
where exists
(
select * from invoices i
where i.custid = c.custid
and i.invoiceDate >= #01/01/2002#
and i.invoiceDate < #01/01/2003#
)
and not exists
(
select * from invoices i
where i.custid = c.custid
and i.invoiceDate >= #01/01/2003#
)
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.