Hi there,
I am trying to do a count based on Account, Vendor, Amount and date range of 3 days, while being able to select the date as well... If that makes sense.
This is my SQL so far:
Without Selecting date:
With Date Selected:
Desired Result:
Thanks for any help on this
I am trying to do a count based on Account, Vendor, Amount and date range of 3 days, while being able to select the date as well... If that makes sense.
This is my SQL so far:
Rich (BB code):
SELECT DATA.ACCOUNT, DATA.VENDOR, DATA.AMOUNT, Count(DATA.AMOUNT) AS CountOfAMOUNT, DATA.DUEDATE
FROM DATA
WHERE (((DATA.DUEDATE) Between [DUEDATE]-1 And [DUEDATE]+1))
GROUP BY DATA.ACCOUNT, DATA.VENDOR, DATA.AMOUNT, DATA.DUEDATE;
Without Selecting date:
Book1 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ACCOUNT | VENDOR | AMOUNT | CountOfAMOUNT | ||
2 | 1 | AAA | 100 | 3 | ||
3 | 2 | CCC | 500 | 2 | ||
4 | 3 | DDD | 10 | 1 | ||
5 | 4 | EEE | 8000 | 1 | ||
Sheet1 |
With Date Selected:
Book1 | |||||||
---|---|---|---|---|---|---|---|
F | G | H | I | J | |||
1 | ACCOUNT | VENDOR | AMOUNT | CountOfAMOUNT | DUEDATE | ||
2 | 1 | AAA | 100 | 1 | 07-Sep-16 | ||
3 | 1 | AAA | 100 | 1 | 08-Sep-16 | ||
4 | 1 | AAA | 100 | 1 | 09-Sep-16 | ||
5 | 2 | CCC | 500 | 1 | 06-Sep-16 | ||
6 | 2 | CCC | 500 | 1 | 07-Sep-16 | ||
7 | 3 | DDD | 10 | 1 | 06-Sep-16 | ||
8 | 4 | EEE | 8000 | 1 | 05-Sep-16 | ||
Sheet1 |
Desired Result:
Book1 | |||||||
---|---|---|---|---|---|---|---|
F | G | H | I | J | |||
1 | ACCOUNT | VENDOR | AMOUNT | CountOfAMOUNT | DUEDATE | ||
2 | 1 | AAA | 100 | 3 | 07-Sep-16 | ||
3 | 1 | AAA | 100 | 3 | 08-Sep-16 | ||
4 | 1 | AAA | 100 | 3 | 09-Sep-16 | ||
5 | 2 | CCC | 500 | 2 | 06-Sep-16 | ||
6 | 2 | CCC | 500 | 2 | 07-Sep-16 | ||
7 | 3 | DDD | 10 | 1 | 06-Sep-16 | ||
8 | 4 | EEE | 8000 | 1 | 05-Sep-16 | ||
Sheet1 |
Thanks for any help on this