Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Location
    Louisville, Kentucky, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Daily Updating (Access 97)

    I'm very new to access. Is there an easy method to import a daily update spreadsheet and use to easily update a master log ? For example, I need to update a master log of people that work in our company and what is their latest version of a couple large software products (i.e. Version 2.5 for Product X and version 3.0 for Product Y). We then know at all times who is not up to date. We get daily logs from who logs into the network and who has received the latest updates. Today I take these in Excel spreadsheets and do a lot of copying and pasting and VLOOKUP function to compare and update. Anyone have a good easy tip to get this done in Access or any other easier method ?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Daily Updating (Access 97)

    It is easy to import an Excel sheet into Access. From there it should be easy enough to update your master table with these details.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Daily Updating (Access 97)

    You could link the excel spread sheets to your Access database, this way they are always up to date. Then set up an append query to add the new records of your choice to the main datatable. All this is very easy to do.

    HTH

    Ian

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Daily Updating (Access 97)

    I think that what you are trying to do sounds more complicated than either of the two previous respondents make it sound.

    My understanding is that you have a table of people and the versions they use. You can bring in from the Excel the network login records. Some people will login many times, others less often perhaps.
    It seems to me that you need to work through the records in the login table, and for each person compare the info here with that in the people table. If the data is the same do nothing, if the login data is different update the people table.

    To do all this, I would use DAO to create a two recordsets. Loop through one them, find the appropriate record in the other, update if necessary etc.

    If you are "very new to Access" this would involve learning quite a lot of VBA in a hurry.
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Daily Updating (Access 97)

    A couple of other avenues to pursue: How do you know that a person has been updated to the latest version of software for a given product - is it simply the fact that they have logged into the network, or is it some other sort of process that might be automated to actually update the data in a database? Login scripts can be created to do very powerful things. Also, if you can import the data into Excel, you can probably also import it directly in Access, bypassing Excel. Access has the advantage of being able to do record processing with relative ease, while Excel has the ability to record a macro which might allow you to automate many of your analysis steps, so tradeoffs are involved. If you don't know database concepts or VBA, you have some fairly tall buildings to leap, but the loungers here would be happy to try to help you along the way.
    Wendell

Posting Permissions

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