Helping a friend

[jphillips]

Does anyone know how I could explain this how to to a friend this question she posed to me

I have MS Access with a table of 500 rows of data. How do I build a report the
a. selects the total count and shows it at the report header
and
b. select from the same table where field_1 = field_2

I use to mess around with access but it has been a very long time.

JP

 

[FancyPrairie]

Use the query builder to build a query that reports the sum and where field1 = field2.

 

[jphillips]

Can you show me an example?

 

[hermanlaksko]

I think you could try to use the var. wizards to acomplish the job and perhaps use a sub report for the calculation.

Herman

 

[FancyPrairie]

I would first use the query builder to build the sql statement that sums the data you want. To do this, open the query builder and select the table that contains the info you want. Then select the field you want to sum and Field1. Select View | Totals via the menu bar. At this point the Total property will be set to Group By for all fields. Change the Total property of the field you want to sum from Group By to Sum. Change the Total property of Field1 from Group By to Where. In the Criteria property of Field1, enter Field2. That should do it. Run it and test it, then save it and set the Recordsource of your report equal to this query. (To view the SQL statement itself, select View | SQL View within the query builder).

Here's a crude example of how the SQL statement would look.

SELECT Sum(YourFieldToSum) AS SumOfYourFieldToSum
FROM YourTable
WHERE (Field1 = Field2);

In the report, simply place the field "SumOfYourFieldToSum" in the Report header.