Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values (a number reflecting number of hours
of time zone difference) to ALL timestamp-related records read in the
'main' part of the query, i.e.:
* Get a series of records from table A, inner joining on B & C to get
some extra data.
* Read the time zone difference factor (call it 'tzd') from
'Locations' ONCE.
* Apply 'tzd' to all timestamp-related fields retrieved from A, B and
C - i.e. A.StartTime = A.StartTime + tzd
Now, I believe the logical way to do this is to obtain the tzd factor
from 'Locations' as a subquery after my FROM clause, therefore
assuring that this operation only occurs once and does not
unnecessarily clog up my query.
So, I have ended up with a query like this - which works perfectly
fine I might add:
SELECT a.v1, a.v2, DateAdd("h", Locations.[Time Difference], a.[t1])
AS v3, b.v1...
FROM a
INNER JOIN (b INNER JOIN c ON ...) ...,
(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
[Location Name] = [Enter the name of your current location]) AS MyLoc
WHERE ...
Note the subquery occurs during the FROM clause!
I can then run and save this query to my hearts content, and use it in
whatever reports I choose. All is fine.
Now, THE PROBLEM IS...
If I come to edit (add/remove fields to/from) this query again in
Access Query Design or raw SQL view, and then go to save the query /
update the Record Source property, I get this error:
"Invalid bracketing of name 'SELECT [Location Name'."
Investigations reveal that the error is caused because Access decides
to do the bloody "smart" thing and reformat my query in it's own
image, so that the part of the query which once read:
FROM a
INNER JOIN (b INNER JOIN c ON ...) ...,
(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
[Location Name] = [Enter the name of your current location]) AS MyLoc
Now reads...
FROM [SELECT [Location Name], [Time Difference] FROM Locations ml
WHERE [Location Name] = [Enter the name of your current location]]. AS
myLoc, a INNER JOIN (b INNER JOIN c ON ...) ON ...
Digging the newsgroups has revealed that Access does not like
occurences of double brackets ('[[' or ']]') - yet the stupid thing
still decides to do it!
Now, I can solve this problem by simply replacing the '[SELECT' with
'(SELECT' and ']]. AS myLoc' with ']) AS myLoc', however,
AND HERE IS MY QUESTION:
Surely there has to be a better way around this? Is this solvable, or
am I stuck with this workaround?
I really do not want to have to rewrite this query because:
* This type of logic/structure is used in 30+ different queroes.
* I cannot see how else I can run a subquery to get this information,
and don't see how a LEFT JOIN or other JOIN type can help me obtain
the data (tzd) that I need without 'affecting' the data I return!
And just to finish off... can I please go back to SQL Server now?
I've learnt my lesson!
Apologies for waffling on, if ANYONE can help, I will be eternally
greatful.
Regards,
Andy McNab
