Expression builder Access 2007

Resolved · High Priority · Version 2007

Thu 13 Jun 2013, 15:24 Delegate 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:50 Trainer 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:38 Delegate 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:15 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Access tip:

Hiding rows and columns

To 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

View all Access hints and tips