Showing only one record

[ktm400]

I have a SQL table Iam selecting items from (obdc linked) and I want to only
show 1 record of a group. The one I want has the latest date..
How would I query to get this?
Thanks for any help

 

[Brendan Reynolds]

Here's an example using the Orders table from the SQL Server version of
Northwind ...

SELECT dbrders.CustomerID, Max(dbrders.OrderDate) AS MaxOfOrderDate
FROM dbrders
GROUP BY dbrders.CustomerID
ORDER BY dbrders.CustomerID;

 

[ktm400]

Thank you.... another question:
This works
SELECT Max([Press 2]!Time) AS [Time]
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

But this does not
SELECT Max([Press 2]!Time) AS [Time], [Press 2].BlankID, [Press 2].ProgramNo
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

Why can't I show the additional columns?

 

[Brendan Reynolds]

When you use aggregate functions (such as Max) each column named in the
SELECT clause must be part of an aggregate function or a GROUP BY clause.
Notice in my original example that CustomerID appears in the GROUP BY clause
as well as in the SELECT clause - the example displays each customer ID
along with the date of the most recent order by that customer.

Sometimes (not always, but sometimes) it might be necessary to use two or
more queries to bring together the information you want. For example, if I
wanted to include additional information about a customer, I might use the
query in my original example and then join that query to the customers table
on the CustomerID column to bring in columns from the customer table.

--
Brendan Reynolds (MVP)

Thank you.... another question:
This works
SELECT Max([Press 2]!Time) AS [Time]
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

But this does not
SELECT Max([Press 2]!Time) AS [Time], [Press 2].BlankID, [Press
2].ProgramNo
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

Why can't I show the additional columns?

Here's an example using the Orders table from the SQL Server version of
Northwind ...

SELECT dbrders.CustomerID, Max(dbrders.OrderDate) AS MaxOfOrderDate
FROM dbrders
GROUP BY dbrders.CustomerID
ORDER BY dbrders.CustomerID;

 

[ktm400]

Thanks Brendan....

When you use aggregate functions (such as Max) each column named in the
SELECT clause must be part of an aggregate function or a GROUP BY clause.
Notice in my original example that CustomerID appears in the GROUP BY clause
as well as in the SELECT clause - the example displays each customer ID
along with the date of the most recent order by that customer.

Sometimes (not always, but sometimes) it might be necessary to use two or
more queries to bring together the information you want. For example, if I
wanted to include additional information about a customer, I might use the
query in my original example and then join that query to the customers table
on the CustomerID column to bring in columns from the customer table.

--
Brendan Reynolds (MVP)

Thank you.... another question:
This works
SELECT Max([Press 2]!Time) AS [Time]
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

But this does not
SELECT Max([Press 2]!Time) AS [Time], [Press 2].BlankID, [Press
2].ProgramNo
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

Why can't I show the additional columns?

Here's an example using the Orders table from the SQL Server version of
Northwind ...

SELECT dbrders.CustomerID, Max(dbrders.OrderDate) AS MaxOfOrderDate
FROM dbrders
GROUP BY dbrders.CustomerID
ORDER BY dbrders.CustomerID;

--
Brendan Reynolds (MVP)


I have a SQL table Iam selecting items from (obdc linked) and I want to
only
show 1 record of a group. The one I want has the latest date..
How would I query to get this?
Thanks for any help

 

[ktm400]

It seems that I will have to join 2 queries. Can you tell me how to do this?
is it in relationships?
Thanks again for your help

When you use aggregate functions (such as Max) each column named in the
SELECT clause must be part of an aggregate function or a GROUP BY clause.
Notice in my original example that CustomerID appears in the GROUP BY clause
as well as in the SELECT clause - the example displays each customer ID
along with the date of the most recent order by that customer.

Sometimes (not always, but sometimes) it might be necessary to use two or
more queries to bring together the information you want. For example, if I
wanted to include additional information about a customer, I might use the
query in my original example and then join that query to the customers table
on the CustomerID column to bring in columns from the customer table.

--
Brendan Reynolds (MVP)

Thank you.... another question:
This works
SELECT Max([Press 2]!Time) AS [Time]
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

But this does not
SELECT Max([Press 2]!Time) AS [Time], [Press 2].BlankID, [Press
2].ProgramNo
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

Why can't I show the additional columns?

Here's an example using the Orders table from the SQL Server version of
Northwind ...

SELECT dbrders.CustomerID, Max(dbrders.OrderDate) AS MaxOfOrderDate
FROM dbrders
GROUP BY dbrders.CustomerID
ORDER BY dbrders.CustomerID;

--
Brendan Reynolds (MVP)


I have a SQL table Iam selecting items from (obdc linked) and I want to
only
show 1 record of a group. The one I want has the latest date..
How would I query to get this?
Thanks for any help

 

[Brendan Reynolds]

Well ... building on my previous example, here's a query that joins the
Customers table to the query from my previous post, to display additional
fields from the Customers table. This isn't really a great example, because
the same result could probably be achieved without using two queries.
Unfortunately, I can't think of a better example at the moment. Anyhow,
here's the 'outer' query (the one that joins the Customers table to the
original query ...

SELECT dbo_Customers.CompanyName, dbo_Customers.ContactName,
qryMostRecentOrderPerCustomer.MaxOfOrderDate
FROM dbo_Customers INNER JOIN qryMostRecentOrderPerCustomer ON
dbo_Customers.CustomerID = qryMostRecentOrderPerCustomer.CustomerID;

.... and here's the 'inner' query, which I've saved as
'qryMostRecentOrderPerCustomer' for this example ...

SELECT dbrders.CustomerID, Max(dbrders.OrderDate) AS MaxOfOrderDate
FROM dbrders
GROUP BY dbrders.CustomerID;

There are a series of three MSDN articles on fundamental, intermediate, and
advanced Jet SQL that you may find of interest. While these articles talk
about Access 2000, they are also very much relevant to Access 2002 and
Access 2003. Here's a link to the first article in the series ...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

--
Brendan Reynolds (MVP)

It seems that I will have to join 2 queries. Can you tell me how to do
this?
is it in relationships?
Thanks again for your help

When you use aggregate functions (such as Max) each column named in the
SELECT clause must be part of an aggregate function or a GROUP BY clause.
Notice in my original example that CustomerID appears in the GROUP BY
clause
as well as in the SELECT clause - the example displays each customer ID
along with the date of the most recent order by that customer.

Sometimes (not always, but sometimes) it might be necessary to use two or
more queries to bring together the information you want. For example, if
I
wanted to include additional information about a customer, I might use
the
query in my original example and then join that query to the customers
table
on the CustomerID column to bring in columns from the customer table.

--
Brendan Reynolds (MVP)

Thank you.... another question:
This works
SELECT Max([Press 2]!Time) AS [Time]
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

But this does not
SELECT Max([Press 2]!Time) AS [Time], [Press 2].BlankID, [Press
2].ProgramNo
FROM [Press 2]
WHERE ((([Press 2]![BlankID])=1301));

Why can't I show the additional columns?




:

Here's an example using the Orders table from the SQL Server version
of
Northwind ...

SELECT dbrders.CustomerID, Max(dbrders.OrderDate) AS
MaxOfOrderDate
FROM dbrders
GROUP BY dbrders.CustomerID
ORDER BY dbrders.CustomerID;

--
Brendan Reynolds (MVP)


I have a SQL table Iam selecting items from (obdc linked) and I want
to
only
show 1 record of a group. The one I want has the latest date..
How would I query to get this?
Thanks for any help