Duplicate Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Wonder if someone can help please.

I have a report that gives me information on batch files that are run
through a processing system.

The batches can come up with two different types of errors once processed,
in this instance we'll call these Red and Blue, and both Red or Blue errors
can appear many times in the files throughout the batch.

Against each error it shows the number of files within the batch.

My problem is that in the report I want a count at the end of each batch
file that only counts that number of files count once.

Hope this makes sense.

Thanks

Chris
 
Hi Chris,

If your errors are reported in one field and you want a
total of:

files with Red errors
files with Blue errors
files with both Red and Blue errors

make a totals query

field --> CountRed:
IIF(Instr([ErrorField],"Red")>0
AND Instr([ErrorField],"Blue")=0,
1,0)
Totals --> Sum

field --> CountBlue:
IIF(Instr([ErrorField],"Red")=0
AND Instr([ErrorField],"Blue")>0,
1,0)
Totals --> Sum

field --> CountRedBlue:
IIF(Instr([ErrorField],"Red")>0
AND Instr([ErrorField],"Blue")>0,
1,0)
Totals --> Sum


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
Hi,

Thanks for your reply.

I know where you are coming from and I've managed to calculate each of the
red and blue errors, but here's the problem, explained better than I
originally did.

In my report the data looks like this:

NUMBER OF FILES IN BATCH FILE TYPE OF ERROR

500
Red

Red
500
Blue

As you will see there are 3 errors within the number of files within the
batch. As you can also see the number of files is shown twice once against
each of the different types of error. I know I can hide duplicates but if I
put a Sum or Count function in my report footer it still counts the number of
files twice i.e. 1000 when I only want it to show 500.

Any ideas please.

Thanks

Chris

strive4peace" <"strive4peace2006 at yaho said:
Hi Chris,

If your errors are reported in one field and you want a
total of:

files with Red errors
files with Blue errors
files with both Red and Blue errors

make a totals query

field --> CountRed:
IIF(Instr([ErrorField],"Red")>0
AND Instr([ErrorField],"Blue")=0,
1,0)
Totals --> Sum

field --> CountBlue:
IIF(Instr([ErrorField],"Red")=0
AND Instr([ErrorField],"Blue")>0,
1,0)
Totals --> Sum

field --> CountRedBlue:
IIF(Instr([ErrorField],"Red")>0
AND Instr([ErrorField],"Blue")>0,
1,0)
Totals --> Sum


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
Hi,

Wonder if someone can help please.

I have a report that gives me information on batch files that are run
through a processing system.

The batches can come up with two different types of errors once processed,
in this instance we'll call these Red and Blue, and both Red or Blue errors
can appear many times in the files throughout the batch.

Against each error it shows the number of files within the batch.

My problem is that in the report I want a count at the end of each batch
file that only counts that number of files count once.

Hope this makes sense.

Thanks

Chris
Click to expand...
Click to expand...
 
Hi Chris,

use IIF in the calculated control to sum as well

if you still have problems, please tell specify the data
structure you are using to indicate errors

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
Hi,

Thanks for your reply.

I know where you are coming from and I've managed to calculate each of the
red and blue errors, but here's the problem, explained better than I
originally did.

In my report the data looks like this:

NUMBER OF FILES IN BATCH FILE TYPE OF ERROR

500
Red

Red
500
Blue

As you will see there are 3 errors within the number of files within the
batch. As you can also see the number of files is shown twice once against
each of the different types of error. I know I can hide duplicates but if I
put a Sum or Count function in my report footer it still counts the number of
files twice i.e. 1000 when I only want it to show 500.

Any ideas please.

Thanks

Chris

:

Hi Chris,

If your errors are reported in one field and you want a
total of:

files with Red errors
files with Blue errors
files with both Red and Blue errors

make a totals query

field --> CountRed:
IIF(Instr([ErrorField],"Red")>0
AND Instr([ErrorField],"Blue")=0,
1,0)
Totals --> Sum

field --> CountBlue:
IIF(Instr([ErrorField],"Red")=0
AND Instr([ErrorField],"Blue")>0,
1,0)
Totals --> Sum

field --> CountRedBlue:
IIF(Instr([ErrorField],"Red")>0
AND Instr([ErrorField],"Blue")>0,
1,0)
Totals --> Sum


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
Hi,

Wonder if someone can help please.

I have a report that gives me information on batch files that are run
through a processing system.

The batches can come up with two different types of errors once processed,
in this instance we'll call these Red and Blue, and both Red or Blue errors
can appear many times in the files throughout the batch.

Against each error it shows the number of files within the batch.

My problem is that in the report I want a count at the end of each batch
file that only counts that number of files count once.

Hope this makes sense.

Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 
Back
Top