Query field size

[Guest]

I know this has been posted already, but I can't seem to figure out how to
get my query to not cut a field down to 255 chars. I've tried some of the
fixes I've found in other questions, but to no avail. Right now I'm back to
square one with my select query(quickly set-up).
The SQL is:


SELECT tblIncidents.IncidentID, tblIncidents.ReportNumber,
tblContactTypes.ContactType AS [Contact Type], tblIncidents.Team,
tblIncidents.IncidentDesc, tblIncidents.Department,
tblIncidents.Class_Injury, tblIncidents.Class_Property,
tblIncidents.FireCheck, tblIncidents.Class_Environmental
FROM (tblIncidents INNER JOIN tblIncidentContact ON tblIncidents.IncidentID
= tblIncidentContact.IncidentID) INNER JOIN tblContactTypes ON
tblIncidentContact.ContactID = tblContactTypes.ContactID
GROUP BY tblIncidents.IncidentID, tblIncidents.ReportNumber,
tblContactTypes.ContactType, tblIncidents.Team, tblIncidents.IncidentDesc,
tblIncidents.Department, tblIncidents.Class_Injury,
tblIncidents.Class_Property, tblIncidents.FireCheck,
tblIncidents.Class_Environmental
HAVING (((tblContactTypes.ContactType)=[Forms]![frmContSelect]![cboCont]))
ORDER BY tblIncidents.ReportNumber;


IncidentDesc is the field being cut off. It is set up as a memo field in
the tblIncidents.

I appreciate any help.

 

[Guest]

This will happen with Memo fields and doing things like Group By. To be
honest, if you are using the data in a memo field to differentiate between
records, your database has issues. A memo field is just a place to store a
lot of information. You really can't sort or group on it effectively. If the
data in the memo field is important to the structure of the record, it
shouldn't be in there.

Why are you doing a Group By anyway? You aren't summing, counting, or doing
any other math on the records being returned. If it's to get rid of duplicate
data, then there's a very good chance that your tables aren't normalized
properly.

Try removing the Group By and converting the Having to a Where clause. Then
you should see the entire Memo field.

Another option would be to remove tblIncidents.IncidentDesc from the Group
By and change the Select to something like Last(tblIncidents.IncidentDesc) .
In this case it just picks one of the tblIncidents.IncidentDesc so if there
is a difference between the data in IncidentDesc, you won't see it.

 

[Guest]

Hi,

This is going to be a little difficult as I just noticed that your query
references a combo box on a form. You will need to open the form and select
something from the combo box. Minimize the form - do not close it - and then
run the query below. See if there is a lot of duplicates or not.

SELECT tblIncidents.IncidentID, tblIncidents.ReportNumber,
tblContactTypes.ContactType AS [Contact Type], tblIncidents.Team,
tblIncidents.IncidentDesc, tblIncidents.Department,
tblIncidents.Class_Injury, tblIncidents.Class_Property,
tblIncidents.FireCheck, tblIncidents.Class_Environmental
FROM (tblIncidents INNER JOIN tblIncidentContact ON tblIncidents.IncidentID
= tblIncidentContact.IncidentID) INNER JOIN tblContactTypes ON
tblIncidentContact.ContactID = tblContactTypes.ContactID
WHERE (((tblContactTypes.ContactType)=[Forms]![frmContSelect]![cboCont]))
ORDER BY tblIncidents.ReportNumber;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm not trying to group by the memo field, that's just the way the query gets
automatically set up. I'm really not very experienced with the workings of
queries, so if you could give me an explanation of how to remove the Group By
and convert the Having to a Where clause that would be great. Sorry for not
really knowing what to do here.

This will happen with Memo fields and doing things like Group By. To be
honest, if you are using the data in a memo field to differentiate between
records, your database has issues. A memo field is just a place to store a
lot of information. You really can't sort or group on it effectively. If the
data in the memo field is important to the structure of the record, it
shouldn't be in there.

Why are you doing a Group By anyway? You aren't summing, counting, or doing
any other math on the records being returned. If it's to get rid of duplicate
data, then there's a very good chance that your tables aren't normalized
properly.

Try removing the Group By and converting the Having to a Where clause. Then
you should see the entire Memo field.

Another option would be to remove tblIncidents.IncidentDesc from the Group
By and change the Select to something like Last(tblIncidents.IncidentDesc) .
In this case it just picks one of the tblIncidents.IncidentDesc so if there
is a difference between the data in IncidentDesc, you won't see it.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I know this has been posted already, but I can't seem to figure out how to
get my query to not cut a field down to 255 chars. I've tried some of the
fixes I've found in other questions, but to no avail. Right now I'm back to
square one with my select query(quickly set-up).
The SQL is:


SELECT tblIncidents.IncidentID, tblIncidents.ReportNumber,
tblContactTypes.ContactType AS [Contact Type], tblIncidents.Team,
tblIncidents.IncidentDesc, tblIncidents.Department,
tblIncidents.Class_Injury, tblIncidents.Class_Property,
tblIncidents.FireCheck, tblIncidents.Class_Environmental
FROM (tblIncidents INNER JOIN tblIncidentContact ON tblIncidents.IncidentID
= tblIncidentContact.IncidentID) INNER JOIN tblContactTypes ON
tblIncidentContact.ContactID = tblContactTypes.ContactID
GROUP BY tblIncidents.IncidentID, tblIncidents.ReportNumber,
tblContactTypes.ContactType, tblIncidents.Team, tblIncidents.IncidentDesc,
tblIncidents.Department, tblIncidents.Class_Injury,
tblIncidents.Class_Property, tblIncidents.FireCheck,
tblIncidents.Class_Environmental
HAVING (((tblContactTypes.ContactType)=[Forms]![frmContSelect]![cboCont]))
ORDER BY tblIncidents.ReportNumber;


IncidentDesc is the field being cut off. It is set up as a memo field in
the tblIncidents.

I appreciate any help.

 

[Guest]

It worked fine. Getting any duplicates was never a problem, I just didn't
know how to get rid of the Group By thing that seemed to be cutting down the
field sizes. Thanks for the help

Hi,

This is going to be a little difficult as I just noticed that your query
references a combo box on a form. You will need to open the form and select
something from the combo box. Minimize the form - do not close it - and then
run the query below. See if there is a lot of duplicates or not.

SELECT tblIncidents.IncidentID, tblIncidents.ReportNumber,
tblContactTypes.ContactType AS [Contact Type], tblIncidents.Team,
tblIncidents.IncidentDesc, tblIncidents.Department,
tblIncidents.Class_Injury, tblIncidents.Class_Property,
tblIncidents.FireCheck, tblIncidents.Class_Environmental
FROM (tblIncidents INNER JOIN tblIncidentContact ON tblIncidents.IncidentID
= tblIncidentContact.IncidentID) INNER JOIN tblContactTypes ON
tblIncidentContact.ContactID = tblContactTypes.ContactID
WHERE (((tblContactTypes.ContactType)=[Forms]![frmContSelect]![cboCont]))
ORDER BY tblIncidents.ReportNumber;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm not trying to group by the memo field, that's just the way the query gets
automatically set up. I'm really not very experienced with the workings of
queries, so if you could give me an explanation of how to remove the Group By
and convert the Having to a Where clause that would be great. Sorry for not
really knowing what to do here.

This will happen with Memo fields and doing things like Group By. To be
honest, if you are using the data in a memo field to differentiate between
records, your database has issues. A memo field is just a place to store a
lot of information. You really can't sort or group on it effectively. If the
data in the memo field is important to the structure of the record, it
shouldn't be in there.

Why are you doing a Group By anyway? You aren't summing, counting, or doing
any other math on the records being returned. If it's to get rid of duplicate
data, then there's a very good chance that your tables aren't normalized
properly.

Try removing the Group By and converting the Having to a Where clause. Then
you should see the entire Memo field.

Another option would be to remove tblIncidents.IncidentDesc from the Group
By and change the Select to something like Last(tblIncidents.IncidentDesc) .
In this case it just picks one of the tblIncidents.IncidentDesc so if there
is a difference between the data in IncidentDesc, you won't see it.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I know this has been posted already, but I can't seem to figure out how to
get my query to not cut a field down to 255 chars. I've tried some of the
fixes I've found in other questions, but to no avail. Right now I'm back to
square one with my select query(quickly set-up).
The SQL is:


SELECT tblIncidents.IncidentID, tblIncidents.ReportNumber,
tblContactTypes.ContactType AS [Contact Type], tblIncidents.Team,
tblIncidents.IncidentDesc, tblIncidents.Department,
tblIncidents.Class_Injury, tblIncidents.Class_Property,
tblIncidents.FireCheck, tblIncidents.Class_Environmental
FROM (tblIncidents INNER JOIN tblIncidentContact ON tblIncidents.IncidentID
= tblIncidentContact.IncidentID) INNER JOIN tblContactTypes ON
tblIncidentContact.ContactID = tblContactTypes.ContactID
GROUP BY tblIncidents.IncidentID, tblIncidents.ReportNumber,
tblContactTypes.ContactType, tblIncidents.Team, tblIncidents.IncidentDesc,
tblIncidents.Department, tblIncidents.Class_Injury,
tblIncidents.Class_Property, tblIncidents.FireCheck,
tblIncidents.Class_Environmental
HAVING (((tblContactTypes.ContactType)=[Forms]![frmContSelect]![cboCont]))
ORDER BY tblIncidents.ReportNumber;


IncidentDesc is the field being cut off. It is set up as a memo field in
the tblIncidents.

I appreciate any help.