finding a condition in a query (1 Viewer)

[vbaInet]

That was why I mentioned an UPDATE query, not an APPEND query.

 

[token_remedie]

but some assets move around three or four times before they're decomissioned, won't an update query just update the existing fields not re-add new entries? or will it be different because the locations will be different, hence, new record?

 

[vbaInet]

I almost confused your query with another poster's query. I had some heavy dinner earlier so I can blame it on that

If it moves around all you want to do is update the location where that asset is.

 

[token_remedie]

yeah that's cool that part is right and I am forever in debt to you. but I'm only updating the main table with the latest location, but I want to keep old locations in the transactions table for that assets ID, because I have a subform that's a history which reads off transactions. so at any point I can see the locations of where the asset has been previously as well as where it is now. trouble is the query finds duplicates and thats cool but the update only moves unique vales over to transactions, so currently there are 638 records in the query with duplicates but the transactions only shows 622. If I use an append query it says didn't add 638 records due to key violations.

Would it help if I attached the DB?

 

[token_remedie]

ohhh it wont add dupes, it only adds unique values, if i delete everything fron transactions it only gets 22 key violations if i run the query twice all records are a key violation. I hope that makes sense, by the way what was for dinner?

 

[vbaInet]

In what table are you looking to save the history? Also tell me the field names and what they represent. Inlude which ones are PKs too.

 

[token_remedie]

k so basically we've got a bazillion assets that have been kept in spreadsheets and someone said hey man can you make us a database? SUUURE I said *raise middle finger*

Anyway so everything is kept in seperate spreadsheets, so I've got an import button that lets you select the sheet to import, if its a redistribution sheet then it does this:
1. check the assets -> serial numbers if theyre the same but location is different (thats the one you helped me with) then move the location from Master (the main table) to transactions (the history table) then update the master table location from the redisttable (the import table).

Master:
PK is UID - Autonumber - this is what I'll be using to reference an asset from here on once the asset is in because the serial number or asset number can be duplicated (its a shitty system)
Location
these two fields are the only thing I really need to work with the rest doesn't change

transactions:
PK is ID - set to number

I've been reading and apparently I can set the table to allow duplicates?
the queries work, I just gotta append the transactions table because I'm moving over UID and Location from master, if the ID field allows duplicates from the UID then when I reference back to it from a subform it can be based on form!master.uid to find all entries in transactions, list them and its then showing where that asset has been in the past.

 

[token_remedie]

i found it - indexed - yes duplicates OK