-----Original Message-----
Hi,
If there is no immediate "join" that can lead you to "naturally" combine
(join) the new crosstab with the actual query/table you use in the actual
report, I would definitively go for a sub-report, that is exactly what I
would do, as you guess, right.
You also guess right in that SQL allows to easily perform powerful tasks
that would otherwise require many lines of codes (and validation), without
counting the reduce amount of required maintenance (code you don't write
don't have to be maintained), and the robustness we so gain in the process.
SQL is NOT a general language for programming, but it excels in what it is
intended to be applied.
About learning SQL, well, the basic can be obtain from "generic" books, one
which is closely compatible with Jet and MS SQL Server is "SQL Queries form
Mere Mortals", by Hernandez and Viescas, at Addison- Wesley. I would stay
away from Joe Celko's books, for introduction: they are too advanced, and
their syntax is quite alien to Jet...(and not up to date with its
possibilities) but to progress past the intermediate stage, they are among
those books I cannot fail to recommend. Sure, this ng, and Google in
general, are not completely rotten either.
Hoping it may help,
Vanderghast, Access MVP
Michel,
I appreciate very much your help. Your query worked.
The report I would like to put the information into has a
different table as it's source. I have tried adding the
query to the source for the report and creating a
relationship with "JobNumber", however that gave me the
wrong information.
Is there a way to insert this query SQL staement into the
On Print event of the "rptJobToDate" make the
report "JobNumber" the criteria and put
the "DistinctCount" in a text box on the report?
Do I need to add a subreport based on the query?
I know that SQL statements and queries are powerful tools
and that there is a lot more I need to learn about them.
Many things I would like to do are probably very easy (or
at least can be done) through these two tools. I am not
certain were to find information so I can grow in this
area.
Thank you for your patience.
-----Original Message-----
Hi,
1- Make the following query (cut and paste in a SQL
view
of a blank query,
in the query designer)
TRANSFORM COUNT(*) as TotalCount
SELECT JobNumber, COUNT(TotalCount) As DistinctCount
FROM SixDifinPackaged
GROUP BY JobNumber
PIVOT UniqueLabelPack IN(NULL);
2- Save the query.
3- Build the report based on the saved query, using the fields DistinctCount
and JobNumber (just forget the "diamond" field,
[ said:
Note: if you ever use a parameter in a crosstab query, you HAVE TO declare
its datatype.
Hoping it may help,
Vanderghast, Access MVP
"AHopper" <
[email protected]> wrote
in
message
Michel, thank you for your response. I don't think I
communicated clearly what I am trying to do. I will try
again.
In a table named "SixDifinPackaged" I have
fields "JobNumber" and "UniqueLabelPack". I want to count
the distinct "UniqueLabelPack" for each "JobNumber" and
use the result in a report. Some "UniqueLabelPack" are
used more than once so I only want them counted once.
How would I put the result in a report? I am using
the
On
Print event of the report to show other information about
each "JobNumber"
Thanks for your help
Allan
-----Original Message-----
Hi,
SELECT COUNT(*)
FROM (SELECT DISTINCT JobNumber FROM myTable) As a
would return the number of distinct JobNumber.
If you wish the number of distinct value by group, an
easy solution is to
use a crosstab query (initial solution proposed by Steve
Dassin):
TRANSFORM COUNT(*) As countTotal
SELECT myGroup, COUNT(countTotal) As CountDistinct
FROM myTable
GROUP BY myGroup
PIVOT JobNumber
would display the number of distinct JobNumber, for each
Group. If you do
not want the various fields created by the crosstab, use,
as example, in
Northwind:
TRANSFORM COUNT(*) As countCity
SELECT Customers.Country, COUNT(countCity) As
DistinctCount, COUNT(*) As
TotalCount
FROM Customers
GROUP BY Country
PIVOT city IN( NULL)
Query1
Country DistinctCount TotalCount <>
Argentina 1 3
Austria 2 2
Belgium 2 2
Brazil 4 9
Canada 3 3
Denmark 2 2
Finland 2 2
France 9 11
Germany 11 11
Ireland 1 1
Italy 3 3
Mexico 1 5
Norway 1 1
Poland 1 1
Portugal 1 2
Spain 3 5
Sweden 2 2
Switzerland 2 2
UK 2 7
USA 12 13
Venezuela 4 4
Hoping it may help,
Vanderghast, Access MVP
"AHopper" <
[email protected]>
wrote
in
message
The following query "SixDifinPackagedUniqueQuery", using
fields "JobNumber" and "UniqueLabelPack" from
table "SixDifinPackaged" gives me a list of unique
carton
labels. I want to count these and use the results to
tell
me the number of cartons packaged in a report. However,
when I try to use Count in the query it counts all the
Unique Labels in the table and not just the labels in
the
query list. Since some of the labels are in the table
more
than once I get a incorrect answer(836 Unique label
values
when counted becomes 859). I have set the query for
unique
values and 836 is the correct answer.
SELECT DISTINCT SixDifinPackaged.JobNumber,
SixDifinPackaged.UniqueLabelPack
FROM SixDifinPackaged
WHERE (((SixDifinPackaged.JobNumber)=[Forms]!
[SixDifinPackageForm]![JobNumber]));
I tried placing the following in the On Print
event
of
the
detail section of a report.
Dim CartonsPackaged as Single
CartonsPackaged = Nz(DCount
("[UniqueLabelPack]", "SixDifinPackagedUniqueQuery", "[JobN
umber]=" & Me.JobNumber))
I get the following error
Run-time error '2001':
You canceled the previous operation.
Thank you in advance for your help.
Allan
.
.
.
