aggregate query

F

Frank De Wael

Hi,
I'm successfully grouping an articles table by Category, ProductGroup, and
ProductFamily;
Since this query is used to select 1 article I needed the articleID to
return the 'first' in each group.
Now my client doesn't agree to show the first of each group but wants to
show another article (wich has a better representative image) in each group.
I therefore gave the ArticleTable a CheckBox field so he can choose which
article needs to be showed.

Adding this checkbox field to my query does result in the correct returned
records using 'min' on this checkboxfield but : How can I return this
(checked) ArticleID ?
note : not all groups will have checked (preferred) articles !!
thanks
f
 
F

Frank De Wael

? SRY Cannot reply to KARLDEWEY ??

1st query
SELECT First(tblartikels.Id) AS ArtID,
IIf([Family],[FamilyName],[ArtikelNaam]) AS Artikel, tblartikels.Indeling,
[tblArtikels.ProductGroep] AS ProdGrID
FROM tblProductgroep RIGHT JOIN (tblFamily RIGHT JOIN tblartikels ON
tblFamily.FamilyID = tblartikels.Family) ON tblProductgroep.Id =
tblartikels.ProductGroep
GROUP BY IIf([Family],[FamilyName],[ArtikelNaam]), tblartikels.Indeling,
[tblArtikels.ProductGroep]
HAVING (((tblartikels.Indeling)="INDOOR") AND
(([tblArtikels.ProductGroep])=1));


2nd query with the checkbox:

SELECT IIf([Family],[FamilyName],[ArtikelNaam]) AS Artikel,
tblartikels.Indeling, [tblArtikels.ProductGroep] AS ProdGrID,
Min(tblartikels.Order) AS MinVanOrder
FROM tblProductgroep RIGHT JOIN (tblFamily RIGHT JOIN tblartikels ON
tblFamily.FamilyID = tblartikels.Family) ON tblProductgroep.Id =
tblartikels.ProductGroep
GROUP BY IIf([Family],[FamilyName],[ArtikelNaam]), tblartikels.Indeling,
[tblArtikels.ProductGroep]
HAVING (((tblartikels.Indeling)="INDOOR") AND
(([tblArtikels.ProductGroep])=1));


Thank U
F
 
K

KARL DEWEY

Try this --
SELECT (tblartikels.Id) AS ArtID, IIf([Family],[FamilyName],[ArtikelNaam])
AS Artikel, tblartikels.Indeling, [tblArtikels.ProductGroep] AS ProdGrID
FROM tblProductgroep RIGHT JOIN (tblFamily RIGHT JOIN tblartikels ON
tblFamily.FamilyID = tblartikels.Family) ON tblProductgroep.Id =
tblartikels.ProductGroep
WHERE (((tblartikels.Indeling)="INDOOR") AND
(([tblArtikels.ProductGroep])=1)) AND ((tblartikels.Order) =-1);

--
Build a little, test a little.


Frank De Wael said:
? SRY Cannot reply to KARLDEWEY ??

1st query
SELECT First(tblartikels.Id) AS ArtID,
IIf([Family],[FamilyName],[ArtikelNaam]) AS Artikel, tblartikels.Indeling,
[tblArtikels.ProductGroep] AS ProdGrID
FROM tblProductgroep RIGHT JOIN (tblFamily RIGHT JOIN tblartikels ON
tblFamily.FamilyID = tblartikels.Family) ON tblProductgroep.Id =
tblartikels.ProductGroep
GROUP BY IIf([Family],[FamilyName],[ArtikelNaam]), tblartikels.Indeling,
[tblArtikels.ProductGroep]
HAVING (((tblartikels.Indeling)="INDOOR") AND
(([tblArtikels.ProductGroep])=1));


2nd query with the checkbox:

SELECT IIf([Family],[FamilyName],[ArtikelNaam]) AS Artikel,
tblartikels.Indeling, [tblArtikels.ProductGroep] AS ProdGrID,
Min(tblartikels.Order) AS MinVanOrder
FROM tblProductgroep RIGHT JOIN (tblFamily RIGHT JOIN tblartikels ON
tblFamily.FamilyID = tblartikels.Family) ON tblProductgroep.Id =
tblartikels.ProductGroep
GROUP BY IIf([Family],[FamilyName],[ArtikelNaam]), tblartikels.Indeling,
[tblArtikels.ProductGroep]
HAVING (((tblartikels.Indeling)="INDOOR") AND
(([tblArtikels.ProductGroep])=1));


Thank U
F



KARL DEWEY said:
Post the SQL of your two queries.
Click to expand...

.
Click to expand...
 
F

Frank De Wael

This apporach returns only one record (one is checked for 1 family) and
would mean that client needs to check in all articleFamilies a preferred
record and when articleFamily is not required the article still needs to be
checked; this is a workaround which would return the correct records ...
I was hoping for another (group by) solution build on other criteria...
frank


----- Original Message -----
From: "KARL DEWEY" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Wednesday, May 12, 2010 7:17 PM
Subject: Re: aggregate query

Try this --
SELECT (tblartikels.Id) AS ArtID, IIf([Family],[FamilyName],[ArtikelNaam])
AS Artikel, tblartikels.Indeling, [tblArtikels.ProductGroep] AS ProdGrID
FROM tblProductgroep RIGHT JOIN (tblFamily RIGHT JOIN tblartikels ON
tblFamily.FamilyID = tblartikels.Family) ON tblProductgroep.Id =
tblartikels.ProductGroep
WHERE (((tblartikels.Indeling)="INDOOR") AND
(([tblArtikels.ProductGroep])=1)) AND ((tblartikels.Order) =-1);

--
Build a little, test a little.


Frank De Wael said:
? SRY Cannot reply to KARLDEWEY ??

1st query
SELECT First(tblartikels.Id) AS ArtID,
IIf([Family],[FamilyName],[ArtikelNaam]) AS Artikel,
tblartikels.Indeling,
[tblArtikels.ProductGroep] AS ProdGrID
FROM tblProductgroep RIGHT JOIN (tblFamily RIGHT JOIN tblartikels ON
tblFamily.FamilyID = tblartikels.Family) ON tblProductgroep.Id =
tblartikels.ProductGroep
GROUP BY IIf([Family],[FamilyName],[ArtikelNaam]), tblartikels.Indeling,
[tblArtikels.ProductGroep]
HAVING (((tblartikels.Indeling)="INDOOR") AND
(([tblArtikels.ProductGroep])=1));


2nd query with the checkbox:

SELECT IIf([Family],[FamilyName],[ArtikelNaam]) AS Artikel,
tblartikels.Indeling, [tblArtikels.ProductGroep] AS ProdGrID,
Min(tblartikels.Order) AS MinVanOrder
FROM tblProductgroep RIGHT JOIN (tblFamily RIGHT JOIN tblartikels ON
tblFamily.FamilyID = tblartikels.Family) ON tblProductgroep.Id =
tblartikels.ProductGroep
GROUP BY IIf([Family],[FamilyName],[ArtikelNaam]), tblartikels.Indeling,
[tblArtikels.ProductGroep]
HAVING (((tblartikels.Indeling)="INDOOR") AND
(([tblArtikels.ProductGroep])=1));


Thank U
F



KARL DEWEY said:
Post the SQL of your two queries.
--
Build a little, test a little.


:

Hi,
I'm successfully grouping an articles table by Category, ProductGroup,
and
ProductFamily;
Since this query is used to select 1 article I needed the articleID to
return the 'first' in each group.
Now my client doesn't agree to show the first of each group but wants
to
show another article (wich has a better representative image) in each
group.
I therefore gave the ArticleTable a CheckBox field so he can choose
which
article needs to be showed.

Adding this checkbox field to my query does result in the correct
returned
records using 'min' on this checkboxfield but : How can I return this
(checked) ArticleID ?
note : not all groups will have checked (preferred) articles !!
thanks
f

.
Click to expand...

.
Click to expand...
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

Query from 3 tables 1
Query filtering by Aggregate 3
Help with Parameter Query 1
Query from 3 tables 3
Aggregate calculations on calculated query fields? 6
Cannot have aggregate function in WHERE clause 4
Query for weeks, getting aggregate func. error 5
Group by query question 1

Top