By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Queries with identical SQL code behave differently (one is expandable)

P: n/a
I've got two queries; one is a modification of an older query; the
other I created from scratch.

The old one is about 5 copies down the road from something that has
been expandable when I'm in View mode--it showed the Points of Contact
for records whose Begin Date was in a form-selected (or manually
inputted) year. That used to be all it did. Then copied it, took out
the year criterion, and changed "POC" to show the Director field
instead.

I forgot why my original POC form was expandable to show the records
under that POC's name. I still don't remember. I've looked in the
original query's SQL code, in Relationships, etc. Nothing there, as
far as I can tell! It's a mystery why I get these handy plus signs in
the original and all its Copy-Paste derived queries.

Anyway, recently I copied that Directors "group by" query, added a
"group by POC" and a "count of POC" and an "after today" to the end
date, and it still expands the Directors group when I click on the + at
the left.

When I created the exact same thing in design view (I was taking
screenshots, making a tutorial for my successors) I suddenly realized
it wasn't showing any plusses.

I've got identical SQL in these things, no references to the
"expandable" one in Relationships or any other tables/queries/forms,
and still they behave differently than each other.

Any ideas as to why I can display the records (a subquery, is it?) for
the first-listed Group By field on one query but not the other??
Here's my SQL code for the handmade one:

SELECT tblUltimateMasterTrackingLog.fldSalesDirectorLast,
tblUltimateMasterTrackingLog.fldPOC,
Count(tblUltimateMasterTrackingLog.fldPOC) AS CountOffldPOC
FROM tblUltimateMasterTrackingLog
WHERE (((tblUltimateMasterTrackingLog.fldEndDate)>#1/3/2006#))
GROUP BY tblUltimateMasterTrackingLog.fldSalesDirectorLast,
tblUltimateMasterTrackingLog.fldPOC
ORDER BY tblUltimateMasterTrackingLog.fldSalesDirectorLast,
tblUltimateMasterTrackingLog.fldPOC;
And here's the SQL code for the expandable "copy-paste-change" one:

SELECT tblUltimateMasterTrackingLog.fldSalesDirectorLast,
tblUltimateMasterTrackingLog.fldPOC,
Count(tblUltimateMasterTrackingLog.fldPOC) AS CountOffldPOC
FROM tblUltimateMasterTrackingLog
WHERE (((tblUltimateMasterTrackingLog.fldEndDate)>#1/3/2006#))
GROUP BY tblUltimateMasterTrackingLog.fldSalesDirectorLast,
tblUltimateMasterTrackingLog.fldPOC
ORDER BY tblUltimateMasterTrackingLog.fldSalesDirectorLast,
tblUltimateMasterTrackingLog.fldPOC;

-KitKat

Jan 3 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Red
I can't wait to get back to an assignment that has Access 2000+....
I've been stuck in access 97 for so long now, I'm having a hard time
remember the sweet little things like expandable queries...
Anyhow, check your relationships.. that may be your problem :D

~Red

Jan 3 '06 #2

P: n/a
It sounds like you have a one-to-many relationship defined. Go to Tools
Relationships. If all you get is a grey screen right-click and choose "Show All". If you have any relationships defined they should show up here.


Jan 3 '06 #3

P: n/a
Whoa, looks like the other half of my post is being read as a quote.
Your relationships can be checked (as Red indicated as well), which can
be done by going to Tools - Relationships. Slightly more info available
if you click on the 'Show quoted text' bit of my last reply. Note that
while I'm too lazy to simply copy 'n' paste those ten or so words here,
I am freely writing two sentences to describe them.

Jan 3 '06 #4

P: n/a
Right. As I said, I already checked in Relationships.
One update, though. It turns out the subqueries expand to show all
records with the same "POC" as the line I expanded, not all records
with the same "Whatever is Grouped By first" as the line I expanded
(which in this case would be "Director").

Jan 3 '06 #5

P: n/a
I'm not stating that I don't think you checked the Relationships
window, but the plus sign you describe and the behavior it exhibits
usually occurs when there is a one-to-many relationship defined for the
table. Is the table underlying your "expandable" query referenced in
the Relationships window? If it's not, use Show Table to add it. Do any
relationships appear now?

Jan 3 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.