Monday, October 1, 2018

Access Experts Out There?

I have a spreadsheet that was clearly not designed to import into Access and the default import Excel command will not work.  Why?  I have some date fields that are a year only, because the primary sources do not have month and date.  In some cases, I have inconsistencies in the text: FAMNONRES in some lines, and NONRESFAM in others.  (Yes, I should have used the enumerated list feature of Excel instead of typing this.  I can fix this with Replace in Excel, and that might be simplest.)  How do I tell Access to replace the year only column with 0/0/year?

Okay, I will just copy and paste as best I can.  I only have 233 records in the spreadsheet so far.  In some cases, I can also fill in a column that I not originally planned and are missing from first few dozen mass murders.

I will solve the mm/dd/yyyy problem by saving as CSV, then using SED to make yyyy into 0,0,yyyy, and m/yyyy into m, 0, yyyy.

1 comment:

Dean in Az said...

In ACCESS you'd be best served to use separate MM DD YYYY fields if your data is not consistently MM/DD/YYYY.
Then you can do whatever you wish.

Most of Database use is how you set it up. There are ways to manipulate that, but for simple tasks each discrete data item should just be it's own field.