Access query problem

[SlipGun]

I have a table with 3 fields that are of importance:
1) Item ID
2) A field with values set to either "Sale" or "Purchase"
3) Date of transaction

I have duplicate entries in this table for items that have been bought/sold many times over. Basically, I want to eliminate all duplicate entries for each item except for the one that has the highest date of them all - the latest transaction.

It's probably simple, but I'm new to this.

[kakyoin]

what prog language you use?
is it ms access?

[fyxxer32]

I dont have an answer but WELCOME to Techimo!

[angelcat]

I don't know either, but I can offer a welcome to TechIMO!

[vass0922]

Hmmm
Unfortunately the problem is not quite as easy as you'd like it to be!

First of all, before attempting this query MAKE SURE you are not working on the live data, its too easy to delete everything!!

First of all, lets set up a select query to see if we can get the correct data...

Tell ya what lemme think about it.. it may take a few steps and w/o the data will have to think about it, to see what we can do... there is a wizard in access that helps get rid of dupes, but not sure if it allows you to specify which ones to keep... will probably use the count(columname) function ...

also a welcome to TechIMO
If you do figure it out, let us know for future reference
I was thinking you could do a nested query, but not sure if it will be that easy... maybe somebody else can pop in an idea too

[Rhino]

It might be safest for a select query on Item ID. Sorted by date, descending. Latest date should be at top. Make new query where date is less than that latest date. Change it to delete query and run.

[vass0922]

Hmm rhino, that may not work...

If you sort by itemId you will get several ItemId's in a row of the same thing... plus you will have to define a date to cut it off and that's not real practical to get rid of ALL dupes.. would it work? partially

I'm assuming by the subject line this is an Access database, unfortunately writing a stored procedure in that involves writing a macro in VBA... not the same thing.

If you're good at writing macros you could make one that goes through each row (slow process) and looks for the itemid, queries a temp table if the itemid exists in that table... if it does not insert that row into the temp table... if it does exist then check for a newer date... if it is a newer date update the row with the new info or delete / insert

kind of a painful way to do it though stinking Access

If you could group by ItemId and pick the one with the latest date that would wonderful, but if you group them including the date it'll return all of the dupes ... useless

[Whir]

Do you have to delete the data? If all you need to know is the latest date an item was bought or sold, you could use a normal query inside Access. Just build your query, and sort it ascending by date. Then in the ItemID field you can set the field (in the query) to no duplicates. Then you'll only see the latest transactions done for that piece of inventory.

-Whir

[thehig]

How do you want to handle multiple sales on the same date?Does the database assign the date which would include time or is it manual entered? If it includes time you have no problems. I think I can help you it will revolve around unique keys. Let us know if you need further help or if one of the above solutions will work.

[Rhino]

First of all, what's the deal with wanting to get rid of duplicates? The only way I would see that happening is if you only had the one table. If that's the case, you need to make a table purely for Inventory....and another that has the Item ID, Date, (I would add time too), and sale/purchase. This way the Sales table would have all unique entries, with Item ID, Date, and Time together being the primary key. Then you wouldn't have to delete anything. You would have no probably with data redundancy that way.