can this be done with a subquery?

G

Guest

Hi. I would like to know whether the following can be done using a subquery
(I 'm currently using 2 queries to get the desired result)

Consider the following table:

ID Date ProdID ModID Inv
------------------------------
1 1/1/1 1 1 10
2 1/2/1 1 2 50
3 1/2/1 2 0 100
4 1/3/1 1 1 20
5 1/4/1 2 0 80
6 1/5/1 1 2 70
7 1/5/1 3 0 30

What I would like to get is Sum(Last(Inv)) for each ProdID in order to get:

ProdID Inv
------------
1 90 (=20+70)
2 80
3 30

If I use only the Last() function, the result for ProdID=2 and ProdID=3
would be accurate. But since ProdID=1 has several SubIDs (ModID 1 and 2),
these would have to be added together. I'm therefore thinking that this
calculation should be feasible with a subquery, but I have no clue.

Thanks for your thoughts

bronson
 
G

Gary Walter

bronson said:
Hi. I would like to know whether the following can be done using a
subquery
(I 'm currently using 2 queries to get the desired result)

Consider the following table:

ID Date ProdID ModID Inv
------------------------------
1 1/1/1 1 1 10
2 1/2/1 1 2 50
3 1/2/1 2 0 100
4 1/3/1 1 1 20
5 1/4/1 2 0 80
6 1/5/1 1 2 70
7 1/5/1 3 0 30

What I would like to get is Sum(Last(Inv)) for each ProdID in order to
get:

ProdID Inv
------------
1 90 (=20+70)
2 80
3 30

If I use only the Last() function, the result for ProdID=2 and ProdID=3
would be accurate. But since ProdID=1 has several SubIDs (ModID 1 and 2),
these would have to be added together. I'm therefore thinking that this
calculation should be feasible with a subquery, but I have no clue.
Click to expand...
"Last" does not mean "Latest"

"Last" just means when I was organizing this group,
this is the field value for the last record I read....

That "last record" is determined by how the data is stored
and may or may not produce the "latest" record...

The SQL value of "Last" is that it is sometimes the case
where you want some "don't care" fields to come from
the same record within a group...

"Date" is an Access reserved word so suggest (if not too late)
that you change field name to something like "InvDate"

IMO there is nothing wrong with "divide-and-conquer"
when it comes to query solutions, i.e., multiple queries....

qryLatestOfProdModGroup:

SELECT
m.InvDate,
m.ProdID,
m.ModID,
m.Inv
FROM yurtable As m
WHERE
m.InvDate =
(SELECT Max(t.InvDate)
FROM yurtable As t
WHERE
t.ProdID = m.ProdID
AND
t.ModID = m.ModID);

should provide latest record (by InvDate)
for each group of ProdID/ModID.

Whether you then sum Inv over the
ProdID group using "qryLatestOfProdModGroup"

or, put above SQL in FROM clause of a summing
query over the ProdID group seems irrelevant to me.

Actually, I "divide-and-conquer" as a rule....
and sleep well at night..... :cool:
 
G

Gary Walter

In fact, with a large set of data, I would
probably use 3 saved queries to use the power
of relational dbs.

q1:

SELECT
t.ProdID,
t.ModID,
Max(t.InvDate) As LatestDate
FROM yurtable As t
Group By
t.ProdID,
t.ModID;

q2:

SELECT
t.ProdID,
t.ModID,
t.InvDate As LatestDate,
t.Inv
FROM
yurtable As t
INNER JOIN
q1
ON
t.ProdID = q1.ProdID
AND
t.ModID = q1.ModID
AND
t.InvDate = q1.LatestDate;

then sum q2.Inv over q2.ProdID
group in final query.....

it won't get much quicker than that
I imagine....
 
G

Gary Walter

bronson said:
Hi Gary

Thanks for your help. I'm currently using two queries. This works fine.
However, I have begun to clean up my database for 2 main reasons:

First, because it's a bit clogged up with as you would put it, "divide-and
conquer" queries
Second, because I would like to learn and understand new techniques
(subqueries in particular)

Your SQL is fine but it's approximately my first query. My second query
then
sums up every position based on ProdID only (There's no need for
sorting/grouping by ModID anymore). Do you have a suggestion how to
perform
the whole calculation in one single step? My guess is to embed your SQL in
an
even lager query that doesnt group by ModID
Click to expand...

q1:

SELECT
t.ProdID,
t.ModID,
Max(t.InvDate) As LatestDate
FROM yurtable As t
Group By
t.ProdID,
t.ModID;

q2:

SELECT
t.ProdID,
t.ModID,
t.InvDate As LatestDate,
t.Inv
FROM
yurtable As t
INNER JOIN
q1
ON
t.ProdID = q1.ProdID
AND
t.ModID = q1.ModID
AND
t.InvDate = q1.LatestDate;

then sum q2.Inv over q2.ProdID
group in final query.....

If your field names are named such that
they do not need bracketed, you could
replace "q1" in FROM clause of q2 with
actual SQL of q1. Just put brackets
around the SQL, end with period and
give it an alias of "q1"

SELECT
t.ProdID,
t.ModID,
t.InvDate As LatestDate,
t.Inv
FROM
yurtable As t
INNER JOIN
[SELECT
t.ProdID,
t.ModID,
Max(t.InvDate) As LatestDate
FROM yurtable As t
Group By
t.ProdID,
t.ModID]. As q1
ON
t.ProdID = q1.ProdID
AND
t.ModID = q1.ModID
AND
t.InvDate = q1.LatestDate;

This bracket thing is the killer.

The query parser will not allow any
*further brackets* within a subquery
used in the FROM clause.

It might be tempting to replace "q2"
in your summing query with SQL above
but Access will choke because we
already "have used up our brackets"
when we put them around q1.

Why not just use parentheses instead of
brackets? For example

SELECT
q2.ProdID,
q2.ModID,
q2.LatestDate,
SUM(q2.Inv) As InvSum
FROM
(SELECT
t.ProdID,
t.ModID,
t.InvDate As LatestDate,
t.Inv
FROM
yurtable As t
INNER JOIN
(SELECT
t.ProdID,
t.ModID,
Max(t.InvDate) As LatestDate
FROM yurtable As t
Group By
t.ProdID,
t.ModID) As q1
ON
t.ProdID = q1.ProdID
AND
t.ModID = q1.ModID
AND
t.InvDate = q1.LatestDate) As q2
GROUP BY
q2.ProdID,
q2.ModID,
q2.LatestDate;

which will "work" but when Access
gets around to saving the query, it will
try to change the parentheses to brackets
with a period and choke.

You see posts asking for help all the time
with this scenario, "it worked, but won't save"
or some such wording.

If your field names do not need brackets,
you get one level of subquery as table in
your big query.

We can go back to qryLatestOfProdModGroup

SELECT
m.InvDate,
m.ProdID,
m.ModID,
m.Inv
FROM yurtable As m
WHERE
m.InvDate =
(SELECT Max(t.InvDate)
FROM yurtable As t
WHERE
t.ProdID = m.ProdID
AND
t.ModID = m.ModID);

which found our latest date through a correlated
subquery. We can use that SQL as a "table" q2
in our summing query:

SELECT
q2.ProdID,
q2.ModID,
q2.LatestDate,
SUM(q2.Inv) As InvSum
FROM
[SELECT
m.InvDate As LatestDate,
m.ProdID,
m.ModID,
m.Inv
FROM yurtable As m
WHERE
m.InvDate =
(SELECT Max(t.InvDate)
FROM yurtable As t
WHERE
t.ProdID = m.ProdID
AND
t.ModID = m.ModID)]. As q2
GROUP BY
q2.ProdID,
q2.ModID,
q2.LatestDate;

hopefully the actual name of "yurtable"
did not require it to be bracketed, or
the bracket-within-a-bracket gotcha
will raise its ugly head again.

Did that help?

You can "design" with parentheses instead
of brackets, but just remember that Access
will try to change when query is saved....
 
G

Gary Walter

as far as what you *originally* asked for (sorry),
I think this will do it all in one query (untested):

SELECT
q2.ProdID,
SUM(q2.Inv) As InvSum
FROM
[SELECT
m.InvDate As LatestDate,
m.ProdID,
m.ModID,
m.Inv
FROM yurtable As m
WHERE
m.InvDate =
(SELECT Max(t.InvDate)
FROM yurtable As t
WHERE
t.ProdID = m.ProdID
AND
t.ModID = m.ModID)]. As q2
GROUP BY
q2.ProdID;


Gary Walter said:
"bronson"wrote
Thanks for your help. I'm currently using two queries. This works fine.
However, I have begun to clean up my database for 2 main reasons:

First, because it's a bit clogged up with as you would put it,
"divide-and
conquer" queries
Second, because I would like to learn and understand new techniques
(subqueries in particular)

Your SQL is fine but it's approximately my first query. My second query
then
sums up every position based on ProdID only (There's no need for
sorting/grouping by ModID anymore). Do you have a suggestion how to
perform
the whole calculation in one single step? My guess is to embed your SQL
in an
even lager query that doesnt group by ModID
Click to expand...

q1:

SELECT
t.ProdID,
t.ModID,
Max(t.InvDate) As LatestDate
FROM yurtable As t
Group By
t.ProdID,
t.ModID;

q2:

SELECT
t.ProdID,
t.ModID,
t.InvDate As LatestDate,
t.Inv
FROM
yurtable As t
INNER JOIN
q1
ON
t.ProdID = q1.ProdID
AND
t.ModID = q1.ModID
AND
t.InvDate = q1.LatestDate;

then sum q2.Inv over q2.ProdID
group in final query.....

If your field names are named such that
they do not need bracketed, you could
replace "q1" in FROM clause of q2 with
actual SQL of q1. Just put brackets
around the SQL, end with period and
give it an alias of "q1"

SELECT
t.ProdID,
t.ModID,
t.InvDate As LatestDate,
t.Inv
FROM
yurtable As t
INNER JOIN
[SELECT
t.ProdID,
t.ModID,
Max(t.InvDate) As LatestDate
FROM yurtable As t
Group By
t.ProdID,
t.ModID]. As q1
ON
t.ProdID = q1.ProdID
AND
t.ModID = q1.ModID
AND
t.InvDate = q1.LatestDate;

This bracket thing is the killer.

The query parser will not allow any
*further brackets* within a subquery
used in the FROM clause.

It might be tempting to replace "q2"
in your summing query with SQL above
but Access will choke because we
already "have used up our brackets"
when we put them around q1.

Why not just use parentheses instead of
brackets? For example

SELECT
q2.ProdID,
q2.ModID,
q2.LatestDate,
SUM(q2.Inv) As InvSum
FROM
(SELECT
t.ProdID,
t.ModID,
t.InvDate As LatestDate,
t.Inv
FROM
yurtable As t
INNER JOIN
(SELECT
t.ProdID,
t.ModID,
Max(t.InvDate) As LatestDate
FROM yurtable As t
Group By
t.ProdID,
t.ModID) As q1
ON
t.ProdID = q1.ProdID
AND
t.ModID = q1.ModID
AND
t.InvDate = q1.LatestDate) As q2
GROUP BY
q2.ProdID,
q2.ModID,
q2.LatestDate;

which will "work" but when Access
gets around to saving the query, it will
try to change the parentheses to brackets
with a period and choke.

You see posts asking for help all the time
with this scenario, "it worked, but won't save"
or some such wording.

If your field names do not need brackets,
you get one level of subquery as table in
your big query.

We can go back to qryLatestOfProdModGroup

SELECT
m.InvDate,
m.ProdID,
m.ModID,
m.Inv
FROM yurtable As m
WHERE
m.InvDate =
(SELECT Max(t.InvDate)
FROM yurtable As t
WHERE
t.ProdID = m.ProdID
AND
t.ModID = m.ModID);

which found our latest date through a correlated
subquery. We can use that SQL as a "table" q2
in our summing query:

SELECT
q2.ProdID,
q2.ModID,
q2.LatestDate,
SUM(q2.Inv) As InvSum
FROM
[SELECT
m.InvDate As LatestDate,
m.ProdID,
m.ModID,
m.Inv
FROM yurtable As m
WHERE
m.InvDate =
(SELECT Max(t.InvDate)
FROM yurtable As t
WHERE
t.ProdID = m.ProdID
AND
t.ModID = m.ModID)]. As q2
GROUP BY
q2.ProdID,
q2.ModID,
q2.LatestDate;

hopefully the actual name of "yurtable"
did not require it to be bracketed, or
the bracket-within-a-bracket gotcha
will raise its ugly head again.

Did that help?

You can "design" with parentheses instead
of brackets, but just remember that Access
will try to change when query is saved....
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting distinct counts in a totals query 3
Subquery not integrating to query to display previous sum of quantities 3
Select Records based another table... 2
WCG Stats Thursday 05 October 2023 3
WCG Stats Tuesday 02 May 2023 2
WCG Stats Saturday 13 May 2023 3
WCG Stats Sunday 11 June 2023 2
WCG Stats Monday 13 February 2023 3

Top