Create 1 query from 5 queries

[rtinsky]

I have 5 queries that all list the same data from my items table. But each query has different criteria for the calculated field Profit. How can I make this into one query. The criteria that each uses is <5, >4.99 and <20, >19.99 and <50, >49.99 and <100, >99.99 and <200, and >199.99. Here is one of the queries.

SELECT Items.ID, Items.[Item Number], Items.[Item Title], Items.[Date Sold], [Items]![BIN Price]+[Items]![S&H Charged]-[Items]![Item Cost]-[Items]![S&H Cost]-[Items]![Ebay Fees]-[Items]![Paypal Fees]-[Items]![Tax] AS Profit
FROM Items
WHERE (((Items.[Date Sold]) Is Null) And ((Items![BIN Price]+Items![S&H Charged]-Items![Item Cost]-Items![S&H Cost]-Items![Ebay Fees]-Items![Paypal Fees]-Items!Tax)>4.99 And (Items![BIN Price]+Items![S&H Charged]-Items![Item Cost]-Items![S&H Cost]-Items![Ebay Fees]-Items![Paypal Fees]-Items!Tax)<20));

My goal is to make a report from this query that can show the count total of each criteria. I am using Access 2010.

Can anyone help me with this project?

[June7]

Use those parameters to calculate a group identifier and use that field for sorting and grouping.

Switch([Profit]<5,1, [Profit]>=5 And [Profit]<20,2, [Profit]>=20 And [Profit]<50,3, [Profit]>=50 And [Profit]<100,4, [Profit]>=100 And [Profit]<200,5, [Profit]>=200,6) As GroupID

Create a group section in report based on the GroupID.

Textbox in group footer can calculate the count:
=Count([GroupID])

Another can sum the Profit field:
=Sum([Profit])

The WHERE filter criteria is not needed and you have one query.

[rtinsky]

I am not that skilled at access. I do not know how or where to put the switch line. I also don't know how to create a group section in a report. I do understand how to sum and count in the group footer. I used that in the 5 reports that use the 5 queries.

Rodney

Use those parameters to calculate a group identifier and use that field for sorting and grouping.

Switch([Profit]<5,1, [Profit]>=5 And [Profit]<20,2, [Profit]>=20 And [Profit]<50,3, [Profit]>=50 And [Profit]<100,4, [Profit]>=100 And [Profit]<200,5, [Profit]>=200,6) As GroupID

Create a group section in report based on the GroupID.

Textbox in group footer can calculate the count:
=Count([GroupID])

Another can sum the Profit field:
=Sum([Profit])

The WHERE filter criteria is not needed and you have one query.

[June7]

The Switch expression can be in query to create a field. Enter it on the Field row of the query designer grid:

GroupID: Switch(...)

If you have a group footer in the report then you have a group section.

Access Help has guidelines on creating fields in query with expressions and on building reports with grouping & sorting.

[rtinsky]

Thanks so far. I got the switch command to work in the query. Now I just need to work on the report.

[rtinsky]

OK, I got the report to work. Thank you very much for your help! One last question. I only want to see the totals for each group. I do not want to see the details listed in this report. I am looking to run a report that will tell me on 1 page how many items are listed for each groupID. Is that possible?

[June7]

Options:

1. set the Details section as not visible

2. make the query a GROUP BY (Totals) aggregate instead of doing the report Grouping