What is the Best Approach: Query for a Report (1 Viewer)

[mgav]

keep hitting blockers on this.

I have a report requiring a select statement with aggregation and conditions for each report field/control (not sure of the nomenclature here).
First I tried the select statement in one query object. However, I keep hitting blockers with the limitations of sql in ms access object. Then tried query design mode next...also a bust. Finally figured out I can use subquery controls in an unbound report but designing the layout sucks.

How would an expert go about completing this report? (db structure and report design below)

Table: tblBrokerageLog
| Field Name             | Data Type |
|------------------------|-----------|
| DATE_P1                | date/time |
| SUPPLIER               | text      |
| MATERIAL               | text      |
| RECY_PO                | text      |
| ALLIED_REP             | text      |
| CONTAINER_ID           | text      |
| ETD                    | date/time |
| ETA                    | date/time |
| RECY_SETTLEMENT_NUM    | nuber     |
| RECY_SETTLEMENT_AMOUNT | number    |
| RECY_DATE_SETTLED      | date/time |
| BUYER                  | text      |
| RECY_SO                | text      |
| BROKER                 | text      |
| COMMISSION             | number    |
| ALLIED_INVOICE_NUM     | number    |
| ALLIED_SOLD_AMOUNT     | number    |
| ALLIED_INVOICE_DATE    | date/time |
| SHIPPERS_FACT_NUM      | text      |
| BL_or_COO_FINAL_DATE   | date/time |
| DESTINATION            | text      |
| IS_COMPLETE            | text      |

Example data from tblBrokerageLog

 

[plog]

First, since this will be based on user input I would build a form to collect that input. It's a simple form--2 date inputs and a button. User fills in the dates, clicks the button, report opens.

Next, I build the query--one field at a time. Unfortunately your jargon doesn't line up with your table. I have no idea what a Bnum is because you have no field even remotely close to it. Further, I have no idea what P1 fields are nor S1 fields. So I am going to use generic code to demonstrate what you need to do:

SELECT SUM(IIf(IS_COMPLETE='n', 1, 0)) AS BNUM_OPEN, SUM(Iif(IS_COMPLETE<>'n' AND S1='n', 1, 0)) AS SALES_OPEN
FROM YourTableNameHere

The key is that I moved the criteria to each field. If a record passes the test for a field (e.g. is not complete) I make it a 1 if not I make it a 0, then I sum up all those 1's and 0's and I get the count. You would continue this pattern for every field you need until your entire query is built.