>>>Hi everybody,
>>>
>>>I'd like to show distribution of accounts based on queue names or based on clients or based on product line or based on status. The distribution is going to be by department. Right now all the accounts are for Outpatient department ("O"). When I'm using distribution by Queues, I start from queues and left join to Trans file. Queues table has departments in it. In other cases I decided to show only actual distribution.
>>>
>>>So, this is my original code
>>>

>>>insert into curStats (cFullName, I, H, M, O, nTotal) ;
>>>		select cFullName, I, H, M, O, cast(I+H+M+O as n(5)) as nTotal from curStatistics

>>>
>>>But in cases other than Queues I may have less fields. What would be the best approach for me to overcome the problem? Build the insert command on the fly?
>>>
>>>Thanks in advance.
>>
>>I think with APPEND BLANK, followed by REPLACE, it is easier to conditionally set, or not set, a specific field.
>
>
>This works quite nicely:
>

>lparameters tnOption
>local loMessage, loWait, lcNotify, lcInsertCmd
>local lnI, lnH, lnM, lnO, lnTotal
>store 0 to lnI, lnH, lnM, lnO, lnTotal
>
>lcNotify = set('notify')
>set notify on
>set talk on
>set talk window
>
>with thisform
>
>	if not .lSilentMode
>		loMessage = createobject("cWaitMessage", "Getting Data...")
>		loWait = createobject("cMessage", "Getting Data...")
>		.WaitMode(.t.)
>	endif
>
>	do case
>	case m.tnOption = 1 && by Queue
>		GetQueuesDistribution()
>	case m.tnOption = 2 && by Clients
>		GetClientsDistribution()
>	case m.tnOption = 3 && by Product Line
>		GetProductLineDistribution()
>	case m.tnOption = 4 && by Status
>		GetStatusDistribution()
>	endcase
>
>	if m.lcNotify = 'OFF'
>		set notify off
>	endif
>	set talk off
>
>	.oXTab.RunXTab()
>
>	zap in curStats
>
>	text TO lcInsertCmd TEXTMERGE NOSHOW PRETEXT 7
>		insert into curStats (cFullName, I, H, M, O, nTotal)
>			select cFullName,
>			<<IIF(TYPE('CurStatistics.I')='N','I','00000')>> as I,
>			<<IIF(TYPE('CurStatistics.H')='N','H','00000')>> as H,
>			<<IIF(TYPE('CurStatistics.M')='N','M','00000')>> as M,
>			<<IIF(TYPE('CurStatistics.O')='N','O','00000')>> as O,
>			 cast(<<IIF(TYPE('CurStatistics.I')='N','I+','') + IIF(TYPE('CurStatistics.H')='N','H+','') + IIF(TYPE('CurStatistics.M')='N','M+','') + IIF(TYPE('CurStatistics.O')='N','O','0')>>
>			 as n(5)) as nTotal from curStatistics
>	ENDTEXT
>
>	lcInsertCmd = chrtran(m.lcInsertCmd, chr(13) + chr(10)," ")
>	&lcInsertCmd
>
>	select curStats
>
>	sum I, H, M, O, nTotal to lnI, lnH, lnM, lnO, lnTotal
>
>	.txtHTotal.value = m.lnH
>	.txtITotal.value = m.lnI
>	.txtMTotal.value = m.lnM
>	.txtOTotal.value = m.lnO
>	.txtTotal.value = m.lnTotal
>
>	go top in curStats
>
>	.WaitMode(.f.)
>	.refresh()
>endwith
>

You can use some "Underground surrogate variables"

lparameters tnOption
local loMessage, loWait, lcNotify, lcInsertCmd
local lnI, lnH, lnM, lnO, lnTotal
store 0 to lnI, lnH, lnM, lnO, lnTotal

lcNotify = set('notify')
set notify on
set talk on
set talk window

with thisform

	if not .lSilentMode
		loMessage = createobject("cWaitMessage", "Getting Data...")
		loWait = createobject("cMessage", "Getting Data...")
		.WaitMode(.t.)
	endif

	do case
	case m.tnOption = 1 && by Queue
		GetQueuesDistribution()
	case m.tnOption = 2 && by Clients
		GetClientsDistribution()
	case m.tnOption = 3 && by Product Line
		GetProductLineDistribution()
	case m.tnOption = 4 && by Status
		GetStatusDistribution()
	endcase

	if m.lcNotify = 'OFF'
		set notify off
	endif
	set talk off

	.oXTab.RunXTab()

	zap in curStats

	LOCAL i,h,m,o	&& surrogate fields
	STORE 0 TO i,h,m,o
	
	insert into curStats (cFullName, I, H, M, O, nTotal);
		SELECT I,H,M,O, I+H+M+O FROM curStatistics && ONE CHAR FIELD'S NAME is a terrible choice

	select curStats

	sum I, H, M, O, nTotal ;
		to	.txtITotal.value	;
		,	.txtHTotal.value	;
		,	.txtMTotal.value	;
		,	.txtOTotal.value	;
		,	.txtTotal.value

	go top

	.WaitMode(.f.)
	.refresh()
endwith