Help! Importing from Excel to Access

[Pootle34]

Morning all,

I am extremely new to Access and have been put on the spot to create a whole new db for a helpdesk and get it all up and running!

Ok so far on the normalisation, etc, but am having difficulties as on a monthly basis we download a set of information to an Excel spreadsheet. The data is made up of incidents logged and their resolution status, etc. Therefore, some old incidents will have been updated in the following month's data.
I know how to import the first month's data and have set primary key, etc but I am unsure on the best way to update the data onwards from then.
Can you advise me on the best way to do this, i.e. update in Excel first and then overwrite - seems long and pointless to me, or merging and updating tables via a query/macro process in Access?
Any ideas/advice on best way to proceed would be much appreciated.

Christine

 

[M8KWR]

with the new data, are you ok in over-riding the data that is in access. Just want to make sure you will not lose any information!!!

In the information that you have imported in the first month, is there a field that would be the same for both the first months data and the updated stuff..??

If so, you then could use an update query.

What you would do is add both table into the query, join the table by the unique id number.... drag the fields from the the main access table, then in the Update area, you would need to put [updated info table]![name of field] - obvioulsy the field name should match the name as the field you are updating.....

Or what you could do is import multiple records per incident logged, but display the information to the user in descending date order, so they always look at the most up-to-date information - this way you have a log of how the incident has changed..

I hope this helps....

 

[Pootle34]

Thanks for your response, just to clarify the data from month one is made up of open and closed calls. I need to keep the closed data as is, but any calls that are open will have been updated in month two and these I need to overwrite with the month two data and so on each month.
Each incident can be identified by a unique predetermined ref number.
So, if I went via an update query each month, would I import the 2nd month's etc data into a new table each time? At the moment when I try to import it into the same table via the import wizard, it errors and won't let me proceed.
Hope this makes sense and thanks again.
C

 

[M8KWR]

Unsure how this sounds, but here goes.

If you have a unique reference for each incident report then how about instead, delete the records that existing already that have a unique number within access when you have your 2nd months import.

Once these have been deleted then just import the 2nd months data.....

This way is very easy to set up.... saves having to update anything, you are just deleting and then importing...

 

[Pootle34]

Not sure about this one...are you suggesting a lookup or macro to locate all duplicates before I import the data?
As these are calls logged with a helpdesk over a month, I am talking a few thousand lines of data each month.

 

[M8KWR]

is the access file used to records call logs as a person takes a call, or is the excel file the call logs, and the access file only a screen shot of everything???

 

[Pootle34]

All data is recorded on a completely separate system. I only have access to the monthly download onto Excel. It is only at this point that I can transfer the data to Access. This is why it is so important that I be able to import and update the data on a monthly basis.
HTH

 

[M8KWR]

Could you have multiple call logs per incident report???

So in the excel file it would have the same reference number put appear to be on 2 or more rows???

Just tryin to get a better understanding of the data you have, and then the best process/easiest process you can adopt in access

 

[Pootle34]

Cheers for the help, to be honest, it's turning out to be a lot trickier than originally planned.
There is only one log per incident, each has it's own unique reference number and is updated as and when required on the separate logging system.
I receive an excel download on a monthly basis. This excel spreadsheet lists every incident identified by it's reference number. Month two's download would include all previously opened calls with their updated status and any new calls that have been logged in month two regardless of status.
hth

 

[M8KWR]

Ok......

what i would do, is import month 1 excel file into access...

When you get to month2,3, etc, cross refernce the main table against month 2 file, using the refernce ID number.

This will show you whatis in the month2 file, that is already existing in the access database.

What i would do now is to delete the records that exist in access, because then exist in the month 2 file.

Once you delete these records from access, import the month 2 file.

This way you would have no duplicate ID numbers, and you have the most up-to-date information in the access database.

Is this what you require??? Cause creating the delete query is really easy to do, it does save time creating an update query, then if you did this you would still need to delete the records from the month2 file that you have updated before you import it.....