I'm trying to find a way to run a report of data in my Access database into an excel spreadsheet file. In other words, I want a permanent excel file saved within my company's local network that can be periodically updated as information is entered and removed from my database.
I work at a volume based business. We receive somewhere between 23-40 referrals a day. We have workers who manually input the basic referral information into our database. Each referral is assigned a hearing date (this is a law firm btw). The hearing is generally scheduled 3-4 weeks after the information is entered into the database. What I'm looking for is an excel spreadsheet that is updated at least once every day. I'd like the spreadsheet to display the basic information of these upcoming hearings. Additionally, I'd like to arrange the "sheets" within the spreadsheet by weeks. In other words, sheet 1 would be "Hearings for the week of 3/30/2009 - 4/3/2009", sheet 2 would be "for the week of 4/6/09 - 4/10/09."
Basically, I would assign 1 row per referral and have the information of that case imported into the various columns of that row.
Is any of this doable? My instincts tell me no. For instance, I'm not sure there's a way to program excel to interpret the data stream from access. Is there a way to program excel, "if hearing date = 3/30/09, go to sheet 1"? Is there a way to tell excel to sort the rows by hearing date as the data comes in? Is there a way to tell excel to insert a new cell between two others when the hearing date falls between two referrals that are already on the spreadsheet? These are the questions that lead to me believe this is impossible.
Please advise if you have any experience in setting up a report like this. This kind of report could really make my company more efficient. If someone could simply point out some literature on the subject, that would be most helpful as well.
Bookmarks