A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating totals on a Crosstab Query



 
 
Thread Tools Display Modes
  #31  
Old March 2nd, 2007, 06:48 PM posted to microsoft.public.access.reports
SBGFF
external usenet poster
 
Posts: 3
Default Calculating totals on a Crosstab Query

IF you like I could send you a db with just qrys and records you need to try
it out
Thanks Blair
This is the first qry, the one the crosstabs are built off.
Qry name is QDailyWhelpingReport

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![MatingYear]) AND
(([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![OptionDead]
Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![OptionDead]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Next is the first crosstab you sent me,
Qry name is QDailyWhelpingReport_Shed

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [The Value]
SELECT QDailyWhelpingReport.[WHELPING DATE],
Count(QDailyWhelpingReport.[NEST #]) AS [Total Of NEST #]
FROM QDailyWhelpingReport
GROUP BY QDailyWhelpingReport.[WHELPING DATE]
PIVOT QDailyWhelpingReport.[SHED #];

Next The 2nd Crosstab you sent me
Qry name is QDailyWhelpingReport_SubTotal

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr1
SELECT "SubTotal" AS Expr2, Count(IIf([Whelping Date] Is Null,Null,[NEST
#])) AS Expr3
FROM QDailyWhelpingReport
GROUP BY "SubTotal"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 3rd Crosstab you sent me
Qry name is QDailyWhelpingReport_Total

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #]
SELECT "Total" AS Expr1, Count(QDailyWhelpingReport.[NEST #]) AS
[CountOfNEST #1]
FROM QDailyWhelpingReport
GROUP BY "Total"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 4th Crosstab you sent me
Qry name is QDailyWhelpingReport_UnionQry

SELECT * FROM QDailyWhelpingReport_Shed
UNION ALL
SELECT * FROM QDailyWhelpingReport_SubTotal
UNION ALL SELECT * FROM QDailyWhelpingReport_Total;



"Marshall Barton" wrote in message
...
Crosstab queries generally require their parameters to be
declared, so that part is necessary. I need to see the
queries involved before I can hope to understand the rest of
what you're describing.

Sometimes you can unravel parameter issues by temporarily
repacing the parameter with a literal value. If that works,
then the problem is in the way the parameter value is
referenced or on the source of the parameter value. If the
literal value doesn't work either, then the problem is in
how the parameter is used.
--
Marsh
MVP [MS Access]


SBGFF wrote:

The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and
every thing works till I try to open the first crosstab you made, then it
has an error concerning the parameter. I tried declaring the parameter in
the query, it then opened but there were no results. I tried declaring
them
in the qry your qry's are based on and I don't get any results, remove
them
and the qry works. remove them from the crosstab and I get the error.
Something ain't just quite right.
In the parameters data type in the qry, number is not there, the only
option
that it will let me use is Date/time

"Marshall Barton" wrote
Check the data type of the [Mating Year] field in the table.
If it's a Text field, the text box on the form shuld
probably be set to: CStr(Year(Date()))

I don't understand why you should need to change your
system's date. If you want to search for a different year,
can't you just type the year in the text box?

I am afraid that there is so much going on now that I have
lost track of what you are now using for the query and
what's in the form.


Blair wrote:
Using Year(Now())
in the form load with the option to change it if I wanted a different
years
results.
Tried it in the text box default value and it did the same thing. It
seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to
start
exporting to another db to hold them till I don't need them

"Marshall Barton" wrote
I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query

Blair wrote:
Something is terribly screwy with something or my db. If I want to
bring
up
2006 year records I have to change my computer Date to 2006 in order
for
the
query to show any records. If I make a new qry it will work properly
in
retrieving the records, But then the qry's you made won't work. They
come
back with the error that it can't find Text6 or MatingYear, which is
what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote
Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls
and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back
and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other
until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)




  #32  
Old March 3rd, 2007, 02:55 AM posted to microsoft.public.access.reports
SBGFF
external usenet poster
 
Posts: 3
Default Calculating totals on a Crosstab Query

The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and
every thing works till I try to open the first crosstab you made, then it
has an error concerning the parameter. I tried declaring the parameter in
the query, it then opened but there were no results. I tried declaring them
in the qry your qry's are based on and I don't get any results, remove them
and the qry works. remove them from the crosstab and I get the error.
Something ain't just quite right.
In the parameters data type in the qry, number is not there, the only option
that it will let me use is Date/time
Thanks for any help you can give me
Blair

"Marshall Barton" wrote in message
...
Check the data type of the [Mating Year] field in the table.
If it's a Text field, the text box on the form shuld
probably be set to: CStr(Year(Date()))

I don't understand why you should need to change your
system's date. If you want to search for a different year,
can't you just type the year in the text box?

I am afraid that there is so much going on now that I have
lost track of what you are now using for the query and
what's in the form.
--
Marsh
MVP [MS Access]


Blair wrote:

Using Year(Now())
in the form load with the option to change it if I wanted a different
years
results.
Tried it in the text box default value and it did the same thing. It seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to start
exporting to another db to hold them till I don't need them

"Marshall Barton" wrote
I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query

Blair wrote:
Something is terribly screwy with something or my db. If I want to bring
up
2006 year records I have to change my computer Date to 2006 in order for
the
query to show any records. If I make a new qry it will work properly in
retrieving the records, But then the qry's you made won't work. They
come
back with the error that it can't find Text6 or MatingYear, which is
what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote
Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls
and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back
and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other
until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)





  #33  
Old March 3rd, 2007, 04:41 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Calculating totals on a Crosstab Query

Crosstab queries generally require their parameters to be
declared, so that part is necessary. I need to see the
queries involved before I can hope to understand the rest of
what you're describing.

Sometimes you can unravel parameter issues by temporarily
repacing the parameter with a literal value. If that works,
then the problem is in the way the parameter value is
referenced or on the source of the parameter value. If the
literal value doesn't work either, then the problem is in
how the parameter is used.
--
Marsh
MVP [MS Access]


SBGFF wrote:

The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and
every thing works till I try to open the first crosstab you made, then it
has an error concerning the parameter. I tried declaring the parameter in
the query, it then opened but there were no results. I tried declaring them
in the qry your qry's are based on and I don't get any results, remove them
and the qry works. remove them from the crosstab and I get the error.
Something ain't just quite right.
In the parameters data type in the qry, number is not there, the only option
that it will let me use is Date/time

"Marshall Barton" wrote
Check the data type of the [Mating Year] field in the table.
If it's a Text field, the text box on the form shuld
probably be set to: CStr(Year(Date()))

I don't understand why you should need to change your
system's date. If you want to search for a different year,
can't you just type the year in the text box?

I am afraid that there is so much going on now that I have
lost track of what you are now using for the query and
what's in the form.


Blair wrote:
Using Year(Now())
in the form load with the option to change it if I wanted a different
years
results.
Tried it in the text box default value and it did the same thing. It seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to start
exporting to another db to hold them till I don't need them

"Marshall Barton" wrote
I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query

Blair wrote:
Something is terribly screwy with something or my db. If I want to bring
up
2006 year records I have to change my computer Date to 2006 in order for
the
query to show any records. If I make a new qry it will work properly in
retrieving the records, But then the qry's you made won't work. They
come
back with the error that it can't find Text6 or MatingYear, which is
what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote
Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls
and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back
and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other
until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)


  #34  
Old March 3rd, 2007, 08:22 PM posted to microsoft.public.access.reports
SBGFF
external usenet poster
 
Posts: 3
Default Calculating totals on a Crosstab Query

sending again cause had date set for 3/2/2007 for testing db, don't know if
it makes any difference in you getting it

IF you like I could send you a db with just qrys and records you need to try
it out
Thanks Blair
This is the first qry, the one the crosstabs are built off.
Qry name is QDailyWhelpingReport

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![MatingYear]) AND
(([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![OptionDead]
Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![OptionDead]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Next is the first crosstab you sent me,
Qry name is QDailyWhelpingReport_Shed

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [The Value]
SELECT QDailyWhelpingReport.[WHELPING DATE],
Count(QDailyWhelpingReport.[NEST #]) AS [Total Of NEST #]
FROM QDailyWhelpingReport
GROUP BY QDailyWhelpingReport.[WHELPING DATE]
PIVOT QDailyWhelpingReport.[SHED #];

Next The 2nd Crosstab you sent me
Qry name is QDailyWhelpingReport_SubTotal

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr1
SELECT "SubTotal" AS Expr2, Count(IIf([Whelping Date] Is Null,Null,[NEST
#])) AS Expr3
FROM QDailyWhelpingReport
GROUP BY "SubTotal"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 3rd Crosstab you sent me
Qry name is QDailyWhelpingReport_Total

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #]
SELECT "Total" AS Expr1, Count(QDailyWhelpingReport.[NEST #]) AS
[CountOfNEST #1]
FROM QDailyWhelpingReport
GROUP BY "Total"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 4th Crosstab you sent me
Qry name is QDailyWhelpingReport_UnionQry

SELECT * FROM QDailyWhelpingReport_Shed
UNION ALL
SELECT * FROM QDailyWhelpingReport_SubTotal
UNION ALL SELECT * FROM QDailyWhelpingReport_Total;

"Marshall Barton" wrote in message
...
Crosstab queries generally require their parameters to be
declared, so that part is necessary. I need to see the
queries involved before I can hope to understand the rest of
what you're describing.

Sometimes you can unravel parameter issues by temporarily
repacing the parameter with a literal value. If that works,
then the problem is in the way the parameter value is
referenced or on the source of the parameter value. If the
literal value doesn't work either, then the problem is in
how the parameter is used.
--
Marsh
MVP [MS Access]


SBGFF wrote:

The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and
every thing works till I try to open the first crosstab you made, then it
has an error concerning the parameter. I tried declaring the parameter in
the query, it then opened but there were no results. I tried declaring
them
in the qry your qry's are based on and I don't get any results, remove
them
and the qry works. remove them from the crosstab and I get the error.
Something ain't just quite right.
In the parameters data type in the qry, number is not there, the only
option
that it will let me use is Date/time

"Marshall Barton" wrote
Check the data type of the [Mating Year] field in the table.
If it's a Text field, the text box on the form shuld
probably be set to: CStr(Year(Date()))

I don't understand why you should need to change your
system's date. If you want to search for a different year,
can't you just type the year in the text box?

I am afraid that there is so much going on now that I have
lost track of what you are now using for the query and
what's in the form.


Blair wrote:
Using Year(Now())
in the form load with the option to change it if I wanted a different
years
results.
Tried it in the text box default value and it did the same thing. It
seems
like the qry and form has some hidden link, besides the obvious.
I do have several of copies until I get what I want.I will have to
start
exporting to another db to hold them till I don't need them

"Marshall Barton" wrote
I don't see where you are using Date(), Now() or whatever it
is that's dependent on the computer's date. I think I need
to be kept abreast of the changes to the form and the query.

The names I suggested that you change are the names of the
controls on the form. The control references in the query
must then be modified to agree with the names on the form.
It sounds like you are having trouble keeping them in sync,
possibly because you have too many copies of the form and
query

Blair wrote:
Something is terribly screwy with something or my db. If I want to
bring
up
2006 year records I have to change my computer Date to 2006 in order
for
the
query to show any records. If I make a new qry it will work properly
in
retrieving the records, But then the qry's you made won't work. They
come
back with the error that it can't find Text6 or MatingYear, which is
what
I
called it in the new qry as you suggested.
Any Idea what's going on here?? Weird!!


"Marshall Barton" wrote
Blair wrote:

Found the problem, but can't fix it
Tracked it down to the name change of my form that has the controls
and
the
query that has the criteria. When I change them it won't work, even
though
it is all spelled correctly, it won't recognize it.I change it back
and
it
will work.

Blair wrote:
It actually says
The Microsoft Jet database engine does not recognize
'[Forms]![FdailyWhelpingReport]![Text6]' as a valid field name or
expression.

This is the sql of the qry QDailyWhelpingReport That I am using. I
guess
I
should have used this from the start, but I was using the other
until
I
got
the results I wanted so I wouldn't have to mess with the criteria
until
I
got it working the way I wanted.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating
Year])=[Forms]![FDailyWhelpingReport]![Text6]) AND
(([98MatingRecords].Dead)
Like [Forms]![FDailyWhelpingReport]![Option4] Or
([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![Option4]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Keep checking the names. The Text6 and Option4 are likely
to be different if you rereated the form. I strongly
suggest that you use more meaningful names such as
txtMatingYear and that you also avoid names with spaces
and/or other non-alphanumeric characters.

Another potential issue is the criteria:
((Dead Like Forms!FDailyWhelpingReport!Option4) Or
(.Dead=IsNull(Forms!FDailyWhelpingReport!Option4))

Note the use of parenthesis to explicitly specify the
evaluation order of precedence between the AND and OR
ecpressions.

The issue is that Like is only appropriate when comparing a
Text field to a wildcard pattern. I think you can get the
intended result from:

Dead = Nz(Forms!FDailyWhelpingReport!Option4, True)




  #35  
Old March 3rd, 2007, 09:41 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Calculating totals on a Crosstab Query

As always, double check the names of the controls on the
form to make sure they are identical to the names you use in
the query parameters.

I don't see where you explained which error message you
received and/or which parameter was causing it.

Your first query's WHERE clause is not quite right. Don't
use Like unless you really are doing a wildcard text
comparison.

WHERE [Mating Year] = Forms!FDailyWhelpingReport!MatingYear
AND (Dead = Forms!FDailyWhelpingReport!OptionDead
Or (Dead=IsNull(Forms!FDailyWhelpingReport!OptionDead )
AND [1st MATING] Is Not Null

The Dead test looks odd, the way it is written, the query
will return only records with Dead = True when you do not
specify OptionDead on the form.

If that still gives you trouble, remove the criteria one at
a time to see if it helps you locate the one that causing
trouble.

If you can deal with it, you can avoid all those extra [ ],
( ), and table names by never switching the query from SQL
view to the QBE grid. When you save a query from SQL view,
it will come back in SQL view.
--
Marsh
MVP [MS Access]


SBGFF wrote:
IF you like I could send you a db with just qrys and records you need to try
it out

This is the first qry, the one the crosstabs are built off.
Qry name is QDailyWhelpingReport

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].[FEMALE #], [98MatingRecords].[Mating Year],
[98MatingRecords].[WHELPING DATE], [98MatingRecords].Dead,
[98MatingRecords].[1st MATING]
FROM 98MatingRecords
WHERE ((([98MatingRecords].[Mating Year])=[Forms]![FDailyWhelpingReport]![MatingYear])
AND (([98MatingRecords].Dead) Like [Forms]![FDailyWhelpingReport]![OptionDead]
Or ([98MatingRecords].Dead)=IsNull([Forms]![FDailyWhelpingReport]![OptionDead]))
AND (([98MatingRecords].[1st MATING]) Is Not Null));

Next is the first crosstab you sent me,
Qry name is QDailyWhelpingReport_Shed

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [The Value]
SELECT QDailyWhelpingReport.[WHELPING DATE],
Count(QDailyWhelpingReport.[NEST #]) AS [Total Of NEST #]
FROM QDailyWhelpingReport
GROUP BY QDailyWhelpingReport.[WHELPING DATE]
PIVOT QDailyWhelpingReport.[SHED #];

Next The 2nd Crosstab you sent me
Qry name is QDailyWhelpingReport_SubTotal

TRANSFORM Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr1
SELECT "SubTotal" AS Expr2,
Count(IIf([Whelping Date] Is Null,Null,[NEST #])) AS Expr3
FROM QDailyWhelpingReport
GROUP BY "SubTotal"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 3rd Crosstab you sent me
Qry name is QDailyWhelpingReport_Total

TRANSFORM Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #]
SELECT "Total" AS Expr1,
Count(QDailyWhelpingReport.[NEST #]) AS [CountOfNEST #1]
FROM QDailyWhelpingReport
GROUP BY "Total"
PIVOT QDailyWhelpingReport.[SHED #];

Next The 4th Crosstab you sent me
Qry name is QDailyWhelpingReport_UnionQry

SELECT * FROM QDailyWhelpingReport_Shed
UNION ALL
SELECT * FROM QDailyWhelpingReport_SubTotal
UNION ALL SELECT * FROM QDailyWhelpingReport_Total;


"Marshall Barton" wrote
Crosstab queries generally require their parameters to be
declared, so that part is necessary. I need to see the
queries involved before I can hope to understand the rest of
what you're describing.

Sometimes you can unravel parameter issues by temporarily
repacing the parameter with a literal value. If that works,
then the problem is in the way the parameter value is
referenced or on the source of the parameter value. If the
literal value doesn't work either, then the problem is in
how the parameter is used.


SBGFF wrote:
The data type in the table is set to Number.Just got your last post.
To night I deleted every thing and started from scratch.Made a new qry and
every thing works till I try to open the first crosstab you made, then it
has an error concerning the parameter. I tried declaring the parameter in
the query, it then opened but there were no results. I tried declaring
them
in the qry your qry's are based on and I don't get any results, remove
them
and the qry works. remove them from the crosstab and I get the error.
Something ain't just quite right.
In the parameters data type in the qry, number is not there, the only
option
that it will let me use is Date/time




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.