You have posted a couple of versions of your database (probably, I
guess, because you have made some changes to it), and that makes it
tricky for me to reproduce what you've done.
For example, the field names in your messages have not been as
consistent as I'd like to see... they need to be spelled the same way
everywhere you use them, so that Queries (and other objects) that depend
on them will use the proper fields. Also, knowing the name of a field
doesn't tell me much about what you have put there. Is it a name? Is
it a number? If it's a number, what does it mean? Must it be an
integer, or can it be a fraction, or can it represent an amount of
money? Stuff like that. I can infer some of the answers from your SQL,
but it would be easier to answer if you said a little bit about what is
supposed to be stored in each field.
It appears that you want to plot a graph based on the results of a Query
that is not working correctly. You have already posted a couple of
versions of your SQL (although some of the fields mentioned there you
did not list in your Table descriptions). You mentioned two examples
that looked something like these:
example
protocol weekid week signed screen fail lfu
dr201 1 1 3 3 0 0
dr201 2 2 4 2 1 0
dr302 3 1 5 5 0 0
dr302 4 2 7 3 2 0
1 - the query will accumulate by each detail weekly
These look like fields in the [WEEK] Table, though field
[WEEK].[weekname] was missing. Was this supposed to represent the raw
data in [WEEK], which form the basis for your Query and your graph?
protocol weekid week signed screen fail lfu
dr201 1 1 3 3 0 0
dr201 2 2 7 5 1 0
dr302 3 1 5 5 0 0
dr302 4 2 12 8 2 0
2- showing in the linear graphic filtering by
protocol
This looks like the result of running a Query that accumulates values,
but none of the Queries that I've seen you post recently will produce
these fields, so maybe this means something different. It's not clear
in either case what [lfu] means, as it's always zero in your examples.
Did you want to plot that?
Did you intend to plot all of the numbers shown here, as separate series
in your graph? It wasn't clear.
What I think would help me (and anyone else who wishes to offer advice) is
- list of each important field in each of your four Tables, showing
not just the name but what data type it is and (briefly) what it means.
Include every field that you mention in any of the Queries that you
are asking about.
- SQL of Queries that you are now trying to use to create your graph
- sample data for ech of the Tables (and what you've already posted
may be enough, I just wasn't sure what it meant)
- your desired output from your Query that you wish to use for graphing
You don't need to supply the output that your current Queries produce
from the sample data, because I can reconstruct that. But given the
rest of what I mentioned (and I think you've already posted much of it),
it should be relatively easy to come up with suggestions.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Thank you Vincent,
When I said that is wrong it is because it is not accumulating right, I
posted on the thread new user "CUMULATIVE". For example, if week 2 form PROTO
1 has 4 signed
will accumulate for the previous week, and show it on the linear graph, I
got the linear going on, but is not filtering by proto, I am not show how can
I put this together, here is the ROW SOURCE from the graph loks like:
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];
TIA
Post from other thread:
These lines wrapped on my newsreader; I think you have said that the
fields in each Table are as follows:
PROTO <-- You also called this "PROTOCOL"
---------
PROTOID
PROTONAME
PROTOITEMS
----------
ITEMID
PROTONAME
SUBJ NEED
WEEKSENROLL
etc
WEEK
---------
WEEKID
WEEK
SIGNED
INSCREEN
ETC
ADD
------
ADNAME
ADTYPE
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
Other related message:
... I am really struggling with this query.
The problem it showing on a linear graph filtering
by PROTOCOL. I have 2 important tables
PROTOCOL
protoid
protoname
WEEK
weekid
weekname
week
signed
screen
fail
lfu
Basically the query will use the data entered
on table week, and will use the data by protocol
and week
example
protocol weekid week signed screen fail lfu
dr201 1 1 3 3 0 0
dr201 2 2 4 2 1 0
dr302 3 1 5 5 0 0
dr302 4 2 7 3 2 0
1 - the query will accumulate by each detail weekly
protocol weekid week signed screen fail lfu
dr201 1 1 3 3 0 0
dr201 2 2 7 5 1 0
dr302 3 1 5 5 0 0
dr302 4 2 12 8 2 0
2- showing in the linear graphic filtering by
protocol it is another struggle.
Appreciate if you can help me!!
:
Savanah,
I ran the Query, got no error messages, and got sums of values for the
two records beginning with 3.
[qry Projected Enrollment] Query Datasheet View:
Max Of Sign In Rand Projected Subjects Weeks
Week Cons Screen Needed Enroll
---- ---- ------ ---- --------- -------- ------
3 5 8 9 2 4 3
2 9 5 6 7 2 4
3 2 2 2 2 4 3
This is the SQL I used, which is pretty similar to yours:
[Q_Savanah] SQL:
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];
[Q_Savanah] Query Datasheet View:
Max Of Signed In Randomized Projected
Week Consent Screening
------ ------- --------- ---------- ---------
2 9 5 6 7
3 7 10 11 2
These sums look accurate, based on the SQL. I'm not sure what you want.
For example, the [protoID] field does not appear in your Query. Do
you have an example showing a wrong calculation, and can you describe
why it's wrong and what would be the correct version?
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Savanah wrote:
Dear Vincent,
I am not sure how to do it, I am a newbie to Access, I did a lot of research
on this newsgroup, but I've been able to find the answer. It seem very simple
when I decide to do the database, if you see my posterior's post question, I
post the sql query, although is not working properly, because is summing all
the weeks, from all protocols. I am trying to show on a linear chart, the
chart row source looks like this:
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];
Appreciated if you can help me with it.
:
Michel Walsh gave you a pattern to follow, not the exact SQL. If you
post a copy of exactly the SQL that you tried to use, someone here can
probably explain what you need to do to fix it.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Savanah wrote:
I tried and the qry1 did not work it appears:"syntax erros (missing operator)
in query expression Proto ID.TIA
:
Hi,
You have to use two queries, one query embedded in the other.
The first query should sum by week, by protoID.
SELECT protoID, weekID, SUM( amount? ) As sumByWeekByProtoID
FROM whatever
GROUP BY protoID, weekID
[...]
