98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Expression builder Access 2007
Expression builder Access 2007
Resolved · High Priority · Version 2007
Thu 13 Jun 2013, 15:24Delegate Stephen said...
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
Fri 14 Jun 2013, 12:50Trainer Doug said...
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
Fri 14 Jun 2013, 13:38Delegate Stephen said...
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 :-(
Mon 17 Jun 2013, 09:15Trainer Doug said...
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
Mon 24 Jun 2013: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Hiding rows and columnsTo hide a column or row in Access, you need to minimise the column or row. This can be done by placing your mouse on the right edge of a column, wait for the mouse to change to a double aroow and then drag to minimise the column |
