Daily CSV file into Access - Help! - Microsoft Community

i'm new access have experience working sybase , oracle dbms. first time finding myself being asked build new db in professional setting , i'm struggling bit.

we have daily csv file manager imported access. current set-up i'm loading file working table , have series of queries copy data working table several different tables. i'm getting stuck rows of data in daily file may change , need able update row new data. instance, 1 of tables member table address information. if member's address changes, need record update new address i'd old address copy historical table.

when upload new csv file working table, run series of append queries , records brand new append appropriate table i'm not sure how handle updates. i'd write query matches records primary key i'm not quite sure how go it. of sql i've written read , not making changes data in tables. i'm not sure how trigger historical record. in oracle, i'd use trigger statement i've been reading online, there's no triggers in access. can me or point me resource figure out?

example of addresses, rather archiving old addresses in separate table, recommend addresses stored in single table includes boolean column name active or similar, along memberid or similar foreign key column referencing primary key of members table.  in day-to-day use of database use queries restricted active return each member's current address.

when inserting new data, unless text file includes distinct memberid column or similar need determine other combination of columns candidate key.  not easy.  names unsuitable can legitimately duplicated.  person's date of birth used in conjunction names, have regularly told story here of when present @ clinic 2 patients arrived within minutes of each other, both female, both same names, , both same data of birth.  so, it's identify suitable candidate key.

having identified candidate key can include columns in unique index.  if execute insert into  statement (what access calls 'append' query) insert row members, rows in text file same candidate key values rejected, new members inserted.

same apply when executing query insert address data referencing addresses table, in have again identified suitable candidate key.  additionally, however, need insert value of true active column in new row inserted, , update value in active column false in row in value true prior inserting new row.  sequence

1.  identify whether address being inserted new address, can first joining table new data existing table on whatever have identified candidate key.  if join returns no match, address new one. if you'd proceed follows, if not you'd abort routine.  can same in code calling dlookup function.

2.  update active column in client's current rows in addresses false.

3.  insert new address new row in addresses, value of true being inserted active table.

other attributes might change, you'd similarly, in cases want update values, , not retain 'historic' values, case of joining table new data operational table on candidate key , executing update statement.  don't need identify whether value has changed, if has not, updated current value.

key thing identification of candidate keys present both in operational tables, , in text file being imported.  how easy depend on how structured text file is.

regards triggers, nearest thing in access known 'data macros'.  these executed automatically on basis of changes in data, regardless of how changes made, avoiding need programmatically.  i'm not sure they'd in context, however, might want explore capabilities.

Office / Access / Windows other / Office 2010