INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query doesn't Group by year

Query doesn't Group by year

(OP)
Hi,

I have a number database with a query with three columns to group by year
Right_hits: Right(Format([Drawnumber],"0000"),2), TotalHits: Count(*) , DrawDate criteria groupby Year(2016)

The query runs without giving no info of the year 2016 I have data in the database of the year 2016.







RE: Query doesn't Group by year

How is your query looks like? Do you need to group by years or select year 2016? Assuming you hve a field [TheYear] where you store the year, in the first case use GROUP BY TheYear, in the second WHERE TheYear=2016. You can extract year from the date using Year function.

combo

RE: Query doesn't Group by year

Year(2016)=1905. You may need Field: Year([DrawDate]), Criteria: 2016, depending on the format and contents of DrawDate.

combo

RE: Query doesn't Group by year

(OP)
Hi Combo,
I've tried your solution but you cannot groupby on totalhits when groupby on totalhits you get an error
the query doesn't sum the total hits for each number
Right_hits: Right(Format([Drawnumber],"00000"),2) TotalHits: Count(*) Year([DrawDate]) drawdate
groupby expression 2016 criteria groupby
expression

RE: Query doesn't Group by year

Can you post the sql and tell what type of data is [DrawDate]? If DrawDate is real date, you need to use Year function in "Field:" line and put 2016 in "Criteria:" line.

combo

RE: Query doesn't Group by year

(OP)
Hi combo here the sql
SELECT Right(Format([Drawnumber],"00000"),2) AS Right_hits, Count(*) AS TotalHits, Numbers_data.Drawnumber, Numbers_data.DrawDate, Year([drawdate]) AS Expr1
FROM Numbers_data
GROUP BY Right(Format([Drawnumber],"00000"),2), Numbers_data.Drawnumber, Numbers_data.DrawDate
HAVING (((Year([drawdate]))=2016))
ORDER BY Right(Format([Drawnumber],"00000"),2);
Drawdate is a date field

RE: Query doesn't Group by year

(OP)
problem solved

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close