"The field is too small..." error coming up on Select query.

G

Guest

I have a SELECT SQL query in Access 2003 that throws the error "The field is
too small to accept the amount of data you attempted to add. Try inserting or
pasting less data." I am not inserting or pasting anything. It is a query
based on other queries, however, but I can't find why it doesn't work. Here
are the SQL statements for the queries. "Batting", "Pitching", and "Teams"
are tables, with pretty simple text and number data:

BScores:
SELECT Batting.Team, Batting.Period, Batting.Year, Sum(Batting.FPTS) AS BPTS
FROM Batting
WHERE Status='A'
GROUP BY Team, Period, Year;

PScores:
SELECT Pitching.Team, Pitching.Period, Pitching.Year, Sum(Pitching.FPTS) AS
PPTS
FROM Pitching
WHERE Status='A'
GROUP BY Year, Team, Period;

TScores:
SELECT BScores.Team, BScores.Period, BScores.Year, BScores.BPTS+PScores.PPTS
AS FPTS
FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND
(BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year);

ScoreSched (this query throws the error):
SELECT TScores.Team, TScores.Period, Teams.Name
FROM TScores, Teams
WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr;

If I copy the data from TScores into Excel and then into a straight Access
table, the query works fine joining that table with Teams. I am hitting my
head against the wall on this, it makes no sense to me.

The only thing the error implies to me is that there is some data type
mismatch, but all the referenced data types are exactly the same, though
referenced through multiple layers of queries.

Please help!
 
B

Brendan Reynolds

One possibility would be that the sum of, for example, Batting.FPTS might
potentially be a much larger number than any of the individual values in the
column. JET may be seeing that the data type of Batting.FPTS is, say,
Single, and assuming that the data type of the expression Sum(Batting.FPTS)
can also be Single, where possibly it may need to be Double. You could try,
for example, Sum(CDbl(Batting.FPTS)).

You also have some field names that are either reserved words or names of
functions, such as Year and Name. I wouldn't expect that to cause this
particular problem, but it wouldn't hurt to put square brackets around your
field names just in case, e.g. Batting.[Year] and Teams.[Name].
 
G

Guest

That did seem to be on the right path. The ScoreSched table doesn't throw the
error anymore, until I add "TScores.[FPTS]" to what's selected. I've tried
adding CDbl() to all the PTS columns in each table, but I'm not exactly sure
if I'm doing it right. I've modified the queries below how i've modified them
in Access, and it still doesn't work (again, it's only ScoreSched that throws
an error):

Any idea what I'm doing wrong?

Brendan Reynolds said:
One possibility would be that the sum of, for example, Batting.FPTS might
potentially be a much larger number than any of the individual values in the
column. JET may be seeing that the data type of Batting.FPTS is, say,
Single, and assuming that the data type of the expression Sum(Batting.FPTS)
can also be Single, where possibly it may need to be Double. You could try,
for example, Sum(CDbl(Batting.FPTS)).

You also have some field names that are either reserved words or names of
functions, such as Year and Name. I wouldn't expect that to cause this
particular problem, but it wouldn't hurt to put square brackets around your
field names just in case, e.g. Batting.[Year] and Teams.[Name].

--
Brendan Reynolds
Access MVP


Mayhew said:
I have a SELECT SQL query in Access 2003 that throws the error "The field
is
too small to accept the amount of data you attempted to add. Try inserting
or
pasting less data." I am not inserting or pasting anything. It is a query
based on other queries, however, but I can't find why it doesn't work.
Here
are the SQL statements for the queries. "Batting", "Pitching", and "Teams"
are tables, with pretty simple text and number data:

BScores:
SELECT Batting.Team, Batting.Period, Batting.Year, Sum(CDbl(Batting.FPTS)) AS
BPTS
FROM Batting
WHERE Status='A'
GROUP BY Team, Period, Year;

PScores:
SELECT Pitching.Team, Pitching.Period, Pitching.Year, Sum(CDbl(Pitching.FPTS))
AS
PPTS
FROM Pitching
WHERE Status='A'
GROUP BY Year, Team, Period;

TScores:
SELECT BScores.Team, BScores.Period, BScores.Year,
CDbl(BScores.BPTS)+CDbl(PScores.PPTS)
AS FPTS
FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND
(BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year);

ScoreSched (this query throws the error):
SELECT TScores.Team, TScores.Period, CDbl(TScores.FPTS), Teams.Name
FROM TScores, Teams
WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr;

If I copy the data from TScores into Excel and then into a straight Access
table, the query works fine joining that table with Teams. I am hitting my
head against the wall on this, it makes no sense to me.

The only thing the error implies to me is that there is some data type
mismatch, but all the referenced data types are exactly the same, though
referenced through multiple layers of queries.

Please help!
Click to expand...
Click to expand...
 
B

Brendan Reynolds

Are you dealing with very large numbers? What are the largest values in
BScores.BPTS and PScores.PPTS?

--
Brendan Reynolds
Access MVP

Mayhew said:
That did seem to be on the right path. The ScoreSched table doesn't throw
the
error anymore, until I add "TScores.[FPTS]" to what's selected. I've tried
adding CDbl() to all the PTS columns in each table, but I'm not exactly
sure
if I'm doing it right. I've modified the queries below how i've modified
them
in Access, and it still doesn't work (again, it's only ScoreSched that
throws
an error):

Any idea what I'm doing wrong?

Brendan Reynolds said:
One possibility would be that the sum of, for example, Batting.FPTS might
potentially be a much larger number than any of the individual values in
the
column. JET may be seeing that the data type of Batting.FPTS is, say,
Single, and assuming that the data type of the expression
Sum(Batting.FPTS)
can also be Single, where possibly it may need to be Double. You could
try,
for example, Sum(CDbl(Batting.FPTS)).

You also have some field names that are either reserved words or names of
functions, such as Year and Name. I wouldn't expect that to cause this
particular problem, but it wouldn't hurt to put square brackets around
your
field names just in case, e.g. Batting.[Year] and Teams.[Name].

--
Brendan Reynolds
Access MVP


Mayhew said:
I have a SELECT SQL query in Access 2003 that throws the error "The
field
is
too small to accept the amount of data you attempted to add. Try
inserting
or
pasting less data." I am not inserting or pasting anything. It is a
query
based on other queries, however, but I can't find why it doesn't work.
Here
are the SQL statements for the queries. "Batting", "Pitching", and
"Teams"
are tables, with pretty simple text and number data:

BScores:
SELECT Batting.Team, Batting.Period, Batting.Year,
Sum(CDbl(Batting.FPTS)) AS
BPTS
FROM Batting
WHERE Status='A'
GROUP BY Team, Period, Year;

PScores:
SELECT Pitching.Team, Pitching.Period, Pitching.Year,
Sum(CDbl(Pitching.FPTS))
AS
PPTS
FROM Pitching
WHERE Status='A'
GROUP BY Year, Team, Period;

TScores:
SELECT BScores.Team, BScores.Period, BScores.Year,
CDbl(BScores.BPTS)+CDbl(PScores.PPTS)
AS FPTS
FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND
(BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year);

ScoreSched (this query throws the error):
SELECT TScores.Team, TScores.Period, CDbl(TScores.FPTS), Teams.Name
FROM TScores, Teams
WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr;

If I copy the data from TScores into Excel and then into a straight
Access
table, the query works fine joining that table with Teams. I am hitting
my
head against the wall on this, it makes no sense to me.

The only thing the error implies to me is that there is some data type
mismatch, but all the referenced data types are exactly the same,
though
referenced through multiple layers of queries.

Please help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

1484 is the largest value for those fields. 2412 is the largest value for the
sum used in TScores.FPTS.

Brendan Reynolds said:
Are you dealing with very large numbers? What are the largest values in
BScores.BPTS and PScores.PPTS?

--
Brendan Reynolds
Access MVP

Mayhew said:
That did seem to be on the right path. The ScoreSched table doesn't throw
the
error anymore, until I add "TScores.[FPTS]" to what's selected. I've tried
adding CDbl() to all the PTS columns in each table, but I'm not exactly
sure
if I'm doing it right. I've modified the queries below how i've modified
them
in Access, and it still doesn't work (again, it's only ScoreSched that
throws
an error):

Any idea what I'm doing wrong?

Brendan Reynolds said:
One possibility would be that the sum of, for example, Batting.FPTS might
potentially be a much larger number than any of the individual values in
the
column. JET may be seeing that the data type of Batting.FPTS is, say,
Single, and assuming that the data type of the expression
Sum(Batting.FPTS)
can also be Single, where possibly it may need to be Double. You could
try,
for example, Sum(CDbl(Batting.FPTS)).

You also have some field names that are either reserved words or names of
functions, such as Year and Name. I wouldn't expect that to cause this
particular problem, but it wouldn't hurt to put square brackets around
your
field names just in case, e.g. Batting.[Year] and Teams.[Name].

--
Brendan Reynolds
Access MVP


I have a SELECT SQL query in Access 2003 that throws the error "The
field
is
too small to accept the amount of data you attempted to add. Try
inserting
or
pasting less data." I am not inserting or pasting anything. It is a
query
based on other queries, however, but I can't find why it doesn't work.
Here
are the SQL statements for the queries. "Batting", "Pitching", and
"Teams"
are tables, with pretty simple text and number data:

BScores:
SELECT Batting.Team, Batting.Period, Batting.Year,
Sum(CDbl(Batting.FPTS)) AS
BPTS
FROM Batting
WHERE Status='A'
GROUP BY Team, Period, Year;

PScores:
SELECT Pitching.Team, Pitching.Period, Pitching.Year,
Sum(CDbl(Pitching.FPTS))
AS
PPTS
FROM Pitching
WHERE Status='A'
GROUP BY Year, Team, Period;

TScores:
SELECT BScores.Team, BScores.Period, BScores.Year,
CDbl(BScores.BPTS)+CDbl(PScores.PPTS)
AS FPTS
FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND
(BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year);

ScoreSched (this query throws the error):
SELECT TScores.Team, TScores.Period, CDbl(TScores.FPTS), Teams.Name
FROM TScores, Teams
WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr;

If I copy the data from TScores into Excel and then into a straight
Access
table, the query works fine joining that table with Teams. I am hitting
my
head against the wall on this, it makes no sense to me.

The only thing the error implies to me is that there is some data type
mismatch, but all the referenced data types are exactly the same,
though
referenced through multiple layers of queries.

Please help!
Click to expand...
Click to expand...
Click to expand...
 
B

Brendan Reynolds

I'm afraid I'm at a loss, then. Those numbers should fit perfectly happily
into an Integer, let alone a Double.

--
Brendan Reynolds
Access MVP

Mayhew said:
1484 is the largest value for those fields. 2412 is the largest value for
the
sum used in TScores.FPTS.

Brendan Reynolds said:
Are you dealing with very large numbers? What are the largest values in
BScores.BPTS and PScores.PPTS?

--
Brendan Reynolds
Access MVP

Mayhew said:
That did seem to be on the right path. The ScoreSched table doesn't
throw
the
error anymore, until I add "TScores.[FPTS]" to what's selected. I've
tried
adding CDbl() to all the PTS columns in each table, but I'm not exactly
sure
if I'm doing it right. I've modified the queries below how i've
modified
them
in Access, and it still doesn't work (again, it's only ScoreSched that
throws
an error):

Any idea what I'm doing wrong?

:


One possibility would be that the sum of, for example, Batting.FPTS
might
potentially be a much larger number than any of the individual values
in
the
column. JET may be seeing that the data type of Batting.FPTS is, say,
Single, and assuming that the data type of the expression
Sum(Batting.FPTS)
can also be Single, where possibly it may need to be Double. You could
try,
for example, Sum(CDbl(Batting.FPTS)).

You also have some field names that are either reserved words or names
of
functions, such as Year and Name. I wouldn't expect that to cause this
particular problem, but it wouldn't hurt to put square brackets around
your
field names just in case, e.g. Batting.[Year] and Teams.[Name].

--
Brendan Reynolds
Access MVP


I have a SELECT SQL query in Access 2003 that throws the error "The
field
is
too small to accept the amount of data you attempted to add. Try
inserting
or
pasting less data." I am not inserting or pasting anything. It is a
query
based on other queries, however, but I can't find why it doesn't
work.
Here
are the SQL statements for the queries. "Batting", "Pitching", and
"Teams"
are tables, with pretty simple text and number data:

BScores:
SELECT Batting.Team, Batting.Period, Batting.Year,
Sum(CDbl(Batting.FPTS)) AS
BPTS
FROM Batting
WHERE Status='A'
GROUP BY Team, Period, Year;

PScores:
SELECT Pitching.Team, Pitching.Period, Pitching.Year,
Sum(CDbl(Pitching.FPTS))
AS
PPTS
FROM Pitching
WHERE Status='A'
GROUP BY Year, Team, Period;

TScores:
SELECT BScores.Team, BScores.Period, BScores.Year,
CDbl(BScores.BPTS)+CDbl(PScores.PPTS)
AS FPTS
FROM BScores INNER JOIN PScores ON (BScores.Team=PScores.Team) AND
(BScores.Period=PScores.Period) AND (BScores.Year=PScores.Year);

ScoreSched (this query throws the error):
SELECT TScores.Team, TScores.Period, CDbl(TScores.FPTS), Teams.Name
FROM TScores, Teams
WHERE TScores.Year=Teams.Year And TScores.Team=Teams.Abbr;

If I copy the data from TScores into Excel and then into a straight
Access
table, the query works fine joining that table with Teams. I am
hitting
my
head against the wall on this, it makes no sense to me.

The only thing the error implies to me is that there is some data
type
mismatch, but all the referenced data types are exactly the same,
though
referenced through multiple layers of queries.

Please help!
Click to expand...
Click to expand...
Click to expand...
 
C

cal

I have a similar problem with a twist. I just converted an Access 97
application to Access 2003 and started seeing this message. It is a
union query in which each piece works fine independently. This error
message is worthless.
 
Top