LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

How to extract Data From Db Access and Group it by Months

Solved!
Go to solution

Hello All,

I'm hoping you can help me to figure this out, I have done an extensively research and can't find a way to make my code works the way I want to. (See below what I want to do and what I have done)

 

Goal:

My Main Goal it to retrieve from an Access Database the following Columns (Quantity, Defects, Etc.) with this fields I'll be doing some calculation that I'll later on display to the user. I need to be able to retrieve it by months going back to a year (for example from 1.26.2020 to 1.27.2021) The end goal is to have a run chart displaying the levels of what I want to calculate per month.

This is Hopefully the outcome I'm looking to achieve. 

 

DateTime         Quantity Total          Defects Total

2021-01             1200                              5

2021-02              200                               2

 

Access Database:

The access Database I'm using has multiple daily entries with fields such as (DateTime, Quantity, defects, etc.)

 

What I have done so far:

I was able to achieve something similar but I was not able to group it by Month, when I tried to group it, I would get the data from 2020 and 2021 mixed up, for example if within the data there was June 2020 and June 2021 it would group it together. Also, if you are wondering why I have a loop to change the date as specified, it's 1 to get ride of the Time, since that would messed up the graph view, and also to adjust to how I want the data to be display it on the graph. 

So far I'm able to achieve the following, retrieve, an array of DateTime, and two more with the corresponding Quantity and Defects information. My Next step should be to group the sum by month so then i can make my calculations. That's where I'm stuck... There might be an easier approach that the one I'm taking, if you don't mind pointing me to it I'll appreciate it, or if you know how i could get this data formatted the way I want it, I'll appreciate the help too.

 

See Photos of My Code Below:

Database ToolKit: The code used to retrieved the information from the access Db.

Graph: An Example of how I would like it to look like (This graph has the data extracted from the Db but previous to making the calculations).

SqlStr: Sql String used to retrieve the data.

Values: This are the values that I extracted (Didn't take a screenshot of all of them, but this is enough to provide an idea). 

 

I hope all the information I have provided it's enough, if not let me know your questions, I'll appreciate any help. Thanks in advance. (Please note i did the best i could with the subject of this topic, yet...i get a feeling somebody will find a reason why it isn't correct...lol)

 

0 Kudos
Message 1 of 7
(1,086 Views)
Solution
Accepted by dreamheart

Sounds like you're trying to pull it by month, when you really should be pulling it by year and then sorting it.  Sort year, month, day, time, in that order, just like you would sort it manually: 2021-01-27 0830.

Bill
CLD
(Mid-Level minion.)
My support system ensures that I don't look totally incompetent.
Proud to say that I've progressed beyond knowing just enough to be dangerous. I now know enough to know that I have no clue about anything at all.
Humble author of the CLAD Nugget.
Message 2 of 7
(1,069 Views)

Hello Bill,

I appreciate taking the time to reply. I believe I'm pulling it by year am I not? My Select statement it's the following:

Select aoi_DateTime, (aoi_Open + aoi_Short + aoi_Void + aoi_EtchVoids + aoi_Conductor + aoi_AnnularRing + aoi_Indents + aoi_MissingFeature + aoi_Fiducials + aoi_PlatingBurn + aoi_Pinhole + aoi_RepeatIndents + aoi_PlatingContactOpens + aoi_PlatingContactVoids + aoi_PlatingContactIndents + aoi_MachineMalfunctionBoards + aoi_PlatingShort + aoi_ScrapRule), (aoi_Quantity) From AOI Where #1/27/2021# And aoi_DateTime > #1/28/2020#

 Sorting it is where I'm stuck, I end up having 3 arrays, DateTime, Quantity and Defects each one of them containing each entry done through the year, but from there how do I sort it so I end up with the monthly sum of these values.

 

I want to end up with something like this:

 

DateTime         Quantity Total          Defects Total

2021-01             1200                              5

2021-02              200                               2

 

I could think of ways of doing it in C# but I struggle with labview so much.. I'm sure this is probably an easy and rather obvious answer...I just can't see it... 

      

0 Kudos
Message 3 of 7
(1,058 Views)

You should be thinking SQL, not LabVIEW or C#.  Sorting in SQL is done with an "ORDER BY" clause in the SELECT statement. 

Message 4 of 7
(1,052 Views)

Hello Bill, 

I apologize, after the comment @drjdpowell did i realized I was too stuck thinking of Labview instead of SQL, then I modified my sql statement to retrieve, year and month and group it by those as well with the sum of the columns I needed. 

 

I appreciate all the help! as i thought it was something simple....sometimes it just takes somebody else to help you think out of the box we stock ourselves sometime. Again Thank you All! 

0 Kudos
Message 5 of 7
(1,045 Views)

@dreamheart wrote:

Hello Bill, 

I apologize, after the comment @drjdpowell did i realized I was too stuck thinking of Labview instead of SQL, then I modified my sql statement to retrieve, year and month and group it by those as well with the sum of the columns I needed. 

 

I appreciate all the help! as i thought it was something simple....sometimes it just takes somebody else to help you think out of the box we stock ourselves sometime. Again Thank you All! 


No worries; we're all good.  I wasn't even thinking about being offended.  🙂

Bill
CLD
(Mid-Level minion.)
My support system ensures that I don't look totally incompetent.
Proud to say that I've progressed beyond knowing just enough to be dangerous. I now know enough to know that I have no clue about anything at all.
Humble author of the CLAD Nugget.
0 Kudos
Message 6 of 7
(1,002 Views)

Hello dreamheart,

 

you could try also our ANV Database Toolkit - it allows to create query configuration using graphical interface, and apply filters, aggregate functions, etc.; and also allows to easily configure sorting based on selected columns.

Actually, there is no need to write SQL statement manually, it'll be generated by the toolkit based on data which you'd like to select.

 

Cheers,

 

kosist90.

0 Kudos
Message 7 of 7
(938 Views)