How to sort/filter records matching certain criteria?

[Tommy]

Within my table, I have two fields, 'Tagname' and 'System'. Tagnames
are a list of tags I'm using e.g. 06TI293.PV. Often these tags are
duplicated. For each tag, there is a corresponding system that it is
on - either 'PHD' or 'XANS'. Not all tags are on both systems, some
are just on PHD, some just on XANS. However there are some tags
sometimes that are on both systems. I would like a query that can show
me all of the tags that appear on both systems in the table i.e if
06TI293.PV matches PHD and XANS in the system field, list that tag.
Any ideas how to do this?

 

[John Spencer]

Use two instances of the table in your query.

SQL would look something like the following

SELECT P.Tagname
FROM YourTable as P INNER JOIN YourTable as X
ON P.TagName = X.TagName
WHERE P.System = "PHD" And X.System = "XANS"

In the query grid
--add your table to the query two times (Second instance will be named with
a "_1" at the end
--Join the two instances by dragging from TagName to Tagname
--Add Tagname from one table to the fields
--Add System from the both tables
--Set the criteria to PHD under one system field and XAN under the other

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tommy]

thanks, it worked a treat