Using Spreadsheet to "feed" info to Microsoft Access

[TheJax]

Hello,

Excel and Access 2007 scneario

We have a system which records employee absences. Some/most of our staff are sub contractors. So we need to send those companies monthly reports of their attendance. The system we enter the attendance info into allows us to export to Excel. But ACCESS (as you all know) is much more powerful for reporting.

Here is my question:

The secretary doing this report right now is doing her best, but really needs a streamlined solution (in other words, the easier the better). I tried importing the Excel spreadsheet, creating a query and then the report (which works great). Where I run into a problem is with the next months spreadsheet. I rename the old spreadsheet, import the new and name it the same as the first spreadsheet I created (thinking this would not break any connections between queries and reports). Unfortunately, after I import the new spreadsheet and run the report, I am still getting the old data.

Any help here would be greatly appreciated.

 

[alansidman]

Just a thought. Instead of changing spreadsheets and renaming, what about just continuing in the existing spreadsheet making sure that there is a date field included. Filter your query for the month required and run your reports accordingly.

 

[TheJax]

Just a thought. Instead of changing spreadsheets and renaming, what about just continuing in the existing spreadsheet making sure that there is a date field included. Filter your query for the month required and run your reports accordingly.

Thanks for the reply...And that does make sense. Here's my only issue with that:

For some strange reason, when sorting by date, it dates are sorted like this:

11/10/2014
11/11/2014
11/12/2014
11/3/2014
11/4/2014

I've tried going into the spreadsheet before importing and formatting the column as a DATE. But when I do that, after it's brought in to Access, access displays it as serial numbers Any ideas on how to correct that?

 

[Joe4]

For some strange reason, when sorting by date, it dates are sorted like this:

11/10/2014
11/11/2014
11/12/2014
11/3/2014
11/4/2014

That indicates that dates are stored as Text. You do not want that. None of the date functionality will work properly on that (and neither will Sorts).

I've tried going into the spreadsheet before importing and formatting the column as a DATE. But when I do that, after it's brought in to Access, access displays it as serial numbers Any ideas on how to correct that?

That is because both Excel and Access actually store dates as serial numbers, specifically the number of days since 1/1/1900.
You just be able to apply a date format to the field in Access to get it to display in the date format that you are used to.

 

[TheJax]

That indicates that dates are stored as Text. You do not want that. None of the date functionality will work properly on that (and neither will Sorts).


That is because both Excel and Access actually store dates as serial numbers, specifically the number of days since 1/1/1900.
You just be able to apply a date format to the field in Access to get it to display in the date format that you are used to.

I'm in business!! Thank you for your input!

 

[Joe4]

I'm in business!! Thank you for your input!

You're most welcome!

 

[alansidman]

Dittos