Query last date by item

F

FA

My table has:
"item" "date" "price" "category" "customer" "market" "product line"
"comments"

I want a query that show only the last date of each item.
 
C

Chaim

Change the name of your 'date' column to something like 'itemdate'. Date ()
is a VB function used to return the current date. As such, it should not be
used as a name.

Having said that, try:

SELECT item, itemdate, price, cat, customer, market, pl, comm
FROM tblTestOut to1
WHERE to1.itemdate = (select max(to2.itemdate)FROM tblTestOut to2
where to1.item = to2.item
GROUP BY to2.item)

Good Luck!
 
F

FA

Thank you very mucho for your response. Following your recommendation I
wrote the following:

SELECT Price_Table.[NDC ], Price_Table.DESC, Price_Table.Fecha
FROM Price_Table to1
WHERE to1.Price_Table.Fecha = (selectmax(to2.Price_Table.Fecha)FROM
Price_Table to2 WHERE to1.[ndc] = to2.[ndc] GROUP BY to2.[ndc]);

I'm obtaining the following error: Syntax error missing operator y query
expression.

What Im doing wrong?
 
M

Michel Walsh

Hi,



Missing a space between the word SELECT and the word MAX.

You can also remove the

GROUP BY to2.[ndc]



Hoping it may help,
Vanderghast, Access MVP

FA said:
Thank you very mucho for your response. Following your recommendation I
wrote the following:

SELECT Price_Table.[NDC ], Price_Table.DESC, Price_Table.Fecha
FROM Price_Table to1
WHERE to1.Price_Table.Fecha = (selectmax(to2.Price_Table.Fecha)FROM
Price_Table to2 WHERE to1.[ndc] = to2.[ndc] GROUP BY to2.[ndc]);

I'm obtaining the following error: Syntax error missing operator y query
expression.

What Im doing wrong?


Chaim said:
Change the name of your 'date' column to something like 'itemdate'. Date
()
is a VB function used to return the current date. As such, it should not
be
used as a name.

Having said that, try:

SELECT item, itemdate, price, cat, customer, market, pl, comm
FROM tblTestOut to1
WHERE to1.itemdate = (select max(to2.itemdate)FROM tblTestOut to2
where to1.item = to2.item
GROUP BY to2.item)

Good Luck!
Click to expand...
Click to expand...
 
F

FA

Now when I run the query it is asking me for the parameter value and when I
press enter all the columns are blank. What can I do to make this work?

This is the query now:
SELECT Price_Table.DESC, Price_Table.[NDC ], Price_Table.Fecha,
Price_Table.WAC
FROM Price_Table
WHERE (((Price_Table.Fecha)=(select max(Price_Table.Fecha)
FROM Price_Table to2 WHERE to1.[ndc] = to2.[ndc] )));

I want to show the latest price for each item (NDC).

Thanks for your help!
Michel Walsh said:
Hi,



Missing a space between the word SELECT and the word MAX.

You can also remove the

GROUP BY to2.[ndc]



Hoping it may help,
Vanderghast, Access MVP

FA said:
Thank you very mucho for your response. Following your recommendation I
wrote the following:

SELECT Price_Table.[NDC ], Price_Table.DESC, Price_Table.Fecha
FROM Price_Table to1
WHERE to1.Price_Table.Fecha = (selectmax(to2.Price_Table.Fecha)FROM
Price_Table to2 WHERE to1.[ndc] = to2.[ndc] GROUP BY to2.[ndc]);

I'm obtaining the following error: Syntax error missing operator y query
expression.

What Im doing wrong?


Chaim said:
Change the name of your 'date' column to something like 'itemdate'. Date
()
is a VB function used to return the current date. As such, it should not
be
used as a name.

Having said that, try:

SELECT item, itemdate, price, cat, customer, market, pl, comm
FROM tblTestOut to1
WHERE to1.itemdate = (select max(to2.itemdate)FROM tblTestOut to2
where to1.item = to2.item
GROUP BY to2.item)

Good Luck!
--

Chaim


My table has:
"item" "date" "price" "category" "customer" "market" "product line"
"comments"

I want a query that show only the last date of each item.
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


SELECT [DESC], [NDC ], Fecha, WAC
FROM Price_Table AS to1
WHERE Fecha=(select max(to2.Fecha)
FROM Price_Table AS to2
WHERE to1.[ndc] = to2.[ndc] );




Hoping it may help,
Vanderghast, Access MVP


FA said:
Now when I run the query it is asking me for the parameter value and when
I
press enter all the columns are blank. What can I do to make this work?

This is the query now:
SELECT Price_Table.DESC, Price_Table.[NDC ], Price_Table.Fecha,
Price_Table.WAC
FROM Price_Table
WHERE (((Price_Table.Fecha)=(select max(Price_Table.Fecha)
FROM Price_Table to2 WHERE to1.[ndc] = to2.[ndc] )));

I want to show the latest price for each item (NDC).

Thanks for your help!
Michel Walsh said:
Hi,



Missing a space between the word SELECT and the word MAX.

You can also remove the

GROUP BY to2.[ndc]



Hoping it may help,
Vanderghast, Access MVP

FA said:
Thank you very mucho for your response. Following your recommendation
I
wrote the following:

SELECT Price_Table.[NDC ], Price_Table.DESC, Price_Table.Fecha
FROM Price_Table to1
WHERE to1.Price_Table.Fecha = (selectmax(to2.Price_Table.Fecha)FROM
Price_Table to2 WHERE to1.[ndc] = to2.[ndc] GROUP BY to2.[ndc]);

I'm obtaining the following error: Syntax error missing operator y
query
expression.

What Im doing wrong?


:

Change the name of your 'date' column to something like 'itemdate'.
Date
()
is a VB function used to return the current date. As such, it should
not
be
used as a name.

Having said that, try:

SELECT item, itemdate, price, cat, customer, market, pl, comm
FROM tblTestOut to1
WHERE to1.itemdate = (select max(to2.itemdate)FROM tblTestOut to2
where to1.item = to2.item
GROUP BY to2.item)

Good Luck!
--

Chaim


My table has:
"item" "date" "price" "category" "customer" "market" "product line"
"comments"

I want a query that show only the last date of each item.
Click to expand...
Click to expand...
Click to expand...
 
F

FA

YES!!
Thank you very much for your help.

Michel Walsh said:
Hi,


SELECT [DESC], [NDC ], Fecha, WAC
FROM Price_Table AS to1
WHERE Fecha=(select max(to2.Fecha)
FROM Price_Table AS to2
WHERE to1.[ndc] = to2.[ndc] );




Hoping it may help,
Vanderghast, Access MVP


FA said:
Now when I run the query it is asking me for the parameter value and when
I
press enter all the columns are blank. What can I do to make this work?

This is the query now:
SELECT Price_Table.DESC, Price_Table.[NDC ], Price_Table.Fecha,
Price_Table.WAC
FROM Price_Table
WHERE (((Price_Table.Fecha)=(select max(Price_Table.Fecha)
FROM Price_Table to2 WHERE to1.[ndc] = to2.[ndc] )));

I want to show the latest price for each item (NDC).

Thanks for your help!
Michel Walsh said:
Hi,



Missing a space between the word SELECT and the word MAX.

You can also remove the

GROUP BY to2.[ndc]



Hoping it may help,
Vanderghast, Access MVP

Thank you very mucho for your response. Following your recommendation
I
wrote the following:

SELECT Price_Table.[NDC ], Price_Table.DESC, Price_Table.Fecha
FROM Price_Table to1
WHERE to1.Price_Table.Fecha = (selectmax(to2.Price_Table.Fecha)FROM
Price_Table to2 WHERE to1.[ndc] = to2.[ndc] GROUP BY to2.[ndc]);

I'm obtaining the following error: Syntax error missing operator y
query
expression.

What Im doing wrong?


:

Change the name of your 'date' column to something like 'itemdate'.
Date
()
is a VB function used to return the current date. As such, it should
not
be
used as a name.

Having said that, try:

SELECT item, itemdate, price, cat, customer, market, pl, comm
FROM tblTestOut to1
WHERE to1.itemdate = (select max(to2.itemdate)FROM tblTestOut to2
where to1.item = to2.item
GROUP BY to2.item)

Good Luck!
--

Chaim


My table has:
"item" "date" "price" "category" "customer" "market" "product line"
"comments"

I want a query that show only the last date of each item.
Click to expand...
Click to expand...
Click to expand...
 
Top