|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 12th, 2010, 02:25 PM
posted to microsoft.public.access.queries
|
|||
|
|||
aggregate query
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 
|
|
#2
May 12th, 2010, 03:45 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
aggregate query
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 .
|
|
#3
May 12th, 2010, 05:32 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
aggregate query
? 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" schreef in bericht news  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 .
|
|
#4
May 12th, 2010, 06:17 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
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" schreef in bericht news 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 . .
|
|
#5
May 13th, 2010, 10:43 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
aggregate query
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" Newsgroups: microsoft.public.access.queries Sent: Wednesday, May 12, 2010 7:17 PM Subject: 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" schreef in bericht news 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 . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
