Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


3 tables with related data. What now?

3 tables with related data. What now?

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.

RE: 3 tables with related data. What now?

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"?

Hook'D on Access
MS Access MVP

RE: 3 tables with related data. What now?

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
  • Case # (Always the same as the other tables)
  • Location (Can be different than table 2 and 3)
  • Date/Time (Always different than table 2 and 3)
  • Details of crime
Table 2 - Who reported the crime
  • Case # (Always the same as the other tables)
  • Location (Can be different than table 1 and 3)
  • Data/Time (Always different than table 1 and 3)
  • Name of good samaritan
Table 3 - Who took the report
  • Case # (Always the same as the other tables)
  • Location (Can be different than table 1 and 2)
  • Date/Time (Always different than table 1 and 2)
  • Name of police officer

RE: 3 tables with related data. What now?

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

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

[u]Table 3 - Who took the report: NewName = tblReceiver


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

RE: 3 tables with related data. What now?

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

RE: 3 tables with related data. What now?


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. smile

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close