aql query

[Sagi]

i'm using access and got a table with the fields:
CatName, CatOrder and StoreID.
i need to get the list of all the CatName and to know how many result i got
or what is the max value in the CatOrder field.
can it be done with only one query or i need to do the max query first?

[Mike MacSween]

"Sagi" <sa**@schieber.net> wrote in message
news:42******@news.bezeqint.net...

i'm using access and got a table with the fields:
CatName, CatOrder and StoreID.
i need to get the list of all the CatName and to know how many result i
got or what is the max value in the CatOrder field.
can it be done with only one query or i need to do the max query first?

A little unclear what you want but this:

SELECT [tblOrders].[CatName], Count([tblOrders].[CatName]) AS numCatName,
Max([tblOrders].[CatOrder]) AS maxCatOrder
FROM tblOrders
GROUP BY [tblOrders].[CatName];

will give you:

one row for each CatName in the table
column 1 will be the CatName
Column 2 will be the number of times each CatName appears in the table
Column 3 will be the maximum value of CatOrder for that CatName.

Mike

[Sagi]

sorry, this i know, but it's not what i need. i will try again.
i need to show all the rows and to know the max(CatOrder) for all the rows.
when i do it i get only one row back, the one that got the max(CatOrder).
i need to know the max(CatOrder) and to get all the rows back too.
do i must use 2 seperated queries??

"Sagi" <sa**@schieber.net> wrote in message
news:42******@news.bezeqint.net...

i'm using access and got a table with the fields:
CatName, CatOrder and StoreID.
i need to get the list of all the CatName and to know how many result i
got or what is the max value in the CatOrder field.
can it be done with only one query or i need to do the max query first?

[Sagi]

sorry, this i know, but it's not what i need. i will try again.
i need to show all the rows and to know the max(CatOrder) for all the rows.
when i do it i get only one row back, the one that got the max(CatOrder).
i need to know the max(CatOrder) and to get all the rows back too.
do i must use 2 seperated queries??

"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

"Sagi" <sa**@schieber.net> wrote in message
news:42******@news.bezeqint.net...

i'm using access and got a table with the fields:
CatName, CatOrder and StoreID.
i need to get the list of all the CatName and to know how many result i
got or what is the max value in the CatOrder field.
can it be done with only one query or i need to do the max query first?

A little unclear what you want but this:

SELECT [tblOrders].[CatName], Count([tblOrders].[CatName]) AS numCatName,
Max([tblOrders].[CatOrder]) AS maxCatOrder
FROM tblOrders
GROUP BY [tblOrders].[CatName];

will give you:

one row for each CatName in the table
column 1 will be the CatName
Column 2 will be the number of times each CatName appears in the table
Column 3 will be the maximum value of CatOrder for that CatName.

Mike

[Lyle Fairfield]

Sagi wrote:

sorry, this i know, but it's not what i need. i will try again.
i need to show all the rows and to know the max(CatOrder) for all the rows.
when i do it i get only one row back, the one that got the max(CatOrder).
i need to know the max(CatOrder) and to get all the rows back too.
do i must use 2 seperated queries??

Air SQL (refers to your table as Table1)

SELECT t.*, sq.MaxCatOrder
FROM Table1 t
LEFT JOIN
[SELECT Max(CatOrder) AS MaxCatOrder FROM Table1]. sq
ON sq.MaxCatOrder>=t.CatOrder

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[Mike MacSween]

"Sagi" <sa**@schieber.net> wrote in message
news:42******@news.bezeqint.net...

sorry, this i know, but it's not what i need. i will try again.
i need to show all the rows and to know the max(CatOrder) for all the
rows.
when i do it i get only one row back, the one that got the max(CatOrder).
i need to know the max(CatOrder) and to get all the rows back too.
do i must use 2 seperated queries??

SELECT tblOrders.CatName, Count(tblOrders.CatName) AS numCatName,
First((SELECT max(CatOrder) FROM tblOrders)) AS maxAllCatOrder
FROM tblOrders
GROUP BY tblOrders.CatName;

Maybe?

It's still not entirely clear what you want. When you say 'i need to show
all the rows ' do you mean each and every row in the table? Or one row for
each CatName (Which is what the above will give you)?

Mike

[Sagi]

lets say that i got 12 rows of catID 1 and catOrder (numbers between 1-30)
and 12 rows of cat iD 2 and catOrder (numbers between 1-30) and .
i need get all the 24 records back and to know what is the the max value of
the catOrder for each catID.
can it be done with one query?
"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

"Sagi" <sa**@schieber.net> wrote in message
news:42******@news.bezeqint.net...

sorry, this i know, but it's not what i need. i will try again.
i need to show all the rows and to know the max(CatOrder) for all the
rows.
when i do it i get only one row back, the one that got the max(CatOrder).
i need to know the max(CatOrder) and to get all the rows back too.
do i must use 2 seperated queries??

SELECT tblOrders.CatName, Count(tblOrders.CatName) AS numCatName,
First((SELECT max(CatOrder) FROM tblOrders)) AS maxAllCatOrder
FROM tblOrders
GROUP BY tblOrders.CatName;

Maybe?

It's still not entirely clear what you want. When you say 'i need to show
all the rows ' do you mean each and every row in the table? Or one row for
each CatName (Which is what the above will give you)?

Mike

[Mike MacSween]

So where did CatID come from?

"Sagi" <sa**@schieber.net> wrote in message
news:42********@news.bezeqint.net...

lets say that i got 12 rows of catID 1 and catOrder (numbers between 1-30)
and 12 rows of cat iD 2 and catOrder (numbers between 1-30) and .
i need get all the 24 records back and to know what is the the max value
of the catOrder for each catID.
can it be done with one query?
"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

"Sagi" <sa**@schieber.net> wrote in message
news:42******@news.bezeqint.net...

sorry, this i know, but it's not what i need. i will try again.
i need to show all the rows and to know the max(CatOrder) for all the
rows.
when i do it i get only one row back, the one that got the
max(CatOrder).
i need to know the max(CatOrder) and to get all the rows back too.
do i must use 2 seperated queries??

SELECT tblOrders.CatName, Count(tblOrders.CatName) AS numCatName,
First((SELECT max(CatOrder) FROM tblOrders)) AS maxAllCatOrder
FROM tblOrders
GROUP BY tblOrders.CatName;

Maybe?

It's still not entirely clear what you want. When you say 'i need to show
all the rows ' do you mean each and every row in the table? Or one row
for each CatName (Which is what the above will give you)?

Mike

[Mike MacSween]

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:4S****************@read1.cgocable.net...

Sagi wrote:

sorry, this i know, but it's not what i need. i will try again.
i need to show all the rows and to know the max(CatOrder) for all the
rows.
when i do it i get only one row back, the one that got the max(CatOrder).
i need to know the max(CatOrder) and to get all the rows back too.
do i must use 2 seperated queries??

Air SQL (refers to your table as Table1)

SELECT t.*, sq.MaxCatOrder
FROM Table1 t
LEFT JOIN
[SELECT Max(CatOrder) AS MaxCatOrder FROM Table1]. sq
ON sq.MaxCatOrder>=t.CatOrder

I don't think he wants that Lyle.

SELECT *
FROM tblOrders AS t
LEFT JOIN [SELECT CatID, Max(CatOrder) AS MaxCatOrder FROM tblOrders GROUP
BY CatID]. AS sq
ON t.CatID = sq.CatID;

perhaps

Mike

[Mike MacSween]

"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:4S****************@read1.cgocable.net...

In fact

SELECT *
FROM tblOrders AS t INNER JOIN [SELECT CatID, Max(CatOrder) AS MaxCatOrder
FROM tblOrders GROUP BY CatID]. AS sq ON t.CatID = sq.CatID;

without the left join.

[Mike MacSween]

"Sagi" <sa**@schieber.net> wrote in message
news:42********@news.bezeqint.net...

lets say that i got 12 rows of catID 1 and catOrder (numbers between 1-30)
and 12 rows of cat iD 2 and catOrder (numbers between 1-30) and .
i need get all the 24 records back and to know what is the the max value
of the catOrder for each catID.
can it be done with one query?

Does a particular value of CatID always = a particular value for CatName?

If so then that should be in a seperate table. I think. Depending what
CatOrder means, and StoreID.

Mike

[Sagi]

i got a field that called CatID with values (1 or 2).
i got a field that called CatOrder with values (1-30)
i got a field that called CatName with the names of the categories.

i need to print out all the names of the categories ordered by CatID and
CatOrder and to print out the maximum value max(CatOrder) when catID=1 and
Max(catOrder) when catID = 2.
can it be done with only one query

thanks

sagi

"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

"Sagi" <sa**@schieber.net> wrote in message
news:42********@news.bezeqint.net...

lets say that i got 12 rows of catID 1 and catOrder (numbers between
1-30) and 12 rows of cat iD 2 and catOrder (numbers between 1-30) and .
i need get all the 24 records back and to know what is the the max value
of the catOrder for each catID.
can it be done with one query?

Does a particular value of CatID always = a particular value for CatName?

If so then that should be in a seperate table. I think. Depending what
CatOrder means, and StoreID.

Mike

[Mike MacSween]

"Sagi" <sa**@schieber.net> wrote in message
news:42********@news.bezeqint.net...

i got a field that called CatID with values (1 or 2).
i got a field that called CatOrder with values (1-30)
i got a field that called CatName with the names of the categories.

i need to print out all the names of the categories ordered by CatID and
CatOrder and to print out the maximum value max(CatOrder) when catID=1 and
Max(catOrder) when catID = 2.
can it be done with only one query

try this

SELECT *
FROM tblOrders AS t INNER JOIN [SELECT CatID, Max(CatOrder) AS MaxCatOrder
FROM tblOrders GROUP BY CatID]. AS sq ON t.CatID = sq.CatID;

[Sagi]

is there a way i can send you the database, my english isn't to good, sorry,
not my first language.

"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

"Sagi" <sa**@schieber.net> wrote in message
news:42********@news.bezeqint.net...

i got a field that called CatID with values (1 or 2).
i got a field that called CatOrder with values (1-30)
i got a field that called CatName with the names of the categories.

i need to print out all the names of the categories ordered by CatID and
CatOrder and to print out the maximum value max(CatOrder) when catID=1
and Max(catOrder) when catID = 2.
can it be done with only one query

try this

SELECT *
FROM tblOrders AS t INNER JOIN [SELECT CatID, Max(CatOrder) AS MaxCatOrder
FROM tblOrders GROUP BY CatID]. AS sq ON t.CatID = sq.CatID;

[Sagi]

tblCat
autoID CatID CatOrder CatName
1 1 1 lamp
2 2 6 ship
3 2 5 ggjh
4 1 4 hhuhui
5 1 3 jljjj
6 2 8 fdsm;s

i need to know that 8 is the higest number for catID= 2 and 4 is the highest
number for catID 1 and to print out all the records.
is that make sence??

sagi

"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

"Sagi" <sa**@schieber.net> wrote in message
news:42********@news.bezeqint.net...

i got a field that called CatID with values (1 or 2).
i got a field that called CatOrder with values (1-30)
i got a field that called CatName with the names of the categories.

i need to print out all the names of the categories ordered by CatID and
CatOrder and to print out the maximum value max(CatOrder) when catID=1
and Max(catOrder) when catID = 2.
can it be done with only one query

try this

SELECT *
FROM tblOrders AS t INNER JOIN [SELECT CatID, Max(CatOrder) AS MaxCatOrder
FROM tblOrders GROUP BY CatID]. AS sq ON t.CatID = sq.CatID;

[Sagi]

i think this one is working
thank you so so so much

sagi
"Mike MacSween" <mi***************************@btinternet.com> wrote in
message news:42***********************@news.aaisp.net.uk.. .

"Sagi" <sa**@schieber.net> wrote in message
news:42********@news.bezeqint.net...

i got a field that called CatID with values (1 or 2).
i got a field that called CatOrder with values (1-30)
i got a field that called CatName with the names of the categories.

i need to print out all the names of the categories ordered by CatID and
CatOrder and to print out the maximum value max(CatOrder) when catID=1
and Max(catOrder) when catID = 2.
can it be done with only one query

try this

SELECT *
FROM tblOrders AS t INNER JOIN [SELECT CatID, Max(CatOrder) AS MaxCatOrder
FROM tblOrders GROUP BY CatID]. AS sq ON t.CatID = sq.CatID;