Working with querys

[CoreyM]

I have a problem to solve and need your help. I have three tables tblSoldiers, tblWeaponAssigned, and tblWeaponQual.
My tables are set up like this; A soldiers can have many weapons but a weapon can only have one soldiers, A weapon can have many qualification but a qulification can only have one weapon at a time.

tblSoldiers
SSN = text
LastName = text
FirstName = text
AssignedWeapon = YES/NO or TRUE/FALSE
SoldierID = autonumber
........

tblWeaponAssigned
TypeWeapon = text (M9, M4/M16)


SightWeapon = text (Iron Sight, ACCOG)
ClassWeapon = text (IND, CREW)
WeaponQualComplete = YES/NO or TRUE/FALSE
SoldiersID = number
WeaponID = autonumber
........

tblWeaponQual
QualTypeWeapon = text (Day, Night, NBC......)
QualDateWeapon = date
WeaponID = number
QualWeaponID = autonumber

Now the problem; For a soldier to be qualifed he needs (Day, Night, and NBC). So how do I set up a query to put a yes or true vaule in the WeaponQualComplete field on the tblWeaponAssigned table.
Example: M9 has a DAY&Date, Night&Date and no NBC data, soldier is not qualified on that weapon.
M4/M16 a Day&date, Night&Date and NBC&Date, soldiers is qualified on weapon.

[June7]

This value can be calculated whenever needed. No real need to save to table.

Try something like:

IIf(Nz(DCount("QualTypeWeapon","tblWeaponQual","We aponID=" & [WeaponID]),0)<3,False,True)

[CoreyM]

This value can be calculated whenever needed. No real need to save to table.

Try something like:

IIf(Nz(DCount("QualTypeWeapon","tblWeaponQual","We aponID=" & [WeaponID]),0)<3,False,True)

Not sure where I would put this code. Would i make a select query and add a field with the code?

[CoreyM]

Thank you for your help. I have found a error in my table design; I use a append query to update my weapons assigned and qualification data all on one table (1tblWeaponAssigned) from a form. Is there a way to append data from that form and append query to two tables at the same time? Or do I need to keep all of the data on one table. And if so can you help me with the same code for that.

Tables look like this write now to accommodate the form and append query;

1tblSoldiersData
SSN = Text
Last Name = Text
First Name = Text
SoldiersID
_______________

tblWeaponAssigned
TypeWeapon = text (M9, M4/M16) (Lookup Box)
SightWeapon = text (Iron Sight, ACCOG) (Lookup Box)
ClassWeapon = text (IND, CREW) (Lookup Box)
WeaponQualType = text (Lookup Box)
WeaponQualDate = Date
WeaponQualComplete = YES/NO or TRUE/FALSE
SoldiersID = number
WeaponID = autonumber

So if I understand what your saying take out the WeaponQualComplete, and create the calculation in a query or report.
Just so you know we are getting into uncharted waters for me. I have not been this deep into coding.

[June7]

If you just calculate the value when needed (in query or textbox), no macro or VBA code needed. Code would be needed to save the value to table.

Combining those two tables means repeating the weapon info for each of the 3 qualifying conditions. More normalized would be separate tables. What you might have lacked before was a foreign key field in tblWeaponQual for record ID from tblWeaponAssigned. If multiple soldiers can qualify with the same weapon, then this pk/fk could be an autonumber field and the weaponID could be a repeatable value in tblWeaponAssigned. If a weaponID is assigned only to one soldier then it could be the pk/fk as you originally had.

[CoreyM]

If you just calculate the value when needed (in query or textbox), no macro or VBA code needed. Code would be needed to save the value to table.

Combining those two tables means repeating the weapon info for each of the 3 qualifying conditions. More normalized would be separate tables. What you might have lacked before was a foreign key field in tblWeaponQual for record ID from tblWeaponAssigned. If multiple soldiers can qualify with the same weapon, then this pk/fk could be an autonumber field and the weaponID could be a repeatable value in tblWeaponAssigned. If a weaponID is assigned only to one soldier then it could be the pk/fk as you originally had.

Thats what I was thinking, the two tables would be more normalized. But that dose not work with my append query, is there a way to append data into two different tables at once. Again thanks for all you help!

[June7]

No, but not understanding why you need to. With proper form/subform/subsubform arrangement, code should not be needed.

[CoreyM]

Ok I figured it out, I just copied the original append query and and adjusted it to update the tblweaponquery. Than add it to my button macro. Thanks so much for your Help.

[CoreyM]

OK I JUMPED TO SOON. Ran some test data and I am seeing something I dot understand. Can I possibly attach my database and have someone look at it. Again Thanks

[June7]

Attach per instructions at bottom of my post.

[CoreyM]

Attach per instructions at bottom of my post.

Thank you for looking a my table design.

[June7]

Now tell me exactly what is the issue you don't understand and which forms are involved.

[CoreyM]

Ok The Tables; tblMobingUnit, tblSoldier, 1tblWeaponAssigned, 1tblWeaponQualification, forms; 1frmMobingUnitData, 1SubfrmUnitSoldierWeapon, querys; appendWeaponQuery, appendWeaponQualQuery

User should be able to open the 1frmMobingUNitData look at his unit and see what soldiers are in the unit, If the user has a sign in roster for a weapons range firing M4/M16 Day,NBC,Night Qual. He can click on the weapons button open isubfrmSoldierWeapon and assign a weapon type and Qualtype and Date to the soldiers on the roster. Then click the summit button witch run the appendWeaponQuery and appendWeaponQualQuery. What I am seeing is the first query run ok but the second query adds more data the selected.

[June7]

Trying to understand data relationships, work flow, and form behaviors.

I find the tab control with the page that has only a label with caption 'Add your details for the form here.' confusing. Makes me think I should type something there. Unless you plan to have other pages on the tab control, I would just put the subform directly on the main form. Either way, I would not have that page and label. Give users credit for some computer literacy. I expect they know they need to enter data to form but you just forced them to make decision and click onto other tab to be able to start entry. This will just become annoying with repeated use.

Do you really need a record in tblWeaponsAssigned if Assigned = False? How can this field = False when there is an associated fk_SoldierID? Why do you need fk_MobUnit in tblWeaponsAssigned? This value is available in tblSoldier - this appears to be duplication of data. Similar question for tblWeaponsQualification and the Shot field. If they didn't shoot, why have a record?

Personally, I would do whatever I could to avoid use of INSERT and UPDATE queries but I am beginning to see how this structure makes those actions virtually necessary.

"If the user has a sign in roster for a weapons range firing M4/M16 Day,NBC,Night Qual." How does user know there is a roster? I see a disconnect between roster and qualifications. There are qualification records but no roster records.

[CoreyM]

Ok first thank you for taking the time to look at my database.

To address the first paragraph, the details tab is for me, I will add information about what the form is used for, when Im ready for the users to use my database.
2. I don't know if i need a record in the tblWeaponAssigned if the value = False, What I do know is; A soldier goes to a range and completes Day, NBC but not night, so he is not qualified. Day,NBC,Night are three separate ranges with three separate sign-in-rosters. I guess that is where the false value comes from when I run the appendQuery. Not sure how to address that, any advice on that would be greatly appreciated.
3. The fk_soldiersID and fk_MobUnitID are primary Key on the tblSoldier, so with my limited knowledge, I thought both keys had to be on any tables they relate to.
4. Ok shot field on the tblWeaponQualification I was using it to record the qualification data from the append Query. It can be removed.
The sign-in roster we get are not generated from the database, we get them from the Mobilizing Units that we are tracking that conducted the training or range.

I have the painful job of tracking, recording and validating all of a the mobilizing units and there required training to include weapon assignment and qualification. So I am trying to create a access database to help with that. We track about 3000 soldiers at any given time, so being able to input mass data with ease would be really helpful.