Select query using count 1

[robinsql]

Hi all,

I need to return a list of pupils with my query. I have three tables..

1. Lists all the dates for a given year and their descriptions, as in weekday or weekend
Fields: DATE, DESC

2. Records all clock-ins.
Fields: DATE, TIME, PUPIL_ID

3. Pupil Details.
Fields: PUPIL_NAME, PUPIL_ID

I need my query to return the details of pupils who have clocked in twice every day, where that day has a weekday desciption. (pupils can clock in a maximum of twice a day)

Can anyone help me with this?
Is so, thanks,

Robin

 

[dhookom]

Do you want only twice or more than once?
What do you mean by "details of pupils"? I this just unique names or names and dates?

You might want to break this into simple queries. First create a query of clock-ins that occur during weekdays. Group by date and count the number of clock-ins. The remainder depends on your needs.

BTW: comments on field names.
DESC is used in query ORDER BY clauses.
DATE is a function
TIME is a function

I'm not sure why you need a table of dates and descriptions unless you store more than just if the date is a weekday or weekend.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[robinsql]

Hi,
Thanks for the reply.
Going in the right direction now. Unfortunately the DB field names were created previously and the DB is live in many places so I cannot change them.

Cheers,
Robin