3 tables with related data. What now?
3 tables with related data. What now?
(OP)
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.
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?
How are these lists different "who reported" and "who took"?
Duane
Hook'D on Access
MS Access MVP
RE: 3 tables with related data. What now?
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 reportRE: 3 tables with related data. What now?
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?
RE: 3 tables with related data. What now?
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++:
CODE
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
RE: 3 tables with related data. What now?
Thanks again
RE: 3 tables with related data. What now?
Duane
Hook'D on Access
MS Access MVP
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.
"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57