3 tables with related data. What now?

[lazyadmin]

Hello all. I am a newb to access but have an important project that my help has been requested on. I'm looking for some guidance on a way to tackle this particular situation. Backround: I can't list what the tables are really for so i'll use a hypothetical example. We'll say i'm a cop. I have 3 access tables. Table 1 contains information about a particular crime. Table 2 contains information on who reported the crime. Table 3 has information on who took the report. They all share a common case number. We'll say we have about 25000 reports.

I've been asked to help create reports to pull data from these three tables. The reports will need to show info from all three tables.

Where i'm getting lost, is I haven't figured out the best method to start. Should I create relationships between the tables based on the shared case number? Leave the tables separate and just query each table as needed. The problem I see with the latter is that I will use a form to search for possibly a keyword. The search will need to access every table using the keyword. Should I create one huge table?

Please if anyone can help me out it would be greatly appreciated.

 

[dhookom]

You need to create junction tables since a crime may be related to multiple "who reported". Can more than one person be "who took the report"?

How are these lists different "who reported" and "who took"?

Duane
Hook'D on Access
MS Access MVP

 

[lazyadmin]

Sorry let me provide a few more details. If this table/form were done properly the first time it should have all been one single table. Unfortunately it was done in three different tables. The information is static and will never be changed or added too. The information cannot be changed or manipulated. The end reports have to contain the information exactly how it is entered in the tables.

To answer the questions from the last post; it will always be one single person who took the report (in this example we'll say a policemans name). The person reporting would be a witness to the crime. Again, it will always be one single person. Hypothetically it could be more but for my purposes it will always be one person. We'll say a good samaritan.

Table 1 - Details of the crime
[ul]
[li]Case # (Always the same as the other tables)[/li]
[li]Location (Can be different than table 2 and 3)[/li]
[li]Date/Time (Always different than table 2 and 3)[/li]
[li]Details of crime[/li]
[/ul]

Table 2 - Who reported the crime
[ul]
[li]Case # (Always the same as the other tables)[/li]
[li]Location (Can be different than table 1 and 3)[/li]
[li]Data/Time (Always different than table 1 and 3)[/li]
[li]Name of good samaritan[/li]
[/ul]

Table 3 - Who took the report
[ul]
[li]Case # (Always the same as the other tables)[/li]
[li]Location (Can be different than table 1 and 2)[/li]
[li]Date/Time (Always different than table 1 and 2)[/li]
[li]Name of police officer[/li]
[/ul]

 

[PHV]

Why not simply use a query joining the 3 tables on [Case #] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Here's another question to ask before doing a straight inner join on the tables..

Are the matches in the tables going to be 100%? What I mean is, for every case in the case detail table, will there absolutely be AT LEAST one record in each of the other two tables? If not, or you're not sure, then I'd use LEFT JOIN from the Case Detail to each of the others. This is really just building off what PHV suggested.

Here's a start: (if you want to give it a try, and assuming your table/field names above are actual names, which I doubt. If they are actual names, I highly suggest renaming the tables):

Create a new query, and view it in SQL view (don't worry about adding tables yet), then paste in this code - make changes before pasting probably if need be... use a text editor, such as notepad or notepad++:

SELECT cd.[Case #] ,cd.[Location] AS CrimeLocation ,cd.[Date/Time] AS CrimeDate ,cd.[Details of crime]
             ,cr.[Location] AS [ReportingLocation] ,cr.[Date/Time] As ReportingDate ,cr.[Name of Good Samaritan]
             ,ct.[Location AS [TakenLocation] ,ct.[Date/Time] AS TakenTime ,ct.[Name of Police Officer] AS Officer
FROM [Table 1] AS cd
LEFT JOIN [Table 2] AS cr ON cd.[Case #] = cr.[Case #]
LEFT JOIN [Table 3] AS ct ON cd.[Case #] = cr.[Case #]

Otherwise, post back with your progress, how it's going.

Oh, if I had to suggest table names, I'd also suggest diff fieldNames... to me, they are too long - you can know what they are without spelling them out in phrases:

Table 1 - Details of the crime: NewName = tblCrime
CaseID
CrimeLoc
CrimeDate
CrimeDetail

Table 2 - Who reported the crime: NewName = tblReporter
CaseID
ReportLoc
ReportDate
Reporter ---You could always add "Name of good samaritan" in the field description in table design

Table 3 - Who took the report: NewName = tblReceiver
[CaseID[/b]
ReceivedLoc
ReceivedDate
Officer

And I'd also suggest (if this isn't the case already), that perhaps you add 2 other tables for Officer and Reporter, and only pull in their IDs to the above tables... so you'd pull in ReporterID and OfficerID. That way if one Reporter, and/or one Officer is involved in multiple cases, you're only using the numeric ID mult times, not a text field for a name.

Or if you'd prefer to build it on your own for starters, you can do like this:
1. Create a query in design view
2. Pick your three tables, and move to the design portion.
3. Drag the CaseID amongst them, and make sure to double-click each join line, and say "show all records in the Crime table, and only those matching in the other table"
4. Then put what fields you want in the list below, add aliases by saying Alias: FieldName

Post back with what you ended up doing or are trying to do, and what progress you've made.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[lazyadmin]

I appreciate all the input. I went with PHV suggestion as i'm not all too familiar with SQL. I was able to get all the data into one place. Now I just have to work on learning how to build forms/querys to search and retrieve it all.

Thanks again

 

[dhookom]

A good resource for learning about Access tables/forms/queries etc is Crystal Long's Access Basics.

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

lazyadmin,

The SQL code I published was for an Access query - it's just in SQL form. Access queries are "Jet SQL" queries. Either way, make sure (unless you know of a 100% certainty that you will ALWAYS ALWAYS ALWAYS have a 100% 1 to 1 to 1 match between the 3 tables (in other words, you'll never be missing a matching record in any of the three) that you are using a LEFT JOIN (an OUTER JOIN is what it's called, but LEFT is assuming your Crime table is on the LEFT). You can do that via the copy/paste into the SQL view or you can do it with the click and drag design view - either way gives the same results.

Here's why I say this, and why I'm emphasizing it (I learned this one the hard way - well, had "learned" it, but REALLY learned it when I forgot!):

Let's say Leroy Brown committed a murder, you wound up with no "reporter", but did have a record where someone took the case down in the end (your 3rd table)... so you have a crime record in table 1, and a record in table 3, but nothing in table 2. When you do the INNER JOIN (default join in Access - so it will do this if you don't specify otherwise), you are telling Access: ONLY GIVE ME RECORDS WHERE ALL 3 TABLES MATCH... THAT ALSO MEANS ONLY GIVE ME RECORDS WHERE I HAVE A MATCH IN EACH TABLE.... So in this example, there is no record in table 2. So when you run the query, guess what, it looks like Leroy's crime was never committed (as far as the final query/report goes).

Well, have a good weekend.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57