OMG I'm brain dead.

I have 1300 records and many are duplicates of Internal Part Number.
One out of each group of duplicates has a unique field called Source and I selected that by "is not null" so it only pulls that record. Column 3.

I need to know how many other records have the same Internal Part Number as the one that is selected so I'm trying to count them and display. Column 2.

My attachment works but the answer is always 1. I want it to be the total number of records with matching Internal Part Numbers like 13, or 9, whatever. When I take out the "is not null" the count works properly. So with it it's just counting that one record.

How can I add this to the query without resorting to a form?

I DID IT!

It's a little funky but it works. I just duplicated the table and joined them to do the group records count then display the one chosen record. If someone has a more eloquent idea let me know. Thanx!:D

Always very saitisfying when you solve a problem in this world of hair pulling:D

You didn't display your entire query but I would guess that you have selected too many columns to get an acurate count. you are ending up with distinct rows which would always count to 1.

You didn't display your entire query but I would guess that you have selected too many columns to get an acurate count. you are ending up with distinct rows which would always count to 1.

I've gone back and worked with this more. Anything I add past the second column changes the quantity back to one. How do I get it to hold the total count for that group of records?

Columns 1 & 2 of my example work fine. Column three is the limiter that I need. I want it to find that one record that is not null but still gives me the total number of records with that part number. Any other suggestions? :confused:

Try unchecking the boxes you are using for selection criteria. Having them checked brings them into the result set and affects your counts.

Try unchecking the boxes you are using for selection criteria. Having them checked brings them into the result set and affects your counts.

Yes, it does, but I have to leave them checked or I can't see the output?

Here's my test data attached.

Now here's something interesting that I didn't know we could do.

Create an "Alias" of the database rather than duplicate data.

The query in this attachment doesn't work as expected either but it's getting there. My thought was to query the same database twice but once as an alias.

Here's the output from that query. As you can see it doubled up or something. I'm not quite sure why it did it.

The correct answer I am trying to get is:

5 301-423-520 ST
13 301-427-001 UC

There are 5 records total for one part number and one record of that group is an ST due to the Is Not Null.

There are 13 records total for the other part number and one record of that group is a UC due to the Is Not Null.

Just for grins I tried to merge a query that looked at the same table twice instead to break them up. No luck. Boy, I wouldn't think trying to do this in Access would be so difficult. :confused: All I want to do is pick one record and get the total of other like records. Any other suggestions?

By adding the table into the query a second time and not specifying a join, you are creating was is known as a Cartesian Product which matches each row of tblA to each row of tblA_1 so if tblA has 100 rows then the resulting query will contain 100 * 100 = 10,000 rows which is generally pretty useless.

Why is it that you need both summary and detail information presented in the same row of the query? Queries can return only one level of summarization. So, if you wanted to show a summary in the same row as details, you would create a query that summarizes to the level you need and then join that query back to the table so you can see the detail. However, this will look like the summary data has been duplicated.

You probably need to create a report and do the summarizing in the report rather than in the query.

Ooh, ooh, I did it. First I created the query to count the records that had similar part numbers. Then I created another query to select the one record and all the other related fields and joined the first one to it. All withing the same query. Here's a snap shot.

And here's the output.

Two lines that summarizes the DB of 18 records. Thanx for keeping me on track Pat! :)

its always a problem that you cant use a totals query to find a max of a column, say and ALSO show some other information for that row (eg the record id - always useful) - since if you do, the other information becomes part of the max criteria so you get loads of maxes instead of just one.

not sure if theres a way round this at all - be useful if there was