Microsoft Access: Vulnerabilities Database - Query Past 6 Months (1 Viewer)

aus_son

New member
Local time
Today, 09:36
Joined
May 1, 2020
Messages
2
I need to make a query that will show the past 6 months for each month. So now for an example, if it was February I need to create a SLA report for February, but I need to generate trend graph for the past 6 months to include in the report. But it just continues to show months that are not for the past 6 months.

SQL VIEW

SELECT Vulnerabilities.[Host IP], Vulnerabilities.CVSS, Vulnerabilities.[Risk Level], Vulnerabilities.Vulnerability, Vulnerabilities.[Scan Date]FROM VulnerabilitiesWHERE (((Vulnerabilities.[Risk Level])="Serious" Or (Vulnerabilities.[Risk Level])="High" Or (Vulnerabilities.[Risk Level])="Medium") AND ((Vulnerabilities.[Scan Date]) Between #8/1/2020# And #1/31/2020#));

DESIGN VIEW
Screen Shot 2020-05-01 at 2.42.43 PM.png
 

Micron

AWF VIP
Local time
Today, 10:36
Joined
Oct 20, 2018
Messages
3,471
I don't get it. You want 6 months back from a date, but you show us a date range of 8 months because you are including 1 day from each month at both ends?
You need to clarify what "back 6 months" really means. It could mean that if today was April 15, you want to go back to October 15. Or it could mean you want to go back to the beginning of the month 6 months ago; i.e. October 01. Or something else.

But it just continues to show months that are not for the past 6 months.
Then what does it show? A clue about the results would help. Shows some months? None at all? No records?
 

isladogs

CID VIP
Local time
Today, 15:36
Joined
Jan 14, 2017
Messages
14,247
Welcome to AWF.
If you want to group your data by month for the past 6 months, you need an aggregate or totals query. Click the totals button on the ribbon.
Look at your date range. Its currently from 1 Aug 2020 to 31 Jan 2020.
 

aus_son

New member
Local time
Today, 09:36
Joined
May 1, 2020
Messages
2
Aug (1st of August) - Jan (31st of January)
Sep (1st of Sep) - Feb (29th of February) **LEAP YEAR THIS YEAR
Oct (1st of Oct) - Mar (31st of March)
and etc.....


The report that I'm making now is for April and I need to generate a trend graph for the previous 6 months (including April)
 

Attachments

  • Screen Shot 2020-05-01 at 3.21.21 PM.png
    Screen Shot 2020-05-01 at 3.21.21 PM.png
    28 KB · Views: 17

isladogs

CID VIP
Local time
Today, 15:36
Joined
Jan 14, 2017
Messages
14,247
Fine but unless you have future data you probably want #8/1/2019#,
In the criteria, the earlier date needs to be first,
And, as stated, you need to group your data
 

plog

Banishment Pending
Local time
Today, 09:36
Joined
May 11, 2011
Messages
10,259
Sounds like you have 2 issues:

1. How to make criteria always look back to the first date of the month 7 months prior (lets call this START) to the end of the month of the prior month (lets call this END).

You build your BETWEEN criteria using these 4 date functions:

Date(https://www.techonthenet.com/access/functions/date/date.php)
--to get the current date

DateAdd(https://www.techonthenet.com/access/functions/date/dateadd.php)
--to subtract 7 months from from the current date for use in START
--to subtract 1 day from the first date of the current month for use in END

Month(https://www.techonthenet.com/access/functions/date/month.php)
--to get the month of the current date for use in END
--to get the month of START after you calulateit using DateAdd

DateSerial(https://www.techonthenet.com/access/functions/date/dateserial.php)
--to build START
--to build the first date of the current month for use in END

It's a puzzle, you must first use one function inside another, inside another to build both START and END. Give it a shot and once you do you will never have to touch your criteria again.

--------

2. What's wrong with my criteria.

I don't know. If you are getting unexpected dates then its possible [Scan Date] isn't a date but a string. Check the data type in the table to verify. Strings and dates compare different. If you want to cmopare [Scan Date] as a date, it must be that type.
 

Micron

AWF VIP
Local time
Today, 10:36
Joined
Oct 20, 2018
Messages
3,471

Users who are viewing this thread

Top Bottom