Pulling the most recent date from a table to create a query or rep

[Guest]

I have a table called pricingtable. In the table it has product names - date
run and pricing1(200lbs price) - pricing2(500lbs price). This table stores
the name of the product / date run and pricing for different lbs. The table
is allowed duplicate records so we can track or have history on pricing. On
any day we can write a record to the table and it will have different pricing
because our raw materials change in price. So we end up with many records
with the same product names and different pricing with different dates.

I want to do a query or report that shows each product name with the most
recent date which would give me the most recent pricing.
Can anyone help??

Many thanks in advance

 

[Lynn Trapp]

You could start out with something like this:

SELECT ProductName, Price1, Price2, Max(DateRun)
FROM YourTable
GROUP BY ProductName, Price1, Price2;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Larry Daugherty]

Design your query sorting descending on date and use the TOP 1
predicate. If you're using the QBE wizard you'll need to get into SQL
view to enter TOP 1

HTH

 

[Guest]

I tried both suggestions (thanks for the response) by editing the SQL(listed
below) in my query and I still had no luck. I am receiving an error for
syntax and can't figure it out.
Do either of you have any other suggestions? Or can I fix this one?
SELECT [Actual Production Cost].[RS Number], [Actual Production
Cost].[Project Description], [Actual Production Cost].[Date Run], [Actual
Production Cost].Lbs1

 

[Lynn Trapp]

From the segment of the SQL statement you posted, I can't know why you are
getting that error. Can you post the full SQL statement?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

I tried both suggestions (thanks for the response) by editing the
SQL(listed
below) in my query and I still had no luck. I am receiving an error for
syntax and can't figure it out.
Do either of you have any other suggestions? Or can I fix this one?
SELECT [Actual Production Cost].[RS Number], [Actual Production
Cost].[Project Description], [Actual Production Cost].[Date Run], [Actual
Production Cost].Lbs1

You could start out with something like this:

SELECT ProductName, Price1, Price2, Max(DateRun)
FROM YourTable
GROUP BY ProductName, Price1, Price2;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Here is the actual SQL(sorry for not posting the entire SQL) behind the
query. In this query I have it setup to ask for the Enter Nomenclature - and
Enter the date which does work. When you do it this way it brings up only one
record, but I would like for it to automatically bring up the last date. If I
do not ask for the date then it brings up all of the products/ nomenclature
with all of the dates?? Thank you so very much for your quick response.
Curt

SELECT [Actual Production Cost].[RS Number], [Actual Production
Cost].[Project Description], [Actual Production Cost].[Date Run], [Actual
Production Cost].Lbs1, [Actual Production Cost].Lbs2, [Actual Production
Cost].Lbs3, [Actual Production Cost].Lbs4, [Actual Production Cost].Lbs5,
[Actual Production Cost].Lbs6, [Actual Production Cost].Lbs7, [Actual
Production Cost].Lbs8, [Actual Production Cost].Lbs9, [Actual Production
Cost].Lbs10, [Actual Production Cost].Lbs11, [Actual Production Cost].Lbs12,
[Actual Production Cost].SalesPriceUSD1, [Actual Production
Cost].SalesPriceUSD2, [Actual Production Cost].SalesPriceUSD3, [Actual
Production Cost].SalesPriceUSD4, [Actual Production Cost].SalesPriceUSD5,
[Actual Production Cost].SalesPriceUSD6, [Actual Production
Cost].SalesPriceUSD7, [Actual Production Cost].SalesPriceUSD8, [Actual
Production Cost].SalesPriceUSD9, [Actual Production Cost].SalesPriceUSD10,
[Actual Production Cost].SalesPriceUSD11, [Actual Production
Cost].SalesPriceUSD12
FROM [Actual Production Cost]
WHERE ((([Actual Production Cost].[RS Number])=[Enter Nomenclature]) AND
(([Actual Production Cost].[Date Run])=[Enter Date]));

From the segment of the SQL statement you posted, I can't know why you are
getting that error. Can you post the full SQL statement?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

I tried both suggestions (thanks for the response) by editing the
SQL(listed
below) in my query and I still had no luck. I am receiving an error for
syntax and can't figure it out.
Do either of you have any other suggestions? Or can I fix this one?
SELECT [Actual Production Cost].[RS Number], [Actual Production
Cost].[Project Description], [Actual Production Cost].[Date Run], [Actual
Production Cost].Lbs1

You could start out with something like this:

SELECT ProductName, Price1, Price2, Max(DateRun)
FROM YourTable
GROUP BY ProductName, Price1, Price2;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I have a table called pricingtable. In the table it has product names -
date
run and pricing1(200lbs price) - pricing2(500lbs price). This table
stores
the name of the product / date run and pricing for different lbs. The
table
is allowed duplicate records so we can track or have history on
pricing.
On
any day we can write a record to the table and it will have different
pricing
because our raw materials change in price. So we end up with many
records
with the same product names and different pricing with different dates.

I want to do a query or report that shows each product name with the
most
recent date which would give me the most recent pricing.
Can anyone help??

Many thanks in advance

 

[Lynn Trapp]

Well, this is going to be a bit more complicated but we can try something.
You probably need a subquery to find your maximum date.

FROM [Actual Production Cost] INNER JOIN
(SELECT [RS Number], Max([Date Run] AS LatestDate) FROM [Actual Production
Cost]
GROUP BY [RS Number]) AS X
ON [Actual Production Cost].[RS Number] = X.[RS Number]
AND [Actual Production Cost].[Date Run] = X.LatestDate
WHERE [Actual Production Cost].[RS Number]=[Enter Nomenclature] AND
[Actual Production Cost].[Date Run]=[Enter Date];

This is obviously untested but should get you going in the right direction.
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

Here is the actual SQL(sorry for not posting the entire SQL) behind the
query. In this query I have it setup to ask for the Enter Nomenclature -
and
Enter the date which does work. When you do it this way it brings up only
one
record, but I would like for it to automatically bring up the last date.
If I
do not ask for the date then it brings up all of the products/
nomenclature
with all of the dates?? Thank you so very much for your quick response.
Curt

SELECT [Actual Production Cost].[RS Number], [Actual Production
Cost].[Project Description], [Actual Production Cost].[Date Run], [Actual
Production Cost].Lbs1, [Actual Production Cost].Lbs2, [Actual Production
Cost].Lbs3, [Actual Production Cost].Lbs4, [Actual Production Cost].Lbs5,
[Actual Production Cost].Lbs6, [Actual Production Cost].Lbs7, [Actual
Production Cost].Lbs8, [Actual Production Cost].Lbs9, [Actual Production
Cost].Lbs10, [Actual Production Cost].Lbs11, [Actual Production
Cost].Lbs12,
[Actual Production Cost].SalesPriceUSD1, [Actual Production
Cost].SalesPriceUSD2, [Actual Production Cost].SalesPriceUSD3, [Actual
Production Cost].SalesPriceUSD4, [Actual Production Cost].SalesPriceUSD5,
[Actual Production Cost].SalesPriceUSD6, [Actual Production
Cost].SalesPriceUSD7, [Actual Production Cost].SalesPriceUSD8, [Actual
Production Cost].SalesPriceUSD9, [Actual Production Cost].SalesPriceUSD10,
[Actual Production Cost].SalesPriceUSD11, [Actual Production
Cost].SalesPriceUSD12
FROM [Actual Production Cost]
WHERE ((([Actual Production Cost].[RS Number])=[Enter Nomenclature]) AND
(([Actual Production Cost].[Date Run])=[Enter Date]));

From the segment of the SQL statement you posted, I can't know why you
are
getting that error. Can you post the full SQL statement?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

I tried both suggestions (thanks for the response) by editing the
SQL(listed
below) in my query and I still had no luck. I am receiving an error for
syntax and can't figure it out.
Do either of you have any other suggestions? Or can I fix this one?
SELECT [Actual Production Cost].[RS Number], [Actual Production
Cost].[Project Description], [Actual Production Cost].[Date Run],
[Actual
Production Cost].Lbs1
:

You could start out with something like this:

SELECT ProductName, Price1, Price2, Max(DateRun)
FROM YourTable
GROUP BY ProductName, Price1, Price2;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



message
I have a table called pricingtable. In the table it has product
names -
date
run and pricing1(200lbs price) - pricing2(500lbs price). This table
stores
the name of the product / date run and pricing for different lbs.
The
table
is allowed duplicate records so we can track or have history on
pricing.
On
any day we can write a record to the table and it will have
different
pricing
because our raw materials change in price. So we end up with many
records
with the same product names and different pricing with different
dates.

I want to do a query or report that shows each product name with the
most
recent date which would give me the most recent pricing.
Can anyone help??

Many thanks in advance

 

[Peter J. Veger]

See insert:

I have a table called pricingtable. In the table it has product names -
date
run and pricing1(200lbs price) - pricing2(500lbs price). This table stores
the name of the product / date run and pricing for different lbs. The
table
is allowed duplicate records so we can track or have history on pricing.
On
any day we can write a record to the table and it will have different
pricing
because our raw materials change in price. So we end up with many records
with the same product names and different pricing with different dates.

I want to do a query or report that shows

Naming your fields: "product", "daterun", "pricing1", "pricing2",
what about a first shot (not optimized for speed))

each product name with the most recent date which would give me the most
recent pricing.

SELECT pt.product, pt.daterun FROM pricingtable AS pt
WHERE pt.daterun >= ALL
(SELECT daterun FROM pricingtable WHERE product=pt.product);

You can read this as:
give me each product and daterun such that the daterun is the latest one
among all dateruns for that product