What is the recommended way to summarize detail records in a SQLite table like the following SQL code. Have the user review the summarized records, currently in a DBGrid, then once all looks good the user can then highlight the row(s) and press a "Processed" button that should then update the detail records with a "processed" code.
Currently I am using Delphi VCL which has a datasource ( SQLite file ), a Devart UniDAC UniQuery and a DBGrid. The UniQuery has the following SQL code:
SELECT PartNum, StoreLoc, CostCenter,
SUM( Weight )
FROM tblMaster
WHERE Weight > 0
GROUP BY PartNum, StoreLoc, CostCenter
HAVING Processed = 0
This actually populates the DBGrid perfectly and I can select a single row, or multiple rows. I should be able to then press a button that acknowledges the transactions which should change the Processed field in the detail record(s) from 0 to 1.
CREATE TABLE tblMaster ( PartNum text, Weight real, Emp text, Defect
text, StoreLoc text, CostCenter text, Date text, Time text, Processed
text );
Here is some sample data:
sqlite> select * from tblMaster
9986-0000|1.2|E1|D1|S1|CC1|6/28/2019|13:55|0
9986-0000|0.5|E1|D1|S1|CC1|6/28/2019|13:55|0
9986-0000|1.0|E1|D1|S2|CC1|6/28/2019|13:55|0
9986-0000|1.2|E1|D1|S3|CC1|6/28/2019|13:55|0
9986-0022|1.2|E1|D1|S1|CC1|6/28/2019|13:55|0
9986-0022|0.5|E1|D1|S1|CC1|6/28/2019|13:55|0
9986-0022|1.0|E1|D1|S2|CC1|6/28/2019|13:55|0
9986-0022|1.2|E1|D1|S3|CC1|6/28/2019|13:55|0
The only way I can think of is to take the highlighted row(s) from the DBGrid and simply read though each detail record and update the Processed field.