1. jobsheet_report_total_sub
CREATE VIEW jobsheet_report_total_sub(jobid, barcode, qty) AS
(SELECT jr_jobid jobid, jr_pu1barcode Barcode, jr_pu1qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu1barcode “”)
union all
(SELECT jr_jobid jobid, jr_pu2barcode Barcode, jr_pu2qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu2barcode “”)
union all
(SELECT jr_jobid jobid, jr_pu3barcode Barcode, jr_pu3qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu3barcode “”)
union all
(SELECT jr_jobid jobid, jr_pu4barcode Barcode, jr_pu4qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu4barcode “”)
union all
(SELECT jr_jobid jobid, jr_pu5barcode Barcode, jr_pu5qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu5barcode “”)
union all
(SELECT jr_jobid jobid, jr_pu6barcode Barcode, jr_pu6qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu6barcode “”)
union all
(SELECT jr_jobid jobid, jr_pu7barcode Barcode, jr_pu7qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu7barcode “”)
union all
(SELECT jr_jobid jobid, jr_pu8barcode Barcode, jr_pu8qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu8barcode “”)
union all
(SELECT jr_jobid jobid, jr_pu9barcode Barcode, jr_pu9qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu9barcode “”)
union all
(SELECT jr_jobid jobid, jr_pu10barcode Barcode, jr_pu10qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu10barcode “”);

2. jobsheet_report_total
CREATE VIEW jobsheet_report_total AS
SELECT barcode, SUM(qty) qtyused
FROM jobsheet_report_total_sub
GROUP BY Barcode;