Using MS Access/Duplicate values


I have a Register-form the data source is from four different tables (Users, Demand, Orders, Actions), One demand have one or many orders:

User Name: abc / Total Demands ( 4 )

DemandNo   OrderNo   Amount  Supplier   Action
001        101       500     abc        order placed
002        102       1000    abc        waiting for shipment
002        103       200     abc        doc passed for payment
003        104       300     abc        file closed

In the above I have (3 demands) and (4 orders), when I count the demands the result is 4 but actually the total demands are 3, so I need to remove the count of duplicate value (002).

Hello Sher,

-In order to get rid of duplicates you need to create another query based on your existing query
-In your new query you need to use the Group By aggregate function for DemandNo and use Count on OrderNo
-In your query toolbar click on the Sigma icon to activate the aggregation feature

Best regards
Manish Batola

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Manish Batola


ALL MICROSOFT ACCESS DESIGN AND Development QUESTIONS which occur in a Access Projects Lifecycle. Can offer constructive help on Designing Advanced MS-ACCESS Applications such CRM/FRM(Follow-up),HSE-Health,Safety & Environmental Applications,School Management Systems,Hotel Software, Hospital Software,Chemicals Indenting,Import/Export,MRP - Manufacturing Resource Planning, ERP - Enterprise Resource Planning, Automotive Management Systems, Business Intelligence for Oil & Gas Drilling Operations, CRM,KPI- Key Performance Indicators,HealthCare, Hospitality, Constructions, Loyalty Clubs, Call Centers, Travel & Tourism, Educational Institutions, Industrial Strength Invoicing and many more being added every year.


29+ yrs in the Software industry with 23yrs completely dedicated on more than 350 Various Microsoft Access Projects from different Industry Sector[s]. I'm using Access ever since it was born.

Worked in various multinationals catering from Aerospace, Oil & Gas etc.

-BSc - Computer Science -MSAP (Master in Software & Applications Programming) -BPRE (Business Process Re-engineering Engineer) -SQAE (Software Quality Assurance Engineer) -STIE (Software Testing & Inspection Engineer) -Project Engineer (Software Development)

Past/Present Clients
Catered to various clients in the following industries • Oil & Natural Gas (BPM/CRM/Access Development) • Sales / Marketing (CRM) • Automotive (Automobile Dealers) • Logistics & Indenters (Import/Export) • HealthCare / Hospitals • Hotels 3* & 5* • Restaurants • Construction Co’s (Project Billing) • Clubs (Concept Selling) • Shoe-Chains • Solicitors/Lawyers • Call Centers • Aerospace (Preventive Maintenance) • Travel & Tourism • Computer Vendors • Manufacturing • Music Shops • Education • Data Warehousing • Health,Safety & Environmental Applications • Mud Engineering • School Management Software

©2016 All rights reserved.