So I’m busy importing survey data (an Excel file) into our SQL Server via Access, and I my queries are turning out screwy results. I finally figured out why.
A field that should have been imported as a varchar (i.e. text) is instead being imported as a floating point number. Access was assigning the wrong data type to the field, and then dumping whatever data didn’t fit that data type. That’s because the first few dozen rows have zeros in that field. The text responses don’t turn up until later. Evidently Access looked at the zeros, decided it was a float, and then in the process of importing just skipped any data that wasn’t a float.
When I sorted the file so that text values were at the top, everything comes in fine.
Still an annoying bug, though.