Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Maintaining records from 1 table in another table

    <P ID="edit"><FONT SIZE=-1>Edited by jp2558 on 01/03/08 01:49.</FONT></P>I have a process that imports a comma separated variable file (.csv) from Excel into an access table (tblA). This process already works fine.

    The next step is to achieve the following:

    I want to write the contents of tblA (the imported .csv) to another table (tblB) within the same database.

    The table layouts are identical. Since tblB will already contain records, I only want to update the records in tblB if the corresponding data fields from tblA have changed, OR when the record from tblA does not exist at all in tblB then the entire record from tblA needs to be added to tblB. Both files are keyed by the same field in both files.

    What would be the best method to achive this? Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Maintaining records from 1 table in another table

    I'm not clear on why you're going through Excel to CSV to Access in order to see if anything has been changed. Are you using Excel for data entry, or what? If the Excel data isn't primarily formulas rather than actual values, you can simply link the Excel file to Access and do the append from that rather than importing a text file.

    I would be very careful about the uniqueness of records in Excel, since it's virtually impossible to enforce, and that makes comparing records in Access a somewhat suspect operation.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maintaining records from 1 table in another table

    What I'm working on is an Events application for a university alumni organization. They manage events such as alumni homecoming, bus trips to away games, faculty/staff events, scholarship fundraisers, auctions, art shows etc. For each event a target group of invitees is determined and from there an AS/400 datafile is generated containing all of the invitee data (name, address, spouse etc). This is the origin of the .CSV file. (I only used excel as a example of generating the csv to avoid going into all this detail - excel will not be used at all in this process).

    Anyway, once the .csv is created it will be placed into a specific pc folder where Access will allow the user to choose from a number of .csv's and import it into a workfile (tblA). (This was the purpose of a prior post regarding sorting the contents of a list box.)

    Now since an invittee can be invited to multiple events, we only want that invitees' data to exist only once in tblB since this is the table that the remainder of the app relies on.

    So if the contents of tblA are different (ie address data has changed on the AS/400), we want to update tblB, or add the record if it doesn't exist at all in tblB.

    What I need to avoid is the situation where a record in tblB is not active for any current event, and I want those records to remain in tblB. Therefore I cannot simply zap the table after each event. (This is necessary since that invitee has related past event registration & payment data, which needs to be preserved so that it can be reviewed in a historical context for say annual events.) Also its possible that an invitee will be selected for multiple events. FYI - I also have an x-ref file that will show which events an invitee was invited to.

    Sorry for rambling. I hope this helps. I'm beginning to wonder if this is something that is too big a task for Access and whether it should be done in VB or directly on the AS/400.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Maintaining records from 1 table in another table

    How many alumni records are there? That should determine whether it's too big for Access. The design is another issue. Doing it in VB won't help, since VB uses the same database engine as Access. All you would be doing is substituting the program that calls that database engine. Access is much friendlier than doing it on the AS/400, certainly. However, I think you may need to rethink the process and logic of what you're attempting to do. DeDuping large lists is a miserable exercise, and that's what you're trying to do. Plus you want to store the lists separately and dedupe them against one another, if I read you correctly. If you decide to go ahead, lay in a supply of antacids and headache remedies.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maintaining records from 1 table in another table

    Each batch could contain anywhere from a few (100) to upwards of 10k records.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Maintaining records from 1 table in another table

    Whoa!! Stop!! We've decided to change the whole import process. No further info is needed regarding this process. Mucho thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •