How to Create a Query Using Multiple Groupings and Criteria

[jgraves]

Using Access 2003, I want to write a query against information in the
following sample format:

Manufacturer Item Number Source System
Acme A-1 GoodSoft
Acme A-1 BadSoft
Acme A-2 BadSoft
Acme A-2 ReallyBadSoft
BooInc. B-1 GoodSoft
BooInc. B-1 BadSoft
BooInc. B-2 GoodSoft

In the example I created above, I have a product data file that contains
information on various products, such as the identification number for the
item, the manufacturer who created the product, and the software system that
the information came from. A product may have more than one product record
with the same Item Number because they were extracted from different systems.

I want to write a query that will tell me the following:
1. How many products have a product record created by the "GoodSoft" system?
2. How many products have no records that were created by the "GoodSoft"
system?

I want to group them by manufacturer, like below. Using the sample data
above, I would want to see the following result:

Mfr Name GoodSoft Products Non-GoodSoft Products
Acme 1 1
BooInc. 2 0

 

[KARL DEWEY]

Sounds like a crosstab query will give you the information for 1.

If there is no record where would the query get the product information? Do
you have other tables besides what you posted?

 

[KARL DEWEY]

TRANSFORM Count(jgraves.[Item Number]) AS [CountOfItem Number]
SELECT jgraves.Manufacturer, Count(jgraves.[Item Number]) AS [Total Of Item
Number]
FROM jgraves
GROUP BY jgraves.Manufacturer
PIVOT jgraves.[Source System];

 

[KARL DEWEY]

You can put your criteria entry in an unbound form and reference the objects
of the form as criteria like this ---
[Forms]![YourFormName]![YourTextbox]
You will need to open the form, enter data, then run the query. You can have
a command button on the form.

 

[jgraves]

Karl,
Excellent information on the crosstab queries. Thank you for that.

You are missing a subtle nuance in the information I am looking for: I want
the query to consider all records with the same item number as one "count".
(Revisit the sample results table for clarification)
The method you describe would bring the following result for my data:
Acme Total Records: 4, GoodSys Products: 1, Non GoodSys Products: 3
I want it to return:
Acme Total Records: 2, GoodSys Products: 1, Non GoodSys Products: 1

Meaning that there are 2 products represented (by 4 records), and I want to
know for each unique product, is there a "GoodSys" qualifier in one of its
records? If yes, consider the count to go up 1 for the GoodSys Product Count.
If there is no, then consider the count for "non" GoodSys Products to go up 1.

This is really a hard one to describe. Thanks for the effort!

You can put your criteria entry in an unbound form and reference the objects
of the form as criteria like this ---
[Forms]![YourFormName]![YourTextbox]
You will need to open the form, enter data, then run the query. You can have
a command button on the form.

--
KARL DEWEY
Build a little - Test a little

Using Access 2003, I want to write a query against information in the
following sample format:

Manufacturer Item Number Source System
Acme A-1 GoodSoft
Acme A-1 BadSoft
Acme A-2 BadSoft
Acme A-2 ReallyBadSoft
BooInc. B-1 GoodSoft
BooInc. B-1 BadSoft
BooInc. B-2 GoodSoft

In the example I created above, I have a product data file that contains
information on various products, such as the identification number for the
item, the manufacturer who created the product, and the software system that
the information came from. A product may have more than one product record
with the same Item Number because they were extracted from different systems.

I want to write a query that will tell me the following:
1. How many products have a product record created by the "GoodSoft" system?
2. How many products have no records that were created by the "GoodSoft"
system?

I want to group them by manufacturer, like below. Using the sample data
above, I would want to see the following result:

Mfr Name GoodSoft Products Non-GoodSoft Products
Acme 1 1
BooInc. 2 0

 

[KARL DEWEY]

These two queries will give you the results you said you wanted.
jgraves_1 ---
SELECT jgraves.Manufacturer, IIf([Source System]="GoodSoft",[Source
System],"Non-GoodSoft") AS GoodStuff
FROM jgraves
GROUP BY jgraves.Manufacturer, IIf([Source System]="GoodSoft",[Source
System],"Non-GoodSoft");

TRANSFORM Count(jgraves_1.GoodStuff) AS CountOfGoodStuff
SELECT jgraves_1.Manufacturer, Count(jgraves_1.GoodStuff) AS [Total Of Item
Number]
FROM jgraves_1
GROUP BY jgraves_1.Manufacturer
PIVOT jgraves_1.GoodStuff;

--
KARL DEWEY
Build a little - Test a little

Karl,
Excellent information on the crosstab queries. Thank you for that.

You are missing a subtle nuance in the information I am looking for: I want
the query to consider all records with the same item number as one "count".
(Revisit the sample results table for clarification)
The method you describe would bring the following result for my data:
Acme Total Records: 4, GoodSys Products: 1, Non GoodSys Products: 3
I want it to return:
Acme Total Records: 2, GoodSys Products: 1, Non GoodSys Products: 1

Meaning that there are 2 products represented (by 4 records), and I want to
know for each unique product, is there a "GoodSys" qualifier in one of its
records? If yes, consider the count to go up 1 for the GoodSys Product Count.
If there is no, then consider the count for "non" GoodSys Products to go up 1.

This is really a hard one to describe. Thanks for the effort!

You can put your criteria entry in an unbound form and reference the objects
of the form as criteria like this ---
[Forms]![YourFormName]![YourTextbox]
You will need to open the form, enter data, then run the query. You can have
a command button on the form.

--
KARL DEWEY
Build a little - Test a little

Using Access 2003, I want to write a query against information in the
following sample format:

Manufacturer Item Number Source System
Acme A-1 GoodSoft
Acme A-1 BadSoft
Acme A-2 BadSoft
Acme A-2 ReallyBadSoft
BooInc. B-1 GoodSoft
BooInc. B-1 BadSoft
BooInc. B-2 GoodSoft

In the example I created above, I have a product data file that contains
information on various products, such as the identification number for the
item, the manufacturer who created the product, and the software system that
the information came from. A product may have more than one product record
with the same Item Number because they were extracted from different systems.

I want to write a query that will tell me the following:
1. How many products have a product record created by the "GoodSoft" system?
2. How many products have no records that were created by the "GoodSoft"
system?

I want to group them by manufacturer, like below. Using the sample data
above, I would want to see the following result:

Mfr Name GoodSoft Products Non-GoodSoft Products
Acme 1 1
BooInc. 2 0

 

[jgraves]

Wow! Thank you so much. You put alot of effort into answering my question and
I really appreciate it.
It looks like it will work just fine.
Jen

These two queries will give you the results you said you wanted.
jgraves_1 ---
SELECT jgraves.Manufacturer, IIf([Source System]="GoodSoft",[Source
System],"Non-GoodSoft") AS GoodStuff
FROM jgraves
GROUP BY jgraves.Manufacturer, IIf([Source System]="GoodSoft",[Source
System],"Non-GoodSoft");

TRANSFORM Count(jgraves_1.GoodStuff) AS CountOfGoodStuff
SELECT jgraves_1.Manufacturer, Count(jgraves_1.GoodStuff) AS [Total Of Item
Number]
FROM jgraves_1
GROUP BY jgraves_1.Manufacturer
PIVOT jgraves_1.GoodStuff;

--
KARL DEWEY
Build a little - Test a little

Karl,
Excellent information on the crosstab queries. Thank you for that.

You are missing a subtle nuance in the information I am looking for: I want
the query to consider all records with the same item number as one "count".
(Revisit the sample results table for clarification)
The method you describe would bring the following result for my data:
Acme Total Records: 4, GoodSys Products: 1, Non GoodSys Products: 3
I want it to return:
Acme Total Records: 2, GoodSys Products: 1, Non GoodSys Products: 1

Meaning that there are 2 products represented (by 4 records), and I want to
know for each unique product, is there a "GoodSys" qualifier in one of its
records? If yes, consider the count to go up 1 for the GoodSys Product Count.
If there is no, then consider the count for "non" GoodSys Products to go up 1.

This is really a hard one to describe. Thanks for the effort!

You can put your criteria entry in an unbound form and reference the objects
of the form as criteria like this ---
[Forms]![YourFormName]![YourTextbox]
You will need to open the form, enter data, then run the query. You can have
a command button on the form.

--
KARL DEWEY
Build a little - Test a little


:

Using Access 2003, I want to write a query against information in the
following sample format:

Manufacturer Item Number Source System
Acme A-1 GoodSoft
Acme A-1 BadSoft
Acme A-2 BadSoft
Acme A-2 ReallyBadSoft
BooInc. B-1 GoodSoft
BooInc. B-1 BadSoft
BooInc. B-2 GoodSoft

In the example I created above, I have a product data file that contains
information on various products, such as the identification number for the
item, the manufacturer who created the product, and the software system that
the information came from. A product may have more than one product record
with the same Item Number because they were extracted from different systems.

I want to write a query that will tell me the following:
1. How many products have a product record created by the "GoodSoft" system?
2. How many products have no records that were created by the "GoodSoft"
system?

I want to group them by manufacturer, like below. Using the sample data
above, I would want to see the following result:

Mfr Name GoodSoft Products Non-GoodSoft Products
Acme 1 1
BooInc. 2 0