MS Access VBA Generating Query Based Tables

Question

I'd like to create a new query (that can build a table "05_DataAnalysis_02") that is referencing a table "05_DataAnalysis-01". Within the "05_DataAnalysis-01" table I have a bunch of Events that each have a date/time associated with them (not necessarily unique [meaning multiple events can happen at the exact same second]).

I also have additional columns that calculate Times (adding/removing hours minutes so on and so forth) from the actual event time. As well as a column that vets a few columns of data to determine if it meets a certain criteria:

The query should then put the start time and end time that the criteria is met along with the maximum number of alarms received within that period (I have this stored in a column called [MaxNumEvents])

Flood Event, EarlyEvent, EventTime, LateEvent, numEvents

0, 8/6/19 3:58 PM, 8/6/19 4:58 PM, 8/6/19 5:58 PM, 3

0, 8/10/19 1:29 PM, 8/10/19 2:29 PM, 8/10/19 3:29 PM, 1

0, 8/23/19 11:21 AM, 8/23/19 12:21 PM, 8/23/19 1:21 PM, 3

1, 8/24/19 8:14 AM, 8/24/19 9:14 AM, 8/24/19 10:14 AM, 38

1, 8/24/19 8:15 AM,8/24/19 9:15 AM, 8/24/19 10:15 AM, 26

1, 8/24/19 8:16 AM, 8/24/19 9:16 AM, 8/24/19 10:16 AM, 38

0, 8/24/19 8:34 PM, 8/24/19 9:34 PM, 8/24/19 10:34 PM, 1

0, 8/24/19 10:29 PM, 8/24/19 11:29 PM, 8/25/19 12:29 AM, 2

The End Result should look something like this:

StartEvent, EndEvent, MaxNumEvents 8/24/19 8:14 AM, 8/24/19 10:16 AM, 38

And should reproduce for everytime that FloodEvent is = 1

Thank you,

I must admit, I have no clue what you have, nor what you are trying to achieve. — Gustav
– Gustav, Commented Apr 17, 2020 at 7:47
Yep, no idea about what is trying to be achieved here. It may need use of recordsets to perform the data building and then inserting into a table rather than using a make table query. — Applecore
– Applecore, Commented Apr 17, 2020 at 8:05
Sorry for any confusion. I have updated my original post to include sample data and a sample result table to hopefully clarify — rlw2235
– rlw2235, Commented Apr 17, 2020 at 13:22
Aren't you just after a simple SELECT [Early Event], [Late Event], numEvents FROM [05_DataAnalysis-01] WHERE [Flood Event]=1? — Applecore
– Applecore, Commented Apr 17, 2020 at 16:09
Close however for the continuous event I'd like to only pull the First [Early Event] and the last [Late Event] keeping in mind that these can occur multiple times throughout the data set. — rlw2235
– rlw2235, Commented Apr 17, 2020 at 17:51

Gustav · Accepted Answer · 2020-04-17 16:25:15Z

1

That could be:

Select
    Min([EarlyEvent]) As StartEvent, 
    Max([LateEvent]) As EndEvent, 
    Max(numEvents]) As MaxNumEvents
From
    [05_DataAnalysis-01] 
Where 
    [Flood Event] = 1

answered Apr 17, 2020 at 16:25

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rlw2235 Over a year ago

This is close as it gives me the solution for one flood condition however i need this query to pull each flood event (there can be multiple within the data set. I hope this makes sense...

Gustav Over a year ago

Then you will have to define how a flood condition is identified and distinguished from other flood conditions - and supply sample data.

Collectives™ on Stack Overflow

MS Access VBA Generating Query Based Tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related