Creating SQL Syntax for Unique Count

[mous]

Advice creating SQL syntax for Unique Count - SORRY!

Hi there

Sorry, for this posting as its been explained a thousand times over but I'm still finding it hard to write this query.

I would like to Count Unique days of the week for a particular code and year.

Year Code Day

03/04 VFOCE 1
03/04 VFOCE 1
03/04 VFOCE 2
03/04 VFOCE 3
03/04 VFOCE 3
03/04 VFOCE 4

This total count would result in 6 but I want a unique count of 4. I know it can be done (I've seen other examples) but I don't understand how to do it.

Can anyone pleaes advise.

Thanks.

 

[Mile-O]

Re: Advice creating SQL syntax for Unique Count - SORRY!

I'm still finding it hard to write this query.

I would like to Count Unique days of the week for a particular code.

YearCode Day

VFOCE

What query?
What code?
What's your structure?
VFOCE?

 

[mous]

That's just it I don't know. I've tried copying from another users code and tailoring it to mine but I can't seem to do it and I can't also find the bloody code now!

Something along the lines of

SELECT DISTINCT FROM (table created) as ?
INNERJOIN (another table created with totals?)
Where First Table.field1 = Second Table.field1

Thanks

 

[Mile-O]

Your [Year] and

 fields are Text and your [Day] is Numeric?
[Year] and [Day] are bad names for fields as these are reserved words in MS Access.

Is [Year] (i.e. 03/04) indicative of a shift between two years (2003 & 2004) or of a month within a year (March, 2004 or April, 2003)?

Is the [Day] field the day of the month (1 to 31, typically) or the WeekDay value (1 to 7 where 1 = Sunday and 7 = Saturday) ?

Is the [Code] field a foreign key? It looks like it should be.

 

[mous]

I understand reserved words. The fields are not really called that I've simplified for the question.

Year is academic year i.e. 03/04 or 04/05. (text)

Code can change within Acadmic year (text)

Day is Day 1-7 where 1 is Monday. (number)

Year is not a foreign key.

Thanks.

 

[Mile-O]

The fields are not really called that I've simplified for the question.

Year is academic year i.e. 03/04 or 04/05. (text)

Code can change within Acadmic year (text)

Day is Day 1-7 where 1 is Monday. (number)

Year is not a foreign key.

I'd expect you to have a table for Academic Years, a table full of codes, and I don't know the relevance of the Day field.

By the way: I asked if Code was a foreign key; not Year.

It would probably be better if you used actual table(s) and field names.