Unanswered: Trying to add, row by row, a new table with the sum of the different rows
God morning to you all
I am a newly hatched programmer who just ran into the proverbial wall just before the learning curve.
The situation I ran into is that my customer uses an old Access 2003 database which was originally designed to only read and store data from their PLC, not present it in any way other than as row upon row of raw data. Now they wish to present this data using forms and reports. The facility in question has several production lines where raw material is sorted onto one of three primary lines and there further sorted and processed by four machines, numbered one through four, onto four secondary lines (each no 1 machine sorts down to secondary line 1, each no 2 machine onto secondary line 2 and so on).
Now the problem. Among other things, the database stores the number of times each sorting machine has directed material down its line ordered by date. The customer would like to know just how many times per day each of these machines has directed materal down a certain line, thus all sortings that have been directed down secondary line number one, line two and so on. The database already has the values for each machine but no value regarding each secondary line. All values are stored as INT-values and the date as DATE/TIME.
Thus I thought I might simply add them all up together, sorted by the same date using a new table. Unfortunatelly this solution continues to elude me.
The last attempt to fill the first line looked as follows:
INSERT INTO [newTable]
Date, SUM(Pos1_primaryLine1, Pos1_primaryLine2, Pos1_primaryLine3) AS 'Sum Secondary line 1'
ORDER BY oldTable.Date
GROUP BY oldTable.Date
I am certain that there is a way of doing this type of handling using queries and sub-tables but I cannot for the life of me see how I add the numbers from several columns, row by row, into a new column (or new table for that matter).
Please, if anyone has come across this situation, would you be so kind as to direct me onto where I might find a solution or if you know how to do it, give me a hint on how. As it is, I seem to be quite literally stuck...