Query Statement

[Kathy]

I am trying to create a query in Access 2003 that will give me a list of ID
numbers that are repeated (X) number of times. For example I want a list that
will give me all id numbers that are in my table 8 times. I am thinking I
need to use the "count" function, but everything I have entered gives me no
results. Can I enter it on the criteria line or do I need to go to the SQL
view and enter.

 

[tina]

try opening a new query to the Design grid. add your table, and add the "ID
numbers" field to the grid twice. change the query to a Totals query. both
fields in the grid with default to GroupBy. change the second field from
GroupBy to Count, and add criteria to that field as 8, or whatever number
you want. it's easy to do in the Design grid, but the SQL statement (in case
you're interested) is

SELECT ID, Count(IDfieldname) AS CountOfID
FROM MyTableName
GROUP BY IDfieldname
HAVING Count(IDfieldname)=8;

hth

 

[John W. Vinson]

I am trying to create a query in Access 2003 that will give me a list of ID
numbers that are repeated (X) number of times. For example I want a list that
will give me all id numbers that are in my table 8 times. I am thinking I
need to use the "count" function, but everything I have entered gives me no
results. Can I enter it on the criteria line or do I need to go to the SQL
view and enter.

You can do this in the query grid. Create a new query based on your table. Add
the ID field to the query *TWICE*, but don't add any other fields.

Change the query to a Totals query by clicking the Greek Sigma icon on the
toolbar (looks like a sideways M).

Change the default Group By on the second instance of ID to Count; put a
criterion of 8 on the Criteria Line under this field.

Open the query and you'll see all those ID's with exactly 8 instances.

 

[Kathy]

Where do you change the query to a Totals query? The only queries I see are
append, select, crosstab, make-table, update, and delete.

 

[Kathy]

YEah. It worked. Now I have another question. I need to use this list to
update other fields. I need all 8 instances of the ID number to show and then
I need to do an update query to update a begin and end time for the 8
instances. Example:
ID Number
1234 0800A 0900A
1234 0900A 1000A
etc.

Is this possible?

 

[John W. Vinson]

Where do you change the query to a Totals query? The only queries I see are
append, select, crosstab, make-table, update, and delete.

It's a different button on the toolbar: the Totals button, a Greek Sigma (it
looks like a sideways M).

It's a different button because (for example) you can have a query which is
both a Totals query and also an Append query.

 

[John Spencer]

Well, you can get the eight records, but updating them with begin and
end times is going to be a lot tougher.

The easiest way to get the groups of eight records is to use the find
duplicates query wizard to build the query to find groups of records of
2 or more. Then modify it to find exactly 8 records.

The Duplicates query would look something like the following in SQL view
SELECT ID
FROM YourTable
WHERE ID in
(SELECT ID
FROM YourTable as Temp
GROUP BY ID
HAVING Count(*) >=2)

You would have to change the >= 2 to =8

That gets you the records to update. At this point you need some way to
order the records within the group so you have a record that is
distinctly the first, the second, the third, etc.

If you can't do that then you can manually enter the start and end times
or you can create a vba function to step through the records and set the
begin and end times.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================