Log in

No account? Create an account
Database stuff - Dave's Ramblings [entries|archive|friends|userinfo]

[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

Database stuff [Nov. 6th, 2012|10:31 pm]
[Current Location |Invercargill]
[mood |accomplishedaccomplished]

I have been performing grotesque experiments on cute, innocent little databases lately and it looks like I've finally figured out a workable structure for one of my projects... but while doing so I've also noticed that Microsoft Access finally does something I've been wanting it to do for... probably just about all the time I've been using it.

The other night I changed the name of a couple of tables in a little database I'm going to split up and experiment on, and expected to have to, as I have always had to in the past, go into all the queries and reports which use data from that table and change each of the query and report fields from the now non-existent tables to tables that actually exist.

Except I didn't have to. Access had actually realised that the table name had changed and had done all the moving of links automatically.

This may sound like something that it should have done in any case, but all older versions of Access I've used wouldn't do that; they'd lose the links and need to have them all manually reinstated.

It is quite a red-letter event for something involving a Microsoft program to actually work. Maybe one of these days Access can actually pass itself off as being relational and I'll be able to start ignoring the database snobs who keep on telling me that I Shouldn't Be Using Access But Instead I Should Be Using One Of Those Big Expensive Database Programs That Will Take Me Ten Years Just To Learn The Language For.

[User Picture]From: mthomas3
2012-11-06 07:47 pm (UTC)
Piffle. Two years to learn the language, max. :>

I crash my students through the core chunk of SQL in 2 weeks, but they've really just gotten started, at that point.
(Reply) (Thread)
[User Picture]From: southerndave
2012-11-10 06:22 am (UTC)
Two years for a student who knows about computers and has time to learn the language, I'll grant you. Still ten years for me, though...
(Reply) (Parent) (Thread)
[User Picture]From: southerndave
2012-11-21 09:49 am (UTC)
And the experiments have started. NOT IN THE SLIGHTEST helped by Office 2010 'help', which basically said to me: "Do it yourself. Here is how to copy and paste the SQL into a new query. Best of British luck to you." ... At least I now know what one of the things I am trying to do is called (a "union query") and that its language seems to be based on fried egg algebra (sets, unions, intersections, etc.) which I do at least vaguely understand from intermediate school maths, and not those left right, left right, left right out, joins in SQL which bamboozle me utterly.
(Reply) (Parent) (Thread)
[User Picture]From: mthomas3
2012-11-22 04:21 am (UTC)
snicker, snicker, snicker.

Yes, 'union' is one of the SQL operators based on the underlying relational algebra. Union, intersection, select, project, subtract/minus/except, etc. Fun stuff.

Once relational algebra became SQL, database people went bonkers and started adding stuff you can't do with the algebra alone. 'Left outer' and 'right outer' joins always send my students' heads spinning. (Though some students working on an independent database project did figure them out a semester or two after their original database class ended. Good boys!)

I can see why Office would punt. Textbooks and textbooks full of SQL details out there; too much to put in their help menus.
(Reply) (Parent) (Thread)
[User Picture]From: southerndave
2012-12-09 08:32 am (UTC)
Well, the union query finally got programmed... and mostly did what I wanted it to do, with one glaring exception (that being the editability of the data). While growling at any poor bastard I could track down in the office who actually knew anything about databases it was explained to me in Words Of As Few Syllables As Possible that union queries are specifically designed not to be editable. Which means I'm going to have to completely rethink they way I was planning on doing things yet again.

If you are in need of something to laugh at I could .zip up the whole fiasco and email it to you so you can see how hopeless my programming actually is.

Are the a.b.m-l crowd organising a card exchange again this year? I've still got a bunch of the regulars on my card list and am hoping there hasn't been too much of the moving of houses in the last year or so.
(Reply) (Parent) (Thread)
[User Picture]From: mthomas3
2012-12-10 03:13 am (UTC)
Interesting. It wouldn't have occurred to me that union query results would be un-editable. More precisely, I'd think either "all query results are editable" or "no query results are editable." Making some query results not editable and some editable sounds... like a great way to send people hunting around the office for experienced DB administrators.

Errr. Thanks for the offer of the zip file, but I have plenty of other people's source code to read already. I'm behind on my grading. (Unlike some of my students, at least you can spell.)

I kinda fell behind and didn't get around to organizing the card exchange. I'm just going to send cards to the old addresses. (Whenever, that is, I get around to sending the cards.) I do know Kat & Jesse H, who are very prompt sending their holiday cards, have moved; I could send you their new address, as long as your old email address is still valid.
(Reply) (Parent) (Thread)
[User Picture]From: southerndave
2012-12-11 08:23 am (UTC)
The story as it was told to me was that it's a 'feature' of the way union queries work (possibly only in Access) that the program doesn't know which of the multiple tables each row of data comes from, so it can't go back and edit it in the original table. Sounds annoying but I'll see if I can adjust what I was planning on doing so that it still vaguely works (e.g. using the union query just as a quick key off to one side while I work on multiple tables).

... I am one of the database administrators at work. This fact either amuses or terrifies people who know me.

Not surprised... I try to avoid other stuff that feels too much like work as often as I can, too.

I've got the first batch of cards off and will be sending another batch in the next week or so (as the first cards I sent were on Friday 7th, and the overseas mails closed on Monday 3rd, they probably won't show up until the New Year... but that's the way things go). If it's OK with Kat and Jesse you can pass me their new address (probably better check with them though, the last I saw of Kat she was taking offence at me saying something that was mildly, I thought, critical of one of the politicians up in your hemisphere).

The address I've used for the last few years is valid... I've just had a quick check and at least one of your emails to me has survived the mass email failure of the start of this year and it's gone to an address that's still current (and will be for the foreseeable future).
(Reply) (Parent) (Thread)