Access SQL help

[lilelvis2000]

I've only recently started on Access - because my clients all liked and understood Access and think its the best thing since sliced bread. The fact they can make their own ad-hoc queries is a bonus to them.

If I mention the development effort and cost to go vb or .net with a SQL Server/Oracle backend their eyes rollup and they pass out.

Doesn't matter whether we think its a proper DBMS or not, clients want it done in Access and I ain't gonna argue with them.

[kramer]

i've spent most my career upgrading Access apps to SQL/VB systems...

just thought i'd mention that

[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.

[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.

[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.

[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.

[MrRobin]

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

[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?

[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.

[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.

[BrilloPad]

Am I doing something wrong?

yes - using Access. try using a database

[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