Revise an Append Query, or Change to an Update Query

  • Thread starter Thread starter MJ
  • Start date Start date
M

MJ

I have a database which has worked well for a long time, until a recent
change request. Now this change has identified a flaw in the original coding
logic, can someone help me with this?

The database processes an input file and produces an output table which is
copied into Excel for graphing. The output table is where the problem is.
The present logic wipes the table completely and then rebuilds the table
using append queries. This works well EXCEPT when one of the ranges is ZERO,
the current queries do not create a 0 entry for that range.

Q1: Is there a simple way to modify the append query to create the range
"zero" row when it occurs? (sample below)

Query...

INSERT INTO tblRefundOutcomeCounts ( AlphaSplit, AgeGroup, Cases )
SELECT ATBC.AlphaSplit, IIf([AgingRange]="
0-30","0-60",IIf([AgingRange]=" 31-60","0-60",IIf([AgingRange]="
61-90","61-180",IIf([AgingRange]="
91-120","61-180",IIf([AgingRange]="121-150","61-180",IIf([AgingRange]="151-180","61-180",IIf([AgingRange]="181-365","181+","181+")))))))
AS AgeRange, Count(ATBC.RefundBalance) AS CountOfRefundBalance
FROM ATBC
GROUP BY ATBC.AlphaSplit, IIf([AgingRange]="
0-30","0-60",IIf([AgingRange]=" 31-60","0-60",IIf([AgingRange]="
61-90","61-180",IIf([AgingRange]="
91-120","61-180",IIf([AgingRange]="121-150","61-180",IIf([AgingRange]="151-180","61-180",IIf([AgingRange]="181-365","181+","181+")))))));

Table... (Note the "*" on the left of rows where there is no NEW entry)

Original NEW
AlphaSplit AgeGroup Cases AlphaSplit AgeGroup Cases
A, I, ... 0-60 633 A, B, ... 0-60 799
A, I, ... 181+ 484 A, B, ... 181+ 323
A, I, ... 61-180 566 A, B, ... 61-180 1202
A, I, ...$ 0-60 -755013 A, B, ...$ 0-60 -1513847.22
A, I, ...$ 181+ -292173 A, B, ...$ 181+ -262282.01
A, I, ...$ 61-180 -586329.7 A, B, ...$ 61-180 -1015011.82
B, C 0-60 653 G, P, X 0-60 12
* B, C 181+ 278
B, C 61-180 1047 G, P, X 61-180 3
B, C $ 0-60 -1140884.76 G, P, X $ 0-60 -6111.71
* B, C $ 181+ -217864.5
B, C $ 61-180 -763321.65 G, P, X $ 61-180 -203.7

Q2: Or is it time to change the logic from append to an update query? If
so, how would you change it to update the table?

Thank you in advance for your time and assistance.
 
What should go in place of the X's below? What can the machine cue off of?
A, I, ...$ 61-180 -586329.7 A, B, ...$ 61-180 -1015011.82
B, C 0-60 653 G, P, X 0-60 12
* B, C 181+ 278 XXXXX XXX XX
B, C 61-180 1047 G, P, X 61-180 3
B, C $ 0-60 -1140884.76 G, P, X $ 0-60 -6111.71
* B, C $ 181+ -217864.5 XXXXXX XXXX XXXXXX
B, C $ 61-180 -763321.65 G, P, X $ 61-180 -203.7

--
Build a little, test a little.


MJ said:
I have a database which has worked well for a long time, until a recent
change request. Now this change has identified a flaw in the original coding
logic, can someone help me with this?

The database processes an input file and produces an output table which is
copied into Excel for graphing. The output table is where the problem is.
The present logic wipes the table completely and then rebuilds the table
using append queries. This works well EXCEPT when one of the ranges is ZERO,
the current queries do not create a 0 entry for that range.

Q1: Is there a simple way to modify the append query to create the range
"zero" row when it occurs? (sample below)

Query...

INSERT INTO tblRefundOutcomeCounts ( AlphaSplit, AgeGroup, Cases )
SELECT ATBC.AlphaSplit, IIf([AgingRange]="
0-30","0-60",IIf([AgingRange]=" 31-60","0-60",IIf([AgingRange]="
61-90","61-180",IIf([AgingRange]="
91-120","61-180",IIf([AgingRange]="121-150","61-180",IIf([AgingRange]="151-180","61-180",IIf([AgingRange]="181-365","181+","181+")))))))
AS AgeRange, Count(ATBC.RefundBalance) AS CountOfRefundBalance
FROM ATBC
GROUP BY ATBC.AlphaSplit, IIf([AgingRange]="
0-30","0-60",IIf([AgingRange]=" 31-60","0-60",IIf([AgingRange]="
61-90","61-180",IIf([AgingRange]="
91-120","61-180",IIf([AgingRange]="121-150","61-180",IIf([AgingRange]="151-180","61-180",IIf([AgingRange]="181-365","181+","181+")))))));

Table... (Note the "*" on the left of rows where there is no NEW entry)

Original NEW
AlphaSplit AgeGroup Cases AlphaSplit AgeGroup Cases
A, I, ... 0-60 633 A, B, ... 0-60 799
A, I, ... 181+ 484 A, B, ... 181+ 323
A, I, ... 61-180 566 A, B, ... 61-180 1202
A, I, ...$ 0-60 -755013 A, B, ...$ 0-60 -1513847.22
A, I, ...$ 181+ -292173 A, B, ...$ 181+ -262282.01
A, I, ...$ 61-180 -586329.7 A, B, ...$ 61-180 -1015011.82
B, C 0-60 653 G, P, X 0-60 12
* B, C 181+ 278
B, C 61-180 1047 G, P, X 61-180 3
B, C $ 0-60 -1140884.76 G, P, X $ 0-60 -6111.71
* B, C $ 181+ -217864.5
B, C $ 61-180 -763321.65 G, P, X $ 61-180 -203.7

Q2: Or is it time to change the logic from append to an update query? If
so, how would you change it to update the table?

Thank you in advance for your time and assistance.
Click to expand...
 
Karl,

B, C 0-60 653 G, P, X 0-60 12
* B, C 181+ 278 G, P, X 181+ 0
B, C 61-180 1047 G, P, X 61-180 3
B, C $ 0-60 -1140884.76 G, P, X $ 0-60 -6111.71
* B, C $ 181+ -217864.5 G, P, X $ 181+ 0
B, C $ 61-180 -763321.65 G, P, X $ 61-180 -203.7

Should be populated with the "AlphaSplit"; the "AgeGroup"; and 0 if there is
no reported values for that alphasplit and agegroup during the run.

Apologize I didn't make that clear in my first post.

--

MJ


KARL DEWEY said:
What should go in place of the X's below? What can the machine cue off of?
A, I, ...$ 61-180 -586329.7 A, B, ...$ 61-180 -1015011.82
B, C 0-60 653 G, P, X 0-60 12
* B, C 181+ 278 XXXXX XXX XX
B, C 61-180 1047 G, P, X 61-180 3
B, C $ 0-60 -1140884.76 G, P, X $ 0-60 -6111.71
* B, C $ 181+ -217864.5 XXXXXX XXXX XXXXXX
B, C $ 61-180 -763321.65 G, P, X $ 61-180 -203.7

--
Build a little, test a little.


MJ said:
I have a database which has worked well for a long time, until a recent
change request. Now this change has identified a flaw in the original coding
logic, can someone help me with this?

The database processes an input file and produces an output table which is
copied into Excel for graphing. The output table is where the problem is.
The present logic wipes the table completely and then rebuilds the table
using append queries. This works well EXCEPT when one of the ranges is ZERO,
the current queries do not create a 0 entry for that range.

Q1: Is there a simple way to modify the append query to create the range
"zero" row when it occurs? (sample below)

Query...

INSERT INTO tblRefundOutcomeCounts ( AlphaSplit, AgeGroup, Cases )
SELECT ATBC.AlphaSplit, IIf([AgingRange]="
0-30","0-60",IIf([AgingRange]=" 31-60","0-60",IIf([AgingRange]="
61-90","61-180",IIf([AgingRange]="
91-120","61-180",IIf([AgingRange]="121-150","61-180",IIf([AgingRange]="151-180","61-180",IIf([AgingRange]="181-365","181+","181+")))))))
AS AgeRange, Count(ATBC.RefundBalance) AS CountOfRefundBalance
FROM ATBC
GROUP BY ATBC.AlphaSplit, IIf([AgingRange]="
0-30","0-60",IIf([AgingRange]=" 31-60","0-60",IIf([AgingRange]="
61-90","61-180",IIf([AgingRange]="
91-120","61-180",IIf([AgingRange]="121-150","61-180",IIf([AgingRange]="151-180","61-180",IIf([AgingRange]="181-365","181+","181+")))))));

Table... (Note the "*" on the left of rows where there is no NEW entry)

Original NEW
AlphaSplit AgeGroup Cases AlphaSplit AgeGroup Cases
A, I, ... 0-60 633 A, B, ... 0-60 799
A, I, ... 181+ 484 A, B, ... 181+ 323
A, I, ... 61-180 566 A, B, ... 61-180 1202
A, I, ...$ 0-60 -755013 A, B, ...$ 0-60 -1513847.22
A, I, ...$ 181+ -292173 A, B, ...$ 181+ -262282.01
A, I, ...$ 61-180 -586329.7 A, B, ...$ 61-180 -1015011.82
B, C 0-60 653 G, P, X 0-60 12
* B, C 181+ 278
B, C 61-180 1047 G, P, X 61-180 3
B, C $ 0-60 -1140884.76 G, P, X $ 0-60 -6111.71
* B, C $ 181+ -217864.5
B, C $ 61-180 -763321.65 G, P, X $ 61-180 -203.7

Q2: Or is it time to change the logic from append to an update query? If
so, how would you change it to update the table?

Thank you in advance for your time and assistance.
Click to expand...
Click to expand...
 
UNTESTED UNTESTED
Make a copy and try changing this part of your query --
Count(ATBC.RefundBalance) AS CountOfRefundBalance
FROM ATBC
to this ---
IIF(ATBC.RefundBalance Is Null, 0, Count(ATBC.RefundBalance)) AS
CountOfRefundBalance
FROM ATBC

--
Build a little, test a little.


MJ said:
Karl,

B, C 0-60 653 G, P, X 0-60 12
* B, C 181+ 278 G, P, X 181+ 0
B, C 61-180 1047 G, P, X 61-180 3
B, C $ 0-60 -1140884.76 G, P, X $ 0-60 -6111.71
* B, C $ 181+ -217864.5 G, P, X $ 181+ 0
B, C $ 61-180 -763321.65 G, P, X $ 61-180 -203.7

Should be populated with the "AlphaSplit"; the "AgeGroup"; and 0 if there is
no reported values for that alphasplit and agegroup during the run.

Apologize I didn't make that clear in my first post.

--

MJ


KARL DEWEY said:
What should go in place of the X's below? What can the machine cue off of?
A, I, ...$ 61-180 -586329.7 A, B, ...$ 61-180 -1015011.82
B, C 0-60 653 G, P, X 0-60 12
* B, C 181+ 278 XXXXX XXX XX
B, C 61-180 1047 G, P, X 61-180 3
B, C $ 0-60 -1140884.76 G, P, X $ 0-60 -6111.71
* B, C $ 181+ -217864.5 XXXXXX XXXX XXXXXX
B, C $ 61-180 -763321.65 G, P, X $ 61-180 -203.7

--
Build a little, test a little.


MJ said:
I have a database which has worked well for a long time, until a recent
change request. Now this change has identified a flaw in the original coding
logic, can someone help me with this?

The database processes an input file and produces an output table which is
copied into Excel for graphing. The output table is where the problem is.
The present logic wipes the table completely and then rebuilds the table
using append queries. This works well EXCEPT when one of the ranges is ZERO,
the current queries do not create a 0 entry for that range.

Q1: Is there a simple way to modify the append query to create the range
"zero" row when it occurs? (sample below)

Query...

INSERT INTO tblRefundOutcomeCounts ( AlphaSplit, AgeGroup, Cases )
SELECT ATBC.AlphaSplit, IIf([AgingRange]="
0-30","0-60",IIf([AgingRange]=" 31-60","0-60",IIf([AgingRange]="
61-90","61-180",IIf([AgingRange]="
91-120","61-180",IIf([AgingRange]="121-150","61-180",IIf([AgingRange]="151-180","61-180",IIf([AgingRange]="181-365","181+","181+")))))))
AS AgeRange, Count(ATBC.RefundBalance) AS CountOfRefundBalance
FROM ATBC
GROUP BY ATBC.AlphaSplit, IIf([AgingRange]="
0-30","0-60",IIf([AgingRange]=" 31-60","0-60",IIf([AgingRange]="
61-90","61-180",IIf([AgingRange]="
91-120","61-180",IIf([AgingRange]="121-150","61-180",IIf([AgingRange]="151-180","61-180",IIf([AgingRange]="181-365","181+","181+")))))));

Table... (Note the "*" on the left of rows where there is no NEW entry)

Original NEW
AlphaSplit AgeGroup Cases AlphaSplit AgeGroup Cases
A, I, ... 0-60 633 A, B, ... 0-60 799
A, I, ... 181+ 484 A, B, ... 181+ 323
A, I, ... 61-180 566 A, B, ... 61-180 1202
A, I, ...$ 0-60 -755013 A, B, ...$ 0-60 -1513847.22
A, I, ...$ 181+ -292173 A, B, ...$ 181+ -262282.01
A, I, ...$ 61-180 -586329.7 A, B, ...$ 61-180 -1015011.82
B, C 0-60 653 G, P, X 0-60 12
* B, C 181+ 278
B, C 61-180 1047 G, P, X 61-180 3
B, C $ 0-60 -1140884.76 G, P, X $ 0-60 -6111.71
* B, C $ 181+ -217864.5
B, C $ 61-180 -763321.65 G, P, X $ 61-180 -203.7

Q2: Or is it time to change the logic from append to an update query? If
so, how would you change it to update the table?

Thank you in advance for your time and assistance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top