I have a membership database and twice a month I get an up-to-date text file of members that has been added or are no longer a member. What I have to do is get the new members in the database file from the text file, the members who are in both files record may have to be updated if there are changes in the text file of that member - ex: last name, address, salary etc., and remove the members who are no longer members but would be in the table but not in the text file. What kind of code(s) should I write for this. I'm new to writing code. Basically, I understand how to setup a table. Please someone help me.....
Record update
- Thread starter charligov
- Start date
I have a membership database and twice a month I get an up-to-date text file of members that has been added or are no longer a member. What I have to do is get the new members in the database file from the text file, the members who are in both files record may have to be updated if there are changes in the text file of that member - ex: last name, address, salary etc., and remove the members who are no longer members but would be in the table but not in the text file. What kind of code(s) should I write for this. I'm new to writing code. Basically, I understand how to setup a table. Please someone help me.....You can do most things with queries so it is worth investigating what they do.
SET UP YOUR MEMBERS TABLE
- Make sure your table has a unique key for each member (Primary Key). Ideally an ID but if not you may have to use a combination of fields ( e.g. Full Name + zip code) That way it won't accept duplicate entries. Hopefully you have an ID as otherwise life is harder.
TO ADD NEW MEMBERS
- Import your text file into a table with exactly the same structure. You can copy the main table and say you want 'Structure only' when pasting.
- Create an Append query to append from the imported table to the main table. You can run this as your Primary key will prevent duplicates - you get messages to that effect.
TO DELETE EXISTING MEMBERS
- You need a query with a join for this (trickier). Create a Delete query and add both tables. create a join (check help?) between the Primary key of the Main table and the imported table. All the links should be (paraphrasing here) 'Include all Fields from Main table and only Fields from Imported table where joined field are equal'. (right-click on the line and select Join Properties) Then drag each of the Primary key fields (1 is best!) from the Imported table to the Field line, one column for each field. In the Criteria line in each column type the word 'null'. What we are going to do here is to delete from the Members table all records that are not in the Imported table. Run the query and check that the number of records it is going to delete makes sense. (? Are you sure you want to delete them and not save them somewhere - at least copy the Members table first ?)
TO UPDATE EXISTING MEMBERS
You can easily update all of the data in the main Members table from the new list replacing everything but this might not be what you want.....?
- To do this we need an Update query. Add both tables and join the primary keys as before but this time don't change the Join properties. Drag each field you want to update from the Members table into the grid. In the Update To line you have to type in the table name and the field name from the Imported table under each Field you want to update (e.g. in the Address column type 'MyImportedTable.Address' ). Running this will update your main table.
You need to take a good look at queries. Believe me code is worse...
SET UP YOUR MEMBERS TABLE
- Make sure your table has a unique key for each member (Primary Key). Ideally an ID but if not you may have to use a combination of fields ( e.g. Full Name + zip code) That way it won't accept duplicate entries. Hopefully you have an ID as otherwise life is harder.
TO ADD NEW MEMBERS
- Import your text file into a table with exactly the same structure. You can copy the main table and say you want 'Structure only' when pasting.
- Create an Append query to append from the imported table to the main table. You can run this as your Primary key will prevent duplicates - you get messages to that effect.
TO DELETE EXISTING MEMBERS
- You need a query with a join for this (trickier). Create a Delete query and add both tables. create a join (check help?) between the Primary key of the Main table and the imported table. All the links should be (paraphrasing here) 'Include all Fields from Main table and only Fields from Imported table where joined field are equal'. (right-click on the line and select Join Properties) Then drag each of the Primary key fields (1 is best!) from the Imported table to the Field line, one column for each field. In the Criteria line in each column type the word 'null'. What we are going to do here is to delete from the Members table all records that are not in the Imported table. Run the query and check that the number of records it is going to delete makes sense. (? Are you sure you want to delete them and not save them somewhere - at least copy the Members table first ?)
TO UPDATE EXISTING MEMBERS
You can easily update all of the data in the main Members table from the new list replacing everything but this might not be what you want.....?
- To do this we need an Update query. Add both tables and join the primary keys as before but this time don't change the Join properties. Drag each field you want to update from the Members table into the grid. In the Update To line you have to type in the table name and the field name from the Imported table under each Field you want to update (e.g. in the Address column type 'MyImportedTable.Address' ). Running this will update your main table.
You need to take a good look at queries. Believe me code is worse...
