Re: QUERY STRUGG

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 <vjohns@xxxxxxxxxxxxxxxxxx>
  Please feel free to quote anything I say here.


Savanah wrote:

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:

Savanah wrote:
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

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!! 






"Vincent Johns" wrote:


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 Data*** 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 Data*** 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 <vjohns@xxxxxxxxxxxxxxxxxx>
  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.


"Vincent Johns" wrote:



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 <vjohns@xxxxxxxxxxxxxxxxxx>
 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

"Michel Walsh" wrote:




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


[...]


.

Quantcast