How to find a specific record

[Guest]

I have a database that contains two tables of participant information. The
first is a mailing list containing First Name, Last Name and Address fields.
I run an append query that brings enrolled participants to the second table,
which also contains First Name, Last NAme and Address Fields.. I want to
create a way of searching across both of these tables by participant last
name so that I can quickly determine what forms to use when a participant
calls (the form based on the mailing list table or the form based on the
enrolled participants table. To complicate things further, enrolled
participants can be calling for 4 different reasons (4 different stages of
data collection). Is there a way to have a value autogenerate on a
switchbaord, that will inform us of what stage of data collection the
participant is at so that we can then open the appropriate form?
I don't know if I'm explaining clearly.

 

[Jeff Boyce]

Rachel

From your description, you have a ... spreadsheet! While it might be
necessary to repeat the name/address info when you are working in Excel or
other spreadsheets, Access is a relational database. You won't get the best
use of Access' features/functions if your table structure isn't
well-normalized.

Consider having a Person table with FN, LN, Address fields, and a PersonID
(autonumber works well for this).

Then, in any other situations requiring "person" data, you simply use the
PersonID as a foreign key to point back to the record that contains
name/address info. No coordination/synchronization issues!

If you need to track enrollment steps, you might use a table something like:

trelEnrollmentStep
EnrollmentID
PersonID (a foreign key, from the tblPerson table)
StepID (a foreign key field, from a list of steps, like in a
tlkpStep table)
StepDate (on this date, the Step was taken)

Now you have a history of each step taken by each person, and can easily
find a person's current step by querying for the Max(StepDate) for each
Person.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

I do have a relational database. The reason I have the information existing
in both tables is that eventually I will be destroying the Mailing/Call List
table for purpose of destroying participant names that do not agree to
participate. The Id number is an Autonumber field on the Mailing list. I have
the tables linked on PTID. However, because not all the Id's on the
enrollment table exit on the mailing table, how would search across both of
them?

 

[Guest]

Additonally:
Each step has a Yes/No field that is checked when completed on the enrolled
participants table.
So how would is there a way to search across both tables for a record that
matches on First Name, Last Name and which steps have been completed for that
record?

 

[Jeff Boyce]

Rachel

If you are saying you want to see records from table1 and table2 that match
on FN, LN, plus some other fields from these tables, it seems like you could
create a new query and join the two tables on the two fields. The only
records that Access would return would be for those "matching" rows.

I understood that you are using Access, but the design I inferred from your
description seemed "spreadsheetly", rather than well-normalized (i.e.,
relational). Too much inference, not enough caffeine!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

I think I found a work-around for the situation. The problem that would exist
with creating a query across both tables is that some records in table1 would
not exist in the table2.
Thanks though.