J
JRough
I have a make table query that creates a large dataset called
2007_data_source. In that query there are only field values no
calculated fields. I need a calculated field called amt_paid. Here
is the calc:
AMT_PAID: IIf([ADJCODE]="#C",[ADJUST]+[NETPLUSINT],[NETPLUSINT])
However in my next step in the process I have another query that
references this amt_paid field twice. First it sums the amt_paid as a
calculation. Then it sums the field (originally I had it in the make
table query but I took it out since I have all the fields to do the
calculation later.. This is in a separate query.
SELECT [2007_DATA_SOURCE].MEMBID, Sum(IIf([ADJCODE]="#C",[ADJUST]+
[NETPLUSINT],[NETPLUSINT])) AS AMT_PAID,
IIf([AMT_PAID]>100000,"Submission","Early Notice") AS SUB_TYPE INTO
2007_SUBMISSIONS
FROM 2007_DATA_SOURCE
GROUP BY [2007_DATA_SOURCE].MEMBID
HAVING (((Sum(IIf([ADJCODE]="#C",[ADJUST]+[NETPLUSINT],
[NETPLUSINT])))>75000));
My question is, would it be better to do the amt_paid field
calculation in the make table query first. Then in this other select
query should I do the amt_paid calculations twice or leave the
amt_paid calc in the data_source table from the make table query. I
am just running these queries over and I make a new table every month
so the rule about not having calculations in the source table may not
apply here but the queries do take a long time to run.
thanks,
2007_data_source. In that query there are only field values no
calculated fields. I need a calculated field called amt_paid. Here
is the calc:
AMT_PAID: IIf([ADJCODE]="#C",[ADJUST]+[NETPLUSINT],[NETPLUSINT])
However in my next step in the process I have another query that
references this amt_paid field twice. First it sums the amt_paid as a
calculation. Then it sums the field (originally I had it in the make
table query but I took it out since I have all the fields to do the
calculation later.. This is in a separate query.
SELECT [2007_DATA_SOURCE].MEMBID, Sum(IIf([ADJCODE]="#C",[ADJUST]+
[NETPLUSINT],[NETPLUSINT])) AS AMT_PAID,
IIf([AMT_PAID]>100000,"Submission","Early Notice") AS SUB_TYPE INTO
2007_SUBMISSIONS
FROM 2007_DATA_SOURCE
GROUP BY [2007_DATA_SOURCE].MEMBID
HAVING (((Sum(IIf([ADJCODE]="#C",[ADJUST]+[NETPLUSINT],
[NETPLUSINT])))>75000));
My question is, would it be better to do the amt_paid field
calculation in the make table query first. Then in this other select
query should I do the amt_paid calculations twice or leave the
amt_paid calc in the data_source table from the make table query. I
am just running these queries over and I make a new table every month
so the rule about not having calculations in the source table may not
apply here but the queries do take a long time to run.
thanks,