Query Incorrect Grouping and Summing Problem

[ipk*]

Here is another question, that I can for the life of it not figure out:

I have a query (let's call it second query), that selects certain fields
from a first query. It does not do anything else, really, but remove all
superfluous fields from the first query, such that I can then group by the
remaining fields.

SELECT query_wellvolumes_via_table.GScreenId, "H2O" AS SubstanceName,
query_wellvolumes_via_table.WellPositionRow,
query_wellvolumes_via_table.WellPositionColumn,
Sum(query_wellvolumes_via_table.volGScreenComponent) AS
SummevonvolGScreenComponent
FROM query_wellvolumes_via_table
GROUP BY query_wellvolumes_via_table.GScreenId, "H2O",
query_wellvolumes_via_table.WellPositionRow,
query_wellvolumes_via_table.WellPositionColumn
HAVING
(((query_wellvolumes_via_table.GScreenId)=[Forms].[form_gscreen_pipetting_do].[cbo_SelectGScreen]));

Now, I want to sum over one of the fields (see above the SQL syntax) in the
second query. The trick seems to be, [volGScreenComponent], the field I want
to sum over, is really calculated from a whole bunch of parameters of my
first query (query_wellvolumes_via_table). My feeling is, that the query
shown above, for some reason still knows about the underlying first query,
and rather than returning 1 record for a certain Row and Column value, it
returns two or three, depending somehow what kind of parameters went into the
calculation of [volGScreenComponent] in the first query.

Can I turn this behaviour off ?

Now the one clutsy solution that occurs to me would be to write the query
result into a table, and then take it from there. That does not seem so
elegant, though.
Also, i am really worried about any zombie tables that one may forget to
clean out and update, and end up with obsolete values somewhere down the
line, without ever noticing ... and since I run up to 96 experiments in one
setup, there would be a continuous writing, updating and deleting of 96
tables, cluttering up my database. That does not seem right either ...

If someone could just nudge me into the right direction ... ???

Thanks a lot ...

Ingo

 

[KARL DEWEY]

I want to sum over one of the fields
What do you mean by this phrase?

 

[ipk*]

hello karl,

first of all ... thanks for trying to help.

What do you mean by this phrase?

uh oh ... i guess i am not using the right language ... well ... i am pretty
much access all self taught. and unfortunately have no formal education in
database design ... so please excuse.

to your question. i think it would be best answered by looking at the sql
query syntax below

SELECT query_wellvolumes_via_table.GScreenId, "H2O" AS SubstanceName,
query_wellvolumes_via_table.WellPositionRow,
query_wellvolumes_via_table.WellPositionColumn,
Sum(query_wellvolumes_via_table.volGScreenComponent) AS
SummevonvolGScreenComponent
FROM query_wellvolumes_via_table
GROUP BY query_wellvolumes_via_table.GScreenId, "H2O",
query_wellvolumes_via_table.WellPositionRow,
query_wellvolumes_via_table.WellPositionColumn
HAVING

(((query_wellvolumes_via_table.GScreenId)=[Forms].[form_gscreen_pipetting_do]. [cbo_SelectGScreen]));

as i said ... the sum is calculated correctly, but the grouping is all
wrong. although GScreenId (here 1), "H2O" (evidentally), WellPositionRow
(here 1), Well Position Column (here 1) are all identical, the query returns
2 or 3 lines rather then one, depending, i think, on whether the parameters
going into calculation of SummevonvolGScreenComponent (carried out in my
"first query") are two different ones or three. this must be feature, rather
than a bug, but how do i turn it off ... ?

not sure ... i think all the information is there. except, of course, i know
what all the fields are by heart and they would not mean anything to you.
please let me know, if you need anything else clarified.

please, let me know, if you need more data ... i'd be more than happy to
provide whatever i can, if that helps ...

ingo

--
Build a little, test a little.

Here is another question, that I can for the life of it not figure out:

I have a query (let's call it second query), that selects certain fields
from a first query. It does not do anything else, really, but remove all
superfluous fields from the first query, such that I can then group by the
remaining fields.

SELECT query_wellvolumes_via_table.GScreenId, "H2O" AS SubstanceName,
query_wellvolumes_via_table.WellPositionRow,
query_wellvolumes_via_table.WellPositionColumn,
Sum(query_wellvolumes_via_table.volGScreenComponent) AS
SummevonvolGScreenComponent
FROM query_wellvolumes_via_table
GROUP BY query_wellvolumes_via_table.GScreenId, "H2O",
query_wellvolumes_via_table.WellPositionRow,
query_wellvolumes_via_table.WellPositionColumn
HAVING
(((query_wellvolumes_via_table.GScreenId)=[Forms].[form_gscreen_pipetting_do].[cbo_SelectGScreen]));

Now, I want to sum over one of the fields (see above the SQL syntax) in the
second query. The trick seems to be, [volGScreenComponent], the field I want
to sum over, is really calculated from a whole bunch of parameters of my
first query (query_wellvolumes_via_table). My feeling is, that the query
shown above, for some reason still knows about the underlying first query,
and rather than returning 1 record for a certain Row and Column value, it
returns two or three, depending somehow what kind of parameters went into the
calculation of [volGScreenComponent] in the first query.

Can I turn this behaviour off ?

Now the one clutsy solution that occurs to me would be to write the query
result into a table, and then take it from there. That does not seem so
elegant, though.
Also, i am really worried about any zombie tables that one may forget to
clean out and update, and end up with obsolete values somewhere down the
line, without ever noticing ... and since I run up to 96 experiments in one
setup, there would be a continuous writing, updating and deleting of 96
tables, cluttering up my database. That does not seem right either ...

If someone could just nudge me into the right direction ... ???

Thanks a lot ...

Ingo

 

[ipk*]

ooops ... the problem, again, went away all by itself .. i'll be ...

it seems like certain changes to my vba only show results after a certain
time, or after a certain action i have not yet been able to nail down ...

maybe, also, i have to follow allen brown's suggestion to my other post ...
compact/decompile etc ... to clean up the database ...

sorry ... will hold back with my posts in the future for a day to say if
problems don't go away by themselves ... very strange ...

ingo