473,721 Members | 1,818 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

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 tblUltimateMast erTrackingLog.f ldSalesDirector Last,
tblUltimateMast erTrackingLog.f ldPOC,
Count(tblUltima teMasterTrackin gLog.fldPOC) AS CountOffldPOC
FROM tblUltimateMast erTrackingLog
WHERE (((tblUltimateM asterTrackingLo g.fldEndDate)># 1/3/2006#))
GROUP BY tblUltimateMast erTrackingLog.f ldSalesDirector Last,
tblUltimateMast erTrackingLog.f ldPOC
ORDER BY tblUltimateMast erTrackingLog.f ldSalesDirector Last,
tblUltimateMast erTrackingLog.f ldPOC;
And here's the SQL code for the expandable "copy-paste-change" one:

SELECT tblUltimateMast erTrackingLog.f ldSalesDirector Last,
tblUltimateMast erTrackingLog.f ldPOC,
Count(tblUltima teMasterTrackin gLog.fldPOC) AS CountOffldPOC
FROM tblUltimateMast erTrackingLog
WHERE (((tblUltimateM asterTrackingLo g.fldEndDate)># 1/3/2006#))
GROUP BY tblUltimateMast erTrackingLog.f ldSalesDirector Last,
tblUltimateMast erTrackingLog.f ldPOC
ORDER BY tblUltimateMast erTrackingLog.f ldSalesDirector Last,
tblUltimateMast erTrackingLog.f ldPOC;


Jan 3 '06 #1
5 2357
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


Jan 3 '06 #2
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Mike N. | last post by:
Hello: I am new to T-SQL programing, and relativly new to SQL statements in general, although I have a good understanding of database theory. I'm a little confused as to the fundamental differences between a view and a query, or rather, when it is more appropriate to use one vs. the other. It seems to me that most select queries can be implemented as views, and I can't see the downside to doing so.
by: MikeT | last post by:
I have a page that produces little thumbnails of the 3D models it finds in a specified directory (and iterates down through any sub directories). It basically scans each directory for 3D Studio Max files using the filesystemobject and writes an activeX component called iDrop for each file so it can be displayed on the page (and drag-dropped straight into Max). If it happens to find a similarly named XML file in the directory, it loads...
by: alanstew | last post by:
With the body tag calling out 'window onload', a function with a 'window.open' fails at the 'window.open' line. If I cut out the body tag, the function executes as normal. At first I thought it was the entire function failing, but I tested with alerts and found that it was only the 'window.open' that fails to execute. The function is being called by a link, and I suspected some problem with the body alink/vlink but after cutting that out...
by: PC Datasheet | last post by:
If a subroutine runs twelve action queries, will they run one-at-a-time where one finishes before the next one starts or will one or more run simultaneously? If they don't run one-at-a-time, how can they be made to do it? Thanks! Steve
by: napi | last post by:
I think you would agree with me that a C compiler that directly produces Java Byte Code to be run on any JVM is something that is missing to software programmers so far. With such a tool one could stay with C and still be able to produce Java byte code for platform independent apps. Also, old programs (with some tweaking) could be re-compiled and ported to the JVM. We have been developing such a tool over the last 2 years and currently...
by: Joe Fallon | last post by:
I have a C# class that works correctly. I translated it to VB and now it runs differently. The C# class evaluates the Public properties and then executes MyBase.New. So default values are set first and then MyBase.New reads in new values (if they exist.) The VB code does it in reverse. It excutes MyBase.New and then overwrites the new values when it sets the defaults.
by: loosecannon_1 | last post by:
Hello everyone, I am hoping someone can help me with this problem. I will say up front that I am not a SQL Server DBA, I am a developer. I have an application that sends about 25 simultaneous queries to a SQL Server 2000 Standard Edition SP4 running on Windows 2000 Server with 2.5 GB of memory. About 11 of these queries are over views (all over the same table) and these queries are all done from JDBC but I am not sure that matters. ...
by: mikes | last post by:
I have 2 separate queries, which effectively are the same except they draw data from separate tables. Both tables are (design-wise) identical, only the data is different. for each query, there are 2 tables with a standard LEFT JOIN. One field of the query is calculated, looking for a NULL in one table, and then using a field from the second table in that case. One query looks like this: PARAMETERS Text ( 255 ); SELECT...
by: Ian Roddis | last post by:
Hello, I want to embed SQL type queries within an XML data record. The XML looks something like this: <DISPLAYPAGE> <FIELD NAME="SERVER" TYPE="DROPDOWN"> <OPTION>1<OPTION> <OPTION>2<OPTION> <OPTION>3<OPTION> </FIELD>
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.