Help with query to select the maximum date from a query

  • Thread starter graeme34 via AccessMonster.com
  • Start date
G

graeme34 via AccessMonster.com

Hi I have a query which selects all the details from a product table, as well
as the account index and date ordered for any order placed for that product
from the purchase order table and finally selects the price paid for that
order from the purchase details table....what I am trying to do is select
only the product that is currently shown in a combo box (displaying Product
codes) then display the order details of the last placed order......I have
tried the following query but it is returning empty, obviously I'm doing
somehting wrong, does anybody know what???

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price,
PO.AccountIndex
FROM tblPurchaseOrder PO INNER JOIN (tblProduct P INNER JOIN
tblPurchaseOrderDetails POD ON P.[Product Code] = POD.ProductCode) ON
PO.PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE PO.DateOrdered = (SELECT MAX(T.DateOrdered) FROM tblPurchaseOrder T
WHERE P.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]);

Thanks
 
G

graeme34 via AccessMonster.com

Just a little update incase it helps, i think the problem lies in the WHERE
clause because if the value of the Product code in the combo box is also in
the detail of the last sales order placed then it is showing the record so I
think the MAX function is working, just not selecting the Product code first
prior to MAX..not too sure how to fix it though.. :(
Hi I have a query which selects all the details from a product table, as well
as the account index and date ordered for any order placed for that product
from the purchase order table and finally selects the price paid for that
order from the purchase details table....what I am trying to do is select
only the product that is currently shown in a combo box (displaying Product
codes) then display the order details of the last placed order......I have
tried the following query but it is returning empty, obviously I'm doing
somehting wrong, does anybody know what???

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price,
PO.AccountIndex
FROM tblPurchaseOrder PO INNER JOIN (tblProduct P INNER JOIN
tblPurchaseOrderDetails POD ON P.[Product Code] = POD.ProductCode) ON
PO.PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE PO.DateOrdered = (SELECT MAX(T.DateOrdered) FROM tblPurchaseOrder T
WHERE P.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]);

Thanks
Click to expand...
 
J

John Spencer

The following may give you what you want. But I'm not sure.

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price,
PO.AccountIndex
FROM tblPurchaseOrder PO INNER JOIN (tblProduct P INNER JOIN
tblPurchaseOrderDetails POD ON P.[Product Code] = POD.ProductCode)
ON PO.PurchaseOrderNumber = POD.PurchaseOrderNumber

SELECT P.[Purchase Order Number]
FROM tblPurchaseOrder P
WHERE P.Purchase Order
WHERE PO.DateOrdered = (SELECT MAX(TPO.DateOrdered)
FROM tblPurchaseOrder TPO INNER JOIN tblPurchaseOrderDetails TPOD
ON TPO.[Purchase Order Number] = TPOD.[Purchase Order Number]
WHERE TPOD.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo])

AND PO.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]
 
G

graeme34 via AccessMonster.com

Nope no joy with that John, thank you anyway :)
John said:
The following may give you what you want. But I'm not sure.

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price,
PO.AccountIndex
FROM tblPurchaseOrder PO INNER JOIN (tblProduct P INNER JOIN
tblPurchaseOrderDetails POD ON P.[Product Code] = POD.ProductCode)
ON PO.PurchaseOrderNumber = POD.PurchaseOrderNumber

SELECT P.[Purchase Order Number]
FROM tblPurchaseOrder P
WHERE P.Purchase Order
WHERE PO.DateOrdered = (SELECT MAX(TPO.DateOrdered)
FROM tblPurchaseOrder TPO INNER JOIN tblPurchaseOrderDetails TPOD
ON TPO.[Purchase Order Number] = TPOD.[Purchase Order Number]
WHERE TPOD.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo])

AND PO.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]
Hi I have a query which selects all the details from a product table, as well
as the account index and date ordered for any order placed for that product
Click to expand...
[quoted text clipped - 15 lines]
Thanks
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Graeme:

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In
Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price, PO.AccountIndex
FROM tblPurchaseOrder PO
INNER JOIN (tblProduct P
INNER JOIN tblPurchaseOrderDetails POD
ON P.[Product Code] = POD.ProductCode)
ON PO.PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE PO.DateOrdered =
(SELECT MAX(T.DateOrdered)
FROM tblPurchaseOrder T
WHERE P.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]);

Hmmm. Looks vaguely familiar.

May I suggest this:

Temporarily remove the last line, so it returns all [Product Code]s. You
get some results, right?

Assuming you do, then it is this filtering that is at fault. Choose one
specific [Product Code] produced by this query and Copy it. Paste it into
your query in a new last line reading:

WHERE P.[Product Code] = "XXXXXXXX";

where the X's are what you paste. Omit the double quotes around the pasted
value if it is not a text value. Run this. Does it produce exactly that
product code?

Make sure the form is open, and that a product code has been entered into
the cboMoveTo control. Change the first line of the query to be:

SELECT [Forms]![frmProductEnquiry]![cboMoveTo] AS cboValue,
P.[Product Code], P.ProductType, P.Description, P.[Quantity In Stock],

This will now show the exact same thing as before, but with a new first
column, being what was entered into the combo box. Does the cboValue column
now exactly match the [Product Code] column?

Please let me know what you learned from this. Perhaps we can soon discover
where the error is.

Tom Ellison


graeme34 via AccessMonster.com said:
Hi I have a query which selects all the details from a product table, as
well
as the account index and date ordered for any order placed for that
product
from the purchase order table and finally selects the price paid for that
order from the purchase details table....what I am trying to do is select
only the product that is currently shown in a combo box (displaying
Product
codes) then display the order details of the last placed order......I have
tried the following query but it is returning empty, obviously I'm doing
somehting wrong, does anybody know what???

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In
Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price,
PO.AccountIndex
FROM tblPurchaseOrder PO INNER JOIN (tblProduct P INNER JOIN
tblPurchaseOrderDetails POD ON P.[Product Code] = POD.ProductCode) ON
PO.PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE PO.DateOrdered = (SELECT MAX(T.DateOrdered) FROM tblPurchaseOrder T
WHERE P.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]);

Thanks
Click to expand...
 
G

graeme34 via AccessMonster.com

Hi Tom
Yes everythng you stated went according to plan, therefore the error must lie
in the > WHERE PO.DateOrdered =
(SELECT MAX(T.DateOrdered)
FROM tblPurchaseOrder T
WHERE P.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]);
Clause, yet this is reason for the query to select only last record from the
tblPurchaseOrder using the MAX function on the date, any suggestions Tom ??
Tom said:
Dear Graeme:

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In
Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price, PO.AccountIndex
FROM tblPurchaseOrder PO
INNER JOIN (tblProduct P
INNER JOIN tblPurchaseOrderDetails POD
ON P.[Product Code] = POD.ProductCode)
ON PO.PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE PO.DateOrdered =
(SELECT MAX(T.DateOrdered)
FROM tblPurchaseOrder T
WHERE P.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]);

Hmmm. Looks vaguely familiar.

May I suggest this:

Temporarily remove the last line, so it returns all [Product Code]s. You
get some results, right?

Assuming you do, then it is this filtering that is at fault. Choose one
specific [Product Code] produced by this query and Copy it. Paste it into
your query in a new last line reading:

WHERE P.[Product Code] = "XXXXXXXX";

where the X's are what you paste. Omit the double quotes around the pasted
value if it is not a text value. Run this. Does it produce exactly that
product code?

Make sure the form is open, and that a product code has been entered into
the cboMoveTo control. Change the first line of the query to be:

SELECT [Forms]![frmProductEnquiry]![cboMoveTo] AS cboValue,
P.[Product Code], P.ProductType, P.Description, P.[Quantity In Stock],

This will now show the exact same thing as before, but with a new first
column, being what was entered into the combo box. Does the cboValue column
now exactly match the [Product Code] column?

Please let me know what you learned from this. Perhaps we can soon discover
where the error is.

Tom Ellison
Hi I have a query which selects all the details from a product table, as
well
Click to expand...
[quoted text clipped - 19 lines]
Thanks
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Graeme:

I have missed something along the way, I believe.

Please try this:

SELECT P.[Product Code], P.ProductType, P.Description,
P.[Quantity In Stock], P.[Quantity Description],
PO.DateOrdered, POD.Price, PO.AccountIndex
FROM tblPurchaseOrder PO
INNER JOIN (tblProduct P
INNER JOIN tblPurchaseOrderDetails POD
ON P.[Product Code] = POD.ProductCode)
ON PO.PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE PO.DateOrdered =
(SELECT MAX(T.DateOrdered)
FROM tblPurchaseOrder T
WHERE T.[Product Code] = P.[Product Code])
AND P.[Product Code] =
[Forms]![frmProductEnquiry]![cboMoveTo]);

It makes a lot more sense to me. Does this fix anything.

Tom Ellison


graeme34 via AccessMonster.com said:
Hi Tom
Yes everythng you stated went according to plan, therefore the error must
lie
in the > WHERE PO.DateOrdered =
(SELECT MAX(T.DateOrdered)
FROM tblPurchaseOrder T
WHERE P.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]);
Clause, yet this is reason for the query to select only last record from
the
tblPurchaseOrder using the MAX function on the date, any suggestions Tom
??
Tom said:
Dear Graeme:

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In
Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price, PO.AccountIndex
FROM tblPurchaseOrder PO
INNER JOIN (tblProduct P
INNER JOIN tblPurchaseOrderDetails POD
ON P.[Product Code] = POD.ProductCode)
ON PO.PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE PO.DateOrdered =
(SELECT MAX(T.DateOrdered)
FROM tblPurchaseOrder T
WHERE P.[Product Code] = [Forms]![frmProductEnquiry]![cboMoveTo]);

Hmmm. Looks vaguely familiar.

May I suggest this:

Temporarily remove the last line, so it returns all [Product Code]s. You
get some results, right?

Assuming you do, then it is this filtering that is at fault. Choose one
specific [Product Code] produced by this query and Copy it. Paste it into
your query in a new last line reading:

WHERE P.[Product Code] = "XXXXXXXX";

where the X's are what you paste. Omit the double quotes around the
pasted
value if it is not a text value. Run this. Does it produce exactly that
product code?

Make sure the form is open, and that a product code has been entered into
the cboMoveTo control. Change the first line of the query to be:

SELECT [Forms]![frmProductEnquiry]![cboMoveTo] AS cboValue,
P.[Product Code], P.ProductType, P.Description, P.[Quantity In Stock],

This will now show the exact same thing as before, but with a new first
column, being what was entered into the combo box. Does the cboValue
column
now exactly match the [Product Code] column?

Please let me know what you learned from this. Perhaps we can soon
discover
where the error is.

Tom Ellison
Hi I have a query which selects all the details from a product table, as
well
Click to expand...
[quoted text clipped - 19 lines]
Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

graeme34 via AccessMonster.com

Success Tom thank you for your help :)
Just had to alter it a bit, Product Code is a field in PurchaseOrderDetails
not PurchaseOrder but you wasnt to know, very grateful for you help Tom....
P.S
I have another question in the Forms Programming section about linking a
Master/Sub form when the Master is data entry, wondering if there is any way
to do it without creating a temp table??

Tom said:
Dear Graeme:

I have missed something along the way, I believe.

Please try this:

SELECT P.[Product Code], P.ProductType, P.Description,
P.[Quantity In Stock], P.[Quantity Description],
PO.DateOrdered, POD.Price, PO.AccountIndex
FROM tblPurchaseOrder PO
INNER JOIN (tblProduct P
INNER JOIN tblPurchaseOrderDetails POD
ON P.[Product Code] = POD.ProductCode)
ON PO.PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE PO.DateOrdered =
(SELECT MAX(T.DateOrdered)
FROM tblPurchaseOrder T
WHERE T.[Product Code] = P.[Product Code])
AND P.[Product Code] =
[Forms]![frmProductEnquiry]![cboMoveTo]);

It makes a lot more sense to me. Does this fix anything.

Tom Ellison
Hi Tom
Yes everythng you stated went according to plan, therefore the error must
Click to expand...
[quoted text clipped - 62 lines]
Click to expand...
 
G

graeme34 via AccessMonster.com

In case your wondering Tom here is the working query

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price, PO.AccountIndex
FROM tblPurchaseOrder AS PO INNER JOIN (tblProduct AS P INNER JOIN
tblPurchaseOrderDetails AS POD ON P.[Product Code] = POD.ProductCode) ON PO.
PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE (((P.[Product Code])=[Forms]![frmProductEnquiry]![cboMoveTo]) AND ((PO.
DateOrdered)=(SELECT MAX(T.DateOrdered) FROM tblPurchaseOrder T INNER
JOIN tblPurchaseOrderDetails TD ON T.PurchaseOrderNumber = TD.
PurchaseOrderNumber WHERE TD.[ProductCode] = P.[Product Code])));

But after getting the query working its had a knock on effect when I
havechanged the forms control source. to this query now my combo box isnt
working which fills the detail section, the recordset is staying empty and
getting the no current record when i try to display the data in the form..
heres the code..

Private Sub cboMoveTo_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveto) Then

'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone

rs.FindFirst "[Product Code] = """ & Me.cboMoveto & """"

'Display the found record in the form.
Me.Bookmark = rs.Bookmark
rs.Close
End If
End Sub

Success Tom thank you for your help :)
Just had to alter it a bit, Product Code is a field in PurchaseOrderDetails
not PurchaseOrder but you wasnt to know, very grateful for you help Tom....
P.S
I have another question in the Forms Programming section about linking a
Master/Sub form when the Master is data entry, wondering if there is any way
to do it without creating a temp table??
Dear Graeme:
Click to expand...
[quoted text clipped - 26 lines]
Click to expand...
 
G

graeme34 via AccessMonster.com

I think I know the problem, as this code is on the afterupdate the query has
not been ran, I think , will have to have a mess about with it...
graeme34 said:
In case your wondering Tom here is the working query

SELECT P.[Product Code], P.ProductType, P.Description, P.[Quantity In Stock],
P.[Quantity Description], PO.DateOrdered, POD.Price, PO.AccountIndex
FROM tblPurchaseOrder AS PO INNER JOIN (tblProduct AS P INNER JOIN
tblPurchaseOrderDetails AS POD ON P.[Product Code] = POD.ProductCode) ON PO.
PurchaseOrderNumber = POD.PurchaseOrderNumber
WHERE (((P.[Product Code])=[Forms]![frmProductEnquiry]![cboMoveTo]) AND ((PO.
DateOrdered)=(SELECT MAX(T.DateOrdered) FROM tblPurchaseOrder T INNER
JOIN tblPurchaseOrderDetails TD ON T.PurchaseOrderNumber = TD.
PurchaseOrderNumber WHERE TD.[ProductCode] = P.[Product Code])));

But after getting the query working its had a knock on effect when I
havechanged the forms control source. to this query now my combo box isnt
working which fills the detail section, the recordset is staying empty and
getting the no current record when i try to display the data in the form..
heres the code..

Private Sub cboMoveTo_AfterUpdate()

Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveto) Then

'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone

rs.FindFirst "[Product Code] = """ & Me.cboMoveto & """"

'Display the found record in the form.
Me.Bookmark = rs.Bookmark
rs.Close
End If
End Sub
Success Tom thank you for your help :)
Just had to alter it a bit, Product Code is a field in PurchaseOrderDetails
Click to expand...
[quoted text clipped - 9 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining 3 queries 2
Query design 1
Crosstab query help please 1
trouble with query results 1
creating a query from two tables 2
VBA to Query. 2
Help with query! 3
Union query converting date to text 2

Top