Re: How to get desired results from query

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








"Michel Walsh" wrote:

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.

"Daniel" <Daniel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8469424E-803F-49C2-A8B5-1422C3495EA4@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: How to get desired results from query
    ... SELECT allWeekYear, COUNT ... FROM allWeekYearTable LEFT JOIN projects ... 01].DwgHistID) AS [Number of AiF Needed Dwgs] ... Drawing Flow 01].) ...
    (microsoft.public.access.queries)
  • Re: How to get desired results from query
    ... I would like to get a listing for each 'Project','Work Package' for each ... 'Week' the number of Drawings whose 'Aif Need Date' is on a given ... SELECT allWeekYear, COUNT ...
    (microsoft.public.access.queries)
  • Re: Announce: tkpath 0.2
    ... This package implements path drawing modelled after its SVG ... similar to existing canvas items I have prepended the names with a "p". ... If tkpath items were being added to existing canvas ...
    (comp.lang.tcl)
  • Re: Can you specify an Array as a Target??
    ... If I absolutely had to separate the data into multiple sheets, ... The first of these is called "Demolition Package 1". ... Each drawing can therefore be ... "Dave Peterson" wrote: ...
    (microsoft.public.excel.programming)
  • Re: geometry 2d with tikz
    ... The package is a set of convenient ... macros for drawing in a plane ... with a Cartesian coordinate system. ... TikZ and is a TeX-friendly drawing ...
    (comp.text.tex)