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

Post the SQL of your two queries.
--
Build a little, test a little.


"Frank De Wael" wrote:

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

? 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" <(E-Mail Removed)> schreef in bericht
news28B17F4-7281-4C3D-B943-(E-Mail Removed)...
> Post the SQL of your two queries.
> --
> Build a little, test a little.
>
>
> "Frank De Wael" wrote:
>
>> 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
>>
>> .
>>

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

> ? 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" <(E-Mail Removed)> schreef in bericht
> news28B17F4-7281-4C3D-B943-(E-Mail Removed)...
> > Post the SQL of your two queries.
> > --
> > Build a little, test a little.
> >
> >
> > "Frank De Wael" wrote:
> >
> >> 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
> >>
> >> .
> >>
>
> .
>

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" <(E-Mail Removed)>
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" wrote:
>
>> ? 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" <(E-Mail Removed)> schreef in bericht
>> news28B17F4-7281-4C3D-B943-(E-Mail Removed)...
>> > Post the SQL of your two queries.
>> > --
>> > Build a little, test a little.
>> >
>> >
>> > "Frank De Wael" wrote:
>> >
>> >> 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
>> >>
>> >> .
>> >>
>>
>> .
>>