queries will not show dates

[John thomas]

Hi
Accesss newbee
I have two tables,orders and order details .In my orders table i have dates for order received and order to be dispatched .In my details table i have no dates .
I want ultimately to be able tp produce a report showing the dates from the orders table.And the products from the details table which are related i get everything except the dates i have tried varius permutations but do not get the dates
All help greatfully received
John

 

[boblarson]

Post the SQL from the query. Click on VIEW > SQL VIEW and copy and paste the text it shows here.

 

[John thomas]

SELECT orders.ordID, orders.surname, orders.dateoforder, orders.fitdate, orderdetails.ordID, orderdetails.product
FROM orders RIGHT JOIN orderdetails ON orders.ordID = orderdetails.ordID;

 

[boblarson]

Try using this instead:

SELECT orders.ordID, orders.surname, orders.dateoforder, orders.fitdate, orderdetails.ordID, orderdetails.product
FROM orders LEFT JOIN orderdetails ON orders.ordID = orderdetails.ordID;

 

[jdraw]

tblOrder
OrderId PK auto
OrderDateReceived
OrderdateDispatch
CustomerId
...

tblOrderDetails
OrderDetailId auto PK
OrderId FK number
ProductID FK number
ProdQty number
ProdPrice currency

tblProduct
ProductId
ProductName
other Prod info...



ProductID in the OrderDetails tells which Product (from Products table using ProductId)
OrderId in the OrderDetrails tells which Order( from the Orders table using OrderId)
using OrderId you can determine the OrderDate

Try (untested)

Select ProductID, ProductName, OrderId, OrderDateReceived, OrderDateDispatched from tblProduct
,tblOrderDetails
,tblOrder
WHERE
 tblProduct.ProductId = tblOrderDetails.ProductId AND
tblOrderDetails.OrderId = tblOrder.OrderId;

Ooops: I see there has been a conversation while I was thinking and typing.

 

[John thomas]

Hi Bob do i copy and paste this somewear.?

 

[John thomas]

Thank you so much for your help also
regards john

 

[boblarson]

Hi Bob do i copy and paste this somewear.?

If you go into the query like I had you do to copy it. Just change the word RIGHT to LEFT and see if that helps.

 

[John thomas]

Hi Bob done that shows all info from orders table including date but only headings fro the order details table prurders

 

[boblarson]

Are you sure that there are records in the Order details table that have matching ORDID values?

 

[John thomas]

none of the records have odrid I have chosen to show the customers surname rather than order id .i kinder imagined the orderid wouldsomehowbe known to access as everything seems to work ok

 

[boblarson]

The ORID should be stored in the order details table as the FOREIGN KEY (the thing that ties each record in the orders table to its associated records in the order details.

I think you have some fixing to do.

 

[John thomas]

Hi bob many thanks .Am attempting to fix things now .strange i had noticed the ordid no were not there .but it seemed to work so i imagined it was ok
Silly me