 | |
#1
 
| |||
| |||
|
QUERY -
12-17-2004
, 01:57 PM
#2
| |||
| |||
|
|
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 |
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-----
#3
| |||
| |||
|
#4
| |||
| |||
|
|
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, |
DS
#5
| |||
| |||
|
|
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 |
haven't sold in 20 Days or longer. I thought it would be easy!
Thnaks
DS
#6
| |||
| |||
|
|
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 |
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-----
#7
| |||
| |||
|
|
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 |
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
#8
| |||
| |||
|
|
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 |
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.
#9
| |||
| |||
|
|
"DS" <bootybox (AT) optonline (DOT) net> wrote in message news:wTGwd.1219$u52.107 (AT) fe08 (DOT) 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, |
DS
#10
| |||
| |||
|
|
"DS" <bootybox (AT) optonline (DOT) net> wrote in message news:wTGwd.1219$u52.107 (AT) fe08 (DOT) 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. |
DS
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.