CUMULATIVE

[Guest]

Hi All, I posted on the wrong thread, I am newbie to Access, any help will be
appreciated.
I have 4 tables:
PROTO PROTOITEMS WEEK
ADD
PROTOID ITEMID WEEKID
ADNAME
PROTONAME PROTONAME WEEK ADTYPE
SUBJ NEED SIGNED
etc
WEEKSENROLL INSCREEN
etc ETC

Anyway, my goal is to produce a multilinear graph with week along the x-axis
and cumulative along y-axis. There will be 2 lines for each PROTOID, one that
shows originated numbers (from Tble PROTOITEMS) and one the shows acumulative
(comes from tble WEEK) .

I have a query :
SELECT Protocol.[Protocol Name], [Protocol Items].[Subjects Needed],
[Protocol Items].[Weeks Enroll], Week.[Week ID], Week.Week, Week.[Signed
Consent], (DSum("[Week].[Signed Consent]","Week","[Week ID]=" & Week.[Week
ID])) AS SIGNED, Week.[In Screening], DSum("[in screening]","[week]","[week
id] <= " & [week id]) AS [In Screen], Week.[Subj on Drug], Week.[Subj
Completed], Week.[Ramdomized LTF], Protocol.[Proto ID]
FROM (Protocol INNER JOIN [Protocol Items] ON Protocol.[Proto ID] =
[Protocol Items].[Proto ID]) INNER JOIN Week ON Protocol.[Proto ID] =
Week.[Proto ID]
GROUP BY Protocol.[Protocol Name], [Protocol Items].[Subjects Needed],
[Protocol Items].[Weeks Enroll], Week.[Week ID], Week.Week, Week.[Signed
Consent], Week.[In Screening], DSum("[in screening]","[week]","[week id] <= "
& [week id]), Week.[Subj on Drug], Week.[Subj Completed], Week.[Ramdomized
LTF], Protocol.[Proto ID];

and the graph's row source I have another query:
SELECT [qry Projected Enrollment].[Max Of Week], Sum([qry Projected
Enrollment].[Sign Cons]) AS [Signed Consent], Sum([qry Projected
Enrollment].[In Screen]) AS [In Screening], Sum([qry Projected
Enrollment].Rand) AS Randomized, [qry Projected Enrollment].Projected FROM
[qry Projected Enrollment] GROUP BY [qry Projected Enrollment].[Max Of Week],
[qry Projected Enrollment].Projected, [qry Projected Enrollment].[Subjects
Needed], [qry Projected Enrollment].[Weeks Enroll];

MY PROBLEM:

The query is not cumulating right, and I need to show in the graph by
PROTOID BY WEEKID.
Any suggestions??
TIA

 

[Vincent Johns]

I don't see a problem with posting on the previous newsgroup,
microsoft.public.access.queries, but I think it's confusing to have the
same (or very similar) questions on two separate newsgroups (both there,
and also here on microsoft.public.access.gettingstarted), so you'll find
my answer to this one (and the other related questions) there. People
who see this one would be unlikely to know about what anyone had said on
this thread on the other newsgroup.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hi All, I posted on the wrong thread, I am newbie to Access, any help will be
appreciated.
I have 4 tables:
PROTO PROTOITEMS WEEK
ADD
PROTOID ITEMID WEEKID
ADNAME
PROTONAME PROTONAME WEEK ADTYPE
SUBJ NEED SIGNED
etc
WEEKSENROLL INSCREEN
etc ETC

Anyway, my goal is to produce a multilinear graph with week along the x-axis
and cumulative along y-axis. There will be 2 lines for each PROTOID, one that
shows originated numbers (from Tble PROTOITEMS) and one the shows acumulative
(comes from tble WEEK) .

I have a query :
SELECT Protocol.[Protocol Name], [Protocol Items].[Subjects Needed],
[Protocol Items].[Weeks Enroll], Week.[Week ID], Week.Week, Week.[Signed
Consent], (DSum("[Week].[Signed Consent]","Week","[Week ID]=" & Week.[Week
ID])) AS SIGNED, Week.[In Screening], DSum("[in screening]","[week]","[week
id] <= " & [week id]) AS [In Screen], Week.[Subj on Drug], Week.[Subj
Completed], Week.[Ramdomized LTF], Protocol.[Proto ID]
FROM (Protocol INNER JOIN [Protocol Items] ON Protocol.[Proto ID] =
[Protocol Items].[Proto ID]) INNER JOIN Week ON Protocol.[Proto ID] =
Week.[Proto ID]
GROUP BY Protocol.[Protocol Name], [Protocol Items].[Subjects Needed],
[Protocol Items].[Weeks Enroll], Week.[Week ID], Week.Week, Week.[Signed
Consent], Week.[In Screening], DSum("[in screening]","[week]","[week id] <= "
& [week id]), Week.[Subj on Drug], Week.[Subj Completed], Week.[Ramdomized
LTF], Protocol.[Proto ID];

and the graph's row source I have another query:
SELECT [qry Projected Enrollment].[Max Of Week], Sum([qry Projected
Enrollment].[Sign Cons]) AS [Signed Consent], Sum([qry Projected
Enrollment].[In Screen]) AS [In Screening], Sum([qry Projected
Enrollment].Rand) AS Randomized, [qry Projected Enrollment].Projected FROM
[qry Projected Enrollment] GROUP BY [qry Projected Enrollment].[Max Of Week],
[qry Projected Enrollment].Projected, [qry Projected Enrollment].[Subjects
Needed], [qry Projected Enrollment].[Weeks Enroll];

MY PROBLEM:

The query is not cumulating right, and I need to show in the graph by
PROTOID BY WEEKID.
Any suggestions??
TIA