Report with two groupings

[access2010]

Can we print one MsAccess 2003 report with two criteria’s?

Start the report by Currency if Trade is “PURCHASED”

Continue report by Currency if Trade is “WATCH” and investigation is “YES”

We have tried many combination reports and would appreciate your suggestion. Otherwise we will continue to print 2 reports

Thank you, Crystal.

 

[plog]

This is a query issue. Create a calculated field in the query to put your records into groups. Then group by that field in the report.

However, your logic isn't complete. You've left out one possibility--Trade=Watch and Investigation=No. You will need to accomodate that in your calculated field, or eliminate it from the query all together.

 

[strive4peace]

since investigation is a yes/no field, and False (No) = 0, you could do this for criteria:

(Trade = "PURCHASED") OR (Trade = "WATCH" AND investigation <> 0)

In the report design, you can sort and group by Trade, then sort by whatever else

by the way, "Currency" is a bad field name since it is a reserved word.

here is a link to a list of "bad words" by Allen Browne which is the best list, imo, for words and phrases that should not be used for names. There are more than 2500 terms you shouldn't use for names.

There is a helpful "letter-picker" jump list at the top of the page so you can immediately start looking at what starts with a specified letter

Problem names and reserved words in Access by Allen Browne

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

On this link, you will also find a Database Issue Checker Utility you can download (also free) to check any existing Access database for violation of rules.

there are several tests you can run. Initially, perhaps only check:

Bad name
Bad characters
No primary key
Record too wide

and then once those things are fixed, you may want to check other issues. This is a great utility! If you haven't explored Allen Browne's site, I encourage you to do so ...

 

[June7]

Calculate in query or in report design. A Group section can be based on an expression

 

[azhar2006]

I think you need such an expression. It will be calculated at the bottom of the report.

=Count(IIf([Trade]="Purchased",0))

=Count(IIf([Trade]="Watch",0))

 

[access2010]

This is a query issue. Create a calculated field in the query to put your records into groups. Then group by that field in the report.

However, your logic isn't complete. You've left out one possibility--Trade=Watch and Investigation=No. You will need to accomodate that in your calculated field, or eliminate it from the query all together.

Thank you Plog for your suggestion, which works.
Crystal

 

[access2010]

since investigation is a yes/no field, and False (No) = 0, you could do this for criteria:

(Trade = "PURCHASED") OR (Trade = "WATCH" AND investigation <> 0)

In the report design, you can sort and group by Trade, then sort by whatever else

by the way, "Currency" is a bad field name since it is a reserved word.

here is a link to a list of "bad words" by Allen Browne which is the best list, imo, for words and phrases that should not be used for names. There are more than 2500 terms you shouldn't use for names.

There is a helpful "letter-picker" jump list at the top of the page so you can immediately start looking at what starts with a specified letter

Problem names and reserved words in Access by Allen Browne

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

On this link, you will also find a Database Issue Checker Utility you can download (also free) to check any existing Access database for violation of rules.

there are several tests you can run. Initially, perhaps only check:

Bad name
Bad characters
No primary key
Record too wide

and then once those things are fixed, you may want to check other issues. This is a great utility! If you haven't explored Allen Browne's site, I encourage you to do so ...

Thank you strive4peace, your suggestion which was implemented in our query works and my boss now has the report she requested.
Crystal

 

[access2010]

since investigation is a yes/no field, and False (No) = 0, you could do this for criteria:

(Trade = "PURCHASED") OR (Trade = "WATCH" AND investigation <> 0)

In the report design, you can sort and group by Trade, then sort by whatever else

by the way, "Currency" is a bad field name since it is a reserved word.

here is a link to a list of "bad words" by Allen Browne which is the best list, imo, for words and phrases that should not be used for names. There are more than 2500 terms you shouldn't use for names.

There is a helpful "letter-picker" jump list at the top of the page so you can immediately start looking at what starts with a specified letter

Problem names and reserved words in Access by Allen Browne

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

On this link, you will also find a Database Issue Checker Utility you can download (also free) to check any existing Access database for violation of rules.

there are several tests you can run. Initially, perhaps only check:

Bad name
Bad characters
No primary key
Record too wide

and then once those things are fixed, you may want to check other issues. This is a great utility! If you haven't explored Allen Browne's site, I encourage you to do so ...

I / we have looked at your links and they are appreciated and useful.
Thank you, Crystal.