Counting Duplicates

[Pam]

Hi,

I have a database with unique serial numbers. Each serial number has
component part numbers. The part numbers can be the same for some of the
serial numbers, but not all. What I need to do is count all the duplicate
part numbers for each component? Not sure if this is clear, so here is an
example:

SN ComponentA ComponentB ComponentC
9999 1234 5556 7774
9998 1234 2525 7774
9997 1234 3436 9524

Would like query to return following:
Component A CountofCompA ComponentB CountofCompB ComponentC
CountofCompC
1234 3
7774 2

Is something like this possible?

Thanks in advance for any help,
Pam

 

[kingston via AccessMonster.com]

Try a totals query where you group by a component and then count the
component. So, create a query based on the table and click the totals button
(looks like a sideways M). The first field should be the Component and the
Total criteria should be Group By. The second field should be the same
component but the Totals criteria should be Count. You will have to create
separate queries for each component, but you can recombine the results in one
query if you want. However, you'll have to come up with an artificial way of
combining the results because it doesn't appear that there is any data
connection among your fields.

 

[Pam]

Thank you for the reply. I have already tried the solution you gave. I was
hoping there would be a way to find and count duplicates for multiple fields
on the same query. I have about 50 different components and so would have
to create that may queries. I decided to try using different queries for
each and then bringing them back together with the serial number, but when I
add the serial number I don't get anything in the query, just the header
names on the datasheet screen.

If you have any further ideas, I'm open for suggestions.

Thanks,
Pam

 

[kingston via AccessMonster.com]

You don't get anything with the serial number because the serial number is
unique and won't result in aggregated values; you'll have to trace the serial
numbers affected through the components themselves. Someone else might have
a better solution but I don't think you're going to get around creating 50
queries, either visually or in code.

You probably don't want to hear this but if you had structured your data
differently, you would not run into this problem. So instead of a different
table for each component, you'd add a field to indicate the component. In
fact, you can still do this with a union query and make one totals or
crosstab query that you might like better.

Thank you for the reply. I have already tried the solution you gave. I was
hoping there would be a way to find and count duplicates for multiple fields
on the same query. I have about 50 different components and so would have
to create that may queries. I decided to try using different queries for
each and then bringing them back together with the serial number, but when I
add the serial number I don't get anything in the query, just the header
names on the datasheet screen.

If you have any further ideas, I'm open for suggestions.

Thanks,
Pam

Try a totals query where you group by a component and then count the
component. So, create a query based on the table and click the totals

[quoted text clipped - 34 lines]

 

[Pam]

Actually, it wasn't that I had structured the data this way. It is in a
spreadsheet in rows with serial number in first column and each related
component in adjacent columns. What I was trying to do is to add pricing to
each item without having to go thru a very long spreadsheet. I was thinking
of having a table with prices for each component and then matching each
component to the price it represents. I need a count for a min/max
inventory standpoint. So, I was trying to use Access to bring all this
together in a more automated manner without scrolling thru a spreadsheet.

Pam

You don't get anything with the serial number because the serial number is
unique and won't result in aggregated values; you'll have to trace the
serial
numbers affected through the components themselves. Someone else might
have
a better solution but I don't think you're going to get around creating 50
queries, either visually or in code.

You probably don't want to hear this but if you had structured your data
differently, you would not run into this problem. So instead of a
different
table for each component, you'd add a field to indicate the component. In
fact, you can still do this with a union query and make one totals or
crosstab query that you might like better.

Thank you for the reply. I have already tried the solution you gave. I
was
hoping there would be a way to find and count duplicates for multiple
fields
on the same query. I have about 50 different components and so would have
to create that may queries. I decided to try using different queries for
each and then bringing them back together with the serial number, but when
I
add the serial number I don't get anything in the query, just the header
names on the datasheet screen.

If you have any further ideas, I'm open for suggestions.

Thanks,
Pam

Try a totals query where you group by a component and then count the
component. So, create a query based on the table and click the totals

[quoted text clipped - 34 lines]

Thanks in advance for any help,
Pam

 

[kingston via AccessMonster.com]

If all you want is a count, does it matter that 7774 was used as ComponentC?
Going back to the use of a union query, you can do something like this:

SELECT SN, "A" AS Type, ComponentA FROM Table
UNION SELECT SN, "B" AS Type, Component B FROM Table
...

This will restructure your data and allow for easier counting and pricing
since you'll only have three fields to deal with. HTH

Actually, it wasn't that I had structured the data this way. It is in a
spreadsheet in rows with serial number in first column and each related
component in adjacent columns. What I was trying to do is to add pricing to
each item without having to go thru a very long spreadsheet. I was thinking
of having a table with prices for each component and then matching each
component to the price it represents. I need a count for a min/max
inventory standpoint. So, I was trying to use Access to bring all this
together in a more automated manner without scrolling thru a spreadsheet.

Pam

You don't get anything with the serial number because the serial number is
unique and won't result in aggregated values; you'll have to trace the

[quoted text clipped - 32 lines]

 

[Pam]

Since these are in columns as to what type of component and I'll have to go
back with the info related to specific serial number, wouldn't I need to
keep them together as a complete record?

If all you want is a count, does it matter that 7774 was used as
ComponentC?
Going back to the use of a union query, you can do something like this:

SELECT SN, "A" AS Type, ComponentA FROM Table
UNION SELECT SN, "B" AS Type, Component B FROM Table
..

This will restructure your data and allow for easier counting and pricing
since you'll only have three fields to deal with. HTH

Actually, it wasn't that I had structured the data this way. It is in a
spreadsheet in rows with serial number in first column and each related
component in adjacent columns. What I was trying to do is to add pricing
to
each item without having to go thru a very long spreadsheet. I was
thinking
of having a table with prices for each component and then matching each
component to the price it represents. I need a count for a min/max
inventory standpoint. So, I was trying to use Access to bring all this
together in a more automated manner without scrolling thru a spreadsheet.

Pam

You don't get anything with the serial number because the serial number
is
unique and won't result in aggregated values; you'll have to trace the

[quoted text clipped - 32 lines]

Thanks in advance for any help,
Pam

 

[Guest]

There is probably an easier way but here is a series of queries that does it.

Pam ---
SELECT YourTable.SN, "A" AS Component, YourTable.[ComponentA] AS [ZZ]
FROM YourTable
UNION ALL SELECT YourTable.SN, "B" AS Component, YourTable.[ComponentB] AS
[ZZ]
FROM YourTable
UNION ALL SELECT YourTable.SN, "C" AS Component, YourTable.[ComponentC] AS
[ZZ]
FROM YourTable;

Pam_1 ---
SELECT Pam.Component, Pam.ZZ, Count(Pam.ZZ) AS CountOfZZ
FROM Pam
GROUP BY Pam.Component, Pam.ZZ;

Pam_1_Crosstab ---
TRANSFORM First([ZZ] & " " & [CountOfZZ]) AS Expr2
SELECT Pam_1.ZZ
FROM Pam_1
WHERE (((Pam_1.ZZ) Is Not Null))
GROUP BY Pam_1.ZZ
PIVOT "Component " & [Component];

SELECT Pam_1_Crosstab.[Component A], Pam_1_Crosstab.[Component B],
Pam_1_Crosstab.[Component C]
FROM Pam_1_Crosstab
WHERE (((Pam_1_Crosstab.ZZ) Is Not Null))
UNION SELECT Pam_1_Crosstab.[Component A], Pam_1_Crosstab.[Component B],
Pam_1_Crosstab.[Component C]
FROM Pam_1_Crosstab
WHERE (((Pam_1_Crosstab.ZZ) Is Not Null))
UNION SELECT Pam_1_Crosstab.[Component A], Pam_1_Crosstab.[Component B],
Pam_1_Crosstab.[Component C]
FROM Pam_1_Crosstab
WHERE (((Pam_1_Crosstab.ZZ) Is Not Null));

If all you want is a count, does it matter that 7774 was used as ComponentC?
Going back to the use of a union query, you can do something like this:

SELECT SN, "A" AS Type, ComponentA FROM Table
UNION SELECT SN, "B" AS Type, Component B FROM Table
...

This will restructure your data and allow for easier counting and pricing
since you'll only have three fields to deal with. HTH

Actually, it wasn't that I had structured the data this way. It is in a
spreadsheet in rows with serial number in first column and each related
component in adjacent columns. What I was trying to do is to add pricing to
each item without having to go thru a very long spreadsheet. I was thinking
of having a table with prices for each component and then matching each
component to the price it represents. I need a count for a min/max
inventory standpoint. So, I was trying to use Access to bring all this
together in a more automated manner without scrolling thru a spreadsheet.

Pam

You don't get anything with the serial number because the serial number is
unique and won't result in aggregated values; you'll have to trace the

[quoted text clipped - 32 lines]

Thanks in advance for any help,
Pam

 

[kingston via AccessMonster.com]

The union query keeps all of the data and just restructures it. So for
example, if you wanted the components for serial number 9998, you'd get a
number of records instead of just one. Then, you can do a crosstab and/or
totals query to get the format that you want. I suggest that you just try it
to see how it works. The result may seem cumbersome but this type of a list
is more efficient for sparse data sets and allows for easier manipulation in
many cases.

Since these are in columns as to what type of component and I'll have to go
back with the info related to specific serial number, wouldn't I need to
keep them together as a complete record?

If all you want is a count, does it matter that 7774 was used as
ComponentC?

[quoted text clipped - 26 lines]

 

[Guest]

Karl and Kingston,
Thank you both for the information you provided. Very helpful and appreciated!

Karl,

At first I was intimidated by the queries you listed and then decided to
tackle them to see if I could make them work. I replaced all field and table
names and I love the results. Actually, I like writing SQL for queries
instead of placing everything on the query grid.

I do have one other question. If I have 25 components, will there be a
problem with maximum length of query with listing each one in SQL statement?
If there is an easier way to do this or if this could create a problem, would
you please let me know.

I will try putting the prices to the part numbers and hopefully all will go
well.
Thanks so much for writing these queries out for me. It is very much
appreciated!!
Pam

There is probably an easier way but here is a series of queries that does it.

Pam ---
SELECT YourTable.SN, "A" AS Component, YourTable.[ComponentA] AS [ZZ]
FROM YourTable
UNION ALL SELECT YourTable.SN, "B" AS Component, YourTable.[ComponentB] AS
[ZZ]
FROM YourTable
UNION ALL SELECT YourTable.SN, "C" AS Component, YourTable.[ComponentC] AS
[ZZ]
FROM YourTable;

Pam_1 ---
SELECT Pam.Component, Pam.ZZ, Count(Pam.ZZ) AS CountOfZZ
FROM Pam
GROUP BY Pam.Component, Pam.ZZ;

Pam_1_Crosstab ---
TRANSFORM First([ZZ] & " " & [CountOfZZ]) AS Expr2
SELECT Pam_1.ZZ
FROM Pam_1
WHERE (((Pam_1.ZZ) Is Not Null))
GROUP BY Pam_1.ZZ
PIVOT "Component " & [Component];

SELECT Pam_1_Crosstab.[Component A], Pam_1_Crosstab.[Component B],
Pam_1_Crosstab.[Component C]
FROM Pam_1_Crosstab
WHERE (((Pam_1_Crosstab.ZZ) Is Not Null))
UNION SELECT Pam_1_Crosstab.[Component A], Pam_1_Crosstab.[Component B],
Pam_1_Crosstab.[Component C]
FROM Pam_1_Crosstab
WHERE (((Pam_1_Crosstab.ZZ) Is Not Null))
UNION SELECT Pam_1_Crosstab.[Component A], Pam_1_Crosstab.[Component B],
Pam_1_Crosstab.[Component C]
FROM Pam_1_Crosstab
WHERE (((Pam_1_Crosstab.ZZ) Is Not Null));

If all you want is a count, does it matter that 7774 was used as ComponentC?
Going back to the use of a union query, you can do something like this:

SELECT SN, "A" AS Type, ComponentA FROM Table
UNION SELECT SN, "B" AS Type, Component B FROM Table
...

This will restructure your data and allow for easier counting and pricing
since you'll only have three fields to deal with. HTH

Actually, it wasn't that I had structured the data this way. It is in a
spreadsheet in rows with serial number in first column and each related
component in adjacent columns. What I was trying to do is to add pricing to
each item without having to go thru a very long spreadsheet. I was thinking
of having a table with prices for each component and then matching each
component to the price it represents. I need a count for a min/max
inventory standpoint. So, I was trying to use Access to bring all this
together in a more automated manner without scrolling thru a spreadsheet.

Pam

You don't get anything with the serial number because the serial number is
unique and won't result in aggregated values; you'll have to trace the
[quoted text clipped - 32 lines]
Thanks in advance for any help,
Pam

 

[Guest]

I do have one other question. If I have 25 components, will there be a
problem with maximum length of query with listing each one in SQL statement?
You need to post this quest in a new thread as I can not answer it.

Karl and Kingston,
Thank you both for the information you provided. Very helpful and appreciated!

Karl,

At first I was intimidated by the queries you listed and then decided to
tackle them to see if I could make them work. I replaced all field and table
names and I love the results. Actually, I like writing SQL for queries
instead of placing everything on the query grid.

I do have one other question. If I have 25 components, will there be a
problem with maximum length of query with listing each one in SQL statement?
If there is an easier way to do this or if this could create a problem, would
you please let me know.

I will try putting the prices to the part numbers and hopefully all will go
well.
Thanks so much for writing these queries out for me. It is very much
appreciated!!
Pam

There is probably an easier way but here is a series of queries that does it.

Pam ---
SELECT YourTable.SN, "A" AS Component, YourTable.[ComponentA] AS [ZZ]
FROM YourTable
UNION ALL SELECT YourTable.SN, "B" AS Component, YourTable.[ComponentB] AS
[ZZ]
FROM YourTable
UNION ALL SELECT YourTable.SN, "C" AS Component, YourTable.[ComponentC] AS
[ZZ]
FROM YourTable;

Pam_1 ---
SELECT Pam.Component, Pam.ZZ, Count(Pam.ZZ) AS CountOfZZ
FROM Pam
GROUP BY Pam.Component, Pam.ZZ;

Pam_1_Crosstab ---
TRANSFORM First([ZZ] & " " & [CountOfZZ]) AS Expr2
SELECT Pam_1.ZZ
FROM Pam_1
WHERE (((Pam_1.ZZ) Is Not Null))
GROUP BY Pam_1.ZZ
PIVOT "Component " & [Component];

SELECT Pam_1_Crosstab.[Component A], Pam_1_Crosstab.[Component B],
Pam_1_Crosstab.[Component C]
FROM Pam_1_Crosstab
WHERE (((Pam_1_Crosstab.ZZ) Is Not Null))
UNION SELECT Pam_1_Crosstab.[Component A], Pam_1_Crosstab.[Component B],
Pam_1_Crosstab.[Component C]
FROM Pam_1_Crosstab
WHERE (((Pam_1_Crosstab.ZZ) Is Not Null))
UNION SELECT Pam_1_Crosstab.[Component A], Pam_1_Crosstab.[Component B],
Pam_1_Crosstab.[Component C]
FROM Pam_1_Crosstab
WHERE (((Pam_1_Crosstab.ZZ) Is Not Null));

If all you want is a count, does it matter that 7774 was used as ComponentC?
Going back to the use of a union query, you can do something like this:

SELECT SN, "A" AS Type, ComponentA FROM Table
UNION SELECT SN, "B" AS Type, Component B FROM Table
...

This will restructure your data and allow for easier counting and pricing
since you'll only have three fields to deal with. HTH


Pam wrote:
Actually, it wasn't that I had structured the data this way. It is in a
spreadsheet in rows with serial number in first column and each related
component in adjacent columns. What I was trying to do is to add pricing to
each item without having to go thru a very long spreadsheet. I was thinking
of having a table with prices for each component and then matching each
component to the price it represents. I need a count for a min/max
inventory standpoint. So, I was trying to use Access to bring all this
together in a more automated manner without scrolling thru a spreadsheet.

Pam

You don't get anything with the serial number because the serial number is
unique and won't result in aggregated values; you'll have to trace the
[quoted text clipped - 32 lines]
Thanks in advance for any help,
Pam