-5

I am building an Access 2010 db which will store and query information relating to time spend by users in our team. Part of the reporting needs to include whether timesheets have been submitted on time.

The process is currently being managed in Excel but is becoming cumbersome due to the growing size of the consolidated data. In the current process, the flag on whether someone is late with their timesheet is applied manually.

Instead of manually adding a Yes / No value to the excel data, I wondered whether it was possible to set up separate TransferSpreadsheet processes in Access to upload the excel data (and attach them to separate command buttons) such that, depending on which one is executed, the import process adds a Yes or a No value to the last column in the data as it's being uploaded.

That way we can import the excel data for those who submitted their timesheets on time (and 'stamp' them Yes for being on time) and then any subsequently late submitted timesheet data can be imported later (and 'stamped' with a No).

I have spent several hours looking at online forums and instruction pages but cannot find anything close to what I am trying to achieve, hence the reason for posting this here.

This is just one of the options I am considering but my VBA skills are insufficient to establish whether such a process could be handled in VBA. All help appreciated. Thanks.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Donald G
  • 1
  • 4
  • 1
    The answer is simple: there's nothing you can't do with VBA. – Thomas G Nov 03 '16 at 09:18
  • Thanks Thomas. Can you expand on your response by giving an example of the VBA code to perform such a task? Thanks. – Donald G Nov 03 '16 at 09:45
  • Please show us what you have tried so far. Stack Overflow is not a code writing service, but people are willing to help you if you at least try to solve the problem at your own. Please read [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) and [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask). – Martin Nyolt Nov 03 '16 at 13:16
  • Thanks. Appreciate this isn't a begging bowl service but this was my first post on StackOverflow and so obviously I have not quite understood how things work on here. I was just reaching out to more technically proficient people for a bit of support. Having persevered with this today and thought about the data process flow a bit more, I've been able to work through to a working solution using VBA. – Donald G Nov 03 '16 at 16:20

1 Answers1

0

Solved this one myself with a bit of perseverance. Ended up running a few DoCmd.RunSQL commands to Alter / Delete / Insert the tables I had and used a 'join' table to load the data from excel and then ran a command to append the data from the 'join' table to the main table. I just invoke slightly different commands to update the table field based on whether the data has been submitted late or on time.

Donald G
  • 1
  • 4