Sorry if Repost...Cant find the original. How to Add additional data into a View at Run Time

New_Creative · February 9, 2020, 10:13pm

I have created a view, however for simplicity I am treating it as a table in this post. BarCodesBetweenDates. This view returns the chemicals delivered to a water body on a site within the specified date range. This works well as far as it goes.

However Its not all this needs to do.

Some of the products have a deposit system. That is a token payment for the drum is charged and then credited on return. These drums are recycled consequently there is a deposit and a return.

First issue is the returns should be reported as a negative number so how do I tell the view to return this specific code as a negative number? As I didn't want to bog down with 40 odd tables for the exercise of the answer please use the string being Drum Return as the indicator for the negative value. I will work it out from the answer I think.

The second is not all drums have a deposit, as not all chemical containers can be refilled. Consequently there is a table identifying the products that can be recycled I call StkDrumDeposit. If the PN is present in this table, then a Deposit should be added for the product.

To make this a bit clearer I have made an excel example as:

The highlighted lines are the ones I would like to get added.

My previous approach was to use recordsets and to add this row to the query via a recordset update. I am not sure this is possible on SQL, and I am unsure what tools to use to solve this .
INSERT INTO [dbo].[BarCodesBetweenDates]
([StaffName]
,[Description]
,[SiteName]
,[WBName]
,[PN]
,[Quant]
,[JobNumber])
Values(
'Victor P','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',3,136,
'Victor P','LIQUID CHLORINE 15 LTR ','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',2,136,
'Victor P','Drum Return','Site 47 for Client 33','Water Body 48 For Site 47','0506CH6420',1,135,
'Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',4,139,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',4,139,
'Victor P','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',1,133,
'Victor P','STABILISER 2.0KG','Site 22 for Client 21','Water Body 39 For Site 22','0514CH4626',1,132,
'Victor P','LIQUID CHLORINE 15 LTR','Site 43 for Client 57','Water Body 44 For Site 43','0506CH6221',2,138,
'Victor P','Drum Return','Site 43 for Client 57','Water Body 44 For Site 43','0506CH6420',2,138,
'Victor P','LIQUID CHLORINE 15 LTR ','Site 44 for Client 55','Water Body 67 For Site 44','0506CH6221',2,127,
'Victor P','NON FUMING ACID 15 LTR ','Site 44 for Client 55','Water Body 67 For Site 44','0508CH6010 ',1,127,
'Victor P','Drum Return','Site 44 for Client 55','Water Body 67 For Site 44','0506CH6420',3,127,
'Victor P','SPA SHOCK 500GMS ','Site 44 for Client 55','Water Body 67 For Site 44','0560CHS1099 ',1,127,
'Victor P','BUFFER 2KG ','Site 44 for Client 55','Water Body 67 For Site 44','0509CH67626 ',1,127,
'Victor P','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 5 For Site 58','0506CH6221',1,139,
'Victor P','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 4 For Site 58','0506CH6221',1,139,
'Victor P','NON FUMING ACID 15 LTR ','Site 58 for Client 21','Water Body 4 For Site 58','0508CH6010 ',1,139,
'Victor P','NON FUMING ACID 15 LTR ','Site 22 for Client 21','Water Body 49 For Site 22','0508CH6010 ',8,132,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 4 For Site 58','0506CH6420',3,139,
'Victor P','Drum Return','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6420',8,132,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',2,139,
'Victor P','Drum Return','Site 47 for Client 33','Water Body 48 For Site 47','0506CH6420',1,135,
'Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',35,139,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',35,139,
'Victor P','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',2,133,
'Victor P','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',2,133,
'Victor P','CALCIUM HARDNESS PLUS 10KG ','Site 61 for Client 65','Water Body 53 For Site 61','0529CHPK4897 ',1,137,
'Victor P','CALCIUM PLUS HARDNESS 4 KG ','Site 61 for Client 65','Water Body 53 For Site 61','052967414 ',1,137,
'Victor P','1l Clarifier','Site 61 for Client 65','Water Body 53 For Site 61','SEAD2036CPC',1,137,
'Victor P','1l Non Copper Algaecide ','Site 61 for Client 65','Water Body 53 For Site 61','SEAD2037CPA',1,137,
'Victor P','LIQUID CHLORINE 15 LTR ','Site 61 for Client 65','Water Body 53 For Site 61','0506CH6221',3,137,
'Victor P','Drum Return','Site 61 for Client 65','Water Body 53 For Site 61','0506CH6420',3,137,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',1,139,
'Victor P','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',4,139,
'Victor P','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 79 For Site 58','SEAD2037CPA',1,139,
'Victor P','CALCIUM HARDNESS PLUS 10KG ','Site 59 for Client 102','Water Body 17 For Site 59','0529CHPK4897 ',2,131,
'Victor P','1l Non Copper Algaecide ','Site 59 for Client 102','Water Body 17 For Site 59','SEAD2037CPA',1,131,
'Victor P','1l Clarifier','Site 22 for Client 21','Water Body 34 For Site 22','SEAD2036CPC',1,132,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',2,139,
'Garry R','1000 Litre Bulk Sodium Hypochlorite','Site 22 for Client 21','Water Body 49 For Site 22','CPSpecial',1,132,
'Garry R','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',3,136,
'Garry R','LIQUID CHLORINE 15 LTR ','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',2,136,
'Garry R','NON FUMING ACID 15 LTR ','Site 58 for Client 21','Water Body 79 For Site 58','0508CH6010 ',2,139,
'Garry R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',1,139,
'Garry R','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',25,139,
'Garry R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',25,139,
'Garry R','NON FUMING ACID 15 LTR ','Site 22 for Client 21','Water Body 49 For Site 22','0508CH6010 ',4,132,
'Garry R','Drum Return','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6420',4,132,
'Garry R','NON COPPER ALGAECIDE 20 LITRES ','Site 58 for Client 21','Water Body 7 For Site 58','0515CHPK-0099 ',1,139,
'Garry R','1l Clarifier','Site 58 for Client 21','Water Body 7 For Site 58','SEAD2036CPC',20,139,
'Garry R','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 14 For Site 58','SEAD2037CPA',2,139,
'Garry R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 7 For Site 58','0508CH6020',2,139,
'Garry R','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 16 For Site 58','CPSpecial2',1,139,
'Garry R','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 16 For Site 58','CPSpecial4',0.24,139,
'Garry R','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 15 For Site 58','CPSpecial4',0.23,139,
'Garry R','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 39 For Site 22','CPSpecial4',0.23,132,
'Garry R','Drum Return','Site 58 for Client 21','Water Body 7 For Site 58','0506CH6420',2,139,
'Garry R','LIQUID CHLORINE 15 LTR ','Site 74 for Client 114','Water Body 50 For Site 74','0506CH6221',1,125,
'Garry R','Drum Return','Site 74 for Client 114','Water Body 50 For Site 74','0506CH6420',1,125,
'Garry R','NON FUMING ACID 15 LTR ','Site 47 for Client 33','Water Body 48 For Site 47','0508CH6010 ',4,135,
'Garry R','BROMINE 10 KG ','Site 22 for Client 21','Water Body 33 For Site 22','0560CH1005 ',1,132,
'Garry R','BROMINE 10 KG ','Site 22 for Client 21','Water Body 38 For Site 22','0560CH1005 ',1,132,
'Garry R','Drum Return','Site 47 for Client 33','Water Body 48 For Site 47','0506CH6420',1,135,
'Garry R','LIQUID CHLORINE 15 LTR ','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6221',3,132,
'Garry R','Drum Return','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6420',2,132,
'Garry R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 10 For Site 58','0508CH6020',0.015,139,
'Garry R','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 23 For Site 58','CPSpecial2',1.5,139,
'Garry R','Chemical - Sodium Bicarbonate Per Kg','Site 58 for Client 21','Water Body 23 For Site 58','CPSpecial3',0.54,139,
'Garry R','1l Clarifier','Site 58 for Client 21','Water Body 23 For Site 58','SEAD2036CPC',0.2,139,
'Garry R','Chemical - Sodium Bicarbonate Per Kg','Site 58 for Client 21','Water Body 2 For Site 58','CPSpecial3',1,139,
'Victor P','Chemical - Sodium Bicarbonate Per Kg','Site 58 for Client 21','Water Body 2 For Site 58','CPSpecial3',1,139,
'Victor P','1l Clarifier','Site 58 for Client 21','Water Body 2 For Site 58','SEAD2036CPC',0.5,139,
'Victor P','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 2 For Site 58','SEAD2037CPA',1,139,
'Victor P','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',2,133,
'Victor P','NON FUMING ACID 15 LTR ','Site 20 for Client 19','Water Body 29 For Site 20','0508CH6010 ',1,133,
'Victor P','LIQUID CHLORINE 15 LTR ','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',4,133,
'Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 8 For Site 58','0506CH6221',1,139,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 8 For Site 58','0506CH6420',1,139,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',4,139,
'Victor P','LIQUID CHLORINE 15 LTR ','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',25,139,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',25,139,
'Victor P','NON FUMING ACID 15 LTR ','Site 20 for Client 19','Water Body 29 For Site 20','0508CH6010 ',1,133,
'Victor P','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',3,133,
'Victor P','LIQUID CHLORINE 15 LTR ','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',2,136,
'Victor P','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',2,136,
'Victor P','CALCIUM PLUS HARDNESS 4 KG ','Site 20 for Client 19','Water Body 29 For Site 20','052967414 ',2,133,
'Victor P','STABILISER 2.0KG ','Site 20 for Client 19','Water Body 29 For Site 20','0514CH4626 ',1,133,
'Victor P','PHOTOMETER PH RED TABS 250 PK ','Site 58 for Client 21','Water Body 79 For Site 58','0584511771BT ',2,139,
'Victor P','PHOTO DPD-1 HR TABS MD100/2 250 PK','Site 58 for Client 21','Water Body 79 For Site 58','0585511501BT ',2,139,
'Victor P','PHOTOMETER DPD NO3 250 PK ','Site 58 for Client 21','Water Body 79 For Site 58','0584511591BT ',2,139,
'Victor P','LIQUID CHLORINE 15 LTR','Site 43 for Client 57','Water Body 44 For Site 43','0506CH6221',2,138,
'Victor P','NON FUMING ACID 15 LTR ','Site 43 for Client 57','Water Body 44 For Site 43','0508CH6010 ',1,138,
'Victor P','Drum Return','Site 43 for Client 57','Water Body 44 For Site 43','0506CH6420',2,138,
'Victor P','NON FUMING ACID 15 LTR ','Site 58 for Client 21','Water Body 4 For Site 58','0508CH6010 ',1,139,
'Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 4 For Site 58','0506CH6221',1,139,
'Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 5 For Site 58','0506CH6221',1,139,
'Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 22 for Client 21','Water Body 31 For Site 22','0515CHPK-0099 ',1,132,
'Victor P','CLARIFIER 20 LITRES ','Site 22 for Client 21','Water Body 31 For Site 22','0518CHPK-01800 ',1,132,
'Victor P','HYDROCHLORIC ACID 20 LTR','Site 22 for Client 21','Water Body 31 For Site 22','0508CH6020',1,132,
'Victor P','Drum Return','Site 22 for Client 21','Water Body 31 For Site 22','0506CH6420',1,132,
'Victor P','Chemical - Sodium Bicarbonate Per Kg','Site 22 for Client 21','Water Body 36 For Site 22','CPSpecial3',6,132,
'Victor P','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 36 For Site 22','CPSpecial4',5,132,
'Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 22 for Client 21','Water Body 36 For Site 22','0515CHPK-0099 ',0.05,132,
'Victor P','CLARIFIER 20 LITRES ','Site 22 for Client 21','Water Body 36 For Site 22','0518CHPK-01800 ',0.05,132,
'Victor P','Chemical - Sodium Bicarbonate Per Kg','Site 22 for Client 21','Water Body 32 For Site 22','CPSpecial3',1,132,
'Victor P','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 32 For Site 22','CPSpecial4',2,132,
'Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 22 for Client 21','Water Body 32 For Site 22','0515CHPK-0099 ',0.025,132,
'Victor P','CLARIFIER 20 LITRES ','Site 22 for Client 21','Water Body 32 For Site 22','0518CHPK-01800 ',0.025,132,
'Victor P','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 6 For Site 58','CPSpecial2',5,139,
'Victor P','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 6 For Site 58','CPSpecial4',2,139,
'Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 58 for Client 21','Water Body 6 For Site 58','0515CHPK-0099 ',0.025,139,
'Victor P','CLARIFIER 20 LITRES ','Site 58 for Client 21','Water Body 6 For Site 58','0518CHPK-01800 ',0.025,139,
'Victor P','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 3 For Site 58','CPSpecial2',5,139,
'Victor P','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 3 For Site 58','CPSpecial4',1,139,
'Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 58 for Client 21','Water Body 3 For Site 58','0515CHPK-0099 ',0.025,139,
'Victor P','CLARIFIER 20 LITRES ','Site 58 for Client 21','Water Body 3 For Site 58','0518CHPK-01800 ',0.025,139,
'Victor P','Chemical - Calcium Chloride Per Kg','Site 22 for Client 21','Water Body 34 For Site 22','CPSpecial2',5,132,
'Victor P','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 34 For Site 22','CPSpecial4',1,132,
'Victor P','NON COPPER ALGAECIDE 20 LITRES ','Site 22 for Client 21','Water Body 34 For Site 22','0515CHPK-0099 ',0.025,132,
'Victor P','CLARIFIER 20 LITRES ','Site 22 for Client 21','Water Body 34 For Site 22','0518CHPK-01800 ',0.025,132,
'Victor P','BUFFER 2KG ','Site 82 for Client 115','Water Body 71 For Site 82','0509CH67626 ',3,136,
'Victor P','BUFFER 2KG ','Site 38 for Client 45','Water Body 66 For Site 38','0509CH67626 ',2,130,
'Victor P','1000 Litre Bulk Sodium Hypochlorite','Site 22 for Client 21','Water Body 49 For Site 22','CPSpecial',1,132,
'Victor P','SALT 20 KG','Site 58 for Client 21','Water Body 16 For Site 58','0565CHPS25',2,139,
'Victor P','SALT 20 KG','Site 58 for Client 21','Water Body 15 For Site 58','0565CHPS25',2,139,
'Victor P','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 4 For Site 58','0508CH6020',0.1,139,
'Victor P','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 8 For Site 58','0508CH6020',0.02,139,
'Victor P','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 11 For Site 58','0508CH6020',0.025,139,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',1,139,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 4 For Site 58','0506CH6420',1,139,
'Garry R','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 4 For Site 58','CPSpecial4',1,139,
'Garry R','1l Clarifier','Site 58 for Client 21','Water Body 4 For Site 58','SEAD2036CPC',0.5,139,
'Garry R','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 4 For Site 58','SEAD2037CPA',0.5,139,
'Garry R','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 5 For Site 58','SEAD2037CPA',0.5,139,
'Garry R','1l Clarifier','Site 58 for Client 21','Water Body 5 For Site 58','SEAD2036CPC',0.5,139,
'Garry R','1l Clarifier','Site 58 for Client 21','Water Body 11 For Site 58','SEAD2036CPC',0.2,139,
'Garry R','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',6,139,
'Garry R','Drum Return','Site 47 for Client 33','Water Body 48 For Site 47','0506CH6420',2,135,
'Garry R','Drum Return','Site 47 for Client 33','Water Body 26 For Site 47','0506CH6420',2,135,
'Garry R','NON FUMING ACID 15 LTR ','Site 47 for Client 33','Water Body 26 For Site 47','0508CH6010 ',2,135,
'Garry R','HYDROCHLORIC ACID 20 LTR','Site 83 for Client 121','Water Body 76 For Site 83','0508CH6020',1,128,
'Garry R','Drum Return','Site 83 for Client 121','Water Body 76 For Site 83','0506CH6420',3,128,
'Garry R','SPA SHOCK 500GMS ','Site 58 for Client 21','Water Body 3 For Site 58','0560CHS1099 ',1,139,
'Garry R','SPA SHOCK 500GMS ','Site 58 for Client 21','Water Body 7 For Site 58','0560CHS1099 ',1,139,
'Garry R','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',2,133,
'Garry R','LIQUID CHLORINE 15 LTR ','Site 83 for Client 121','Water Body 76 For Site 83','0506CH6221',2,128,
'Garry R','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',2,133,
'Garry R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',3,139,
'Garry R','SPA SHOCK 500GMS ','Site 66 for Client 76','Water Body 52 For Site 66','0560CHS1099 ',1,129,
'Garry R','Chemical - Calcium Chloride Per Kg','Site 66 for Client 76','Water Body 52 For Site 66','CPSpecial2',10,129,
'Garry R','1l Clarifier','Site 66 for Client 76','Water Body 52 For Site 66','SEAD2036CPC',1,129,
'Garry R','LIQUID CHLORINE 15 LTR','Site 66 for Client 76','Water Body 52 For Site 66','0506CH6221',4,129,
'Garry R','Drum Return','Site 66 for Client 76','Water Body 52 For Site 66','0506CH6420',4,129,
'Victor P','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',25,139,
'Victor P','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',25,139,
'Tony R','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 15 For Site 58','CPSpecial2',1,139,
'Tony R','Chemical-Cyanuric Acid Per Kg','Site 22 for Client 21','Water Body 39 For Site 22','CPSpecial4',0.14,132,
'Tony R','LIQUID CHLORINE 15 LTR ','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',3,136,
'Tony R','HYDROCHLORIC ACID 20 LTR','Site 82 for Client 115','Water Body 71 For Site 82','0508CH6020',1,136,
'Tony R','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',3,136,
'Tony R','NON FUMING ACID 15 LTR ','Site 22 for Client 21','Water Body 49 For Site 22','0508CH6010 ',4,132,
'Tony R','NON COPPER ALGAECIDE 20 LITRES ','Site 58 for Client 21','Water Body 7 For Site 58','0515CHPK-0099 ',1,139,
'Tony R','1l Clarifier','Site 58 for Client 21','Water Body 7 For Site 58','SEAD2036CPC',20,139,
'Tony R','Chemical - Calcium Chloride Per Kg','Site 58 for Client 21','Water Body 7 For Site 58','CPSpecial2',175,139,
'Tony R','Chemical-Cyanuric Acid Per Kg','Site 58 for Client 21','Water Body 7 For Site 58','CPSpecial4',14,139,
'Tony R','1l Non Copper Algaecide ','Site 58 for Client 21','Water Body 14 For Site 58','SEAD2037CPA',2,139,
'Tony R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 7 For Site 58','0508CH6020',2,139,
'Tony R','Drum Return','Site 58 for Client 21','Water Body 7 For Site 58','0506CH6420',2,139,
'Tony R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0508CH6020',1,139,
'Tony R','HYDROCHLORIC ACID 20 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0508CH6020',1,139,
'Tony R','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',2,133,
'Tony R','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',3,139,
'Tony R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',3,139,
'Tony R','Drum Return','Site 22 for Client 21','Water Body 49 For Site 22','0506CH6420',4,132,
'Tony R','Chemical - Sodium Bicarbonate Per Kg','Site 47 for Client 33','Water Body 26 For Site 47','CPSpecial3',25,135,
'Tony R','Chemical - Calcium Chloride Per Kg','Site 47 for Client 33','Water Body 48 For Site 47','CPSpecial2',100,135,
'Tony R','NON FUMING ACID 15 LTR ','Site 47 for Client 33','Water Body 48 For Site 47','0508CH6010 ',3,135,
'Tony R','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',4,133,
'Tony R','Chemical - Calcium Chloride Per Kg','Site 47 for Client 33','Water Body 48 For Site 47','CPSpecial2',100,135,
'Tony R','LIQUID CHLORINE 15 LTR','Site 81 for Client 114','Water Body 69 For Site 81','0506CH6221',1,134,
'Tony R','Drum Return','Site 81 for Client 114','Water Body 69 For Site 81','0506CH6420',1,134,
'Tony R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',2,139,
'Tony R','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',4,133,
'Tony R','SPA SHOCK 500GMS ','Site 58 for Client 21','Water Body 10 For Site 58','0560CHS1099 ',1,139,
'Tony R','1000 Litre Bulk Sodium Hypochlorite','Site 22 for Client 21','Water Body 49 For Site 22','CPSpecial',1,132,
'Tony R','HYDROCHLORIC ACID 20 LTR','Site 82 for Client 115','Water Body 71 For Site 82','0508CH6020',1,136,
'Tony R','Drum Return','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6420',3,136,
'Tony R','LIQUID CHLORINE 15 LTR','Site 82 for Client 115','Water Body 71 For Site 82','0506CH6221',2,136,
'Tony R','BUFFER TA INCREASER 10KG','Site 82 for Client 115','Water Body 71 For Site 82','0509CH67134',1,136,
'Tony R','LIQUID CHLORINE 15 LTR','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6221',20,139,
'Tony R','Drum Return','Site 58 for Client 21','Water Body 79 For Site 58','0506CH6420',20,139,
'Tony R','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',4,133,
'Tony R','STABILISER 2.0KG ','Site 81 for Client 114','Water Body 69 For Site 81','0514CH4626 ',1,134,
'Tony R','NON FUMING ACID 15 LTR ','Site 20 for Client 19','Water Body 29 For Site 20','0508CH6010 ',2,133,
'Tony R','LIQUID CHLORINE 15 LTR','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6221',2,133,
'Tony R','1l Non Copper Algaecide ','Site 20 for Client 19','Water Body 29 For Site 20','SEAD2037CPA',1,133,
'Tony R','1l Clarifier','Site 20 for Client 19','Water Body 29 For Site 20','SEAD2036CPC',1,133,
'Tony R','Drum Return','Site 20 for Client 19','Water Body 29 For Site 20','0506CH6420',4,133)

The other related tables are:
INSERT INTO [dbo].[ShtStock]
([Barcode]
,[PN]
,[Description])
VALUES
('9317370908074','0506CH6221','LIQUID CHLORINE 15 LTR '),
('9319737142619','0508CH6010 ','NON FUMING ACID 15 LTR '),
('9319737123403','0509CH67626 ','BUFFER 2KG '),
('9319737080102','0512CH67686 ','AQUACHLOR PH UP 2KG '),
('9319737432208','0514CH4626 ','STABILISER 2.0KG '),
('SEAQ0515CHAG1935','0515CHAG1935 ','SODIUM THIOSULPHATE PER KG '),
('SEAQ0515CHMET','0515CHMETRID ','METAL RID STAIN REMOVER 2KG '),
('SEAQ0515CHPK0099','0515CHPK-0099 ','NON COPPER ALGAECIDE 20 LITRES '),
('9319737410121','0518CH66166 ','POOL WATER CLARIFIER 1 LTR '),
('SEAQ0518CHK01800','0518CHPK-01800 ','CLARIFIER 20 LITRES '),
('SEAQ520CH91020','0520CH91020 ','CARTRIDGE CLEANER '),
('9319737489943','052967414 ','CALCIUM PLUS HARDNESS 4 KG '),
('9319737489738','0529CHPK4897 ','CALCIUM HARDNESS PLUS 10KG '),
('9334024002346','0540CH1006 ','POOL MAGIC 10 KG FINE GRADE CHLORIN'),
('9319737602557','0560CH1005 ','BROMINE 10 KG '),
('9319737046191','0560CHS1099 ','SPA SHOCK 500GMS '),
('4049991090214','0584511051BT ','PHOTOMETER DPD NO 1 TABLETS 250 PK '),
('4049991057064','0584511082BT ','PHOTOMETER DPD NO3 TABLETS PK 500 '),
('4049991091471','0584511371BT ','PHOTOMETER CYNURIC STAB TABS 250PK'),
('4049991084046','0584511591BT ','PHOTOMETER DPD NO3 250 PK '),
('4049991059075','0584511770BT ','PHOTOMETER PHENOL RED TABS 100PK '),
('4049991059143','0584511771BT ','PHOTOMETER PH RED TABS 250 PK '),
('4049991059181','0584511772BT ','PHOTOMETER PHENOL RED TAB PK 500 '),
('4049991091846','0584513211BT ','PHOTOMETER T/A TABS 250 PK '),
('4049991110172','0584513241BT ','PHOTOMETER ALKA M TABS PKT 250 HR '),
('4049991093512','0584517761BT ','CALCIO COMBO 1 & 2 TABS 100 PACK '),
('4049991093536','0584517762 ','CALCIO COMBO PK1& 2 250ea PHOTO '),
('4049991084015','0585511501BT ','PHOTO DPD-1 HR TABS MD100/2 250 PK'),
('4049991084039','0585511590BT ','PHOTO DPD-3 HR TABS MD100/2 -100PK '),
('4049991084046','0585511591BT ','PHOTO DPD-3-HR TABS-MD100/2 250PK '),
('656055000016','0515CH4621','POOL BLOCKS (5) 1.5KG'),
('65605500002','0515CH4622','POOL DROPS PLUS H/D ALGICIDE'),
('656055000061','0560CHS1101','OXYCLEAR SPA/POOL SHOCK 1.5kg'),
('656055000122','0515SSR100','SERIOUS STAIN REMOVER'),
('9312113001159','0515CHAMS210','MULTI STAIN REMOVER LO/CHLO'),
('9312113002392','0534AM7075','TILE CLEANER 750MM SPRAY'),
('9312113004419','0520CHACM200','MAXI FLOC PLUS'),
('9312113010052','0520BAF500','ANTIFOAM'),
('9312113020204','0515AMV001','STARVER X REMOVER PHOS 1.0 LTR'),
('9315325000750','0565CHPS25','SALT 20 KG'),
('9319737001008','0540CH1003','POOL/K 10 KG L/RESIDUE CHLORINE'),
('9319737028531','0560CHS1009','BOOST & BALANCE 1.0 KG'),
('9319737086357','0520CH5003301C','SAND CLEANER'),
('9319737090224','0508CH6020','HYDROCHLORIC ACID 20 LTR'),
('9319737180024','0514CH4626','STABILISER 2.0KG'),
('9319737200104','0507CH67093','1 KG CHLORINE TABLETS'),
('9319737210103','0515AQ-331010','AVERT DE-SCALER 1 LTR'),
('9319737250055','0514CH317','STABILISER 25 KG'),
('9319737270022','0509CH67626','BUFFER 2KG'),
('9319737320024','0529CHPK4898','CALCIUM PLUS 2 KG'),
('9319737330047','052967414','CALCIUM PLUS HARDNESS 4 KG'),
('9319737410121','0518CH66166','POOL WATER CLARIFIER 1 LTR'),
('9319737500501','0508CH6005','HYDROCHLORIC ACID 5 LTR'),
('9319737534018','0520CH4628','1 LITRE CELL CLEANER'),
('9319737601505','0506CH6221','LIQUID CHLORINE 15 LTR'),
('9319737602557','0560CH1005','BROMINE 10 KG'),
('9319737800205','0540CH1001','POOL/K 2KG L/RESIDUE CHLORINE'),
('9319737800403','0509CH67685','BUFFER TA INCREASER 4KG'),
('9319737801509','0508CH6010','NON FUMING ACID 15 LTR'),
('9319737900400','0540CH1002','POOL/K 4KG L/ RESIDUE CHLORINE'),
('9319737123205','0509CH67134','BUFFER TA INCREASER 10KG'),
('9334024002346','0540CH1006','POOL MAGIC 10 KG FINE GRADE CHLORIN'),
('9339941005005','0560CHS1001','SPA BROMINE 1KG'),
('9342344000461','0560CHS1002','SPA BROMINE 2KG'),
('SEAQ0506CHSell','0506CH6420','Drum Deposit'),
('SEAQ0506CHPlus','0506CH6420','Drum Return'),
('9319737090507','0506CH6221','LIQUID CHLORINE 15 LTR');

And

INSERT INTO [dbo].[ShtStockDrumDeposit]
           ([ShtP])
     VALUES
           ('0507CH6220'),
		   ('0508CH6020'),
		   ('0506CH6221'),
		   ('0508CH6010');

I am so new to SQL I just don't even know if this is a good approach or if I should revert to the old recordset approach?

yosiasz · February 10, 2020, 6:16am

Hey again

Did you try the sample data you provided on a local SQL server?

For example the section for

INSERT INTO [dbo].[BarCodesBetweenDates

yosiasz · February 10, 2020, 6:36am

Also question for you on the design consideration of following data

'9317370908074','0506CH6221','LIQUID CHLORINE 15 LTR '

If tomorrow you get audited by the government or the local municipality or some government entity, or you needed to provide a report on your inventory

How would you know how much
LIQUID CHLORINE you have in stock?

New_Creative · February 10, 2020, 7:04am

This is not an accounting program. It interfaces with an accounting program, manually at the moment, however likely will connect online later. The accounting program is designed to manage stock levels.

I dont know how to describe this programs function. It is like another person helping me with lots of little bits. Without it, and its previous versions I simply couldnt do the job required.

Literally all this specific bit does is record the delivery of product to the water body. This then is used for reports for clients who want to know consumption by Water body and provides input to the accounting software as in stock used.

it also provides input for accounting as in hours of labour, orders placed and then goes into engineering design as well...for interest it even knows how to calculate the lay up for a fiberglass pressure vessel as I did that many years ago and not something I want to loose.

Its a very strange application. Thats why I said there was 40 odd interelated tables to get the stock used list out.