Counting in a query

[Guest]

I have a table detailing customer's visits to an office and their activities.
Each record has a customer ID field, visit date and activity code. In each
date, a customer usually has some activities in the office, therefore each
visit to the office is represented by 5-10 records, each with the same
customer ID and visit date.
I need to create a new table that has only 2 columns- the customer ID and
the total count of his visits to the office (i.e. grouping all visits dates
and counting the number of different groups).
How can is be done?

Thanks!

 

[Guest]

You can use a Totals query. Create a Select query In the query builder that
includes the Customer ID and the Visit Date, click the icon on the tool bar
that looks like the Greek letter Sigma. You will see a new row appear with
the title Total: In that row, select Group By for the Customer ID and Count
for the visit date. Once that is returning the correct information, make it
into either an Append or Make Table query.

 

[John Spencer]

IF your field names or table names have spaces this will require two
queries.

First query saved as qUniqueDates
SELECT Distinct [Customer ID], [Visit Date]
FROM [Your Table Name]

Second query
SELECT [CustomerID], Count([Visit date]) as DaysVisited
FROM qUniqueDates
GROUP BY [Customer Id]

If you don't have spaces in your field names or table names, you can do this
all in one query.

IF you don't understand the above and can only use the query grid, post back
telling us that and give us the field names and table name(s).

 

[Guest]

Thank you- I got it.

Nir

IF your field names or table names have spaces this will require two
queries.

First query saved as qUniqueDates
SELECT Distinct [Customer ID], [Visit Date]
FROM [Your Table Name]

Second query
SELECT [CustomerID], Count([Visit date]) as DaysVisited
FROM qUniqueDates
GROUP BY [Customer Id]

If you don't have spaces in your field names or table names, you can do this
all in one query.

IF you don't understand the above and can only use the query grid, post back
telling us that and give us the field names and table name(s).

I have a table detailing customer's visits to an office and their
activities.
Each record has a customer ID field, visit date and activity code. In each
date, a customer usually has some activities in the office, therefore each
visit to the office is represented by 5-10 records, each with the same
customer ID and visit date.
I need to create a new table that has only 2 columns- the customer ID
and
the total count of his visits to the office (i.e. grouping all visits
dates
and counting the number of different groups).
How can is be done?

Thanks!

 

[Guest]

Thanks!


Nir

You can use a Totals query. Create a Select query In the query builder that
includes the Customer ID and the Visit Date, click the icon on the tool bar
that looks like the Greek letter Sigma. You will see a new row appear with
the title Total: In that row, select Group By for the Customer ID and Count
for the visit date. Once that is returning the correct information, make it
into either an Append or Make Table query.