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

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

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

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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++:

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 [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
[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

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

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

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