update query based on condition

[slimjen1]

Hi all, I have an access front end linking to a sql back end. I have an automated update query function that is working fine. Now I need to put a condition to this and having trouble where to begin. My first table consist of 3fields: a list of salestickets, a three digit code and an amount. This table is an weekly excel import. The salesticket needs to be unique but there are times when the excel sheet would have duplicate salesticket numbers with different three digit codes Ex. '56789 DRE' and '56789 CRE'. The users wants to import the ticket with the largest amount. I was thinking I could use an if statement but the problem is I would not know the largest amount at any given time. Ex. '56789 DRE 300' and '56789 CRE 400'. In this case; I would import the one with 400. This excel sheet can consist of more than 50000 rows so scrubing manually before the import is not an option especially since its automated. Some guidance please. Thank you

 

[plog]

Not to be pedantic, but I suspect you are calling the 'DRE' portion of your data a 3 digit code. Its confusing me as to what each part of your data looks like. Part of me thinks '56789 DRE 400' is all 3 fields, but the other part of me thinks it might just be the sales ticket.

Is '56789 DRE 400' all 3 fields or just the sales ticket? Also, when you import the data is it going into 3 fields or 1?

 

[slimjen1]

Sorry for the confusion. There are 3 fields in the excel spreadsheet. I will be only 2 fields would be imported into access; salesticket(56789) and the code(DRE). Which Duplicates will be decided based on the third field which contains an amount in the spreadsheet. All the fields may have to be imported into a temp tbl before appending to the final tbl. You think?
Thanks

 

[plog]

I would import all 3 fields into a temporary table (Temp), use a query (LargestTicket) to determine the record for each sales ticket that has the largest 3rd field (Amount), and then create an append query (TempAppend) to put those sales tickets into your table (FinalTable).

Here is the SQL for the LargestTicket query:

SELECT salesticket, Max(amount) AS LargestAmount
FROM Temp
GROUP BY salesticket;

And here's the SQL for the TempAppend query:

INSERT INTO FinalTableName ( salesticket, code )
SELECT salesticket, code
FROM Temp INNER JOIN LargestTicket ON (Temp.amount = LargestTicket.LargestAmount) AND (Temp.salesticket = LargestTicket.salesticket);