Filter and Merge in the Same Query

Question

I have a query in which I want to filter and merge data together. As is the SQL for the query looks as such:

SELECT UCase$(DLookUp("Abbreviation", "tblRankStructure", "tblRankStructure!ID = " & [tblRoster].[Rank])) AS Rank
    ,UCase$([tblRoster].[LastName] & ", " & [tblRoster].[FirstName] & " " & IIf(IsNull([tblRoster].[MiddleName]), "", Left$([tblRoster].[MiddleName], 1))) AS [Full Name]
    ,tblRoster.EDIPI
    ,tblBasicIndividualRecords.PriMilOccSpec AS MOS
    ,tblBasicIndividualRecords.Gender
    ,tblBasicIndividualRecords.BloodType
    ,tblUnitInformationCodes.PltCode
    ,IIf(IsNull([tblBasicIndividualRecords] ! [MealCardNumber]), "COMRATS", [tblBasicIndividualRecords] ! [MealCardNumber]) AS [Meal Card]
    ,tblBasicIndividualRecords.SecurityClearance AS Clearance
    ,tblBasicWeaponRecords.WeaponType
    ,tblBasicWeaponRecords.WeaponSerial
    ,tblBasicWeaponRecords.OpticSerial
FROM (
    (
        (
            tblRankStructure INNER JOIN tblRoster ON tblRankStructure.ID = tblRoster.Rank
            ) INNER JOIN tblBasicIndividualRecords ON tblRoster.EDIPI = tblBasicIndividualRecords.EDIPI
        ) LEFT JOIN tblBasicWeaponRecords ON tblRoster.EDIPI = tblBasicWeaponRecords.EDIPI
    )
INNER JOIN tblUnitInformationCodes ON tblRoster.EDIPI = tblUnitInformationCodes.EDIPI;

With the query as such it will return as rows for the same person if they have multiple weapons. How can I merge to return same data but only one row from the query and still display all required data?

Current Output:

What I would like is to have the row with the M9A1 serial under the pistol serial column and only have one row for SSgt Pilewski

This sample is created by added another field to the tblBasicWeaponRecords for the PistolSerial. I do not want this field there and would like to make my query work with out having this field in the tblBasicWeaponRecords.

All data is pulled from the following tables:

tblRoster

+-------+------------------+----------+-----------+------------+
| EDIPI |       Rank       | LastName | FirstName | MiddleName |
+-------+------------------+----------+-----------+------------+
|       | First Lieutenant | Mozzetta | Jennifer  | L          |
|       | Staff Sergeant   | Pilewski | Troy      | Andrew     |
|       | Sergeant         | Irizarry | Jonathon  |            |
+-------+------------------+----------+-----------+------------+

tblBasicIndividualRecords
+----+-------+-------------+------------+-------------------------------+--------------------+---------------+---------------+--------+------------+--------+---------+--------+-----------+-------------------+----------------+-----------------+
| ID | EDIPI | DateOfBirth | DateOfRank | ArmedForcesActiveDutyBaseDate | EndOfActiveService | PriMilOccSpec | AddMilOccSpec | Billet | Deployable | OnHand | Remarks | Gender | BloodType | SecurityClearance | MealCardNumber | MartialArtsBelt |
+----+-------+-------------+------------+-------------------------------+--------------------+---------------+---------------+--------+------------+--------+---------+--------+-----------+-------------------+----------------+-----------------+
|  1 |       |             |            |                               | 12-Aug-2015        |          0602 |               |        | TRUE       | TRUE   |         |        |           |                   |                |                 |
|  2 |       |             |            |                               | 23-Aug-2014        |          0659 |               |        | TRUE       | TRUE   |         |        |           |                   |                |                 |
|  3 |       |             |            |                               | 30-Aug-2013        |          0651 |               |        | TRUE       | TRUE   |         |        |           |                   |                |                 |
+----+-------+-------------+------------+-------------------------------+--------------------+---------------+---------------+--------+------------+--------+---------+--------+-----------+-------------------+----------------+-----------------+

tblBasicWeaponRecords
+----+-------+------------+--------------+-------------+
| ID | EDIPI | WeaponType | WeaponSerial | OpticSerial |
+----+-------+------------+--------------+-------------+
|  1 |       | M4         | W151644      |      224604 |
|  3 |       | M4         | W142663      |      228541 |
|  4 |       | M16A4      | 164522845    |      225487 |
|  6 |       | M9A1       | 1476541      |             |
+----+-------+------------+--------------+-------------+

tblUnitInformationCodes
+----+-------+---------+
| ID | EDIPI | PltCode |
+----+-------+---------+
|  1 |       | CYB     |
|  2 |       | CYB     |
|  3 |       | CYB     |
+----+-------+---------+

What do you mean by 'return same data but only one row from the query and still display all required data'? Show example source data and desired output. — June7, Jun 21 '17 at 05:08
You *may* be looking for https://stackoverflow.com/questions/13278590/combine-values-from-related-rows-into-a-single-concatenated-string-value . If not, please create a [mcve] with some rows of sample data, and the expected result. — Andre, Jun 21 '17 at 14:38
@Andre: I reviewed the question you referenced; This is not what I am looking for. WeaponType is a LookupColumn in which I specified the values of {"M16A4","M4","M9","M9A1"}, in the same I have the column WeaponSerial which you would enter the serial for the selected WeaponType. There is a 1-∞ relationship between tblRoster and tblBasicWeaponRecords; in which a person in tblRoster can have multiple records in tblBasicWeaponRecords because they would have multiple weapons issued to them. As to not add an extra field to the tblBasicWeaponRecords for PistolSerial; I wanted to see if I could — TroyPilewski, Jun 21 '17 at 15:06
@Andre: determine in the same query as above if the person had a record for a M9* in the tblBasicWeaponRecords and insert just the WeaponSerial number in the PistolSerial column for the query. Thereby the query only returning one row for SSgt Pilewski instead of the two that is shown. — TroyPilewski, Jun 21 '17 at 15:08
[How to ask a good SQL question](http://meta.stackoverflow.com/a/271056/3820271). Again, sample data and expected result is much more useful than a textual description. — Andre, Jun 21 '17 at 15:13
@Andre: I'm not sure what exactly you are looking for; I have post the SQL statement for the query that I built, I have posted an image of it's output. Would another image help? — TroyPilewski, Jun 21 '17 at 16:07
That's better, but we're missing your input data. Data as text is better than images. Build your sample data and expected result in Excel, and use http://ozh.github.io/ascii-tables/ to get pasteable text from it. Reduce the data and the query to the relevant columns. Good sample data requires some work, but it is necessary. [Example](https://stackoverflow.com/questions/32274070/self-join-with-missing-rows-on-both-sides) — Andre, Jun 21 '17 at 16:50
@Andre: I have added the tables; I appologize as I had to remove all the EDIPIs — TroyPilewski, Jun 21 '17 at 18:03
You omitted the "reduce / simplify" part, but OK. How does the database know, which weapon is a pistol? You need a criterion. And what happens, if one person has > 1 weapon record for each type? — Andre, Jun 22 '17 at 05:48
@Andre: All the tables are related via the EDIPI (which I had to remove to post here) but the field is still there. 1 person can only ever have 2 weapons issued to them. Now those two weapons can either be an rifle or a pistol or both. The rifle types are M16A4 or M4; the pistol types are M9 or M9A1. 1 person can have 1 or 2 weapons issued to them. — TroyPilewski, Jun 22 '17 at 13:33
re: Rifle vs. Pistol: it's not very future-proof to hardcode these values. You should have a table e.g. `tblWeapons (ID, WeaponType, isRifle)`, where this is configured. — Andre, Jun 22 '17 at 14:06
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/147382/discussion-between-troypilewski-and-andre). — TroyPilewski, Jun 22 '17 at 14:39
@Andre: Would you be able to explain a bit more.. I not exact sure what you mean? — TroyPilewski, Jun 22 '17 at 14:40

Filter and Merge in the Same Query

0 Answers0