CODEWITHSUNDEEP
ms-accessms-access-2003
GuessWho
GuessWho

Reputation: 99

Microsoft Access 2003 Query - Count records and sum it

I'm creating a query with Microsoft Access 2003 and had encounter an issue. I'm new!

I've got 2 tables. First table, i have a list of records that include the name, property name and the country state. Second table, i have a list of property names, the number of units in the property and the property's country state.

I will like to count the number of records in the first table by its state, meanwhile summing up the number of units the property has in the state.

What I encountered is, when I sum the number of units, the units repeats!

Taking for example;

Table1:

Name | State | Property Name

Mr 1 | State A | Building AAA

Mr 2 | State A | Building AAA

Mr 3 | State A | Building BBB

Mr 4 | State B | Building XXX

Mr 5 | State B | Building XXX

Table2:

Property Name | State | Number of Units

Building AAA | State A | 100

Building BBB | State A | 50

Building XXX | State B | 20

My Result:

State | Number of Units | No of Records

State A | 250 | 3

State B | 40 | 2

The result i want:

State | Number of Units | No of Records

State A | 150 | 3

State B | 20 | 2

Upvotes: 0

Views: 13514

Answers (1)

chuff
chuff

Reputation: 5866

EXPANDED

Assuming you are using the Access query builder, you will need to construct three Select queries:

1) Table1 will be the source table for the first query. Use the State field twice in the query, first as a Group By field and second as a Count field. (Any of the fields could have been used for the count, since you are only interested in the number of records.) Save the query for use in the third query.

enter image description here

2) Table2 will be the source table for the second query. Use the State field as a Group By field and the Units field as a Sum field. Save this query, too.

enter image description here

3) The third query will bring the information together. For the source, use the first and second queries, with a join between them on the State field. Select the State field (from either query) as a Group By Field, the CountOfState field from the first query as a Sum field, and the SumofUnits field from the second query as a Sum field.

enter image description here

While the actual amount of work done by Access in producing the final result will not change, the three queries can be consolidated into a single query by editing the underlying SQL.

The new query was produced by inserting the Table1 and Table2 queries into the third, final result query, one on either side of the INNER JOIN statement. The T1 and T1 in the new query are aliases for the embedded queries that eliminate ambiguity in referencing the fields of those queries.

The new query cannot be created using the Query Builder (although the original three queries provide the raw material for it). Instead, the SQL must be written/pasted in/edited in the SQL View of the Query Builder. 

    SELECT T1.State AS State,
           Sum(T1.CountOfState) AS Records, 
           Sum(T2.SumOfUnits) AS Units
    FROM 
          (SELECT Table1.State, 
                  Count(Table1.State) AS CountOfState
           FROM Table1
           GROUP BY Table1.State) T1
    INNER JOIN 
           (SELECT Table2.State, 
                   Sum(Table2.Units) AS SumOfUnits
            FROM Table2
            GROUP BY Table2.State) T2
    ON T1.State = T2.State
    GROUP BY T1.State;

Upvotes: 2

Related Questions