A question on SQL

[Wei-Meng]

Hello all:
Got a question on SQL to ask:

Say I have an Access database containing a table with the following fields
and records:

Model Serial
A 1234
A 2345
A 3456
B 9876
B 8765

What is the SQL statement to generate the following result:

Model Serial Qty
A 1234
A 2345
A 3456 3
B 9876
B 8765 2

I need to print the subtotal for each model only once for each unique model.
How does the SQL look like?

Thanks!

 

['69 Camaro]

Hi, Wei-Meng.

I need to print the subtotal for each model only once for each unique
model.

Then you need to decide which row will show the count for that model. In
your example, you display the count for the A models next to the highest
serial number, but for the B models, you display the count next to the
lowest serial number.

If you want the count for each model to only display next to the highest
serial number for that model, try:

SELECT SM.Model, SM.Serial,
IIF(SM.Serial =
(SELECT MAX(Serial)
FROM tblSerialModels
WHERE Model = SM.Model),
(SELECT COUNT(Model)
FROM tblSerialModels
WHERE Model = SM.Model), NULL) AS Qty
FROM tblSerialModels AS SM
ORDER BY 1, 2;

.. . . where tblSerialModels is the name of the table.

How does the SQL look like?

Scary. (And slow if you have a lot of rows.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Wei-Meng]

Hi Gunny:
Thanks for the help! I tried it but it took a long time to execute and
no results were returned. Did I miss out anything?

Thanks!

 

[John W. Vinson]

Hello all:
Got a question on SQL to ask:

Say I have an Access database containing a table with the following fields
and records:

Model Serial
A 1234
A 2345
A 3456
B 9876
B 8765

What is the SQL statement to generate the following result:

Model Serial Qty
A 1234
A 2345
A 3456 3
B 9876
B 8765 2

I need to print the subtotal for each model only once for each unique model.
How does the SQL look like?

Thanks!

I would not *DO* this using a query; I'd do it using a Report. Reports have
very nice subtotaling tools, optional display, etc. - you're talking about a
data presentation problem (for which the Report is the appropriate tool)
rather than a data-analysis tool (where Queries shine).

John W. Vinson [MVP]

 

['69 Camaro]

Hi, Wei-Meng.

I tried it but it took a long time to execute

That's expected.

no results were returned. Did I miss out anything?

It should have returned a row in the query for each row in the table.
Please post your SQL statement.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[DrGUI]

If you are still looking for a SQL to generate this, try the following:

select model, serial,
(case when serial = (select max(serial) from tblTest1 where model=
tt1.model)
then cast((select count(model) from tblTest1 where model =
tt1.model) as varchar(5))
else ''
end) myCount
from tblTest1 tt1
order by model, serial

 

['69 Camaro]

Hi, DrGUI.

If you are still looking for a SQL to generate this, try the following:

select model, serial,
(case when serial = (select max(serial) from tblTest1 where model=
tt1.model)
then cast((select count(model) from tblTest1 where model =
tt1.model) as varchar(5))
else ''
end) myCount
from tblTest1 tt1
order by model, serial

Sorry. That syntax won't work in Jet SQL, which doesn't support the CASE
..... THEN statement block.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[DrGUI]

Sorry... I had my SQL Server T-SQL hat on. My bad. Try this, it should work:

SELECT tt1.model, tt1.serial, IIf(serial=(select max(serial) from tblTest1
where model= tt1.model),((select count(model) from tblTest1 where model =
tt1.model)),'') AS myCount
FROM tblTest1 AS tt1
ORDER BY tt1.model, tt1.serial;