How to get desired results from query

[Guest]

Good afternoon,

Below is my current query sql statement

*****
SELECT [Report - Drawing Flow 01].[Associated Project], [Report - Drawing
Flow 01].[Work Package], [Weeks Tbl].Year AS [AiF Need Year],
Format([Week],"00") AS [AiF Need Week], Count([Report - Drawing Flow
01].DwgHistID) AS [Number of AiF Needed Dwgs]
FROM [Weeks Tbl] LEFT JOIN [Report - Drawing Flow 01] ON ([Weeks Tbl].Week =
[Report - Drawing Flow 01].[AiF Need Week]) AND ([Weeks Tbl].Year = [Report -
Drawing Flow 01].[AiF Need Year])
GROUP BY [Report - Drawing Flow 01].[Associated Project], [Report - Drawing
Flow 01].[Work Package], [Weeks Tbl].Year, Format([Week],"00")
HAVING ((([Weeks Tbl].Year)>=Min([AiF Need Year])))
ORDER BY [Report - Drawing Flow 01].[Associated Project], [Report - Drawing
Flow 01].[Work Package], [Weeks Tbl].Year, Format([Week],"00");
*****

I created a table with a listing of the number of weeks 1-52... by year from
2003 to 2020. In a seperate table I have a listing of 'AiF Need Year' for
various drawings. These drawing are organized by 'Project' and also by 'Work
Package'. I am trying to create a query that will return the number of
drawings AiF Neek Date by Year-Week. Each Project - Work Package should have
entries 2003-1 to 52, 2004-1 to 52,...

I hope I managed to explain myself, do not hesitate to ask if you need more
info.

Thank you!

Daniel

 

[Michel Walsh]

Hi,



something like:

SELECT allWeekYear, COUNT(projects.projectID)
FROM allWeekYearTable LEFT JOIN projects
ON allWeekYearTable.week = projects.week
AND allWeekYearTable.year = projects.year
GROUP BY allWeekYear


The LEFT JOIN can produce null under output for the right table, so
COUNT( ) on such field will produce 0, if there is no project for the given
year-week, as, I assume, you want. By opposition, COUNT(*) would have
counted the number of records, nulls or not.



Hoping it may help,
Vanderghast, Access MVP.

 

[Guest]

Michel,

I sorry but I'm entirely following you on this one. I apologize if you did
answer this already, I tried to explicitly explain the situation. If
possible could you show me the proper sql statement using the values provided
below, this may help in my comprehension.

I have a qry 'Source Data Qry' which has the following
fields:'Project','Work Package','Drawing','AiF Need Date'. I then have a
table 'Weeks Tbl' with the following fields: 'Year', 'Week'.

I would like to get a listing for each 'Project','Work Package' for each
'Year', 'Week' the number of Drawings whose 'Aif Need Date' is on a given
'Year','Week'.

Thank you so very much!

Danie

Hi,



something like:

SELECT allWeekYear, COUNT(projects.projectID)
FROM allWeekYearTable LEFT JOIN projects
ON allWeekYearTable.week = projects.week
AND allWeekYearTable.year = projects.year
GROUP BY allWeekYear


The LEFT JOIN can produce null under output for the right table, so
COUNT( ) on such field will produce 0, if there is no project for the given
year-week, as, I assume, you want. By opposition, COUNT(*) would have
counted the number of records, nulls or not.



Hoping it may help,
Vanderghast, Access MVP.

Good afternoon,

Below is my current query sql statement

*****
SELECT [Report - Drawing Flow 01].[Associated Project], [Report - Drawing
Flow 01].[Work Package], [Weeks Tbl].Year AS [AiF Need Year],
Format([Week],"00") AS [AiF Need Week], Count([Report - Drawing Flow
01].DwgHistID) AS [Number of AiF Needed Dwgs]
FROM [Weeks Tbl] LEFT JOIN [Report - Drawing Flow 01] ON ([Weeks Tbl].Week
=
[Report - Drawing Flow 01].[AiF Need Week]) AND ([Weeks Tbl].Year =
[Report -
Drawing Flow 01].[AiF Need Year])
GROUP BY [Report - Drawing Flow 01].[Associated Project], [Report -
Drawing
Flow 01].[Work Package], [Weeks Tbl].Year, Format([Week],"00")
HAVING ((([Weeks Tbl].Year)>=Min([AiF Need Year])))
ORDER BY [Report - Drawing Flow 01].[Associated Project], [Report -
Drawing
Flow 01].[Work Package], [Weeks Tbl].Year, Format([Week],"00");
*****

I created a table with a listing of the number of weeks 1-52... by year
from
2003 to 2020. In a seperate table I have a listing of 'AiF Need Year' for
various drawings. These drawing are organized by 'Project' and also by
'Work
Package'. I am trying to create a query that will return the number of
drawings AiF Neek Date by Year-Week. Each Project - Work Package should
have
entries 2003-1 to 52, 2004-1 to 52,...

I hope I managed to explain myself, do not hesitate to ask if you need
more
info.

Thank you!

Daniel

 

[Michel Walsh]

Hi,


To get the count by week/year :


SELECT a.Year, a.Week, COUNT(b.Project)
FROM [Weeks Tbl] As a LEFT JOIN [Source Data Qry] As b
ON a.week = b.week AND a.year = b.year
GROUP BY a.Year, a.Week



to get a list of the projects, by week, add an extra column to [Weeks Tbl],
call it List, make it a varchar(255) and try something like:


UPDATE [Weeks Tbl] INNER JOIN [Source Data Qry]
ON [Weeks Tbl].week = [Source Data Qry].week AND [Weeks Tbl].year = [Source
Data Qry] .year
SET List = (List + ", ") & CStr(Projet)




Hoping it may help,
Vanderghast, Access MVP

Michel,

I sorry but I'm entirely following you on this one. I apologize if you
did
answer this already, I tried to explicitly explain the situation. If
possible could you show me the proper sql statement using the values
provided
below, this may help in my comprehension.

I have a qry 'Source Data Qry' which has the following
fields:'Project','Work Package','Drawing','AiF Need Date'. I then have a
table 'Weeks Tbl' with the following fields: 'Year', 'Week'.

I would like to get a listing for each 'Project','Work Package' for each
'Year', 'Week' the number of Drawings whose 'Aif Need Date' is on a given
'Year','Week'.

Thank you so very much!

Danie

Hi,



something like:

SELECT allWeekYear, COUNT(projects.projectID)
FROM allWeekYearTable LEFT JOIN projects
ON allWeekYearTable.week = projects.week
AND allWeekYearTable.year = projects.year
GROUP BY allWeekYear


The LEFT JOIN can produce null under output for the right table, so
COUNT( ) on such field will produce 0, if there is no project for the
given
year-week, as, I assume, you want. By opposition, COUNT(*) would have
counted the number of records, nulls or not.



Hoping it may help,
Vanderghast, Access MVP.

Good afternoon,

Below is my current query sql statement

*****
SELECT [Report - Drawing Flow 01].[Associated Project], [Report -
Drawing
Flow 01].[Work Package], [Weeks Tbl].Year AS [AiF Need Year],
Format([Week],"00") AS [AiF Need Week], Count([Report - Drawing Flow
01].DwgHistID) AS [Number of AiF Needed Dwgs]
FROM [Weeks Tbl] LEFT JOIN [Report - Drawing Flow 01] ON ([Weeks
Tbl].Week
=
[Report - Drawing Flow 01].[AiF Need Week]) AND ([Weeks Tbl].Year =
[Report -
Drawing Flow 01].[AiF Need Year])
GROUP BY [Report - Drawing Flow 01].[Associated Project], [Report -
Drawing
Flow 01].[Work Package], [Weeks Tbl].Year, Format([Week],"00")
HAVING ((([Weeks Tbl].Year)>=Min([AiF Need Year])))
ORDER BY [Report - Drawing Flow 01].[Associated Project], [Report -
Drawing
Flow 01].[Work Package], [Weeks Tbl].Year, Format([Week],"00");
*****

I created a table with a listing of the number of weeks 1-52... by year
from
2003 to 2020. In a seperate table I have a listing of 'AiF Need Year'
for
various drawings. These drawing are organized by 'Project' and also by
'Work
Package'. I am trying to create a query that will return the number of
drawings AiF Neek Date by Year-Week. Each Project - Work Package
should
have
entries 2003-1 to 52, 2004-1 to 52,...

I hope I managed to explain myself, do not hesitate to ask if you need
more
info.

Thank you!

Daniel