Access SQL help

[MrRobin]

Hi,

I'm having a fit with the way Access is rewriting my SQL once I have saved a query and then reopen it...

I am quering a subtable a bit like this:

Select mytable.name, mytable.cost
From (
  Select name, cost, date, store
  From sometable
  Where cost > 100
) mytable

Which works perfectly as I need it to but then when I save, close and reopen, Access has rewritten as:

Select mytable.name, mytable.cost
From [
  Select name, cost, date, store
  From somestable
  Where cost > 100
]. as mytable

I.e. it has replaced my curved brackets ( ) with square ones followed by a full stop [ ]. Now the code won't work and I have to change it back!!

Am I doing something wrong? What do I do to turn off automatic query editing in Access?

Thanks very much.

Robin

 

[BrilloPad]

Am I doing something wrong?

yes - using Access. try using a database

 

[MrRobin]

Haha yes very good, I would if the client had anything else.

Solved it thought... had some more [ ] square brackets in the subquery/derived table when naming fields that were screwing things up.

 

[lilelvis2000]

Nothing wrong on your part. that's just what Access does. It is a pain when you need to edit it again. The "best" way to do this is not use a subquery but create a query and use it within another one. You can dynamically change this query to your liking without messing things up.

I do the same with my forms which use a passthrough query which is dynamically re-written when combo boxes on the form are changed.

 

[TheRightStuff]

yes - using Access. try using a database

It is a database and a pretty good one at that. good for small apps.
You some SQL server / oracle monkey?

 

[MrRobin]

Cheers lilelvis2000, I ended up using 2 separate queries/views, one that references the other one, in the end as you suggested.

 

[Cowboy Bob]

It is a database

It may or may not be good as a data store for small apps, but database it isn't. It's a form designer built on top of a pseudo-SQL implementation that reads from a flat file.

 

[xoggoth]

Aaaaah!!! The old Access is/isn't a real database thing. This is not so much a technical discussion as a religious one.

Sticking with the purely practical. For smallish apps, as has been said, it would be perfectly adequate if it was not SO FECKIN' FLAKY! Do other databases (or "databases") get into unrecoverable totally buggered-up modes quite so easily? I imagine not.

PS The way it edits SQL really pissed me off too. If you had a query that used result of another query and then changed output name of that, it never just let you get round to editing that as necessary, it had to stick in loads of [Never 'eard of it] AS etc. PISS off ACCESS!, who is programming this you or me? I think some applications just need a damn good slapping.

 

[BrilloPad]

It is a database and a pretty good one at that. good for small apps.
You some SQL server / oracle monkey?

good for small apps with 1 user. I once worked on a multi user system implemented on access - what a mare.

 

[Cowboy Bob]

Sticking with the purely practical. For smallish apps, as has been said, it would be perfectly adequate if it was not SO FECKIN' FLAKY! Do other databases (or "databases") get into unrecoverable totally buggered-up modes quite so easily? I imagine not.

That's because it fails the fundamental test of what a real database is (religious definition or not) and that's the ACID test.