Innner join using date part

A

Andy G

Can I do this?

FROM dbo_testInformation INNER JOIN
dbo_testAdministered ON dbo.testInformation.Test_ID =
dbo_testAdministered.Test_ID INNER JOIN
dbo_settings ON DatePart("yyyy",
dbo.testAdministered.Date_Completed) =
DatePart("yyyy", dbo.settings.Date)

Below is the full query

SELECT TOP 100 PERCENT dbo_testInformation.Category,
DatePart("m",[dbo_testAdministered].[Date_Completed]) AS Month,
dbo_testInformation.Name, dbo_testInformation.Abbreviaton,
dbo_testAdministered.TestAssignID, DatePart("yyyy",[Date]) AS SetYear,
DatePart("yyyy",[Date_Completed]) AS AdminYear

FROM dbo_testInformation INNER JOIN
dbo_testAdministered ON dbo.testInformation.Test_ID =
dbo_testAdministered.Test_ID INNER JOIN
dbo_settings ON DatePart("yyyy",
dbo.testAdministered.Date_Completed) =
DatePart("yyyy", dbo.settings.Date)

GROUP BY dbo_testInformation.Category,
DatePart("m",[dbo_testAdministered].[Date_Completed]),
dbo_testInformation.Name, dbo_testInformation.Abbreviaton,
dbo_testAdministered.TestAssignID, DatePart("yyyy",[Date]),
DatePart("yyyy",[Date_Completed])

ORDER BY dbo_testInformation.Category,
DatePart("m",[dbo_testAdministered].[Date_Completed]),
dbo_testInformation.Name;
 
A

Andy G

Sorry I wasn't to specific before.

I want to know if you can use DatePart function in a join clause in the From
area of a query.
 
M

Marshall Barton

Andy said:
Sorry I wasn't to specific before.

I want to know if you can use DatePart function in a join clause in the From
area of a query.


"Andy G" wrote
Can I do this?

FROM dbo_testInformation INNER JOIN
dbo_testAdministered ON dbo.testInformation.Test_ID =
dbo_testAdministered.Test_ID INNER JOIN
dbo_settings ON DatePart("yyyy",
dbo.testAdministered.Date_Completed) =
DatePart("yyyy", dbo.settings.Date)

Below is the full query

SELECT TOP 100 PERCENT dbo_testInformation.Category,
DatePart("m",[dbo_testAdministered].[Date_Completed]) AS Month,
dbo_testInformation.Name, dbo_testInformation.Abbreviaton,
dbo_testAdministered.TestAssignID, DatePart("yyyy",[Date]) AS SetYear,
DatePart("yyyy",[Date_Completed]) AS AdminYear

FROM dbo_testInformation INNER JOIN
dbo_testAdministered ON dbo.testInformation.Test_ID =
dbo_testAdministered.Test_ID INNER JOIN
dbo_settings ON DatePart("yyyy",
dbo.testAdministered.Date_Completed) =
DatePart("yyyy", dbo.settings.Date)

GROUP BY dbo_testInformation.Category,
DatePart("m",[dbo_testAdministered].[Date_Completed]),
dbo_testInformation.Name, dbo_testInformation.Abbreviaton,
dbo_testAdministered.TestAssignID, DatePart("yyyy",[Date]),
DatePart("yyyy",[Date_Completed])

ORDER BY dbo_testInformation.Category,
DatePart("m",[dbo_testAdministered].[Date_Completed]),
dbo_testInformation.Name;
Click to expand...
Click to expand...


Yes, you can. However, I can not be sure in your case
because it looks like you are doing some strange (maybe even
non-Access/Jet stuff) here. Why the TOP 100 PERCENT? What
is the GROUP BY clause for when you don't use any aggregate
functions?
 
Top