Read Database Column According to Number of Rows

[Miu]

Hi guys,

I am currently stuck at the problem of retrieving data from my database (MS Access) according to the number of rows it has at any one time (without having to know how many rows there are going to be while programming this part).

Firstly, I have to introduce how my program works. I am working on an automated food ordering system and after the customer has selected his/her food, information such as the food name, food price and quantity will be written to the database MS Access table. (for e.g. table name "Orderingtable" in MS Access) For my case, 1 order of food item will occupy 1 row of the database table. In other words, under the same customer, if he orders 3 different food items, 3 rows will be occupied in my database table.

I would then like to retrieve the number of the "Quantity" part for each order from the database and sum up the quantity eventually to count the number of total orders in the database table at any point of time. This addition of result will then be shown on the Front Panel to inform the customer how many pending orders there are just before his order. In this case, he can back out if he wants to if the number of orders is too huge for his waiting time.

However, I do not know how many rows my "Orderingtable" will have at any one time because the "Orderingtable" will accumulate rows and rows of data until being commanded to be deleted. Therefore, I cannot predict the number of rows as I program the part to sum up the number of quantity for each and every row.

Is there a way that I can retrieve the "Quantity" part without having to know the number of rows in the database so that I can count the total number of current orders just by summing up the value of quantity for each row?

I do not wish to "hardcode" my program by limiting the database tables to let's say, only 50 rows at any one time.

Below attached is how my "Orderingtable" database table looks like, which is going to be used to retrieve the column "Quantity" so that it can count the total number of orders and be shown/indicated on the Front Panel of my Labview program.

I hope you guys are able to help me!

Thank you so much in advance.

Cheers,

Miu

[Gak]

Hi Miu,

You can use command to get entire "Quantity" column from database. Convert it to number array and use "add array elements" function to get total number.

I think this is what you want.

[G_K]

Hi Miu,

As gak suggested we can get the total order without reading all the rows present in the table using the commands listed below.

SELECT [ALL | DISTINCT] columnname1 [,columnname2]
FROM tablename1 [,tablename2]
[WHERE condition] [ and|or condition...]
[GROUP BY column-list]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]

Also the attached snippet will find the total number of order for the given Queue_No. Post your code and database incase if you need more help..

[josborne · Accepted solution]

You could also just use the SUM function:

     SELECT SUM(Quantity) FROM Ordering WHERE Queue_No = %d

And no need for an "Order By" clause if you are just adding up the quantities.

[Miu]

Hi everyone,

Thanks for all the different solutions but I have decided to use josborne's because it is short and simple, and they worked perfectly well for my program.

Nonetheless, thank you everyone, they were of great help!

There is another thing that I would like to ask and that is if there is a way to "Copy and move" rows of data from one table to another without having to specify what columns and rows to copy from.

For an example, I just want to copy everything from 1 table to another at one point of time, and there is no need for specific rows to be selected.

I have tried copying the rows and columns from table(s) by creating a subVI (attached as a picture below) and I think the problem with this is that it is a form of "hard coding" because I have to repeat the steps of specifying row number and column number(s) a hundred times if I have a hundred rows and columns that I want to copy.

I was hoping that there is another way in which I can just copy and move everything (move as in delete after copying) by using a type of command without having to "hard code" like the way that I have done and I will be more than happy to learn from you guys.

Smiles,

Miu

[Miu]

Oops I forgot to attach the picture so here it is!

[josborne]

>>I just want to copy everything from 1 table to another

Absolutely.  But as far as I know, there is no "move" command.  So you will need to do this in two separate operations:  first copy, and then delete.

• To copy data:     SELECT * INTO target_table FROM source_table

NOTE:  By specifying * you are copy all the columns.  And the columns must match up. If your source and target tables do not match, then you'll need to replace * with a list of columns.  See here for more info.

• To delete the data in the original table:     DELETE * FROM source_table

NOTE:  This will delete the entire contents of the source table.  Which may not be ideal.  Depending on your application, it could potentially leave you with the possibility of data loss.  For example, what if "someone" manages to insert some data into the source_table inbetween your copy and delete operations?  That data would be lost (you'd delete it).  But that would only be a concern if there were multiple people accessing your database simultaneously.  If that could be a problem, let me know, and we can consider a different solution, or consider using locks/transactions to prevent that situtation.