I think I can use a Query to do this

Lupus

Registered User.
Local time
Today, 12:08
Joined
May 28, 2011
Messages
10
I've spent the last 4 hours searching Google for an answer and can't seem to locate any advice that works for me so here goes:

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
British Military Orders (where the requirements for the awards are stored)
This has the following Fields:
Order
Order Rank
Order Letters
Points Required
Witnesses Required
Minimum Missions Flown
I have a Querry called Pilot Points Earned that calculates the following values for each pilot:
Total Missions Flown
Total Ariel Victories
Total Kills Witnessed
Total Points Earned
I need a way to compare the values for each pilot in Total Missions Flown, Total Kills Witnessed, and Total Points Earned against the values in the table for British Military Orders to see who has met the minimum requirements for which awards (whether it returns all the awards a pilot qualifies for or just the highest award a pilot qualifies for is all the same to me)

Thank you in advance for any help you can provide.
 
Hi Lupus!
Welcome to AWF,

you need to create another table tblmission for:

F_PilotID
MissionDate
ArielVictories
KillsWitnessed
PointsErned

The above table would be one-to-many relationships with the F_PilotID as a foreign Key from tblPilot, and tblPilot should have the following fields:

PilotID
PilotName
DateJoin
Ranking

Now when a pilot flys for a mission the mission data will be recorded to tblmission with the PilotID, after you get the data in this format, it would be easy to calculate for each:

Total Missions Flown
Total Ariel Victories
Total Kills Witnessed
Total Points Earned

in a query.

Hope this make sense.
 
As I stated in the original post, I already have a Query that does the following:
I have a Querry called Pilot Points Earned that calculates the following values for each pilot:
Total Missions Flown
Total Ariel Victories
Total Kills Witnessed
Total Points Earned​

What I'm looking for is a way to compare the information generated by this Query to the requirements in the table British Military Orders and see which pilots have earned what awards. Or did I miss something?
 
It's probably easiest to do this using what's callled a cartesian product. This is where you add both tables to a query but don't create a join. The result is a recordset of every combination of records from both tables.

It's then just a matter of applying the criteria to the query.

See the example I've attached

hth
Chris
 

Attachments

It's probably easiest to do this using what's callled a cartesian product. This is where you add both tables to a query but don't create a join. The result is a recordset of every combination of records from both tables.

It's then just a matter of applying the criteria to the query.

See the example I've attached

hth
Chris

Ok, I've looked at your solution and tried it with what I currently have and its not working. My guess would be because the Totals earned for each pilot currently exist only in a Query and not in a table as you have set up in your example. How do I get the results of the Query I have that calculates the totals for each pilot to feed into a new table that I can then use in the same fashion as the one you used?
 
Ok, I've looked at your solution and tried it with what I currently have and its not working. My guess would be because the Totals earned for each pilot currently exist only in a Query and not in a table as you have set up in your example. How do I get the results of the Query I have that calculates the totals for each pilot to feed into a new table that I can then use in the same fashion as the one you used?
Should work exactly the same for a query. Can you post your database?
 
Should work exactly the same for a query. Can you post your database?

Sure, I found an error I had made, it now pulls some results, but its not pulling them correctly and I"m not sure why.

Perhaps I should tell you this is my first foray into the world of Access.

Thank you very much for your help with this.
 

Attachments

Can you give me an example of where it is not working.

Chris
 
Sure, In the database I supplied, All pilots should list as "None" since it has 0 as the minimum requirement for all 3 criteria.

Also, Doc has met the requirements to be a member of the order of the British Empire and yet has not been awarded that.

After looking at it in each case it is where they have met the minimum requirements stated in the table but not gone over. So it is a case of > or = to, the query recognizes the > but is not set for = to. How do we add this?
 
there's several problems.

You have included ID in your Pilot Points Earned query. This means that it won't really group.

Also, you have "First" as an aggregate. I'm pretty sure you don't want that.

You've also have both Mission Results and Mission Points Total in your Points Earned Query. But Mission Points Total already has all the fields you need. No need to join Mission Results.

Another key problem is that you've used > as the criteria. I think you need >= to catch all the records.

As mentioned in the other thread, I'll have a go at sorting it.

Hope I'm not sounding critical. I appreciate it's not the easiest stuff to learn.
 
Take a look at this one.

I've implemented all the changes I've mentioned. I also had to alter the combos in your forms to correct the combo problem.

I've added the query called ConclusionSummary which is the solution to the other thread.

hth
Chris
 

Attachments

A couple of other tips (maybe for your next project):

It's advisable not to put spaces in your field names (or anything other than alpha-numeric). It can give you a headache later.

You have a field called ID in several tables. I would name then specific to the table e.g. ID in the Conclusion table could be ConclusionID. Also you have missionID in the mission table etc. It will help you a lot when you are creating queries.

Good luck.

Chris
 
Thank you for all your help with this. The ID field is something that it suggested everytime I made a query or table and I'd click "ok" but I wasn't reas sure why it should be there. As I said, this is my first use of Access for anything at all. Now I get to try and figure out how to upload some of these results to the Squad website, which uses Joomla. Lol, but thats a problem for another day. For now I'll just manually input the data
 

Users who are viewing this thread

Back
Top Bottom