how to query Select Distinct field + showing other fields? (1 Viewer)

[deanvilar]

Gurus,

how to query Select Distinct field + showing other fields?

e.g.:

SELECT DISTINCTROW assetMovementTable.assetNo, assetMovementTable.moveCode, assetMovementTable.compCode, assetMovementTable.compCodeDesc, assetMovementTable.assetDesc, assetMovementTable.equipType, assetMovementTable.equipManufacturer, assetMovementTable.equipModel, assetMovementTable.constYear, assetMovementTable.plateNo
FROM assetMovementTable
WHERE (assetMovementTable.equipType)=Forms!ASSET_PRINT_MODULE!txtEquipType And (assetMovementTable.toJobCode)<>"EXPORTED" And (assetMovementTable.toJobCode)<>"PSP" And (assetMovementTable.toJobCode)<>"FOR PSP" And (assetMovementTable.toJobCode)<>"DRILLING - TO" And (assetMovementTable.toJobCode)<>"DRILLING - FROM" And (assetMovementTable.toJobCode)<>"ATYRAU ERSAI" And (assetMovementTable.toJobCode)<>"ATYRAU SAIPEM" And (assetMovementTable.toJobCode)<>"BARGE E400"
ORDER BY assetMovementTable.assetNo;

 

[Pat Hartman]

Your query doesn't give me any clue what your problem is. Once you start showing additional fields, you can end up with "duplicates" in the previously "distinct" part of the query. Please tell us in words what you are trying to do and the results you are experiencing. Data lists might summarize it.

 

[deanvilar]

one field to be distinct but pull data from the other fields without creating any additional rows

0001 425525 TOYOTA PRADO
0002 425525 TOYOTA PRADO
0001 623455 LADA NIVA
0001 623111 OFFICE BUILDING
0002 623111 OFFICE BUILDING

as you can see ..0001,0002 an incrementing number for asset number 425525 I want to eliminate duplicates by taking the last increment number e.g. 0002 425525 TOYOTA PRADO .. i hope its clear sir

 

[Brianwarnock]

On the data shown a Totals query max on increment and group by on the rest should do the trick.

BTW. Pat is a lady, you are not the first to make the mistake and she takes no offence.

Brian

 

[deanvilar]

oh am so sorry Ms. Pat ... =)
sir @Brian can you show me a sample code on both? thanks

 

[Brianwarnock]

I'm about to go walking so don't have much time but this is a basic approach to things like finding the data to go with a particular max value be it a date or your increment number. Normally two queries or a sub query are used.
Using two stacked queries the first is a simple totals query , in the design grid select the field you wish to find the max of and the group field that applies to so in your case asset number and increment

Select assetnumber, max(increment) as maxofincrement
From mytable
Group by assetnumber

This can then be joined back to the table on these two fields to select any other data required.

You run the last query in the stack.

Brian

 

[Pat Hartman]

Unless there are more columns that can't actually be grouped, a simple totals query would do the job.
in the QBE, select the three columns
Press the sigma button to create the totals query. Access automatically adds Group By to every column.
Change Group By to Max for the incrementing field.
Save
Run

 

[deanvilar]

I GOT IT!!!! thanks guys!