Daily Updating (Access 97)

Topic

New Reply

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 ?

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote