Unique Query in not working correctly ... too complex?

I have 4 linked tables in Access 2007. 3 fo the tables contain a notes
field (SiteNotes, GroupNotes and EquipmentNotes. I want to create a
report that lists all notes. When I created an associated query I
cannot get it to be unique (it list multiple records that are identical.

The SQL is:
SELECT DISTINCT LocationTBL.LocationNumber, SiteTBL.SiteNumber,
SiteTBL.SiteNotes, CategoryGroupingTBL.GroupNotes,
EquipmentTBL.EquipmentNotes
FROM (LocationTBL INNER JOIN SiteTBL ON LocationTBL.ID =
SiteTBL.LocationID) INNER JOIN (CategoryGroupingTBL INNER JOIN
EquipmentTBL ON CategoryGroupingTBL.ID = EquipmentTBL.GroupID) ON
SiteTBL.ID = CategoryGroupingTBL.SiteID

WHERE (((EquipmentTBL.EquipmentNotes)"" And
(EquipmentTBL.EquipmentNotes) Is Not Null)) OR
(((CategoryGroupingTBL.GroupNotes)"" And
(CategoryGroupingTBL.GroupNotes) Is Not Null)) OR
(((SiteTBL.SiteNotes)"" And (SiteTBL.SiteNotes) Is Not Null));

If I cut out 1 of the fields it becomes unique. I assume it is too
complex? Is there a better way to do this?

On Fri, 25 Dec 2009 22:00:45 -0500, BobC wrote:

I have 4 linked tables in Access 2007. 3 fo the tables contain a notes
field (SiteNotes, GroupNotes and EquipmentNotes. I want to create a
report that lists all notes. When I created an associated query I
cannot get it to be unique (it list multiple records that are identical.

The SQL is:
SELECT DISTINCT LocationTBL.LocationNumber, SiteTBL.SiteNumber,
SiteTBL.SiteNotes, CategoryGroupingTBL.GroupNotes,
EquipmentTBL.EquipmentNotes
FROM (LocationTBL INNER JOIN SiteTBL ON LocationTBL.ID =
SiteTBL.LocationID) INNER JOIN (CategoryGroupingTBL INNER JOIN
EquipmentTBL ON CategoryGroupingTBL.ID = EquipmentTBL.GroupID) ON
SiteTBL.ID = CategoryGroupingTBL.SiteID

WHERE (((EquipmentTBL.EquipmentNotes)"" And
(EquipmentTBL.EquipmentNotes) Is Not Null)) OR
(((CategoryGroupingTBL.GroupNotes)"" And
(CategoryGroupingTBL.GroupNotes) Is Not Null)) OR
(((SiteTBL.SiteNotes)"" And (SiteTBL.SiteNotes) Is Not Null));

If I cut out 1 of the fields it becomes unique. I assume it is too
complex? Is there a better way to do this?

Yes; use *one* Notes table with a NoteType field.

With your current three notes tables, you'll probably need to create a
subsidiary UNION query:

SELECT EquipmentTBL.GroupID, EquipmentTBL.EquipmentNotes AS Note, "Equipment"
AS NoteType
FROM EquipmentTBL
WHERE EquipmentNotes IS NOT NULL
UNION
SELECT CategoryGroupingTBL.GroupID, CategoryGroupingTBL.GroupNotes AS Note,
"Group" AS NoteType
FROM CategoryGroupingTBL
WHERE GroupNotesNotes IS NOT NULL
UNION
SELECT SiteTbl.GroupID, SiteTbl.SiteNotes AS Note, "Site" AS NoteType
FROM SiteTbl
WHERE SiteNotes IS NOT NULL


and join this to your main table.
--

John W. Vinson [MVP]

Thanks for the recommendation.

I have never tried a Union Query before ... I'll respond back if I have
any problems.

Thanks!
Bob

John W. Vinson wrote:
On Fri, 25 Dec 2009 22:00:45 -0500, wrote:

I have 4 linked tables in Access 2007. 3 fo the tables contain a notes
field (SiteNotes, GroupNotes and EquipmentNotes. I want to create a
report that lists all notes. When I created an associated query I
cannot get it to be unique (it list multiple records that are identical.

The SQL is:
SELECT DISTINCT LocationTBL.LocationNumber, SiteTBL.SiteNumber,
SiteTBL.SiteNotes, CategoryGroupingTBL.GroupNotes,
EquipmentTBL.EquipmentNotes
FROM (LocationTBL INNER JOIN SiteTBL ON LocationTBL.ID =
SiteTBL.LocationID) INNER JOIN (CategoryGroupingTBL INNER JOIN
EquipmentTBL ON CategoryGroupingTBL.ID = EquipmentTBL.GroupID) ON
SiteTBL.ID = CategoryGroupingTBL.SiteID

WHERE (((EquipmentTBL.EquipmentNotes)"" And
(EquipmentTBL.EquipmentNotes) Is Not Null)) OR
(((CategoryGroupingTBL.GroupNotes)"" And
(CategoryGroupingTBL.GroupNotes) Is Not Null)) OR
(((SiteTBL.SiteNotes)"" And (SiteTBL.SiteNotes) Is Not Null));

If I cut out 1 of the fields it becomes unique. I assume it is too
complex? Is there a better way to do this?

Yes; use *one* Notes table with a NoteType field.

With your current three notes tables, you'll probably need to create a
subsidiary UNION query:

SELECT EquipmentTBL.GroupID, EquipmentTBL.EquipmentNotes AS Note, "Equipment"
AS NoteType
FROM EquipmentTBL
WHERE EquipmentNotes IS NOT NULL
UNION
SELECT CategoryGroupingTBL.GroupID, CategoryGroupingTBL.GroupNotes AS Note,
"Group" AS NoteType
FROM CategoryGroupingTBL
WHERE GroupNotesNotes IS NOT NULL
UNION
SELECT SiteTbl.GroupID, SiteTbl.SiteNotes AS Note, "Site" AS NoteType
FROM SiteTbl
WHERE SiteNotes IS NOT NULL


and join this to your main table.