if file('t_orders.dbf')
 drop table t_orders
endif

if not file('t_orders.dbf')
   create table t_orders  (id autoinc, t_size_order numeric (2),;
   t_size character(13),t_color character(8),qty numeric(10))
endif

tshirt1 = new query()
tshirt1.sql = 'select * from TShirtSorted where quantity > 0  order by shirtsize,color'
tshirt1.active = true

t_orders1 = new query()
t_orders1.sql =  'select * from t_orders'
t_orders1.active = true

do while not tshirt1.rowset.endofset
   cColor = tshirt1.rowset.fields['Color'].value
 
	if tshirt1.rowset.fields['ShirtSize'].value = 'XXS'
		cSize = 'XX-Small'
		cSizeOrder = 1
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'XS'
		cSize = 'X-Small'
		cSizeOrder = 2
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'SM'
		cSize = 'Small'
		cSizeOrder = 3
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'MD'
		cSize = 'Medium'
		cSizeOrder = 4
   elseif tshirt1.rowset.fields['ShirtSize'].value = 'LG'
		cSize = 'Large'
		cSizeOrder = 5
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'XL'
		cSize = 'X-Large'
		cSizeOrder = 6
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'XXL'
		cSize = 'XX-Large'
		cSizeOrder = 7
	elseif tshirt1.rowset.fields['ShirtSize'].value = '3XL'
		cSize = '3X-Large'
		cSizeOrder = 8
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'YS'
		cSize = 'Youth Small'
		cSizeOrder = 9
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'YM'
		cSize = 'Youth Medium'
		cSizeOrder = 10
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'YL'
		cSize = 'Youth Large'
		cSizeOrder = 11
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'WS'
		cSize = 'Women Small'
		cSizeOrder = 12
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'WM'
		cSize = 'Women Medium'
		cSizeOrder = 13
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'WL'
		cSize = 'Women Large'
		cSizeOrder = 14
	elseif tshirt1.rowset.fields['ShirtSize'].value = 'WXL'
		cSize = 'Women X-Large'
		cSizeOrder = 15
	endif
	
	nQty = tshirt1.rowset.fields['Quantity'].value

	if not  t_orders1.rowset.applyLocate( "t_size = '" + cSize+ "' and t_color = '" + cColor +"'"  )
      //no record in _ordered table.  Add it
      t_orders1.rowset.beginAppend()
      t_orders1.rowset.fields['t_size' ].value = cSize
      t_orders1.rowset.fields['t_size_order' ].value = cSizeOrder
		t_orders1.rowset.fields['t_color' ].value = cColor
      t_orders1.rowset.fields['qty' ].value = nQty
      t_orders1.rowset.save()
   elseif   t_orders1.rowset.applyLocate( "t_size = '" +cSize +"' and t_color = '"+cColor +"'" )
      //size and color already in table. Increment count.
      t_orders1.rowset.fields['qty' ].value += nQty
      t_orders1.rowset.save()        
   elseif   t_orders1.rowset.applyLocate( "t_size = '" +cSize +"' and not t_color = '"+cColor+"'"  )
      // size  already in t_orders table but not color. Add it
      t_orders1.rowset.beginAppend()
      t_orders1.rowset.fields['t_size' ].value = cSize
      t_orders1.rowset.fields['t_size_order' ].value = cSizeOrder
		t_orders1.rowset.fields['t_color' ].value = cColor
      t_orders1.rowset.fields['qty' ].value = nQty
      t_orders1.rowset.save()
   elseif  not  t_orders1.rowset.applyLocate( " t_size = '" +cSize +"'"  )    
      //size not in table.  Add it
      t_orders1.rowset.beginAppend()
      t_orders1.rowset.fields['t_size' ].value = cSize
      t_orders1.rowset.fields['t_size_order' ].value = cSizeOrder
		t_orders1.rowset.fields['t_color' ].value = cColor
      t_orders1.rowset.fields['qty' ].value = nQty
      t_orders1.rowset.save()
   endif
	
   tshirt1.rowset.next()
 enddo
   
tshirt1.active = false
t_orders1.active = false
msgbox( "The T_orders table should now have data in it" )