[AccessD] Multiple Table Search

probably are looking for a union - this looks thru
different table with a prompt for a last name

select [BoardCommLastName], [BoardCommFirstName],
[BoardCommCommissionTitleName],[BoardCommDateAppointed],
[BoardCommDateOfExpiration],
[BoardCommDateOfCommission], [BoardCommDateOfOath]
from [tblBoards&Commission]
WHERE ((([tblBoards&Commission].BoardCommLastName)
Like [Type the last name followed by *:]))
UNION select [BranchPilotsLastName],
[BranchPilotsFirstName],
[BranchPilotsPort],[BranchPilotsDateOfAppointment],
[BranchPilotsDateOfExpiration],
[BranchPilotsDateOfCommission],
[BranchPilotsDateOfOath]
from [tblBranchPilots]
WHERE ((([tblBranchPilots].BranchPilotsLastName) Like
[Type the last name followed by *:]))
UNION select [DistrictAttorneyLastName],
[DistrictAttorneyFirstName], [DistrictAttorneyTitle],
[DistrictAttorneyDateOfAppointment],
[DistrictAttorneyDateOfExpiration],
[DistrictAttorneyDateOfCommission],
[DistrictAttorneyDateOfOath]
from [tblDistrictAttorneys]
WHERE
((([tblDistrictAttorneys].DistrictAttorneyLastName)
Like [Type the last name followed by *:]))
UNION select [DistrictJudgesLastName],
[DistrictJudgesFirstName],
[DistrictJudgesFormOfAddress],
[DistrictJudgesDateOfAppointment],
[DistrictJudgesDateOfExpiration],
[DistrictJudgesDateOfCommission],
[DistrictJudgesDateOfOath]  
from [tblDistrictJudges]
WHERE ((([tblDistrictJudges].DistrictJudgesLastName)
Like [Type the last name followed by *:]))
UNION select [ElectedOfficialsLastName],
[ElectedOfficialsFirstName], [ElectedOfficialsTitle],
[ElectedOfficialsDateElected],
[ElectedOfficialsDateOfExpiration],
[ElectedOfficialsDateOfCommission],
[ElectedOfficialsDateOfOath]
from [tblElectedStateOfficials]
WHERE
((([tblElectedStateOfficials].ElectedOfficialsLastName)
Like [Type the last name followed by *:]))
UNION select [HigherCourtLastName],
[HigherCourtFirstName], [HigherCourtFormOfAddress],
[HigherCourtDateOfAppointment],
[HigherCourtDateOfExpiration],
[HigherCourtDateOfCommission], [HigherCourtDateOfOath]
from [tblHigherCourts]
WHERE ((([tblHigherCourts].HigherCourtLastName) Like
[Type the last name followed by *:]))
UNION select [SpecialJudgesAppointedJudge],
[SpecialJudgesAppointedJudge],
[SpecialJudgesFormOfAddress],
[SpecialJudgesDateOfAppointment],
[SpecialJudgesDateOfCommission],
[SpecialJudgesDateOfCommission],
[SpecialJudgesDateOfCommission]
from [tblSpecialCountyJudges]
WHERE
((([tblSpecialCountyJudges].SpecialJudgesAppointedJudge)
Like [Type the last name followed by *:]))
UNION select [TaskForceLastName],
[TaskForceFirstName], [TaskForceName],
[TaskForceDateOfAppointment],
[TaskForceDateOfExpiration],
[TaskForceDateOfCommission], [TaskForceDateOfOath]
from [tblTaskForce]
WHERE ((([tblTaskForce].TaskForceLastName) Like [Type
the last name followed by *:]))
ORDER BY 1;




--- "Mitsules, Mark S. (Newport News)"
<Mark.Mitsules at ngc.com> wrote:
> Jim,
> 
> Thanks for the reply.  How about where myTable can
> be any of 70+ tables and
> myFieldName can be any field within those 70+
> tables?
> 
> Mark
> 
> 
> 
> -----Original Message-----
> From: Jim Lawrence (AccessD)
> [mailto:accessd at shaw.ca] 
> Sent: Monday, July 26, 2004 3:58 PM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] Multiple Table Search
> 
> 
> Mark
> 
> select * from myTable where myFieldName like *Mark*
> 
> The previous sql should return all matches, from
> table myTable where the
> word 'Mark' is anywhere in the field myFieldName.
> 
> HTH
> Jim
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On
> Behalf Of Mitsules, Mark
> S. (Newport News)
> Sent: Monday, July 26, 2004 11:38 AM
> To: [AccessD]
> Subject: [AccessD] Multiple Table Search
> 
> 
> Group,
> 
> Strange request, but I've been handed this db and am
> not familiar enough
> with the data to do an educated search.   What is
> the simplest method to
> find an instance of data amongst 70+ tables.
> 
> Mark
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 



		
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail