Query Needs to show Min Value without Dups with checkbox 2

[Basshopper]

Hi I have a query I want to run that will autocheck the box [Use Part?] by picking the lowest [Alt Unit 1] value for each group of [Line #] in other words there could be 2 [Alt Unit 1] prices for the same [Line #] When I group and use the Min function I can no longer update the [Use Part?]
Thanks for your help

SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[quote=#], [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Use Part?], [t_BOM Part Cross].[Alt Unit 1]
FROM [t_BOM Part Cross]
WHERE ((([t_BOM Part Cross].[quote=#])=[FORMS]![Parameter]![quote=#]));

 

[JerryKlmns]

Save this query as Q_Sel

SELECT [Customer ID], [Assembly #], [Line #], Min([Alt Unit 1])
FROM [t_BOM Part Cross]
GROUP BY [Customer ID], [Assembly #], [Line #],
WHERE

=[FORMS]![Parameter]!

;

Then update
UPDATE Q_Sel As Q INNER JOIN [t_BOM Part Cross] As U ON
((Q.[Customer ID] = U.[Customer ID]) AND
(Q.[Assembly #] = U.[Assembly #]) AND
(Q.[Line #] = U.[Line #]) AND
(Q.[[Alt Unit 1]] <> U.[[Alt Unit 1]]))
SET [Use Part?] = -1

 

[Basshopper]

Is the after "then update" included in the query or do you run that as code on a command button? I am using Acc 97 getting an undefined function Where in expression Error

 

[JerryKlmns]

Q_Sel, to be saved

SELECT [Customer ID], [Assembly #], [Line #], Min([Alt Unit 1])
FROM [t_BOM Part Cross]
GROUP BY [Customer ID], [Assembly #], [Line #],
WHERE [quote=#]=[FORMS]![Parameter]![quote=#];

other query saved as Upd_qwS

UPDATE Q_Sel As Q INNER JOIN [t_BOM Part Cross] As U ON
((Q.[Customer ID] = U.[Customer ID]) AND
 (Q.[Assembly #] = U.[Assembly #]) AND
 (Q.[Line #] = U.[Line #]) AND
 (Q.[[Alt Unit 1]] <> U.[[Alt Unit 1]]))
SET [Use Part?] = -1

to be executed
A] Double-click it
B] Select it and click Run button
C] A button click event
DoCmd.OpenQuery "Upd_qwS", acViewNormal, acEd

 

[Basshopper]

Hi Jerry, I am getting error messages on both Q not allowing me to save them, The first one "Syntax error (Missing Operator) in query expression Where

=[FORMS]![Parameter]!

;"

2nd one "Syntax error in query expression ((Q.[Customer ID] = U.[Customer ID]) AND (Q.[Assembly #] = U.[Assembly #]) AND (Q.[Line #] = U.[Line #]) AND
(Q.[[Alt Unit 1]] <> U.[[Alt Unit 1]]))"
Does this have to do with Access 97?? I did a copy paste to do the queries will I need to type from scratch?? Thanks

 

[JerryKlmns]

You do have a form named Parameter with a textbox named Quote # ?
--------------------------
UPDATE Q_Sel As Q INNER JOIN [t_BOM Part Cross] As U ON
((Q.[Customer ID] = U.[Customer ID]) AND
(Q.[Assembly #] = U.[Assembly #]) AND
(Q.[Line #] = U.[Line #]) AND
(Q.[Alt Unit 1] <> U.[Alt Unit 1]))
SET [Use Part?] = -1

 

[Basshopper]

Jerry: The Parameter box that comes up was from using
=[FORMS]![Parameter]!

in the critera line on the query grid. its not a seperate form but a parameter dialog box. It looks like the update query is working but I am not gettng to put in the quote # as it related to above wo it is updating all quotes. I am still getting the Where error message on both queries now. So getting closer

 

[JerryKlmns]

Aha! It is a parameter query!

PARAMETERS

Text(255); -- If it is a Text Data Type Field
PARAMETERS

Long; -- If it is a Numeric Long Data Type Field
SELECT [Customer ID], [Assembly #], [Line #], Min([Alt Unit 1])
FROM [t_BOM Part Cross]
GROUP BY [Customer ID], [Assembly #], [Line #],
WHERE

;

 

[Basshopper]

Jerry: I am getting the same error as before except it a Parameter clause instead of Where. Still getting the Where error on the Upd_qwS query. ONce these are fixed then I can test.What am I doing wrong here? Might have to hit the sheets and pick this up tomorrow. Please hang with me on this its the last thing to finish so I can get this updated to my purchasing dept. Thanks

 

[JerryKlmns]

Pls clarify this:
Is fiedl Quote # in table t_BOM Part Cross, a text field, a numeric one or what?

Would you post the SQL for both queries that you have at this point? I might be missing something

 

[Basshopper]

Hi Jerry: Yes the Quote # is in t_BOM Part Cross as a text field. Here is the Upd_qwS Query Getting Undefined function WHERE in expression error message.

UPDATE Q_Sel AS Q INNER JOIN [t_BOM Part Cross] AS U ON (Q.[Alt Unit 1] <> U.[Alt Unit 1]) AND (Q.[Line #] = U.[Line #]) AND (Q.[Assembly #] = U.[Assembly #]) AND (Q.[Customer ID] = U.[Customer ID]) SET [Use Part?] = -1;

Some more explaination might help. I will post the actual query that is behind the subform in datasheet view that I manually undate now. If I put the update query change the name to update the actual final query with a parameter of exact

then the update could do that and we could leave out the Q_Sel query. Here is the actual query I am trying to automate the [Use Part?] check box.

 SELECT [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[quote=#], [t_BOM Part Cross].[Ascentron #], [t_BOM Part Cross].Rev, [t_BOM Part Cross].Ref, [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Customers #], [t_BOM Part Cross].Description, [t_BOM Part Cross].MFG, [t_BOM Part Cross].[U/M], [t_BOM Part Cross].[Qty Per], [t_BOM Part Cross].[Use Part?], [t_BOM Part Cross].[Alt MFG #], [t_BOM Part Cross].[Cust Cost], [t_BOM Part Cross].[Alt Unit 1], [t_BOM Part Cross].[Alt Unit 2], [t_BOM Part Cross].[Alt Unit 3], [t_BOM Part Cross].[Alt Unit 4], [t_BOM Part Cross].[Alt Unit 5], [t_BOM Part Cross].[Alt Unit 6], [t_BOM Part Cross].[MIN LT], [t_BOM Part Cross].[MAX LT], [t_BOM Part Cross].[Min Qty], [t_BOM Part Cross].[Alt Pkg Size], [t_BOM Part Cross].[Lot Charge], [t_BOM Part Cross].[Lot Qty], [t_BOM Part Cross].NRE, [t_BOM Part Cross].ETF, [t_BOM Part Cross].Tooling, [t_BOM Part Cross].[Other NRE], [t_BOM Part Cross].Vendor, [t_BOM Part Cross].Stock, [t_BOM Part Cross].Comments, [t_Quote Qty]![Qty 1]*[Qty Per]*[Alt Unit 1] AS [Unit 1 Ext], IIf([Min Qty]=0,0,IIf([Qty 1]*[Qty Per]=0,0,IIf([Qty 1]*[Qty Per]<[Min Qty],(([Min Qty]-[Qty 1]*[Qty Per])*[Alt Unit 1]),IIf(([Qty 1]*[Qty Per]/[Alt Pkg Size])=Int([Qty 1]*[Qty Per]/[Alt Pkg Size]),0,[Alt Pkg Size]-(((([Qty 1]*[Qty Per]/[Alt Pkg Size])-Int([Qty 1]*[Qty Per]/[Alt Pkg Size]))*[Alt Pkg Size])))*[Alt Unit 1]))) AS [XCS 1 Ext], [t_Quote Qty]![Qty 2]*[Qty Per]*[Alt Unit 2] AS [Unit 2 Ext], IIf([Min Qty]=0,0,IIf([Qty 2]*[Qty Per]=0,0,IIf([Qty 2]*[Qty Per]<[Min Qty],(([Min Qty]-[Qty 2]*[Qty Per])*[Alt Unit 2]),IIf(([Qty 2]*[Qty Per]/[Alt Pkg Size])=Int([Qty 2]*[Qty Per]/[Alt Pkg Size]),0,[Alt Pkg Size]-(((([Qty 2]*[Qty Per]/[Alt Pkg Size])-Int([Qty 2]*[Qty Per]/[Alt Pkg Size]))*[Alt Pkg Size])))*[Alt Unit 2]))) AS [XCS 2 Ext], [t_Quote Qty]![Qty 3]*[Qty Per]*[Alt Unit 3] AS [Unit 3 Ext], IIf([Min Qty]=0,0,IIf([Qty 3]*[Qty Per]=0,0,IIf([Qty 3]*[Qty Per]<[Min Qty],(([Min Qty]-[Qty 3]*[Qty Per])*[Alt Unit 3]),IIf(([Qty 3]*[Qty Per]/[Alt Pkg Size])=Int([Qty 3]*[Qty Per]/[Alt Pkg Size]),0,[Alt Pkg Size]-(((([Qty 3]*[Qty Per]/[Alt Pkg Size])-Int([Qty 3]*[Qty Per]/[Alt Pkg Size]))*[Alt Pkg Size])))*[Alt Unit 3]))) AS [XCS 3 Ext], [t_Quote Qty]![Qty 4]*[Qty Per]*[Alt Unit 4] AS [Unit 4 Ext], IIf([Min Qty]=0,0,IIf([Qty 4]*[Qty Per]=0,0,IIf([Qty 4]*[Qty Per]<[Min Qty],(([Min Qty]-[Qty 4]*[Qty Per])*[Alt Unit 4]),IIf(([Qty 4]*[Qty Per]/[Alt Pkg Size])=Int([Qty 4]*[Qty Per]/[Alt Pkg Size]),0,[Alt Pkg Size]-(((([Qty 4]*[Qty Per]/[Alt Pkg Size])-Int([Qty 4]*[Qty Per]/[Alt Pkg Size]))*[Alt Pkg Size])))*[Alt Unit 4]))) AS [XCS 4 Ext], [t_Quote Qty]![Qty 5]*[Qty Per]*[Alt Unit 5] AS [Unit 5 Ext], IIf([Min Qty]=0,0,IIf([Qty 5]*[Qty Per]=0,0,IIf([Qty 5]*[Qty Per]<[Min Qty],(([Min Qty]-[Qty 5]*[Qty Per])*[Alt Unit 5]),IIf(([Qty 5]*[Qty Per]/[Alt Pkg Size])=Int([Qty 5]*[Qty Per]/[Alt Pkg Size]),0,[Alt Pkg Size]-(((([Qty 5]*[Qty Per]/[Alt Pkg Size])-Int([Qty 5]*[Qty Per]/[Alt Pkg Size]))*[Alt Pkg Size])))*[Alt Unit 5]))) AS [XCS 5 Ext], [t_Quote Qty]![Qty 6]*[Qty Per]*[Alt Unit 6] AS [Unit 6 Ext], IIf([Min Qty]=0,0,IIf([Qty 6]*[Qty Per]=0,0,IIf([Qty 6]*[Qty Per]<[Min Qty],(([Min Qty]-[Qty 6]*[Qty Per])*[Alt Unit 6]),IIf(([Qty 6]*[Qty Per]/[Alt Pkg Size])=Int([Qty 6]*[Qty Per]/[Alt Pkg Size]),0,[Alt Pkg Size]-(((([Qty 6]*[Qty Per]/[Alt Pkg Size])-Int([Qty 6]*[Qty Per]/[Alt Pkg Size]))*[Alt Pkg Size])))*[Alt Unit 6]))) AS [XCS 6 Ext]
FROM [t_Quote Qty] LEFT JOIN [t_BOM Part Cross] ON [t_Quote Qty].[quote=#] = [t_BOM Part Cross].[quote=#]
ORDER BY [t_BOM Part Cross].[Customer ID], [t_BOM Part Cross].[Assembly #], [t_BOM Part Cross].[quote=#], [t_BOM Part Cross].[Line #];

thanks Larry

 

[Basshopper]

Hi Jerry: After a 2nd thought the subquery above does not have any parameters on it, Thoses come from the main form query. Your way to do this is best. I oould not get the revised Parameter Code above to save as a query, This is the error code from that

Syntax error in PARAMETER clause. (Error 3139)

Larry

 

[Basshopper]

Got the Parameter Query working. The update query is giving me an error message getting close.

The specified field [Use Part?]could refer to more than one table listed in the FROM clause in your sql statement.

is this refering to the SET [Use Part?] = -1 of the Update Query? Any suggestions.

 

[JerryKlmns]

Basshopper,

congrats on doing searching and fixing!
I think this should be the final

UPDATE Q_Sel As Q INNER JOIN [t_BOM Part Cross] As U ON
((Q.[Customer ID] = U.[Customer ID]) AND
(Q.[Assembly #] = U.[Assembly #]) AND
(Q.[Line #] = U.[Line #]) AND
(Q.[Alt Unit 1] <> U.[Alt Unit 1]))
SET U.[Use Part?] = -1

 

[Basshopper]

I had already tried that also including the table name, When I run Query the Q.field names come up as a parameter box, What might be the next step to try???

Larry

 

[JerryKlmns]

SELECT [Customer ID] As Cust, [Assembly #] As Asbl, [Line #] As Ln, Min([Alt Unit 1]) As MinAl
FROM [t_BOM Part Cross]
GROUP BY [Customer ID], [Assembly #], [Line #],
WHERE

= .....

UPDATE Q_Sel As Q INNER JOIN [t_BOM Part Cross] As U ON
((Q.Cust = U.[Customer ID]) AND
(Q.Asbl = U.[Assembly #]) AND
(Q.Ln = U.[Line #]) AND
(Q.MinAl <> U.[Alt Unit 1]))
SET U.[Use Part?] = -1

 

[Basshopper]

After all this, it comes down to one thing. When the select query gives me the correct results it is NOT updatable, When its updatable it does not give me the Min function or group by to get the lowest unit 1 for 1 line #.

Your above would work if the select query was editable.

thoughts?????

 

[lespaul]

there are certain requirements for an updatable query, I'm pretty sure one of them is that you have to include the PK from all the tables involved. There are several posts that PHV has linked an article regarding updatable queries.

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Basshopper]

that link was great info, got me on track , here is what I have so far. I am getting an operator missing error, in the 2nd select statement. any help would be great Thanks

[Code}
SELECT [t_BOM Part Cross].

, [t_BOM Part Cross].[Line #], [t_BOM Part Cross].[Alt Unit 1], [t_BOM Part Cross].[Use Part?]
FROM [t_BOM Part Cross]
WHERE (([t_BOM Part Cross].[Alt Unit 1])>
(SELECT MIN([t_BOM Part Cross].[Alt Unit 1])) As [MinOfAlt Unit 1]))
FROM [t_BOM Part Cross])))
[/Code]