aggregate query

[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

 

[KARL DEWEY]

Post the SQL of your two queries.

 

[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

 

[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.

? 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

Post the SQL of your two queries.

.

 

[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.

? 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

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

.

.