Results 1 to 9 of 9

Thread: Queries?

  1. #1
    Join Date
    Jun 2014
    Posts
    9

    Question Unanswered: Queries?

    Hello All,

    I have a question about queries, how to get specific data from a table based on 3 fields. Sounds simple I know, but the twist is I know the data is not there and that is the information I need to demonstrate......

    So here goes......

    I have a Soldier table with basic info, and a related table for training qualifications, with three different training events:

    tblTraining.TrainingID, tblTraining.Training, tblTraining.TrainingDate, tblTraining.SoldierID

    tblTraining.TrainingID = PK
    tblTraining.SoldierID = FK with 1 to Many relationship with tblSoldier.SoldierID

    What I am looking to do is demonstrate that a Soldier has not attended a training event. There are 3 training events listed in a combobox with the data being stored in the same field. If the Soldier has completed 2 of the 3, how do I show that there is not a record for one of the training events? I have used the Find unmatched query wizard but it only shows if they are missing all three, I need to show if they are missing 1, 2 or 3.

    Any thoughts would be greatly appreciated.....

    Sincerely,
    Joe Limata

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you could do is do a count of the training events and then join that to the soldier table

    first find the number of events soldiers have attended
    ..that will be something like
    Code:
    select soldierID, count(soldierID) from tblTraining
    group by soldierID
    having count(soldierID)  <=2
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    9

    Queries!

    Quote Originally Posted by healdem View Post
    what you could do is do a count of the training events and then join that to the soldier table

    first find the number of events soldiers have attended
    ..that will be something like
    Code:
    select soldierID, count(soldierID) from tblTraining
    group by soldierID
    having count(soldierID)  <=2
    Thank You Very Much,

    That gets me closer, it at least tells me what Soldier has less than 3. But I am not able to determine which training without searching manually searching.

    Joe

  4. #4
    Join Date
    Jun 2014
    Posts
    9
    Quote Originally Posted by healdem View Post
    what you could do is do a count of the training events and then join that to the soldier table

    first find the number of events soldiers have attended
    ..that will be something like
    Code:
    select soldierID, count(soldierID) from tblTraining
    group by soldierID
    having count(soldierID)  <=2
    Ok, so you helped me work through the issue, thank you. Here is what I came up with!

    SELECT tblS.ID, tblS.Unit, tblS.Rank, tblS.Soldier, tblTraining.Training, tblTraining.TrainingDate, tblS.SSN
    FROM tblS LEFT JOIN tblTraining ON tblS.[ID] = tblTraining.[TrainingID]
    WHERE (((tblTraining.TrainingID) Is Null)) OR (((tblTraining.TrainingDate)<=Now()-270));

    This returns all Soldier records where there is not a corresponding training record and also tells me what training records are older than 90 months (270 days). From here I created three identical queries only adding criteria for tblTraining.Training = either "AT Level 1", "TARP", or "ISOPREP". Creating 3 reports one for each demonstrating old records and those that don't exist. Thanks again for your help, I don't have a person that I can speak to here at work, so you helped me work my way through it logically.

    Joe
    Last edited by jalimata; 07-29-14 at 05:02. Reason: Typographical error.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you know what types of training there are?
    and or what types of training a soldier has completed?

    lets assume you have a table for types of training, called, say TrainingTypes (Type (PK), Description (text50), and you have a column in your training table called, say TrainingType, which is a foreign key to the TrainingTypes table

    we need to refine the earlier query. that query worked fine assuming that all soldiers had doen at least one bit of training, it wouldn't return soldiers who had none

    Code:
    select tblsoldier.soldierID, count count(tblTraining.soldierID) as NoCourses from tblsoldier, tblTraining
    left join tbltraining on tblsoldier.soldierID = tbltraining.soldierID
    group by soldier.soldierID
    ..save it as say, CountOfSoldiersTraining
    note the having clause has been dropped, as I rather fancy you may want that information elsewhere

    ...you will almost certainly need to tweak this to make it work, I tend to forget the syntax Access uses for joins, its one of the areas where Access/JET strays furthest from ANSI SQL

    ...next write another query that uses the above query in addition to your exisiting tables

    .becuase the requirement has changed we need to extract all types of training and all soldiers but reject soldiers who have doe 3 bits of training
    so

    Code:
    select tblsoldier.soldierID, Description from tblSoldier
    left join CountOfSoldiersTraining on tblSoldier.soldierID= CountOfSoldiersTraining.soldierID
    left join trainingtypes on tblsoldier.TrainingType = trainingtypes.type
    where CountOfSoldiersTraining.NoCourses <=2
    and isnull(description)
    ..this will definiotelyt need refinign as I don't know your table design and two joins usuall means Access throws a wobbler and sulks till you use its desired syntax. if however you defiend realtionships between the various tables then creatignt he query in the designer should be a piece of proverbial

    incidentally I'd strongly recommend in future rethinking the way you name tables and columns
    there's as many naming conventions as there are countermanding orders, but
    try nbot to re use the name of the table in the columns defined in that table, bit DO use the table name if its a foreign key. it makes it easy to spot the relationship

    ferinstance
    dont use SoldierID in tblSoldier, use ID, but do use SoldierID in the trainign table
    the tbl prefix can confuse some people
    make certain you know the reserved words (words you shouldn't use in your application) Each and every language has them. Although Access is quite good at resolving such issues at some stage ignoring the rule will cause problems.
    decide if you are going to use CamelCase or underscore to identify words in a column name. use abbreviations where appropriate (eg No instead of number, but dont be temtped to use #, which WILL cause problems)
    eg
    SoldierID OR soldier_id
    some recommend the former, some the latter, it doesn't really matter that much. many argue the lowercase separated by underscores is more compatable across different computer systems
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    A suggestion:-
    don't use magic numbers such as 270, which Im guessing you've approximated to be 9 months, not 90 months. use a function such as dateadd to do date mathmatics

    now() returns the current system date AND time, date() returns the current system date.

    Im also guessing that you will probably want to look at an abstract date(such as the start of the month, as opposed to the current date - 9 months
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2014
    Posts
    9

    Queries

    Wow,

    That is a lot of info to digest.

    I am self taught, I have never taken any classes just read a few books, and the rest is just sitting on my computer dissecting Access. Build one tear it down and then build another.

    As far as magic numbers are concerned, it was really the only way I could figure out when a date is a minimum of 9 (Not 90) months old. By using now() I wanted to insure that every day when the records were check I would get the most up to date info. I could use an abstract date, but the three training events I track are valid for 12 months, so by having a warning at the 9 month mark I can give commanders enough lead time to insure the training is completed prior to expiration.

    There are only three training events that I track in my office, the ones I listed. AT Level 1, TARP, ISOPREP

    This whole thing started in excel, as a flat file that I imported as follows:
    tblSoldier.SoldierID, tblSoldier.Unit, tblSoldier.Rank, tblSoldier.Name, tblSoldier.SSN, tblSoldier.MOS, tblSoldier.Clearance, tblSoldier.ClearanceDate, tblSoldier.InvestigationDate, tblSoldier.PeriodicReviewDate, tblSoldier.ExpirationDate, tblSoldier.InvestigationType, tblSoldier.ATLevel1, tblSoldier.TARP, tblSoldier.ISOPREP, tblSoldier.Remarks, tblSoldier.SF_312, tblSoldier.CountryBrief

    Crazy I know, I decided to divide it up into three main tables: tblSoldier, tblClearance, tblTraining, the training table has given me the most trouble, my biggest concern are users, ok with excel, not so much with access, that is why I am trying to create an application for them.

    See my attached pdf. It is my relationship report.

    This is really great.

    Thanks Joe
    Attached Files Attached Files

  8. #8
    Join Date
    Jun 2014
    Posts
    9
    Forgive me, I am in Afghanistan right now, I am unable to do this from work, when I return to my room at night this is my entertainment. My internet is real bad and will cut out on me sometimes when I am answering your responses.

    Thanks for all your help,
    Joe

  9. #9
    Join Date
    Jun 2014
    Posts
    9

    Queries!

    Here is another question in reference to my training events that I must track, currently I have one field for the three events and a date completed field. Is this the best way to tack or would it be better to have three fields one for each event plus a date completed field for each?

    Option 1: tblTraining.ID, tblTraining.Training, tblTraining.Date, tblTraining.SoldierID

    Option 2: tblTraining.ID, tblTraining.ATLevel1, tblTraining.TARP, tblTraining.ISOPREP, tblTraining.Date, tblTraining.SoldierID

    Option 2 emulates the original excel file and treats all three events as one record.

    Either way I have had difficulty trying to develop queries that pull the data I am searching for.

    Any thoughts,
    Joe

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •