only 2 hours ago i thought it would be an easy fix...
why does the first query work and the second one doesn't? the only difference is i am trying to join on the calculated field in the second one (bolded).. is it a no no?
the error message is "The specified field tbl1.[PropIDbx14] could refer to more than one table listed in the FROM clause of your SQL statement".
help,
l
SELECT tbl1.[Prop ID], tbl1.Date, IIf(Right([tbl1]![Prop ID],1)="o",Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID]) AS PropIDbx14
FROM (SELECT tbl1.[Prop ID] FROM tbl1
WHERE (((tbl1.[Inspection Year])=2009) AND ((tbl1.Season)="spring") AND ((tbl1.Round)=2))) AS RoundMarker INNER JOIN (tbl1 INNER JOIN FeatureRatings ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID]) ON RoundMarker.[Prop ID] = tbl1.[Prop ID];
SELECT tbl1.[Prop ID], tbl1.Date, IIf(Right([tbl1]![Prop ID],1)="o",Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID]) AS PropIDbx14
FROM (SELECT tbl1.[Prop ID] FROM tbl1
WHERE (((tbl1.[Inspection Year])=2009) AND ((tbl1.Season)="spring") AND ((tbl1.Round)=2))) AS RoundMarker INNER JOIN (tbl1 INNER JOIN FeatureRatings ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID]) ON RoundMarker.[Prop ID] = tbl1.[PropIDbx14];
darn...
can i trick the compiler, so to say, into believing that the variable exists, somehow?
i know i can do the task the long way - run the make-table query which will create the table with the new variable and then use it - but was wondering if there is more elegant (and easily reproducible) way.
SELECT
tbl1.[Prop ID],
tbl1.Date,
IIf(Right([tbl1]![Prop ID],1)="o",
Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID]) AS PropIDbx14
FROM
(
SELECT
tbl1.[Prop ID]
FROM
tbl1
WHERE
(((tbl1.[Inspection Year])=2009) AND
((tbl1.Season)="spring") AND
((tbl1.Round)=2))
) AS RoundMarker
INNER JOIN
(
tbl1 INNER JOIN FeatureRatings
ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID]
)
ON RoundMarker.[Prop ID] = tbl1.[PropIDbx14];
I don't see you selecting PropIDbx14 from RoundMarker which as far as I can tell, comes from tb1 but the final query only has RoundMarker and FeatureRatings?
Never mind I missed it the first time.
What happen if you do this:
Code:
ON RoundMarker.[Prop ID] = Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID])
I see. Jet isn't quite flexible on joining compared to other RDBMS. Let's try and make it a subquery in the JOIN instead:
Code:
SELECT
t.[Prop ID],
t.Date,
foo,
t.PropIDbx14
FROM
(
SELECT
tbl1.[Prop ID]
FROM
tbl1
WHERE
(((tbl1.[Inspection Year])=2009) AND
((tbl1.Season)="spring") AND
((tbl1.Round)=2))
) AS RoundMarker
INNER JOIN
(
(
SELECT
tbl1.[Prop ID],
tbl1.Date,
IIf(Right([tbl1]![Prop ID],1)="o" AS foo,
Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID]) AS PropIDbx14,
[Inspection ID]
FROM
tbl1
) AS t
INNER JOIN FeatureRatings
ON t.[Inspection ID] = FeatureRatings.[Inspection ID]
)
ON RoundMarker.[Prop ID] = t.[PropIDbx14];
Of course, if this works, you may want to move the criteria you used upon the tbl1 from the other subqueries into that new subqueries so you aren't performing so many subqueries...
I'm not a wizard on subqueries, infact I'm pretty poor, so what is happening here is a bit too much for my old brain, but I can't help feeling that cascading queries might be better.
Well, in fact, cascading queries are likely to be less error-prone than freehanding SQL. The only complaint I have is I end up with too many saved queries objects. For that reason, I usually like to have several QBE windows open, build one subquery in one QBE window, verify it's correct, then move on to other subquery, verify it, then onto the final query where I copy all SQL from each verified subqueries and verify the result, giving me just one saved query, especially if I don't intend to reuse any of subquery anywhere else in which case I'd parcel it out.
As for performance, I could be very wrong, but I believe there is no difference whether we use cascading queries or subqueries, as they're essentially the same thing.... ?
Yes with cascading queries you need a naming convention to link them, its just that in this case you can link cascading queries on calculated fields, which seems to be the problem, he doesn't need to go as far as a temp table.
We agree on not needing a temp table at all, and I'd encourage the OP that if my freehand SQL doesn't work, to try the cascading query to get the SQL down pat first. He can decide to make it one saved query after it all works afterwards.
Whenever there is any doubt, "divide and conquer" works almost every time when dealing with SQL issues. Because the nature of SQL makes problems divisible into elements. Do the elements. Then build the combination out to do what you want. Simplifies everything to the point that even Access and Jet can agree on what to do.
that's the only way i can do it, generally - build subqueries in design view, save them, then plug in their code into final query.. not enough brain power to use SQL code directly..
Banana, i lost you on line 2.. regardless, i used your code and it didn't work.
correct me if i am wrong - your idea is to move the subquery from the FROM clause and into the INNER JOIN clause?
Correct. As Brian pointed out, when we reference a query, the calculated field can be used correctly. Try and build it in design view, save it and reference it in the main query, as Brian suggested.
The query seems to be over complicating the mechanism for acheiving the final result desired. It seems that the goal is to find all the records in tbl1 that meet the following criteria:
[Prop ID] does not have an "o" at the end {INNER JOIN between [PROP ID] and [PropIDbx14]}
And [Inspection Year]=2009 {As specified in a WHERE clause}
And [Season]="spring" {As specified in a WHERE clause}
And [Round]=2 {As specified in a WHERE clause}
And [Inspection ID] Is in the table "FeatureRatings" {INNER JOIN between tbl1.[Inspection ID] and Feature.Ratings.[Inspection ID]}
So ... here is my suggestion with the criteria specified:
Code:
SELECT tbl1.[Prop ID], tbl1.[Date]
FROM tbl1
WHERE tbl1.[Inspection Year]=2009
AND tbl1.Season="spring"
AND tbl1.[Round]=2
AND Right(tbl1.[Prop ID],1) <> "o"
AND tbl1.[Inspection ID] IN (SELECT [Inspection ID] FROM FeatureRatings)
Which could also be expressed with:
Code:
SELECT tbl1.[Prop ID], tbl1.[Date]
FROM tbl1 INNER JOIN FeatureRatings
ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID]
WHERE tbl1.[Inspection Year]=2009
AND tbl1.Season="spring"
AND tbl1.[Round]=2
AND Right(tbl1.[Prop ID],1) <> "o"
I read this sorta quickly, so if I missed something, let me know! ...
i sometimes do this, and i always create one query with a calculated field - then use that as the basis for another query
this is also necessary for some calculations /aggregate queries, as its not always easy to incorporate a calculated field in a further calculated field
it's getting more and more complicated by the minute.. at least for my overheated brain.
i'll have some time tomorrow to test all this, will post the results.. or lack thereof.
datAdrenaline, not sure if your idea will work because the whole problem is that some of the "Prop ID"s have changed - they got an "o" (for "old") at the end (they were moved into another category). now i have a situation where some of the Prop IDs have different names, e.g. F001 and F001o, where latter is not "live" anymore but contains the historical info for F001..
voila, something worked!! not sure how, AND took 2 1/2 hours but.. users can't be choosers..
so, in order -
tbl1 is the table with inspection data, 1 entry per "Inspection ID". "FeatureRatings" is the table that expands on inspection data, it has multiple entries for each "Inspection ID" (which is used to link them). not sure what else to say about them..
what worked is this -
SELECT tbl1.[Prop ID], tbl1.Date, IIf(Right([tbl1]![Prop ID],1)="o",Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID]) AS PropIDbx14
FROM tbl1
INNER JOIN FeatureRatings ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID]
WHERE (((IIf(Right([tbl1]![Prop ID],1)="o",Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID]))
In (SELECT tbl1.[Prop ID] FROM tbl1
WHERE (((tbl1.[Inspection Year])=2009) AND ((tbl1.Season)="spring") AND ((tbl1.Round)=2)))) AND ((tbl1.Date)>=#7/1/2006#))
ORDER BY tbl1.[Prop ID], tbl1.Date DESC;
now, per Namliam, i know that those "In" subqueries are MURDER, although 2 1/2 hours is actually not bad (at least compared to 23 hours i had to wait once).
i may stick to the one above for now, but am open for time-saving suggestions..
Side note ... spaces are not really good characters to use in field names, also, "Date" is a reserved word is not such a good choice for a field name. Consider the "_" in place of a space, and when you have a data value you wish to record, use an expanded name, ie: InspectionDate. I kept the field names the same in order to develop the SQL and have it look the same from a field name perspective.
Why do you need an Inspection Year if you have the Date (although I do notice you are filtering on a Date in 2006, yet an inspection year of 2009 ... interesting ...) ...
Ok ... back to it ... ... So ... I notice that you are not grabbing any fields out of FeatureReadings? ... which leads again to HOW are you using this data? .. In a Report? Bound to a Form? only viewed as a Datasheet? ...
So lets take a look at your SQL statement, mainly for my clarification! .. The SELECT is fairly strait forward ...
Continuing with your FROM clause, you INNER JOIN tbl1 with FeatureReadings on Inspection ID ...
Code:
FROM tbl1 INNER JOIN FeatureRatings ON tbl1.[Inpection ID] = FeatureRating.[Inspection ID]
Which is fairly straight forward to since Inspection ID is UNIQUE (the ONE side of the relationship) in tbl1, and multiple (the MANY side of the relationship) in FeatureReadings, so your rows may look like this (note: included Inspection ID simply for clarity, plus assumed "RecordID" to be a Unique ID in FeatureRatings)
WHERE IIf(Right([tbl1]![Prop ID],1)="o"
,Left([tbl1]![Prop ID]
,Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID]))
In (SELECT tbl1.[Prop ID] FROM tbl1
WHERE tbl1.[Inspection Year]=2009
AND tbl1.Season="spring"
AND tbl1.Round=2
AND tbl1.Date>=#7/1/2006#)
This is saying you want all Constructed [Prop ID]'s (no "o"'s) that are in the set of of [Prop ID]'s (which contains "o") that have a specific Inspection Year, a particular season, a particular Round, and have a Date that is greater than a given value.
So, interpreting, you want your resultant records to include the [Prop ID]'s (both old and new) that match the literal filters for [Inspection Year], [Season], [Round], and only the OLD [Prop ID's] ([Prop ID] & "o"), which HAVE a "Parent" [Prop ID] {ie: F002 from my small snapshot would NOT be desired in the results} ... Sound right? ... so ... with the small snapshot of data I have posted, F002o would NOT be in the resultant records of the query since there is not an F002.
So ... where are the ineffiencies .... Despite what namlaim must have indicated, IN clauses are actually quite efficient (not faster than an INNER JOIN, but not terribly inefficient), so I would not be gun shy on using them, the "NOT IN", is definately operator to avoid, ESPECIALLY if you don't have indexes on the comparitive fields, and having an expression as one of the comparative values is a sure way to knock out any chance of INDEX use. Note that the "NOT" is the killer ... NOT IN, NOT EXISTS are notoriously slow in JET without index use. Also note, that many times an IN clause will be "Optimized" by the query optimizer into an INNER JOIN, you wont see it, but that is what is happening ... for example:
SELECT * FROM tbl1
WHERE tbl1.[Inspection ID] IN (SELECT [Inspection ID] FROM FeatureReadings)
Will probably automatically get "optimized" by the query optimizer/processor as ...
SELECT * FROM tbl1 INNER JOIN FeatureReadings ON tbl1.[Inspection ID] = FeatureReadings.[Inspection ID]
Which now leads me to my suggestion, which may not may a huge difference, but if you don't mind testing, I'd like to here how it turns out!
Code:
SELECT [COLOR=royalblue]vTbl1[/COLOR].*
FROM [COLOR=royalblue](SELECT tbl1.[Prop ID][/COLOR]
[COLOR=royalblue] , tbl1.[Date][/COLOR]
[COLOR=royalblue] , IIf(Right(tbl1.[Prop ID],1)="o"[/COLOR]
[COLOR=royalblue] ,Left(tbl1.[Prop ID][/COLOR]
[COLOR=royalblue] ,Len(tbl1.[Prop ID])-1)[/COLOR]
[COLOR=royalblue] ,tbl1.[Prop ID]) AS PropIDbx14[/COLOR]
[COLOR=royalblue] FROM tbl1 [/COLOR]
[COLOR=royalblue] INNER JOIN FeatureRatings[/COLOR]
[COLOR=royalblue] ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID][/COLOR]
[COLOR=royalblue] WHERE tbl1.[Inspection Year]=2009[/COLOR]
[COLOR=royalblue] AND tbl1.Season = "spring"[/COLOR]
[COLOR=royalblue] AND tbl1.Round = 2[/COLOR]
[COLOR=royalblue] AND tbl1.Date >= #7/1/2006#)) As vTbl1[/COLOR] INNER JOIN tbl1
ON [COLOR=royalblue]vTbl1[/COLOR].PropIDBx4 = tbl1.[Prop ID]
ORDER BY [COLOR=royalblue]vTbl1[/COLOR].[Prop ID], [COLOR=royalblue]vTbl1[/COLOR].[Date] DESC
Going back to your original post, this is how you can "trick" the query processor into not evaluating your IIf() twice ... I made a source (which is aliased as vTbl1 - the "v" reads "virtual" in my brain) a SELECT statement with all the literal filters, as well as the IIf() expression. I then use the virtual (aka: aliased) source to INNER JOIN as the method for including only the [Prod ID]'s that have a "parent" or are a "parent", per se, in and of themselves.
Note that the technique applied is similar to the suggestions that state to create a Query with the IIf() expression {basically the entire SELECT statement in blue}, then use the expression/filtering query object as a source in the query you are developing.
Please let me know if my statement is any faster ..... or even if it works! ... ...
your query did not work, sorry. it gave me a 'syntax error in the FROM clause'. not sure why, it's a bit too complex for me..
i am just exporting the resulting data into Excel for further manipulations. crudely copy-pasting..
i am stuck with the names because i am an end user of the database. i am going to mention it to the database people, but i am doubtful that they'll act on my suggestions, as things run somehow (in fact i think i did already a while back). what could be the potential problem with spaces and reserved words in field names? maybe if i scare them bad enough, they'll do something about it..
re: filtering on a date - let me repost the query that worked, but with the date filter moved to the first WHERE clause (produces same results, but makes it easier to see what's going on here, at least to me) -
SELECT tbl1.[Prop ID], tbl1.Date, IIf(Right([tbl1]![Prop ID],1)="o",Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID]) AS PropIDbx14
FROM tbl1 INNER JOIN FeatureRatings ON tbl1.[Inspection ID] = FeatureRatings.[Inspection ID]
WHERE (((tbl1.Date)>=#7/1/2006#) AND ((IIf(Right([tbl1]![Prop ID],1)="o",Left([tbl1]![Prop ID],Len([tbl1]![Prop ID])-1),[tbl1]![Prop ID])) In (SELECT tbl1.[Prop ID] FROM tbl1
WHERE (((tbl1.[Inspection Year])=2009) AND ((tbl1.Season)="spring") AND ((tbl1.Round)=2)))))
ORDER BY tbl1.[Prop ID], tbl1.Date DESC;
now, the second WHERE clause only pics the subset of Prop ID's in question (2009spring2). the whole goal is to see the historical data for this subset, but i don't need it to go too far back, hence i filter by date starting at 7/1/06. you may ignore the date filter for the purpose of this exercise, in fact it slipped in here by mistake, sorry..
i WILL grab variables from FeatureRating eventually, i just wanted to simplify the query to bare minimum to address the problem i am having..
...
