Max or DMax - Issue

C

CJ

Hi Groupies

In my database, equipment is placed on a Rig via a Work Order. I need to be
able to retrieve the highest Work Order number for each piece of equipment,
the data needs to be editable (no grouping) and the name of the Rig needs to
be retrieved from a combo box on my form.

I have tried Max and DMax and I can get neither to do what I require and I
am beyond frustrated.
My query SQL is below:

SELECT tblWorkOrderDetails.[SKU Number], [strMake] & " - " & [Model] AS
Item, tblWorkOrderDetails.dtmDateIn, tblWorkOrderDetails.[Work Order ID],
tblWorkOrderDetails.[End Date], tblWorkOrders.RigName, tblWorkOrders.Status
FROM tblWorkOrders INNER JOIN (tblMake INNER JOIN (tblInventory INNER JOIN
tblWorkOrderDetails ON tblInventory.[SKU Number] = tblWorkOrderDetails.[SKU
Number]) ON tblMake.lngMakeID = tblInventory.Make) ON tblWorkOrders.lngWOId
= tblWorkOrderDetails.[Work Order ID]
WHERE (((tblWorkOrderDetails.dtmDateIn) Is Null) AND
((tblWorkOrders.RigName)=[Forms]![frmTruckToSite]![cmbRigSite]) AND (Not
(tblWorkOrders.Status)="void"))
ORDER BY tblWorkOrderDetails.[SKU Number];

Can somebody Please help me get this to work!!
 
K

KARL DEWEY

retrieve the highest Work Order number for each piece of equipment, the
data needs to be editable
Why need to edit the history?

ERROR -- AND (Not (tblWorkOrders.Status)="void"))
Needs to read -- AND (tblWorkOrders.Status)<>"void")
 
R

Roger Carlson

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MaxQueryProblem.mdb " which illustrates two ways to do this.
One way (using two queries) is not editable, but the other (using a
subquery) *is* editable. I'm not going to try to apply it you your SQL, but
you can download the sample for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=326 and
implement the method in your database.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
C

CJ

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Roger Carlson said:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MaxQueryProblem.mdb " which illustrates two ways to do
this. One way (using two queries) is not editable, but the other (using a
subquery) *is* editable. I'm not going to try to apply it you your SQL,
but you can download the sample for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=326 and
implement the method in your database.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



CJ said:
Hi Groupies

In my database, equipment is placed on a Rig via a Work Order. I need to
be able to retrieve the highest Work Order number for each piece of
equipment, the data needs to be editable (no grouping) and the name of
the Rig needs to be retrieved from a combo box on my form.

I have tried Max and DMax and I can get neither to do what I require and
I am beyond frustrated.
My query SQL is below:

SELECT tblWorkOrderDetails.[SKU Number], [strMake] & " - " & [Model] AS
Item, tblWorkOrderDetails.dtmDateIn, tblWorkOrderDetails.[Work Order ID],
tblWorkOrderDetails.[End Date], tblWorkOrders.RigName,
tblWorkOrders.Status
FROM tblWorkOrders INNER JOIN (tblMake INNER JOIN (tblInventory INNER
JOIN
tblWorkOrderDetails ON tblInventory.[SKU Number] =
tblWorkOrderDetails.[SKU
Number]) ON tblMake.lngMakeID = tblInventory.Make) ON
tblWorkOrders.lngWOId
= tblWorkOrderDetails.[Work Order ID]
WHERE (((tblWorkOrderDetails.dtmDateIn) Is Null) AND
((tblWorkOrders.RigName)=[Forms]![frmTruckToSite]![cmbRigSite]) AND (Not
(tblWorkOrders.Status)="void"))
ORDER BY tblWorkOrderDetails.[SKU Number];

Can somebody Please help me get this to work!!
Click to expand...
Click to expand...
 
C

CJ

Thanks Roger.

I will take a look and let you know if I need some clarification.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Roger Carlson said:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MaxQueryProblem.mdb " which illustrates two ways to do
this. One way (using two queries) is not editable, but the other (using a
subquery) *is* editable. I'm not going to try to apply it you your SQL,
but you can download the sample for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=326 and
implement the method in your database.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



CJ said:
Hi Groupies

In my database, equipment is placed on a Rig via a Work Order. I need to
be able to retrieve the highest Work Order number for each piece of
equipment, the data needs to be editable (no grouping) and the name of
the Rig needs to be retrieved from a combo box on my form.

I have tried Max and DMax and I can get neither to do what I require and
I am beyond frustrated.
My query SQL is below:

SELECT tblWorkOrderDetails.[SKU Number], [strMake] & " - " & [Model] AS
Item, tblWorkOrderDetails.dtmDateIn, tblWorkOrderDetails.[Work Order ID],
tblWorkOrderDetails.[End Date], tblWorkOrders.RigName,
tblWorkOrders.Status
FROM tblWorkOrders INNER JOIN (tblMake INNER JOIN (tblInventory INNER
JOIN
tblWorkOrderDetails ON tblInventory.[SKU Number] =
tblWorkOrderDetails.[SKU
Number]) ON tblMake.lngMakeID = tblInventory.Make) ON
tblWorkOrders.lngWOId
= tblWorkOrderDetails.[Work Order ID]
WHERE (((tblWorkOrderDetails.dtmDateIn) Is Null) AND
((tblWorkOrders.RigName)=[Forms]![frmTruckToSite]![cmbRigSite]) AND (Not
(tblWorkOrders.Status)="void"))
ORDER BY tblWorkOrderDetails.[SKU Number];

Can somebody Please help me get this to work!!
Click to expand...
Click to expand...
 
C

CJ

Hi Karl

I need to edit the history because the Work Order is not finalized if it is
Open. Equipment might be returned or traded.

Thanks for catching the error.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
KARL DEWEY said:
data needs to be editable
Why need to edit the history?

ERROR -- AND (Not (tblWorkOrders.Status)="void"))
Needs to read -- AND (tblWorkOrders.Status)<>"void")

--
Build a little, test a little.


CJ said:
Hi Groupies

In my database, equipment is placed on a Rig via a Work Order. I need to
be
able to retrieve the highest Work Order number for each piece of
equipment,
the data needs to be editable (no grouping) and the name of the Rig needs
to
be retrieved from a combo box on my form.

I have tried Max and DMax and I can get neither to do what I require and
I
am beyond frustrated.
My query SQL is below:

SELECT tblWorkOrderDetails.[SKU Number], [strMake] & " - " & [Model] AS
Item, tblWorkOrderDetails.dtmDateIn, tblWorkOrderDetails.[Work Order ID],
tblWorkOrderDetails.[End Date], tblWorkOrders.RigName,
tblWorkOrders.Status
FROM tblWorkOrders INNER JOIN (tblMake INNER JOIN (tblInventory INNER
JOIN
tblWorkOrderDetails ON tblInventory.[SKU Number] =
tblWorkOrderDetails.[SKU
Number]) ON tblMake.lngMakeID = tblInventory.Make) ON
tblWorkOrders.lngWOId
= tblWorkOrderDetails.[Work Order ID]
WHERE (((tblWorkOrderDetails.dtmDateIn) Is Null) AND
((tblWorkOrders.RigName)=[Forms]![frmTruckToSite]![cmbRigSite]) AND (Not
(tblWorkOrders.Status)="void"))
ORDER BY tblWorkOrderDetails.[SKU Number];

Can somebody Please help me get this to work!!
Click to expand...
Click to expand...
 
C

CJ

Hi Roger and company

Thank you for sharing your example. I have applied your example to a scaled
down version of my problem but I can only get it to work for the subquery.

My SQL of the main query is below but here is the detail of what happens:

The subquery returns the Max Work Order ID for each equipment SKU number.
The subquery SQL is as follows:

SELECT Max(tblWorkOrderDetails.[Work Order ID]) AS [MaxOfWork Order ID]
FROM tblWorkOrderDetails
GROUP BY tblWorkOrderDetails.[SKU Number];

However, when I apply that to my main query, it is not returning the Maximum
Work Order for each rig, it is returning all of them. For example

Work Order ID SKU Number Rig Name Status
379 RA303 Nabors 34 Invoiced
547 RA303 Nabors 34 Open

I need to just see the data for the second record, Work Order ID 547.

The SQL for this main query is:

SELECT tblWorkOrders.lngWOId, tblWorkOrderDetails.[SKU Number],
tblWorkOrders.RigName, tblWorkOrders.Status
FROM tblWorkOrders INNER JOIN tblWorkOrderDetails ON tblWorkOrders.lngWOId =
tblWorkOrderDetails.[Work Order ID]
WHERE (((tblWorkOrders.lngWOId) In (SELECT Max(tblWorkOrderDetails.[Work
Order ID]) AS [MaxOfWork Order ID]
FROM tblWorkOrderDetails
GROUP BY tblWorkOrderDetails.[SKU Number])));

I have tried all kinds of scenarios but I am not having any luck with
retrieving what I need AND keeping the data updateable. Any ideas?

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
CJ said:
Thanks Roger.

I will take a look and let you know if I need some clarification.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Roger Carlson said:
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "MaxQueryProblem.mdb " which illustrates two ways to do
this. One way (using two queries) is not editable, but the other (using a
subquery) *is* editable. I'm not going to try to apply it you your SQL,
but you can download the sample for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=326 and
implement the method in your database.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



CJ said:
Hi Groupies

In my database, equipment is placed on a Rig via a Work Order. I need to
be able to retrieve the highest Work Order number for each piece of
equipment, the data needs to be editable (no grouping) and the name of
the Rig needs to be retrieved from a combo box on my form.

I have tried Max and DMax and I can get neither to do what I require and
I am beyond frustrated.
My query SQL is below:

SELECT tblWorkOrderDetails.[SKU Number], [strMake] & " - " & [Model] AS
Item, tblWorkOrderDetails.dtmDateIn, tblWorkOrderDetails.[Work Order
ID],
tblWorkOrderDetails.[End Date], tblWorkOrders.RigName,
tblWorkOrders.Status
FROM tblWorkOrders INNER JOIN (tblMake INNER JOIN (tblInventory INNER
JOIN
tblWorkOrderDetails ON tblInventory.[SKU Number] =
tblWorkOrderDetails.[SKU
Number]) ON tblMake.lngMakeID = tblInventory.Make) ON
tblWorkOrders.lngWOId
= tblWorkOrderDetails.[Work Order ID]
WHERE (((tblWorkOrderDetails.dtmDateIn) Is Null) AND
((tblWorkOrders.RigName)=[Forms]![frmTruckToSite]![cmbRigSite]) AND (Not
(tblWorkOrders.Status)="void"))
ORDER BY tblWorkOrderDetails.[SKU Number];

Can somebody Please help me get this to work!!
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Your subquery needs to read more like

WHERE tblWorkOrderDetails.[Work Order ID] = (
SELECT Max(Temp.[Work Order ID]) AS [MaxOfWork Order ID]
FROM tblWorkOrderDetails as TEMP
WHERE Temp.[SKU Number] = tblWorkOrderDetails.[SKU Number])


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
C

CJ

John, thank you very very much!
I would never have figured out how to write the subquery:

SELECT [WHERE tblWorkOrderDetails].[Work Order ID]=(SELECT Max(Temp.[Work
Order ID]) AS [MaxOfWork Order ID]
FROM tblWorkOrderDetails as TEMP
WHERE Temp.[SKU Number] = tblWorkOrderDetails.[SKU Number]);

Proving once again that newsgroups are FABULOUS!!!
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
John Spencer said:
Your subquery needs to read more like

WHERE tblWorkOrderDetails.[Work Order ID] = (
SELECT Max(Temp.[Work Order ID]) AS [MaxOfWork Order ID]
FROM tblWorkOrderDetails as TEMP
WHERE Temp.[SKU Number] = tblWorkOrderDetails.[SKU Number])


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi Roger and company

Thank you for sharing your example. I have applied your example to a
scaled down version of my problem but I can only get it to work for the
subquery.

My SQL of the main query is below but here is the detail of what happens:

The subquery returns the Max Work Order ID for each equipment SKU number.
The subquery SQL is as follows:

SELECT Max(tblWorkOrderDetails.[Work Order ID]) AS [MaxOfWork Order ID]
FROM tblWorkOrderDetails
GROUP BY tblWorkOrderDetails.[SKU Number];

However, when I apply that to my main query, it is not returning the
Maximum Work Order for each rig, it is returning all of them. For example

Work Order ID SKU Number Rig Name Status
379 RA303 Nabors 34 Invoiced
547 RA303 Nabors 34 Open

I need to just see the data for the second record, Work Order ID 547.

The SQL for this main query is:

SELECT tblWorkOrders.lngWOId, tblWorkOrderDetails.[SKU Number],
tblWorkOrders.RigName, tblWorkOrders.Status
FROM tblWorkOrders INNER JOIN tblWorkOrderDetails ON
tblWorkOrders.lngWOId = tblWorkOrderDetails.[Work Order ID]
WHERE (((tblWorkOrders.lngWOId) In (SELECT Max(tblWorkOrderDetails.[Work
Order ID]) AS [MaxOfWork Order ID]
FROM tblWorkOrderDetails
GROUP BY tblWorkOrderDetails.[SKU Number])));

I have tried all kinds of scenarios but I am not having any luck with
retrieving what I need AND keeping the data updateable. Any ideas?
Click to expand...
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

Listbox execute SQL error 2
Need an Expert... Update with a DMAX 1
Using Max or DMax in DLookUp criteria? 1
Max of UniqID in Sql 1
Find Max Value of Text Field 7
Max Value In Query 4
Min and Max for Date Range in Report 4
SQL or maybe a hardware issue for finding lowest Repair order date or mileage 1

Top