Re: Problem with Dual subselect

Vincent, Thanks for all the work on this problem.

I saw your SQL statement and I'm just not up to that level of creating SQL
statements. I realized that one of the problems with my statement was that I
have to select both PROJECT and STATUS_DATE when I want to return the one
record where B.S_IND = 'Y". So I've constructed a UNION QUERY as follows.
I've included Project and Status_Date in the select to assist with viewing
the results.

SELECT PROJECT, STATUS_DATE, Sum([COST]) AS SumCosts, Sum([SAVINGS]) AS
SumSavings
FROM TABLEA AS A
WHERE EXISTS
(SELECT B.PROJECT, B.STATUS_DATE FROM [TABLEB] As B
WHERE A.PROJECT = B.PROJECT
AND A.STATUS_DATE = B.STATUS_DATE
AND B.S_IND = 'Y')
GROUP BY A.PROJECT, A.STATUS_DATE
UNION SELECT PROJECT, STATUS_DATE, Sum([COST]) AS SumCosts, Sum([SAVINGS]) AS
SumSavings
FROM TABLEA AS A
WHERE (((A.STATUS_DATE) In
(SELECT MAX(B.STATUS_DATE)
FROM [TABLEB] As B
WHERE B.[PROJECT] = A.PROJECT)))
GROUP BY A.PROJECT, A.STATUS_DATE;

This statement returns the correct results, but needs more testing.

Thanks for all of your help and any comments you have on my revised statement
are greatly appreciated.

Vincent Johns wrote:
>Robert,
>
>I revised your Tables a bit, adding a primary key (Autonumber data type)
>to each one to simplify the Queries slightly. It's not necessary to do
>that, but my suggestion is to at least look at what I did so you can
>decide if it makes sense to do the same thing in your own Tables.
>
>Adding the primary keys is simple; just define a field with Autonumber
>type in each Table.
>
>Adding the foreign key to [TABLEA] is slightly trickier; it's used to
>link each record in [TABLEA] to its corresponding record in [TABLEB].
>To add the [TableB_ID] foreign-key field to [TABLEA], open [TABLEA] in
>Table Design View and add a field called [TableB_ID] with Data Type =
>Number (long integer) and no index. (It will look like an Autonumber
>but will behave differently -- it's not unique, and the values will be
>copied from [TABLEB] instead of being generated automatically.) At this
>point, it will have no values; to set the values, run the following
>Update Query:
>
>[QU_TableB_ID_Setup] SQL:
> UPDATE TABLEA INNER JOIN TABLEB
> ON (TABLEA.Project = TABLEB.Project)
> AND (TABLEA.[Status Date] = TABLEB.[Status Date])
> SET TABLEA.TableB_ID = [TABLEB]![TableB_ID];
>
>Then, in the Relationships window, delete the existing relationship
>between [TABLEB] and [TABLEA] and set a new one linking
>[TABLEB].[TableB_ID] and [TABLEA].[TableB_ID], setting Referential
>Integrity on this relationship.
>
>Having done this, and no longer needing the duplicate fields
>[TABLEA].[Project] and [TABLEA].[Status Date], I deleted them from
>[TABLEA]. At this point, the Tables contained the following records:
>
>[TABLEB] Table Data*** View:
> TableB_ID Project Status Date S_IND
> ----------- -------- ------------- -----
> -1869411747 A 4/29/2004
> -1679588807 B 11/23/2003 Y
> -1670788141 A 12/11/2005
> -1585945064 B 2/11/2004
> -1499531505 D 10/31/2004
> -727128156 E 7/25/2005
> -12772858 C 1/25/2003
> 191389786 A 6/27/2005
> 1136072244 B 1/31/2004
>
>[TABLEA] Table Data*** View:
>
> TableA_ID TableB_ID Cost Savings
> ----------- ------------ ------------- ------------
> -2008314815 -1679588807 $2,250,000.00 $90,000.00
> -1244319657 -12772858 $4,000,000.00 $200,000.00
> -205746725 1136072244 $2,250,000.00 $100,000.00
> 581413196 -1499531505 $5,000,000.00 $250,000.00
> 1283352731 191389786 $1,000,000.00 $50,000.00
> 1619660142 -1670788141 $3,000,000.00 $150,000.00
> 1676911936 -1585945064 $2,000,000.00 $100,000.00
>
>If you wish, you can then set a Lookup Property on the new
>[TABLEA].[TableB_ID] foreign key, so that it will have a meaningful
>appearance. (Raw key values are often, such as in my example here, not
>meaningful to human beings. A Lookup property on the foreign-key field
>allows you to read the records more easily, and I normally set it for
>each foreign key I use, but some people prefer not to do so. In case
>you wish to use a Lookup, I'll describe what I did to set it up. In
>case you DON'T wish to use it, just skip to the bottom -- the Queries
>will work just as well without the Lookup.)
>
>For the Lookup property, I defined a Query to summarize the data in each
>record in [TABLEB], including ">" if S_IND = "Y", the project name, and
>the status date. There's nothing magic about these choices, but the
>result should be meaningful to you, unique within your Table, and fairly
>short. I sorted them by [S_IND], project name, and date.
>
>[QL_TableB_Projects] SQL:
> SELECT TABLEB.TableB_ID,
> IIf([TABLEB]![S_IND]="Y",">"," ")
> & [TABLEB]![Project] & " "
> & [TABLEB]![Status Date] AS Proj
> FROM TABLEB
> ORDER BY TABLEB.S_IND DESC ,
> TABLEB.Project, TABLEB.[Status Date] DESC;
>
>[QL_TableB_Projects] Query Data*** View:
> TableB_ID Proj
> -------------- --------------
> -1679588807 >B 11/23/2003
> -1670788141 A 12/11/2005
> 191389786 A 6/27/2005
> -1869411747 A 4/29/2004
> -1585945064 B 2/11/2004
> 1136072244 B 1/31/2004
> -12772858 C 1/25/2003
> -1499531505 D 10/31/2004
> -727128156 E 7/25/2005
>
>To apply this, I opened [TABLEA] in Table Design View, selected the
>[TableB_ID] field, opened its Lookup tab, and set the properties as follows:
>
> Display Control = List Box (instead of Text Box)
> Row Source = QL_TableB_Projects
> Column Count = 2
> Column Widths = 0;1
>
>I then opened [TABLEA] in Table Data*** View, selected the [TableA_ID]
>column, and used Format --> Hide Columns to hide it, since I had no need
>to see those values. Actually, as it turns out, I had no need for the
>entire [TABLEA].[TableA_ID] field, since no other Table makes reference
>to it, so I could have deleted it, but leaving it in there does little
>harm. I just don't want to have to look at it.
>
>The result of setting Lookup property on the foreign key [TableB_ID] and
>hiding the primary key [TableA_ID] was a prettier, more legible [TABLEA].
>
>[TABLEA] Table Data*** View (with Lookup):
> TableB_ID Cost Savings
> -------------- --------------- ------------
> A 6/27/2005 $1,000,000.00 $50,000.00
> B 1/31/2004 $2,250,000.00 $100,000.00
> B 2/11/2004 $2,000,000.00 $100,000.00
> C 1/25/2003 $4,000,000.00 $200,000.00
> D 10/31/2004 $5,000,000.00 $250,000.00
> E 7/25/2005 $3,000,000.00 $150,000.00
> >B 11/23/2003 $2,250,000.00 $90,000.00
>
>Please bear in mind that the values stored in the [TableB_ID] field here
>are still those key values you saw earlier, but they are displayed here
>in a more meaningful format. (However, some people find this confusing
>and recommend against using Lookup properties largely for that reason.)
>
>If you use the Form Wizard to generate a Form for this Table, the Lookup
>property will be copied to the List Box or Combo Box on that Form that
>matches this field.
>
>===
>
>OK. Now we set up a revised Query (using the revised [TABLEB]
>and[TABLEA], but I expect you can easily edit it to use your original
>Table structures).
>
>When I ran (my version of) your original Query on your Tables, I got the
>following results, and I assume this is what you saw, too (extra record
>for Project B):
>
>[Q_Old_Orig_Select] Query Data*** View:
> Project Status Date Cost Savings
> ------- ----------- ------------- -----------
> A 12/11/2005 $3,000,000.00 $150,000.00
> B 2/11/2004 $2,000,000.00 $100,000.00
> B 1/31/2004 $2,250,000.00 $100,000.00
> B 11/23/2003 $2,250,000.00 $90,000.00
> C 1/25/2003 $4,000,000.00 $200,000.00
> D 10/31/2004 $5,000,000.00 $250,000.00
>
>The Query I defined -- well, actually, I set it up in two steps to make
>it easier to debug -- looks like this. First step lists the records,
>second step lists the totals.
>
>[Q_New_Select] SQL:
> SELECT B.Project, B.[Status Date],
> A.Cost, A.Savings, B.S_IND
> FROM (TABLEB AS B INNER JOIN TABLEB AS B2
> ON B.Project = B2.Project)
> INNER JOIN TABLEA AS A
> ON B.TableB_ID = A.TableB_ID
> GROUP BY B.Project, B.[Status Date],
> A.Cost, A.Savings, B.S_IND
> HAVING (((B.[Status Date])=Max([B2].[Status Date])))
> OR (((B.S_IND)="Y"))
> ORDER BY B.Project, B.[Status Date] DESC;
>
>[Q_New_Select] Query Data*** View:
>
> Project Status Date Cost Savings S_IND
> ------- ----------- ------------- ------------ -----
> B 2/11/2004 $2,000,000.00 $100,000.00
> B 11/23/2003 $2,250,000.00 $90,000.00 Y
> C 1/25/2003 $4,000,000.00 $200,000.00
> D 10/31/2004 $5,000,000.00 $250,000.00
> E 7/25/2005 $3,000,000.00 $150,000.00
>
>The last step is pretty simple -- just add them up.
>
>[Q_New_Select_Sums] SQL:
>
> SELECT Sum(Q_New_Select.Cost) AS SumCosts,
> Sum(Q_New_Select.Savings) AS SumSavings
> FROM Q_New_Select;
>
>[Q_New_Select_Sums] Query Data*** View:
>
> SumCosts SumSavings
> -------------- -----------
> $16,250,000.00 $790,000.00
>
> -- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
> Please feel free to quote anything I say here.
>
>> You are right, that set of pseudo data does work correctly (I used my actual
>> data; I abbreviated a lot of things in what I posted).
>[quoted text clipped - 28 lines]
>>>
>>>>>>Any help is appreciated.

--
Message posted via http://www.accessmonster.com
.