Sub Query

[Maldrid]

Sub Query [Resolved]

Does anyone know if it is possible to use the DISTINCT keyword on one particular field, but yet pull up multiple fields in the query? Or another way, where your query will pull up records based on any unique value for a certain field, but also pull up a bunch of other fields as well. I am pretty sure you can do this using some kind of sub query, but I am not so good with those so if someone can help me set it up it would be great.

Here is my query

sSQLQuery = "SELECT [Max Config], Class FROM AircraftInfo WHERE KitchenID = '" & txtKitchenID _
                        & "' AND [Customer ID] = '" & txtCustomerID _
                        & "' AND [Aircraft] = '" & "" & rsFlightRecord.Fields("Aircraft") _
                        & "' AND [Model] = '" & "" & rsFlightRecord.Fields("Model") _
                        & "' AND [Effective Date] <= #" & txtFlightDate _
                        & "# AND ([Discontinue Date] >= #" & txtFlightDate _
                        & "# OR [Discontinue Date] IS NULL) ORDER BY Class"

I want to grab all records that have a unique Class only. If I were to use Distinct in front, it would grab any record that has a distinct max config and class, but I just want it based on class.

 

[Maldrid]

I think you have it a little confused. Each class has a max config associated with it. So for every class record it grabs, I want it to grab the max config along with it. But I want it to only grab unique class records. So say if I have two records for First Class, I only want it to grab one of the records and grab the max config for that record as well.



[Edit]
I think a picture would help

Class Max Config
10 F/C 12
10 F/C 10
20 B/C 34
30 Y/C 145

So I want the query to grab one record for each type of class, I don't care what the max config. So in this example there are two records for class 10 F/C. I don't care which one of these records it grabs, as long as it grabs only one of them and it's max config.

 

[crook]

If you don't care which [max config] value is returned, then replace the [max config] field with:

maxConfig: max([max config])

Or alternately, you can select max in the totals row of the query.

 

[Maldrid]

Sorry, I guess I didn't say what I really meant. What I wanted to say is I don't care if it grabs the first or second record in that particular example. The example I showed is only trying to grab two fields, but in reality I am trying to grab several fields, and if I use Max([Max Config]) it won't work because that field won't match with the rest of the record. I did find a solution though, but it wasn't exactly what I had in mind. I went and did a query for all distinct classes. Then I looped through my recordset grabbing one record for each class. I guess it works, thanks for those that tried to help.