Update only ONE duplicate row

[djmousie]

I was helped in another thread in relation to this problem, but ive tweaked my needs and now im not sure how to resolve...

I currently have two tables. The MASTER TABLE has a list of accounts and quantities. The SECOND table has additional quantities that need to be merged in by account #, however,the quantities need to add in a new row based on account #, BUT only update one of the rows, not both. For example,

MASTER TABLE
Account # Qty
123 25
123 25

TABLE 2
Account # Qty
123 50

Currently, if i try to add a new field in the MASTER table, if includes the quantity of 50 twice, from TABLE 2, since the account #'s are the same (see below)

MAKE TABLE QUERY RESULTS
Account # Qty Table 2 Qty
123 25 50
123 25 50

The query should only spit back the following results, any help?

MAKE TABLE QUERY RESULTS
Account # Qty Table 2 Qty
123 25 50
123 25 <NULL>

 

[djmousie]

Any thoughts?

 

[VRoscioli]

What is the primary key for the MASTER table? Also, how do you want to determine which row to update? Or does it not matter?

-V

 

[djmousie]

The primary key is Account #, it does not matter which row it updates.

 

[VRoscioli]

Account # cannot be a primary key if there are duplicate values... Primary keys must be unique.

-V

 

[djmousie]

There is no current primary key then. I was helped on thread701-1391321, however, I had thought there was an effective dated field (and there wasn't) and I need to somehow have the same effect with the below code, but removing any mention of effecting dating and just update based maybe on FIRST/LAST. let me know ur thoughts, thanks.

SELECT M.[Account #], M.Qty, M.[Eff Date], T.Qty2
FROM [MASTER TABLE] AS M LEFT JOIN (
SELECT A.[Account #], Max(A.[Eff Date]) AS LastDate, B.Qty AS Qty2 FROM [MASTER TABLE] AS A
INNER JOIN [TABLE 2] AS B ON A.[Account #] = B.[Account #] GROUP BY A.[Account #], B.Qty
) AS T ON M.[Account #] = T.[Account #] AND M.[Eff Date] = T.LastDate

 

[VRoscioli]

Are there any fields that would be unique between the two rows in the MASTER table? If not, I don't know if it is possible to address only one of them (as this is a violation of what it means to be a relational database).

-V

 

[AlexCuse]

Temporarily add an auto number column, and then just update either the row with the lowest(appearing first in table) or highest(appearing last in table) value for this column.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[djmousie]

Any help on the SQL by any chance? Im better at the GUI JET SQL...if what you say above should work then results should be...

MAKE TABLE QUERY RESULTS
Auto # Account # Qty Table 2 Qty
1 123 25 50
2 123 25 <NULL>

 

[VRoscioli]

Try:

UPDATE TABLE2 RIGHT JOIN (MASTER RIGHT JOIN (SELECT [Account #],min([Auto #]) as index FROM MASTER GROUP BY [Account #]) AS S ON MASTER.[Auto #]=S.[index]) AS T ON TABLE2.[Account #] = T.[Account #] SET T.[Table 2 Qty]=TABLE2.[Qty];

Might need a little fixing... just doing it off the top of my head.

-V

 

[djmousie]

Hmm, gets caught up (highlights) the last "AS" int he statement and stating SYNTAX error in update statement.

 

[VRoscioli]

UPDATE TABLE2 RIGHT JOIN (MASTER RIGHT JOIN (SELECT [Account #],min([Auto #]) as index FROM MASTER GROUP BY [Account #]) AS S ON MASTER.[Auto #]=S.[index]) ON TABLE2.[Account #] = MASTER.[Account #] SET MASTER.[Table 2 Qty]=TABLE2.[Qty];

Try that... again, just trying to do it off the top of my head since I don't have access to Access here.


-V

 

[djmousie]

Getting the error "Operation must be an updatable query"...

 

[VRoscioli]

Oh, I suppose those should be INNER JOINs, first of all. Wow, I'm out of it today... anyone spot any other dumb mistakes in there?

UPDATE TABLE2 INNER JOIN (MASTER INNER JOIN (SELECT [Account #],min([Auto #]) as index FROM MASTER GROUP BY [Account #]) AS S ON MASTER.[Auto #]=S.[index]) ON TABLE2.[Account #] = MASTER.[Account #] SET MASTER.[Table 2 Qty]=TABLE2.[Qty];

-V

 

[PHV]

So, with the AutoNumber replacing the date:
SELECT M.[Auto #], M.[Account #], M.Qty, T.Qty2
FROM [MASTER TABLE] AS M LEFT JOIN (
SELECT A.[Account #], Min(A.[Auto #]) AS FirstNum, B.Qty AS Qty2 FROM [MASTER TABLE] AS A
INNER JOIN [TABLE 2] AS B ON A.[Account #] = B.[Account #] GROUP BY A.[Account #], B.Qty
) AS T ON M.[Account #] = T.[Account #] AND M.[Auto #] = T.FirstNum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886