Query Comparing Multiple Columns based on different data in each column in Access SQL

user_14750286 0
Viewed 46

Good Morning/Afternoon,

I will attempt to phrase this correctly. I have a table with multiple fields that I am trying to create certain output results on in a query.

QueryA:

Property Bldg Unit Flag1 Date1 Cost1
1 A 1 Y 1/1/2022 $1,000
2 A 1 Y 1/1/2022 $2,000
2 A 1 N 1/1/2022 $1,000
3 A 1 N 1/1/2022 $1,000
3 A 1 N 1/1/2022 $1,000
4 A 1 Y 5/5/2022 $1,000
4 A 1 N 1/1/2022 $1,000
5 A 1 Y 1/1/2022 $1,000
5 A 1 N 1/1/2022 $1,000

I would like to create the desired output below:

Property Bldg Unit Flag1 Date1 Cost1
1 A 1 Y 1/1/2022 $1,000
2 A 1 Y 1/1/2022 $2,000
3 A 1 N 1/1/2022 $1,000
4 A 1 Y 5/5/2022 $1,000
5 A 1 Y 1/1/2022 $1,000

Essentially if there is a Y flag then the table should pull that row. Barring that a single N field should be selected. I've tried Max(Flag1) but both Y and N rows are still pulled whenever another value in a different column is different.

Any help would be appreciated.

sql
1 Answers

Access is way more limited than I could ever imagine hehe... Check if you can make this work, I used the strategy to create tables to save the data in a way Access accepts the queries.

First create this table:

SELECT Property,
       Bldg,
       Unit,
       Max(Flag1) AS Flag_1       
INTO BaseTable
FROM MyTable
GROUP BY Property,
         Bldg,
         Unit

Then create this other table:

SELECT BaseTable.Property,
       BaseTable.Bldg,
       BaseTable.Unit,
       BaseTable.Flag_1,
       MAX(MyTable.Date1) AS Date_1
INTO BaseTableWithDate
FROM MyTable
INNER JOIN BaseTable ON BaseTable.Property = MyTable.Property
                  AND BaseTable.Bldg = MyTable.Bldg
                  AND BaseTable.Unit = MyTable.Unit
                  AND BaseTable.Flag_1 = MyTable.Flag1             
GROUP BY BaseTable.Property,
         BaseTable.Bldg,
         BaseTable.Unit,
         BaseTable.Flag_1

And finally you can run the query that returns what you need:

SELECT BaseTableWithDate.Property,
       BaseTableWithDate.Bldg,
       BaseTableWithDate.Unit,
       BaseTableWithDate.Flag_1,
       BaseTableWithDate.Date_1,
       MAX(MyTable.Cost1)
FROM MyTable
INNER JOIN BaseTableWithDate ON BaseTableWithDate.Property = MyTable.Property
                      AND BaseTableWithDate.Bldg = MyTable.Bldg
                      AND BaseTableWithDate.Unit = MyTable.Unit
                      AND BaseTableWithDate.Flag_1 = MyTable.Flag1
                      AND BaseTableWithDate.Date_1 = MyTable.Date1                     
GROUP BY BaseTableWithDate.Property,
         BaseTableWithDate.Bldg,
         BaseTableWithDate.Unit,
         BaseTableWithDate.Flag_1,
         BaseTableWithDate.Date_1
user_192235860
Related