an unusual distinct query

[Guest]

here is my tabel:

page1 chapter1
page2 chapter1
page3 chapter1
page4 chapter2
page5 chapter2
page6 chapter3

and I would like to make a query to give me:

page1 chapter1
page4 chapter2
page6 chapter3

Help please? anyone?

 

[Douglas J. Steele]

Assuming your table fields are named PageName and ChapterName, you could
try:

SELECT Min([PageName]), ChapterName
FROM MyTable
GROUP BY ChapterName

Note that this probably won't work with your existing page numbering: since
it's text, it's going to sort as Page1, Page10, Page11, Page2, Page3, ...

 

[Marshall Barton]

here is my tabel:

page1 chapter1
page2 chapter1
page3 chapter1
page4 chapter2
page5 chapter2
page6 chapter3

and I would like to make a query to give me:

page1 chapter1
page4 chapter2
page6 chapter3

If those fields are just numbers (without the word page),
then use:

SELECT Chapter, Min(Page) As StartPage
FROM table
GROUP BY Chapter

 

[Guest]

I wrote:

SELECT Min(page_numbers.page_no), page_numbers.chap_no
FROM page_numbers;

and I get this error:

You tried to execute a query that does not include the specified expression
'chap_no' as part of an aggregate function

I have no idea what it means.

 

[Douglas J. Steele]

Did you not notice the GROUP BY clause both Marsh & I had in our
suggestions?

SELECT Min(page_numbers.page_no), page_numbers.chap_no
FROM page_numbers
GROUP BY page_numbers.chap_no

 

[Guest]

Yes, I did miss that. It worked once I added it, thank you very much