Dave-
Interesting problem given that some clients grow up to be suspects. With this table structure, every query that looks for clients and related suspects requires two copies of the "Names" table. Give one an alias of "Clients" and relate it to the client key in your MDT table. Give the other the alias of "Suspects" and relate it to the suspect key in your MDT table. Remove the extra "suspect" keys in your linking table. Your linking table should also have other fields that describe the client / suspect interaction, such as date of the event, nature of the event, location of the event, etc.
Using the three tables this way, you'll get one row per client / suspect pair, and that row will include details of the interaction from the MDT columns. A query might end up looking like:
SELECT Clients.FirstName, Clients.LastName, Clients.DOB, Clients.Address, Suspects.FirstName, Suspects.LastName, Suspects.DOB, Suspects.Address, MDT.EventDate, MDT.EventTime, MDT.EventLocation, MDT.EventDescription
FROM Names As Clients INNER JOIN MDT
ON Clients.NameID = MDT.ClientID
INNER JOIN Names As Suspects
ON Suspects.NameID = MDT.SuspectID;
John Viescas, author
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On Sep 7, 2017, at 01:39, dhelvey@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I was recently hired to start a Child Advocacy Center for a county in Washington. The budget is small, and I need a way to collect and store some data related to each child client we serve. I have limited experience with Access and am trying to build a database.
One of the things I need to collect and relate are Child Client Name, DOB, demographic information as well as Suspect Name, DOB, demographic information. I need the Database to be able to reference three suspects per client for those rare occassions where there is more than one suspect.
Here's a part of what I have done.
names table containing fields for nameid, first, last, dob, gender, race, ethnicity, etc.
mdt table containing fields for clientid (nameid), suspect1id (nameid), suspect2id (nameid), suspect3id (nameid) As weird as it seems some clients may grow up to be suspects so each person would have their individual name record that could be related to multiple mdt table records.
I have tried a many to many relationship between the names table and the mdt table, but am not able to query the names table and mdt table and return any records. I'm stuck and looking for some assistance. I would be more than happy to zip and email the database to someone willing to help.
Thank you,
Dave Helvey
One of the things I need to collect and relate are Child Client Name, DOB, demographic information as well as Suspect Name, DOB, demographic information. I need the Database to be able to reference three suspects per client for those rare occassions where there is more than one suspect.
Here's a part of what I have done.
names table containing fields for nameid, first, last, dob, gender, race, ethnicity, etc.
mdt table containing fields for clientid (nameid), suspect1id (nameid), suspect2id (nameid), suspect3id (nameid) As weird as it seems some clients may grow up to be suspects so each person would have their individual name record that could be related to multiple mdt table records.
I have tried a many to many relationship between the names table and the mdt table, but am not able to query the names table and mdt table and return any records. I'm stuck and looking for some assistance. I would be more than happy to zip and email the database to someone willing to help.
Thank you,
Dave Helvey
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar