Good afternoon all,
I'm trying to build a database that pulls data from several different linked excel files and combines them with data entered directly into Access. I have the tables linked successfully; my quandary is how to handle yes/no fields so that they populate properly in Access (I'm using the Office 2010 suite - it's a company system, so updating is not an option.)
I'm tracking activities for several departments within our organizations and one of the fields is called "Completed". In Access, I have this as a yes/no column. When I export the Access tables into Excel it populates this column as True/False. The problem with this is the group using the Excel spreadsheet that I want to link from puts "Y" in the Completed column for "yes" and leave it blank for "no". Due to the way that the data is displayed on the source spreadsheet forcing the users to use True/False makes the columns wide enough that the entire spreadsheet doesn't display on the monitor at once - this is not an insurmountable issue, but does detract from the usability of the source spreadsheet (there are several columns that use this convention).
What would be the best way to handle this information so that I can combine it with a yes/no column in Access? I would like to generate reports where the items marked Completed from all sources populate in one area. I would prefer to handle the data conversion in Access to minimize changes for the user; however, I'm not opposed to making the changes in Excel. I'm not skilled with VBA or Macros so I would need help to automate it.
Thank you in advance for the help.
Theresa