Help needed sorting this one out

[Lupus]

Ok, problem 2 I have with my current project.

Ok, first allow me to tell you what I'm doing. I am setting up a database to track Pilot Statistics and Awards for a group of gamers that play IL-2, a WW2 combat flight sim game.

I have established 2 tables:

Mission Results (where the results of individual missions are stored)

This has the following Fields:

Pilot Name
Mission Date
Ariel Victories
Kills Witnessed
Points Earned
​

Mission Conclusion (where the total points earned for a missions multiplier is stored)

This has the following 2 fields:

Conclusion Status
Point Multiplier
​

The Points Earned Field is a lookup column where you select how the pilot ended the mission (RTB-Safe, RTB-Ditched, RTB-Crashed, etc. a total of 8 options) each of these has a differnt valued multiplier tied to it which is then used in a query to calculate the total points a pilot earned for a given mission.

The data I am looking for is to retrieve how many times each pilot has ended a mission in each fashion. So the results should be something like:

Pilot A RTB-Safe: 3 Times, RTB-Ditched: 2 Times etc.
Pilot B RTB-Safe: 0 Times, RTB-Ditched: 1 Times etc.

Any help is greatly appreciated.

 

[stopher]

You can do this with a cross-tab query. See example attached.

 

[Lupus]

I"m using Access 2003 and I can't open the file you posted with the extension .accdb

 

[stopher]

Sorry, here's the 2003 version

 

[Lupus]

I can't get the relationship between the two tables to be the same as in your example. The only 'common field' it shows is the ID Column.

 

[Lupus]

Ok, I repaired the relationship thing, but it still isn't pulling any results. I"m afraid I've jacked something up somewhere with the result that it isn't doing what it should. I've set up the query just like the example you provided as best I can. Any ideas?

 

[stopher]

I can't get the relationship between the two tables to be the same as in your example. The only 'common field' it shows is the ID Column.

There's a problem with the way you have set up the Points field. For one thing you have set it up as a "look up" which will give you a headache. Change it back to "Text box" (combo box) so you can see what the data really is. Also you should really store the Conclusion Status ID instead. Then you use the ID to get the points from the other table using a join query.

I'll play around with your database and send you an amended version.