Stephen has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Expression builder Access 2007
I am trying to write into a query to count records. I can get it to count the records in the database but I want to remove any duplicates for example. Database has 100 trades with 10 counterpartys. When viewing results in a pivot if I count the counterparties it always comes up with 100, I need it to say 10
I have managed it by exporting data to excel and re creating the query but I needed to add this formula to the end of the row "=IF(B2=B3,0,1)" Then just counted the "1" and it brings up the correct data. Somehow there must be a way of doing this in a Query or via SQL
Unfortunately my SQL friend is on holiday for a week
Thanks
Stephen
RE: Expression builder Access 2007
Hi Stephen
In Access there's a query property called Unique Values.
(right click, properties when in query design view)
Setting this property to Yes removes duplicate records in a query result.
In SQL this adds DISTINCT after the Select staement.
For example the following creates a unique list of departments from an employee table.
SELECT DISTINCT Employee_information.Department
FROM Employee_information;
Hope this helps so you don't have to manipulate the data in Excel.
Doug Dunn
Best STL
RE: Expression builder Access 2007
It didn't work tried a couple of times
My SQL states
SELECT Holdings.ID, Holdings.CISID, Holdings.ClientGripsCode, Holdings.ISIN, Holdings.SecurityDescription, Holdings.QuantitywithCorporateActions, Holdings.Price, Holdings.Value, Holdings.CCY, Holdings.ValueGBPwithcorporateactions, Holdings.DepotHeld, Holdings.SecuritiesHeld, Holdings.Returnprocedure, IIf([Returnsettlementdate] Is Null,[ValueGBPwithcorporateactions]) AS [Open Assets], IIf([Returnsettlementdate] Is Not Null,[ValueGBPwithcorporateactions]) AS [Returned Assets], Client_Data.ClaimStatus, Holdings.Returnsettlementdate, IIf([SecuritiesHeld]="Held",[ValueGBPwithcorporateactions]) AS [Sum Held], IIf([SecuritiesHeld]="Not-Held",[ValueGBPwithcorporateactions]) AS [Sum Not Held], Count(Client_Data.CISID) AS CountOfCISID
FROM Client_Data INNER JOIN Holdings ON Client_Data.CISID = Holdings.CISID
GROUP BY Holdings.ID, Holdings.CISID, Holdings.ClientGripsCode, Holdings.ISIN, Holdings.SecurityDescription, Holdings.QuantitywithCorporateActions, Holdings.Price, Holdings.Value, Holdings.CCY, Holdings.ValueGBPwithcorporateactions, Holdings.DepotHeld, Holdings.SecuritiesHeld, Holdings.Returnprocedure, IIf([Returnsettlementdate] Is Null,[ValueGBPwithcorporateactions]), IIf([Returnsettlementdate] Is Not Null,[ValueGBPwithcorporateactions]), Client_Data.ClaimStatus, Holdings.Returnsettlementdate, IIf([SecuritiesHeld]="Held",[ValueGBPwithcorporateactions]), IIf([SecuritiesHeld]="Not-Held",[ValueGBPwithcorporateactions]);
It is the "Count(Client_Data.CISID) AS CountOfCISID" that I want to count as the distinc value
There are 1713 lines of data in holdings and 273 lines of data in CISID. everytime I run it it counts the CISID in holdings, which is correct but I want it to count the CISID in holdings but remove the duplicates leaving distinct. The answer should be 273.
If not possible then I guess it will have to be excel :-(
RE: Expression builder Access 2007
Hi Stephen
My suggestion was to creatre uniques records in a single table query. Sorry it didn't apply to your example. You may still have to use Excel. There is an option in Excel (Data, Remove Duplicates) which could avoids the need to create and count the '1's.
Regards
Doug
