QUERY

[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

[MGFoster]

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQcNBVYechKq OuFEgEQKIzwCfab 2TPEOLeUtTse8/peixGsEP8XAAoJi i
jrlh6GiVu51CrdI NxJE/aOoQ
=sF1t
-----END PGP SIGNATURE-----

[strauss.sean]

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...

[DS]

st**********@gm ail.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

[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

[MGFoster]

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQcNHaoechKq OuFEgEQLDkgCeKY GtgLItkyxP4ntS2 1RHEyT0InEAoOJw
HWn0xxgTXvThSbZ ZOmuhSrDw
=+FhC
-----END PGP SIGNATURE-----

[Fred Zuckerman]

"DS" <bo******@opton line.net> wrote in message
news:wT******** ********@fe08.l ga...

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

[Bob Quintal]

DS <bo******@opton line.net> wrote in
news:ys******** ********@fe08.l ga:

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_Descriptio n
FROM Sales
WHERE Item_Key NOT IN
(SELECT Item_Key
FROM Sales
WHERE DateDiff("d",Sa lesDate, date()) < 20)
--
Bob Quintal

PA is y I've altered my email address.

[DS]

Fred Zuckerman wrote:

"DS" <bo******@opton line.net> wrote in message
news:wT******** ********@fe08.l ga...

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

[DS]

Fred Zuckerman wrote:

"DS" <bo******@opton line.net> wrote in message
news:wT******** ********@fe08.l ga...

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