Using VS2005, I can fill a DataSet with multiple DataTables. After this is
done, how would I make a call to that DataSet that can produce distinct
results without having to make a direct SQL call?

I don't even understand that question! So, let me explain further:

In our SQL Server's database, there are many tables, but only a select few
distinct SystemIDs that they are related to.

A stored procedure like this works, but I want to know how to do something
like this using all of the data that has already been collected in my DataSet:

DECLARE @DATE1 CHAR(10)
DECLARE @DATE2 CHAR(10)
SET @DATE1='06/15/2008'
SET @DATE2='07/20/2008'

SELECT A.[System_ID] FROM
(
SELECT DISTINCT [System_ID]
FROM PartsCatalog
WHERE (@DATE1 < Date_Time) AND (Date_Time < @DATE2)
 UNION
SELECT DISTINCT [System_ID]
FROM Historic_Records
WHERE (@DATE1 < Date_Time) AND (Date_Time < @DATE2)
 UNION
SELECT DISTINCT [System_ID]
FROM Inspections
WHERE (@DATE1 < Date_Time) AND (Date_Time < @DATE2)
 UNION
SELECT DISTINCT [System_ID]
FROM Test_Data
WHERE (@DATE1 < Date_Time) AND (Date_Time < @DATE2)
) as A
WHERE ([System_ID] like '%Weld%')

Each of these tables are already in my DataSet, but I do not know how I
would get the new table of information from it like I can using the query
above.

Could someone give me advice on this?

Thank you,
Joe