Please! Please! Please! Help Me! I desperate!!!

[miki]

Hello.
I sorry about my poor english ;(

I have 2 tables :

TBL1: name = products
Field1: ID_product
Field2: product_Name
Field3: product_description
and so on

TBL1: name = orders
Field1: ID_product
Field2: id_order
Field3: Date_order
Field3: quantity
and so on

The relationship between tables is one(products) to many(orders)

How i can create query that for all row in products
i can see in same row last order (by Date_order)
and not duplication ID_product

I desperate!!!
Please! Please! Please! Help Me!

Thanks.

 

[Liv Manto]

FIrst create a crosstab query using the orders table.

ID- Group By
Date-Order- Last or Maximum

then link the products table to the Crosstabbed table.

 

[miki]

Hello.
I sorry about my poor english ;(

Thanks Liv Manto.
but it's not work,i need more details or syntax sample.

i need in one row this Fields:

products.ID_product
products.product_Name
products.product_description
orders.id_order
orders.Date_order
orders.quantity

if not have order for products then orders Fields be null ,but products Fields be display and not duplication ID_product

it's possible???

Thanks All.



[This message has been edited by miki (edited 02-26-2002).]

 

[RV]

Miki,

if I understand you correctly, you only want to show the most recent order per product.
If so, try this SQL statement:

SELECT products.ID_product, products.product_Name, products.product_description, orders.id_order, orders.Date_order, orders.quantity
FROM products INNER JOIN orders ON products.ID_product = orders.ID_product
WHERE orders.Date_order=
(SELECT Max(orders.Date_order)
FROM orders AS orders2
WHERE products.ID_product=orders2.ID_product)
UNION
SELECT products.ID_product, products.product_Name, products.product_description, Null, Null, Null
FROM products
WHERE NOT EXISTS
(SELECT *
FROM orders
WHERE products.ID_product=orders.ID_product)
;

Suc6,

RV

 

[JohnT]

Try the following:

select products.product_name,
max(orders.Date_order)
from products join orders on products.ID_product = orders.ID_Product
group by products.product_name

 

[miki]

To RV and JohnT
Many thanks for your help