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

B

BobC

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?
 
J

John W. Vinson

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.
 
B

BobC

Thanks for the recommendation.

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

Thanks!
Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top