Friday, October 5, 2018

So Many Things Forgotten

I have successfully imported the spreadsheet into Access.  Two issues hard to figure out.  I had it normalize one of my columns, but it discovered some errors UNKNOWN and UNKOWN are both in the column.  How do I find all UNKOWNs and convert them to UNKNOWN.  I have a few similar problems.

I have forgotten too much SQL.  I thought SELECT count(*) FROM Massmurder where date > 1820; would give me a count of records where the date column (which is defined as an INTEGER) < 1820.  No.  I get
Solved that one.  I want to change the column headers from the field names.  I used to know how to do this, and all the online references say this is correct:
SELECT  Table3.year AS 'decade', Count(*) as "total mass murders"
FROM Table3 where year >=1820 and year < 1830;
<1830 blockquote="">But when trying to run it:

Every source says field AS HeaderName.  What am I doing wrong?\
Beginning to wonder if Access works.  The suicide column has lots of -1s where the imported spreadsheet had 1.  So:
UPDATE Table3 SET suicide =1 where suicide = -1; 
Nothing changes.  I know SQL well enough to know that this should work.  Nor does replace work.  This field is defined as Yes/No.  Does Access store Yes as -1?  Shouldn't it show TRUE or FALSE?

I think that I have learned my lesson: Microsoft's idea of SQL is not the same as everyone else's.  MySQL?  I am frustrated that Microsoft Access, a purportedly commercial product, does not do SQL the same as everyone else's SQL.  Let me try OpenOffice.

OpenOffice is not a general purpose database.  When you try to create a database, you are given the choice of a business database and a personal database.  There is no apparent way to start from scratch building an arbitrary database.  You start with one of the two existing databases and modify the fields as you need.

I should have realized the MySQL download could not be complete: 15 MB.  That is only an update.  I am now down loading their 471 MB file, which I suspect is the useful product.

1 comment:

Karl said...

Once you have the table in Access, you can use the tools built into Access to fix this.

1: Use a modify data query to find all instances of UNKOWN and replace them with UNKNOWN.


2: Sort the column with UNKOWN in it. All the instances of UNKOWN will group together. At this point, if there aren't too many of them, change one instance to UNKNOWN, copy (ctrl-C) and paste (ctrl-V) down the column.


3) Sort the column in Excel, and I know you can select the entire range of UNKOWN and paste UNKNOWN over the wrong data in one swell foop, then re-import into Access. (I'm not entirely sure you can paste UNKNOWN over a whole range of UNKOWN in an Access table all at once. I suspect you can, but I haven't tried that myself recently.)