crosstab query: incorrect values

  • Thread starter Thread starter Michel Peeters
  • Start date Start date
M

Michel Peeters

I have following:

TRANSFORM Sum(tblSalesDetails.Kg) AS [Weight]

SELECT tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount,
Sum(tblSalesDetails.Kg) AS TotalWeight

FROM tblSalesDetails

WHERE (((tblSalesDetails.Datum) Between Now() And Now()-45))

GROUP BY tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount

PIVOT "W" & DateDiff("ww",Now(),Now()-45) In
("W0","W1","W2","W3","W4","W5","W6","W7");





The cellvalues from "TotalWeight" are correct. It is the sum of the 8 weeks
(= underlying records)

But the cellvalues (the weight) from WO, W1, ...W7 are empty.



tks for help

Michel
 
Hi,



Because the PIVOT expression does NOT depend on any field. It is a constant
( for a given date where you run the query) :


? DateDiff("ww", Now, Now-45)
-6


So, all records would report their values under "W-6" if such a column was
in the IN-list (which is not).


Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

It could be simply that you always
want to use the earlier date as the
first parameter in DateDiff, or you
get a negative number.

?"W" & DateDiff("ww",Now(),Now()-45)
W-6

i.e., there is no column "W6" to put anything
under.

?"W" & DateDiff("ww",Now()-45,Now())
W6

That gets you one and only one column.

How did you want to get the other column
headings?

Did you mean

"W" & DateDiff("ww",tblSalesDetails.Datum, Date())


"Michel Peeters" wrote
 
tks Gary, you were correct but this does not solve the problem.
The sql I wrote was just to explain the problem.
In my application the dates were matching the "In" statement (and
form!parameter declared).
The problem is I have about 2000 records in the crosstabquery and that
TotalWEight is not always = the sum of the 8 weeks.
I exported it to XL, inserted a column "Difference" with a calculated cell:
TotalWeight minus the sum of the 8 weeks, which should be zero.
On about 20 rows I have a difference: 1, 2 or more weeks are blank but the
total is correct.
The other 1980 rows are correct: difference = 0.
What could the problem be?

The application works with (every night) imported data. Some days it is
correct, other days not???

tks - michel


Gary Walter said:
Hi Michel,

It could be simply that you always
want to use the earlier date as the
first parameter in DateDiff, or you
get a negative number.

?"W" & DateDiff("ww",Now(),Now()-45)
W-6

i.e., there is no column "W6" to put anything
under.

?"W" & DateDiff("ww",Now()-45,Now())
W6

That gets you one and only one column.

How did you want to get the other column
headings?

Did you mean

"W" & DateDiff("ww",tblSalesDetails.Datum, Date())


"Michel Peeters" wrote
I have following:

TRANSFORM Sum(tblSalesDetails.Kg) AS [Weight]

SELECT tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount,
Sum(tblSalesDetails.Kg) AS TotalWeight

FROM tblSalesDetails

WHERE (((tblSalesDetails.Datum) Between Now() And Now()-45))

GROUP BY tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount

PIVOT "W" & DateDiff("ww",Now(),Now()-45) In
("W0","W1","W2","W3","W4","W5","W6","W7");





The cellvalues from "TotalWeight" are correct. It is the sum of the 8 weeks
(= underlying records)

But the cellvalues (the weight) from WO, W1, ...W7 are empty.



tks for help

Michel
Click to expand...
Click to expand...
 
HI,


Because some records have their PIVOT value NOT IN the list you
supplied. They are accounted in the SUM, but appear nowhere since only those
IN the list ("W1", "W2", ... "W7") appear. Other columns ARE NOT created
when you supply a IN-list. REMOVE the IN( list ) and you should see the
other columns (other than W1, W2, ... W7) that do not "appear" when you
specify the list. You have to correct either the data, either the list,
either to leave the list out, completely.



Vanderghast, Access MVP



Michel Peeters said:
tks Gary, you were correct but this does not solve the problem.
The sql I wrote was just to explain the problem.
In my application the dates were matching the "In" statement (and
form!parameter declared).
The problem is I have about 2000 records in the crosstabquery and that
TotalWEight is not always = the sum of the 8 weeks.
I exported it to XL, inserted a column "Difference" with a calculated cell:
TotalWeight minus the sum of the 8 weeks, which should be zero.
On about 20 rows I have a difference: 1, 2 or more weeks are blank but the
total is correct.
The other 1980 rows are correct: difference = 0.
What could the problem be?

The application works with (every night) imported data. Some days it is
correct, other days not???

tks - michel


Gary Walter said:
Hi Michel,

It could be simply that you always
want to use the earlier date as the
first parameter in DateDiff, or you
get a negative number.

?"W" & DateDiff("ww",Now(),Now()-45)
W-6

i.e., there is no column "W6" to put anything
under.

?"W" & DateDiff("ww",Now()-45,Now())
W6

That gets you one and only one column.

How did you want to get the other column
headings?

Did you mean

"W" & DateDiff("ww",tblSalesDetails.Datum, Date())


"Michel Peeters" wrote
I have following:

TRANSFORM Sum(tblSalesDetails.Kg) AS [Weight]

SELECT tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount,
Sum(tblSalesDetails.Kg) AS TotalWeight

FROM tblSalesDetails

WHERE (((tblSalesDetails.Datum) Between Now() And Now()-45))

GROUP BY tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount

PIVOT "W" & DateDiff("ww",Now(),Now()-45) In
("W0","W1","W2","W3","W4","W5","W6","W7");





The cellvalues from "TotalWeight" are correct. It is the sum of the 8 weeks
(= underlying records)

But the cellvalues (the weight) from WO, W1, ...W7 are empty.



tks for help

Michel
Click to expand...
Click to expand...
Click to expand...
 
tks for your suggestion but my "IN" was correct.
I nearly found it:
When I omit 1 or 2 "row headings fields" (from tblCustomer - text fields -
255 length and as good as full in the 'wrong' records) the query works
correct.
It looks that there is maximum length for the "row headings" in a
crosstabquery.
My SetWarnings = true but I did not receive a warning.
If above is correct is there a way to check this?
Michel



Michel Walsh said:
HI,


Because some records have their PIVOT value NOT IN the list you
supplied. They are accounted in the SUM, but appear nowhere since only those
IN the list ("W1", "W2", ... "W7") appear. Other columns ARE NOT created
when you supply a IN-list. REMOVE the IN( list ) and you should see the
other columns (other than W1, W2, ... W7) that do not "appear" when you
specify the list. You have to correct either the data, either the list,
either to leave the list out, completely.



Vanderghast, Access MVP



Michel Peeters said:
tks Gary, you were correct but this does not solve the problem.
The sql I wrote was just to explain the problem.
In my application the dates were matching the "In" statement (and
form!parameter declared).
The problem is I have about 2000 records in the crosstabquery and that
TotalWEight is not always = the sum of the 8 weeks.
I exported it to XL, inserted a column "Difference" with a calculated cell:
TotalWeight minus the sum of the 8 weeks, which should be zero.
On about 20 rows I have a difference: 1, 2 or more weeks are blank but the
total is correct.
The other 1980 rows are correct: difference = 0.
What could the problem be?

The application works with (every night) imported data. Some days it is
correct, other days not???

tks - michel


Gary Walter said:
Hi Michel,

It could be simply that you always
want to use the earlier date as the
first parameter in DateDiff, or you
get a negative number.

?"W" & DateDiff("ww",Now(),Now()-45)
W-6

i.e., there is no column "W6" to put anything
under.

?"W" & DateDiff("ww",Now()-45,Now())
W6

That gets you one and only one column.

How did you want to get the other column
headings?

Did you mean

"W" & DateDiff("ww",tblSalesDetails.Datum, Date())


"Michel Peeters" wrote
I have following:

TRANSFORM Sum(tblSalesDetails.Kg) AS [Weight]

SELECT tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount,
Sum(tblSalesDetails.Kg) AS TotalWeight

FROM tblSalesDetails

WHERE (((tblSalesDetails.Datum) Between Now() And Now()-45))

GROUP BY tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount

PIVOT "W" & DateDiff("ww",Now(),Now()-45) In
("W0","W1","W2","W3","W4","W5","W6","W7");





The cellvalues from "TotalWeight" are correct. It is the sum of the
Click to expand...
8
weeks
(= underlying records)

But the cellvalues (the weight) from WO, W1, ...W7 are empty.



tks for help

Michel
Click to expand...
Click to expand...
Click to expand...
 
Hi,


I just tried with a group making 300 characters (2 fields of 150
characters each) and there was no problem.

TRANSFORM Max(Z4) AS MaxOfZ4
SELECT Max(Z4) AS [Total Of Z4]
FROM zs
GROUP BY z1 , z2
PIVOT z3;



where all my z1 are equal, and z2 have 149 first characters equal, but the
last 150th one different.


On the other hand, if I use a concatenation:

TRANSFORM Max(Z4) AS MaxOfZ4
SELECT Max(Z4) AS [Total Of Z4]
FROM zs
GROUP BY z1 & z2
PIVOT z3;


that silently fails, as usual, since a computed expression is limited to 255
characters. So, I got only one "row" in this case (because the "group" was
truncated to the first 255 chars of the concatenation).

That is not JUST for crosstab query, but applicable to all standard "total"
query (query with GROUP BY ).




Hoping it may help,
Vanderghast, Access MVP


Michel Peeters said:
tks for your suggestion but my "IN" was correct.
I nearly found it:
When I omit 1 or 2 "row headings fields" (from tblCustomer - text fields -
255 length and as good as full in the 'wrong' records) the query works
correct.
It looks that there is maximum length for the "row headings" in a
crosstabquery.
My SetWarnings = true but I did not receive a warning.
If above is correct is there a way to check this?
Michel



Michel Walsh said:
HI,


Because some records have their PIVOT value NOT IN the list you
supplied. They are accounted in the SUM, but appear nowhere since only those
IN the list ("W1", "W2", ... "W7") appear. Other columns ARE NOT created
when you supply a IN-list. REMOVE the IN( list ) and you should see the
other columns (other than W1, W2, ... W7) that do not "appear" when you
specify the list. You have to correct either the data, either the list,
either to leave the list out, completely.



Vanderghast, Access MVP



Michel Peeters said:
tks Gary, you were correct but this does not solve the problem.
The sql I wrote was just to explain the problem.
In my application the dates were matching the "In" statement (and
form!parameter declared).
The problem is I have about 2000 records in the crosstabquery and that
TotalWEight is not always = the sum of the 8 weeks.
I exported it to XL, inserted a column "Difference" with a calculated cell:
TotalWeight minus the sum of the 8 weeks, which should be zero.
On about 20 rows I have a difference: 1, 2 or more weeks are blank but the
total is correct.
The other 1980 rows are correct: difference = 0.
What could the problem be?

The application works with (every night) imported data. Some days it is
correct, other days not???

tks - michel


"Gary Walter" <[email protected]> schreef in bericht
Hi Michel,

It could be simply that you always
want to use the earlier date as the
first parameter in DateDiff, or you
get a negative number.

?"W" & DateDiff("ww",Now(),Now()-45)
W-6

i.e., there is no column "W6" to put anything
under.

?"W" & DateDiff("ww",Now()-45,Now())
W6

That gets you one and only one column.

How did you want to get the other column
headings?

Did you mean

"W" & DateDiff("ww",tblSalesDetails.Datum, Date())


"Michel Peeters" wrote
I have following:

TRANSFORM Sum(tblSalesDetails.Kg) AS [Weight]

SELECT tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount,
Sum(tblSalesDetails.Kg) AS TotalWeight

FROM tblSalesDetails

WHERE (((tblSalesDetails.Datum) Between Now() And Now()-45))

GROUP BY tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount

PIVOT "W" & DateDiff("ww",Now(),Now()-45) In
("W0","W1","W2","W3","W4","W5","W6","W7");





The cellvalues from "TotalWeight" are correct. It is the sum of
Click to expand...
Click to expand...
the
8
Click to expand...
Click to expand...
 
Hi Michel(s),

Are you using Access 97?

A while back Ian had a crosstab
that would have "some" missing
values for records that had a row
heading field whose length >232.
Some were there and correct for
those specific records, but some
were missing.

He sent sample db to me and it
worked properly in Access200x.

But when I tried in 97, I got the
same result as him. We found that
using Left function gave us the length
where it began to fail.
left(textfield,232)....... works.

left(textfield,233+)..... doesn't.

And, of course, if you leave out this field, the xtab
works perfectly.
Click to expand...

I sent the sample db to Duane but have not
heard back. I assumed he might go through
MVP channels to sort this out (actually I assumed
Duane probably knew *exactly* what was wrong). 8-)

Have you seen this problem Michel Walsh?

"Michel Peeters" wrote
tks for your suggestion but my "IN" was correct.
I nearly found it:
When I omit 1 or 2 "row headings fields" (from tblCustomer - text fields -
255 length and as good as full in the 'wrong' records) the query works
correct.
It looks that there is maximum length for the "row headings" in a
crosstabquery.
My SetWarnings = true but I did not receive a warning.
If above is correct is there a way to check this?
Michel



Michel Walsh said:
HI,


Because some records have their PIVOT value NOT IN the list you
supplied. They are accounted in the SUM, but appear nowhere since only those
IN the list ("W1", "W2", ... "W7") appear. Other columns ARE NOT created
when you supply a IN-list. REMOVE the IN( list ) and you should see the
other columns (other than W1, W2, ... W7) that do not "appear" when you
specify the list. You have to correct either the data, either the list,
either to leave the list out, completely.



Vanderghast, Access MVP



Michel Peeters said:
tks Gary, you were correct but this does not solve the problem.
The sql I wrote was just to explain the problem.
In my application the dates were matching the "In" statement (and
form!parameter declared).
The problem is I have about 2000 records in the crosstabquery and that
TotalWEight is not always = the sum of the 8 weeks.
I exported it to XL, inserted a column "Difference" with a calculated cell:
TotalWeight minus the sum of the 8 weeks, which should be zero.
On about 20 rows I have a difference: 1, 2 or more weeks are blank but the
total is correct.
The other 1980 rows are correct: difference = 0.
What could the problem be?

The application works with (every night) imported data. Some days it is
correct, other days not???

tks - michel


"Gary Walter" <[email protected]> schreef in bericht
Hi Michel,

It could be simply that you always
want to use the earlier date as the
first parameter in DateDiff, or you
get a negative number.

?"W" & DateDiff("ww",Now(),Now()-45)
W-6

i.e., there is no column "W6" to put anything
under.

?"W" & DateDiff("ww",Now()-45,Now())
W6

That gets you one and only one column.

How did you want to get the other column
headings?

Did you mean

"W" & DateDiff("ww",tblSalesDetails.Datum, Date())


"Michel Peeters" wrote
I have following:

TRANSFORM Sum(tblSalesDetails.Kg) AS [Weight]

SELECT tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount,
Sum(tblSalesDetails.Kg) AS TotalWeight

FROM tblSalesDetails

WHERE (((tblSalesDetails.Datum) Between Now() And Now()-45))

GROUP BY tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount

PIVOT "W" & DateDiff("ww",Now(),Now()-45) In
("W0","W1","W2","W3","W4","W5","W6","W7");





The cellvalues from "TotalWeight" are correct. It is the sum of the 8
weeks
(= underlying records)

But the cellvalues (the weight) from WO, W1, ...W7 are empty.



tks for help

Michel
Click to expand...
Click to expand...
Click to expand...
 
Hi,


I am using using Access 2003, I can't say about Access 97. Sorry.

Vanderghast, Access MVP


Gary Walter said:
Hi Michel(s),

Are you using Access 97?

A while back Ian had a crosstab
that would have "some" missing
values for records that had a row
heading field whose length >232.
Some were there and correct for
those specific records, but some
were missing.

He sent sample db to me and it
worked properly in Access200x.

But when I tried in 97, I got the
same result as him. We found that
using Left function gave us the length
where it began to fail.
left(textfield,232)....... works.

left(textfield,233+)..... doesn't.

And, of course, if you leave out this field, the xtab
works perfectly.
Click to expand...

I sent the sample db to Duane but have not
heard back. I assumed he might go through
MVP channels to sort this out (actually I assumed
Duane probably knew *exactly* what was wrong). 8-)

Have you seen this problem Michel Walsh?

"Michel Peeters" wrote
tks for your suggestion but my "IN" was correct.
I nearly found it:
When I omit 1 or 2 "row headings fields" (from tblCustomer - text fields -
255 length and as good as full in the 'wrong' records) the query works
correct.
It looks that there is maximum length for the "row headings" in a
crosstabquery.
My SetWarnings = true but I did not receive a warning.
If above is correct is there a way to check this?
Michel



Michel Walsh said:
HI,


Because some records have their PIVOT value NOT IN the list you
supplied. They are accounted in the SUM, but appear nowhere since only those
IN the list ("W1", "W2", ... "W7") appear. Other columns ARE NOT creat ed
when you supply a IN-list. REMOVE the IN( list ) and you should see the
other columns (other than W1, W2, ... W7) that do not "appear" when you
specify the list. You have to correct either the data, either the list,
either to leave the list out, completely.



Vanderghast, Access MVP



tks Gary, you were correct but this does not solve the problem.
The sql I wrote was just to explain the problem.
In my application the dates were matching the "In" statement (and
form!parameter declared).
The problem is I have about 2000 records in the crosstabquery and that
TotalWEight is not always = the sum of the 8 weeks.
I exported it to XL, inserted a column "Difference" with a calculated
cell:
TotalWeight minus the sum of the 8 weeks, which should be zero.
On about 20 rows I have a difference: 1, 2 or more weeks are blank
Click to expand...
but
the
total is correct.
The other 1980 rows are correct: difference = 0.
What could the problem be?

The application works with (every night) imported data. Some days it is
correct, other days not???

tks - michel


"Gary Walter" <[email protected]> schreef in bericht
Hi Michel,

It could be simply that you always
want to use the earlier date as the
first parameter in DateDiff, or you
get a negative number.

?"W" & DateDiff("ww",Now(),Now()-45)
W-6

i.e., there is no column "W6" to put anything
under.

?"W" & DateDiff("ww",Now()-45,Now())
W6

That gets you one and only one column.

How did you want to get the other column
headings?

Did you mean

"W" & DateDiff("ww",tblSalesDetails.Datum, Date())


"Michel Peeters" wrote
I have following:

TRANSFORM Sum(tblSalesDetails.Kg) AS [Weight]

SELECT tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount,
Sum(tblSalesDetails.Kg) AS TotalWeight

FROM tblSalesDetails

WHERE (((tblSalesDetails.Datum) Between Now() And Now()-45))

GROUP BY tblSalesDetails.CustName, tblSalesDetails.CustId,
tblSalesDetails.Munt, tblSalesDetails.Artnr, tblSalesDetails.Price,
tblSalesDetails.LijnDiscount, tblSalesDetails.FactDiscount

PIVOT "W" & DateDiff("ww",Now(),Now()-45) In
("W0","W1","W2","W3","W4","W5","W6","W7");





The cellvalues from "TotalWeight" are correct. It is the sum of
Click to expand...
the
8
weeks
(= underlying records)

But the cellvalues (the weight) from WO, W1, ...W7 are empty.



tks for help

Michel
Click to expand...
Click to expand...
Click to expand...
 
Back
Top