Access Help

titan18

I need help with Access queries and date functions.I'm required to do this and I would appreciate any help.

The answer should display the startdate as a four digit year; as a numeric month and then a count of the projects.

I think you use date part to get the year and month but the count doesn't work

stephenlane80

Hi Titan,

Post the table schema, and an sql version of the query so we can see whats going on,

To get the query in sql view, right click it and go to sql view

titan18

stephenlane80 wrote: »

Hi Titan,

Post the table schema, and an sql version of the query so we can see whats going on,

To get the query in sql view, right click it and go to sql view

SELECT tblProject.[Project Name], tblProject.[Start Date], tblProject.[End Date], tblProject.[Client Num], DatePart("yyyy",[Start Date]) AS [Year of Project], DatePart("m",[Start Date]) AS [Month of Project], Count(tblProject.[Start Date]) AS [Count of Projects in Month]
FROM tblProject
GROUP BY tblProject.[Project Name], tblProject.[Start Date], tblProject.[End Date], tblProject.[Client Num], DatePart("yyyy",[Start Date]), DatePart("m",[Start Date])
HAVING (((tblProject.[Start Date])>#1/1/2007# And (tblProject.[Start Date])<#12/31/2008#))
ORDER BY tblProject.[Start Date];



Whats the table schema

Delirium

titan18 wrote: »

SELECT tblProject.[Project Name], tblProject.[Start Date], tblProject.[End Date], tblProject.[Client Num], DatePart("yyyy",[Start Date]) AS [Year of Project], DatePart("m",[Start Date]) AS [Month of Project], Count(tblProject.[Start Date]) AS [Count of Projects in Month]
FROM tblProject
GROUP BY tblProject.[Project Name], tblProject.[Start Date], tblProject.[End Date], tblProject.[Client Num], DatePart("yyyy",[Start Date]), DatePart("m",[Start Date])
HAVING (((tblProject.[Start Date])>#1/1/2007# And (tblProject.[Start Date])<#12/31/2008#))
ORDER BY tblProject.[Start Date];



Whats the table schema

You could try something like this.

SELECT DatePart("yyyy",[Start Date]) AS [Year of Project], DatePart("m",[Start Date]) AS [Month of Project], Count(tblProject.[Start Date]) AS [Count of Projects in Month]
FROM tblProject
WHERE (((tblProject.[Start Date])>#1/1/2007# And (tblProject.[Start Date])<#12/31/2008#))
GROUP BY DatePart("yyyy",[Start Date]), DatePart("m",[Start Date])
ORDER BY DatePart("yyyy",[Start Date]) DatePart("m",[Start Date])  ;

titan18

koth wrote: »

You could try something like this.

SELECT DatePart("yyyy",[Start Date]) AS [Year of Project], DatePart("m",[Start Date]) AS [Month of Project], Count(tblProject.[Start Date]) AS [Count of Projects in Month]
FROM tblProject
WHERE (((tblProject.[Start Date])>#1/1/2007# And (tblProject.[Start Date])<#12/31/2008#))
GROUP BY DatePart("yyyy",[Start Date]), DatePart("m",[Start Date])
ORDER BY DatePart("yyyy",[Start Date]) DatePart("m",[Start Date])  ;

thanks, that worked

Delirium

titan18 wrote: »

thanks, that worked

No problem, glad it helped:)