how to write these queries

[cdoyle]

Hi,

I've been asked to create a couple queries for my db.

I have a code table, and then a data table with a code_ID (FK) to the code table.

The first query would to see how many codes have only been used once.
Then another query that pulls codes that have been used twice.
Then another query that will only pull codes that have been used 3 times.

I can't think of the logic needed to make these?

I'm thinking there is some SQL I can used in my code_ID field, but not sure what it would be.

Or could this be done easier in a report?

Thanks

 

[MSAccessRookie]

The following query gets them all at the same time

Select Code ID, Count(*) 
From CodeTable INNER JOIN DataTable 
ON CodeTable.Code_ID = DataTable.Code_ID
Group By Code_ID HAVING Count(*) IN (1, 2, 3)

Count allows you to get items who only match n times in the table

 

[cdoyle]

Hi!
Thanks for your help, this works pretty good.

They have another request now, and puts another kink in the query.

in our data table, we have a start date/end date fields.
they only want to see codes with 2 or 3 records that don't have the same start date. Is there a way to filter out any that might have the same start date?

for example

abcd has a start date 1/1/2008, and another record for abcd has a start date 1/1/2008.
The wouldn't want to see those, since the start date is the same.

but if
efg has a start date 1/1/2008 and another record for efg with a start date of 1/5/2008.
The would want to see those.

 

[sqlbot2000]

How about adding a few lines to MSAccessRookie's query just before the GROUP BY line:

WHERE CodeTable.Code_ID NOT IN 
(
    SELECT Code_ID
    FROM DataTable
    GROUP BY Code_ID, StartDate
    HAVING Count(*) > 1
)