Count Query

[kbsudhir]

Hi All,

I am having two tables namely inbox and sent.
Both have a field named "Identifier". I want to create a query which pulls only those records from both tables where the values in "Identifier" is same. Showing the count of duplicates in ("identifier"field) the last field of the query.

I am able to pull the records from both the table having same value in Identifier field, but am not able to pull the count of it.

Can anybody teach me how to write a count query or suggest any links where I can learn how to create simple & complex queries for Access.

:help :banghead: :think: :(

Thanks
Sudhir

[XLGibbs]

YOu can do this in query design view, join the two tables on the identifier field and pick the columns you want to use in your query. You can then right click the grid and choose "Totals" and you will see options for aggregating the query (sum,avg,count, min, max).

Count works to count all records, where the other aggregates needs to use value columns.

If you are able to join the tables and return all records where the identifiers match, you can also just add a count to the SQL statement.

Select Table1.Column1,Count(Table1.*)

FROM Table1 inner join Table2 on Table1.Identifier = Table2.Identifier
GROUP by Table1.Column1
ORDER by Count(Table1.*) desc

would return the count of table1 where all records match table 2 on identifier, grouped by Table1 column1 and ordered by the count descending.

[kbsudhir]

:bow: Thanks Gibbs,

It is working, can you suggest some nice links where I can learn about queries simple to complex.

As my need is to count all the records in both the tables which have
1. common value in identifier field.
2. I want to capture subject, reciveddatetime(which will be the time of the first value in identifier field in Table 1(outlook)) and last replied Time (which will be the last value in identifier field in table 2(sentItems),Where the value of Identifier field matched that of identifier field in table 1)).
3. Now the last column will show the count of the identifiers whcih are same in both the tables.

Can u pls guide me on the same.

Thanks for spending your valuable time in my request.

Sudhir

:bow:

[DarkSprout]

LINK: http://www.fontstuff.com/access/acctut14.htm