How do I create a Master Detail Table?

[JKwan]

I am really novice with Access and from what I google, all I found were how to create Master Detail Forms, which I don't think is what I wanted. I needed a table because I need to pull this info into Spotfire to do analysis. My scenario is that I have 2 tables which summarized to an InjuryType and BodyHarmed tables. To make it simple InjuryType contains CaseNumber and InjuryType as fields and BodyHarmed has CaseNumber and TreamentType

InjuryType
CaseNumber - 1
InjuryType - First Aid

BodyHarmed
CaseNumber - 1
TreatmentType - Head
CaseNumber - 1
TreatmentType - Leg

From the perspective when I create a query, I want to get:
How many First Aid - 1
How many Treatment Types - 2

However, when I structure my query, I get 2 First Aid, which the join gives me.... How do I structure my tables to do the above? Thanks.

[JKwan]

hmm, after posting the thread.... I don't know why i did not think of it... I guess I can query the InjuryType table, this will give me 1 First Aid. Then I can query BodyHarmed table to get my Treatments of 2. Is this the approach to do this?

[OBP]

The fact that you are trying to do 2 separate unconnected counts it would probably be best to use 2 queries.
You can use more than one table in a query and control how the data is presented by using how the tables are "Joined" in th query, but in your case I am not sure it will achieve what you want.
Are you using Group BY and the functions that that allows, like count, sum, max & min etc?

Are you restricting the data by Case Number or looking at all cases?

If you can provide some dummy data and explain what the outcome should be, I can see what works.

[JKwan]

Thank you for answering... I got it sorted out like you suggested with multiple queries.