Subquery doesn't return GUIDs

[Peter Baughman]

BACKGROUND:
I'm using microsoft access 2003 Version 11.8204.8221 SP3

I have a database that contains a table "Parts" and a table "Tests"
Parts contains the following columns: Name (text) and PartId (Replication Id)
Tests contains the following columns: PartId (ReplicationId) TestId
(ReplicationId )TestTime (Date/Time) and Data (double)

Tests has a many to one relationship with Parts on Tests.PartId and
Parts.PartId.

THE PROBLEM:
I'm trying to write a query that will return a part name and the test Id of
the most recent test. The query that i've written is:
SELECT Parts.Name, (SELECT TOP 1 (Tests.TestId) FROM Tests WHERE
Tests.PartId = Parts.PartId ORDER BY TestTime DESC) AS Expr1
FROM Parts;

This query returns the Part Names in the first column, but empty cells in
the second column. I expect it to return the Part Name in the first column
and the GUID of the latest test in the second column.

ADDITIONAL INFORMATION:
The query:
SELECT Parts.NAME, (SELECT TOP 1 (Tests.Data) FROM Tests WHERE Tests.PartId
= Parts.PartId ORDER BY TestTime DESC) AS Expr1
FROM Parts;

Returns the Part names in the first column and the data from the most recent
test in the second column just like I would expect it to. The only thing
that has changed is the arguement to TOP 1 in the subquery.

Finally, the query:
SELECT Parts.Name, (SELECT COUNT(Tests.TestId) FROM Tests WHERE Tests.PartId
= Parts.PartId) AS Expr1
FROM Parts;

Returns the name of each part in the first column and the number of tests
that were run on that part in the second column just like I would expect it
to.
This indicates to me that inside the subquery, Tests.TestId works, however
for some reason the subquery cannot return this replication ID and have it
display properly in the first column

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Replication IDs are not considered data and won't show in a query. The
COUNT(<replication ID>) is equivalent to COUNT(*), i.e., it only counts
the rows the query will "normally" return.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSRoaoYechKqOuFEgEQLMxwCglwnEXYVzYgKo+OUF8bvdQ9+0/R8AoKAw
58JuwDw4fkVY5k3q5ulNSnyV
=c7eo
-----END PGP SIGNATURE-----

 

[Peter Baughman]

So is there a way to get this query

SELECT Parts.Name, Tests.TestTime
FROM Parts INNER JOIN Tests ON Parts.PartId=Tests.PartId
WHERE Tests.TestId = (SELECT TOP 1 T1.TestId FROM Tests AS T1 INNER JOIN
Parts as P1 ON T1.PartId = P1.Partid WHERE P1.PartId = Parts.PartId ORDER BY
T1.TestTime);

to return rows and still have TestId be a globally unique identifier or will
I have to make TestId some other less unique data type? I could have the
subquery return the TestTime but I'm not thrilled about identifying an entry
by a potentially non unique value.