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

QUERY

P: n/a
DS
I need to run a Query that returns Products wirh a Sale Date Older Than
20 Days and doesn't have a Sale Date of earlier than 20 Days, Any
Suggestions. I hope this clearer than my last post.
Thnaks
DS
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
DS wrote:
I need to run a Query that returns Products wirh a Sale Date Older Than
20 Days and doesn't have a Sale Date of earlier than 20 Days, Any
Suggestions. I hope this clearer than my last post.
Thnaks
DS


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what you mean, but...

SELECT *
FROM Sales
WHERE SalesDate < Date() - 20

Date() - 20 means subtract 20 days from the current date. If the Sales
Date is less than that date then it is older than 20 days.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQcNBVYechKqOuFEgEQKIzwCfab2TPEOLeUtTse8/peixGsEP8XAAoJii
jrlh6GiVu51CrdINxJE/aOoQ
=sF1t
-----END PGP SIGNATURE-----
Nov 13 '05 #2

P: n/a
I've seen this in the MS Access Cookbook, published by O'Reilly. (I
recommend this book highly!) I'll dig it out and post in an hour or
so...

Nov 13 '05 #3

P: n/a
DS
st**********@gmail.com wrote:
I've seen this in the MS Access Cookbook, published by O'Reilly. (I
recommend this book highly!) I'll dig it out and post in an hour or
so...

Thanks,
I'm going crazy here!
DS
Nov 13 '05 #4

P: n/a
DS
MGFoster wrote:
DS wrote:
I need to run a Query that returns Products wirh a Sale Date Older
Than 20 Days and doesn't have a Sale Date of earlier than 20 Days, Any
Suggestions. I hope this clearer than my last post.
Thnaks
DS

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what you mean, but...

SELECT *
FROM Sales
WHERE SalesDate < Date() - 20

Date() - 20 means subtract 20 days from the current date. If the Sales
Date is less than that date then it is older than 20 days.

Yeah but the problem is that it doesn't take into account if theres a
sale made earlier. I need to set-up a Query that shows Products that
haven't sold in 20 Days or longer. I thought it would be easy!
Thnaks
DS
Nov 13 '05 #5

P: n/a
DS wrote:
MGFoster wrote:
DS wrote:
I need to run a Query that returns Products wirh a Sale Date Older
Than 20 Days and doesn't have a Sale Date of earlier than 20 Days,
Any Suggestions. I hope this clearer than my last post.
Thnaks
DS


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what you mean, but...

SELECT *
FROM Sales
WHERE SalesDate < Date() - 20

Date() - 20 means subtract 20 days from the current date. If the Sales
Date is less than that date then it is older than 20 days.

Yeah but the problem is that it doesn't take into account if theres a
sale made earlier. I need to set-up a Query that shows Products that
haven't sold in 20 Days or longer. I thought it would be easy!
Thnaks
DS


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You said "Sale Date Older Than 20 Days," that usually means product
hasn't sold in the last 20 days. That's why I said "Not sure what you
mean." Do you mean elapsed time (ET) between sales? E.g. (number of
days between sales for the dates 1/1/04 to 6/30/04):

Product ET (days)
widget 0
widget 15
widget 20
widget 30
dodad 0
dodad 5
dodad 10

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQcNHaoechKqOuFEgEQLDkgCeKYGtgLItkyxP4ntS21RHEy T0InEAoOJw
HWn0xxgTXvThSbZZOmuhSrDw
=+FhC
-----END PGP SIGNATURE-----
Nov 13 '05 #6

P: n/a
"DS" <bo******@optonline.net> wrote in message
news:wT****************@fe08.lga...
I need to run a Query that returns Products wirh a Sale Date Older Than
20 Days and doesn't have a Sale Date of earlier than 20 Days, Any
Suggestions. I hope this clearer than my last post.
Thnaks
DS


I'll make an assumption that you have a table with item# and sale date, such
that the item# repeats every time you make a sale. And I suspect that you're
looking for any items that HAVEN'T been sold in the last 20 days but WERE
sold earlier.

I would create 2 queries, first, a select query with the following fields:
1) item#
2) sale date
3) OldSale: iif([sale date]<date()-20,1,0)
4) RecentSale: iif([sale date]<date()-20,0,1)

second, create a total query using query1 as the source. Add the following
fields:
1) item# as groupby
2) OldSale as sum, with criteria >0
3) RecentSale as sum, with criteria = 0

Does this give you the info that you want?
Fred Zuckerman
Nov 13 '05 #7

P: n/a
DS <bo******@optonline.net> wrote in
news:ys****************@fe08.lga:
MGFoster wrote:
DS wrote:
I need to run a Query that returns Products wirh a Sale Date
Older Than 20 Days and doesn't have a Sale Date of earlier
than 20 Days, Any Suggestions. I hope this clearer than my
last post. Thnaks
DS

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what you mean, but...

SELECT *
FROM Sales
WHERE SalesDate < Date() - 20

Date() - 20 means subtract 20 days from the current date. If
the Sales Date is less than that date then it is older than
20 days.

Yeah but the problem is that it doesn't take into account if
theres a sale made earlier. I need to set-up a Query that
shows Products that haven't sold in 20 Days or longer. I
thought it would be easy! Thnaks
DS


Try:

SELECT DISTINCT
Item_Key
Item_Description
FROM Sales
WHERE Item_Key NOT IN
(SELECT Item_Key
FROM Sales
WHERE DateDiff("d",SalesDate, date()) < 20)
--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #8

P: n/a
DS
Fred Zuckerman wrote:
"DS" <bo******@optonline.net> wrote in message
news:wT****************@fe08.lga...
I need to run a Query that returns Products wirh a Sale Date Older Than
20 Days and doesn't have a Sale Date of earlier than 20 Days, Any
Suggestions. I hope this clearer than my last post.
Thnaks
DS

I'll make an assumption that you have a table with item# and sale date, such
that the item# repeats every time you make a sale. And I suspect that you're
looking for any items that HAVEN'T been sold in the last 20 days but WERE
sold earlier.

I would create 2 queries, first, a select query with the following fields:
1) item#
2) sale date
3) OldSale: iif([sale date]<date()-20,1,0)
4) RecentSale: iif([sale date]<date()-20,0,1)

second, create a total query using query1 as the source. Add the following
fields:
1) item# as groupby
2) OldSale as sum, with criteria >0
3) RecentSale as sum, with criteria = 0

Does this give you the info that you want?
Fred Zuckerman

Thanks,
I'll give it a whirl!
DS
Nov 13 '05 #9

P: n/a
DS
Fred Zuckerman wrote:
"DS" <bo******@optonline.net> wrote in message
news:wT****************@fe08.lga...
I need to run a Query that returns Products wirh a Sale Date Older Than
20 Days and doesn't have a Sale Date of earlier than 20 Days, Any
Suggestions. I hope this clearer than my last post.
Thnaks
DS

I'll make an assumption that you have a table with item# and sale date, such
that the item# repeats every time you make a sale. And I suspect that you're
looking for any items that HAVEN'T been sold in the last 20 days but WERE
sold earlier.

I would create 2 queries, first, a select query with the following fields:
1) item#
2) sale date
3) OldSale: iif([sale date]<date()-20,1,0)
4) RecentSale: iif([sale date]<date()-20,0,1)

second, create a total query using query1 as the source. Add the following
fields:
1) item# as groupby
2) OldSale as sum, with criteria >0
3) RecentSale as sum, with criteria = 0

Does this give you the info that you want?
Fred Zuckerman

Thanks Fred. It worked! Thanks also to everyone that responded.
Sincerely,
DS
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.