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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet 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 Datasheet View:
SumCosts SumSavings
-------------- -----------
$16,250,000.00 $790,000.00
-- Vincent Johns <
[email protected]>
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]
