Duplicate Count

[karl009]

Hi,

I am using a spreadsheet that keeps a list of price requests, within that sheets is a basic formula that keeps a count to how many duplicate price requests there have been against a part number, how ever the spreadsheet is getting large and slow so Access would be better to handle a lot of data.

The formula used in excel is: =COUNTIF (B:B,B3340)

I have attached an image of how the spreadsheet looks;

I have made a query in access to select all the data including Part Number; I have tried a number of things for example;

Duplicate Check: Count ([Part_No])

The aim is to have a count of how many of that part number in the database next to each data entry, or can a report be made to show a list of part numbers and the number of times they are in the database?

Thanks
Karl

 

[Datagopher]

You can use Access's built in query wizard and select "Find Duplicates Query Wizard". Just add the part number field to the wizard and go into design view and change the criteria from >1 to >=1.

Dan
Access Development

Hi,

I am using a spreadsheet that keeps a list of price requests, within that sheets is a basic formula that keeps a count to how many duplicate price requests there have been against a part number, how ever the spreadsheet is getting large and slow so Access would be better to handle a lot of data.

The formula used in excel is: =COUNTIF (B:B,B3340)

I have attached an image of how the spreadsheet looks;

I have made a query in access to select all the data including Part Number; I have tried a number of things for example;

Duplicate Check: Count ([Part_No])

The aim is to have a count of how many of that part number in the database next to each data entry, or can a report be made to show a list of part numbers and the number of times they are in the database?

Thanks
Karl

 

[karl009]

Hi,

I have been able to create the query to get the counts of duplicate data, however am having trouble integrating this into the a form where data is displayed and edited.

I have uploaded the database that I have been building; if you open the form "frm_Edit_Price_Request" you will see the subform I would like to place the duplicate count in there.

At present the form is working directly from the table, if I change that to a query and add in the duplicate counter then I am unable to edit the data.

How can this be solved?

Thanks
Karl

 

[Datagopher]

You have your data grouped, so it's read only. Why would you need to edit like that? Are you trying to get rid of duplicates or set all records in a set of duplicates to a set value? Let me know what you're trying to do, your end result, so I tell you the best route to take.

Dan
Access Development

Hi,

I have been able to create the query to get the counts of duplicate data, however am having trouble integrating this into the a form where data is displayed and edited.

I have uploaded the database that I have been building; if you open the form "frm_Edit_Price_Request" you will see the subform I would like to place the duplicate count in there.

At present the form is working directly from the table, if I change that to a query and add in the duplicate counter then I am unable to edit the data.

How can this be solved?

Thanks
Karl

 

[karl009]

Hi,

All the number is for is to check the number of times a duplicate Part Number appears, so when someone is using the database they can see the about of times a price has been requested for that Part Number.

In the example database I have the Part Number "ABC123" twice, so the query counts that twice and the other two once, I would like to have them numbers in the form so they can be seen this can then prompt the user to look at the other information which maybe current enough, so they don’t need to request the information again.

Thanks for your help.
Karl

 

[Datagopher]

A real simple way to do it would be to just reference the current part # in your duplicates query and have a button for them to push, which would show them the # of duplicates.

Dan
Access Development

Hi,

All the number is for is to check the number of times a duplicate Part Number appears, so when someone is using the database they can see the about of times a price has been requested for that Part Number.

In the example database I have the Part Number "ABC123" twice, so the query counts that twice and the other two once, I would like to have them numbers in the form so they can be seen this can then prompt the user to look at the other information which maybe current enough, so they don’t need to request the information again.

Thanks for your help.
Karl