adding a field causes 90% of report to go away

  • Thread starter Thread starter cmpgeek
  • Start date Start date
C

cmpgeek

i just went in and added a field to my report and i went
from having 381 returns to 10! i know that it is not a
case of the field being null in the other returns because
this is a required field on the data entry side so it has
to have SOMETHING in it...

also, when i do not have the field in there, the 10
returns that were left are not listed at all...

(can i scream yet?)

does anyone have any idea why this would be happening?

i am using Access 2000
 
Adding a field to a report will not affect the number of records displayed.
Adding it to the underlying query will. Why don't you post your query so we
can see what it is pulling. Let us know which field it is that you added.

Rick B


i just went in and added a field to my report and i went
from having 381 returns to 10! i know that it is not a
case of the field being null in the other returns because
this is a required field on the data entry side so it has
to have SOMETHING in it...

also, when i do not have the field in there, the 10
returns that were left are not listed at all...

(can i scream yet?)

does anyone have any idea why this would be happening?

i am using Access 2000
 
i went through the steps you posted yesterday to my
question about adding a field to the report that was not
showing up in the list... i added it to the SQL design
grid (?) and it finally showed up in the field list window
so i could choose it and add it to the report. is there
another way to get it into the field list window so i can
add it to the report?

also - i apologize for sounding so dumb, but i just
discovered these newsgroups yesterday and i am not sure
how to attach anything to this post...

thanks for your help...
 
There is no other way to get a field in the field list other than including
it in the underlying query or record source.

To attach your query, just view it in SQL format, highlight it, copy it, and
paste it into your response.

Let us know which field is causing the problem. Unless you have criteria
under your field, it will not change the results of your report.

If the field is in a different table than the other records on your report,
and you created a relationship that only pulls certain records, that could
also do it. You would need to right-click on the line connecting the two
tables and edit the relationship so that it pulls all records from both
tables.

Rick B

i went through the steps you posted yesterday to my
question about adding a field to the report that was not
showing up in the list... i added it to the SQL design
grid (?) and it finally showed up in the field list window
so i could choose it and add it to the report. is there
another way to get it into the field list window so i can
add it to the report?

also - i apologize for sounding so dumb, but i just
discovered these newsgroups yesterday and i am not sure
how to attach anything to this post...

thanks for your help...
 
ok - here is my query:

SELECT [AS400].[acctnum], [AS400].[mrn], [AS400].[ssn],
[AS400].[dob], [AS400].[patlstnm], [AS400].[patfstnm],
[AS400].[admitdate], [AS400].[admtype], [AS400].
[admsource], [IPATHDBA_CASE].[CAS_CASENUM],
[IPATHDBA_CASE].[CAS_ACCOUNT], [IPATHDBA_CASE].
[CAS_MEDREC], [IPATHDBA_CASE].[CAS_LNAME], [IPATHDBA_CASE].
[CAS_FNAME], [IPATHDBA_CASE].[CAS_DOB], [IPATHDBA_CASE].
[CAS_DATE], [IPATHDBA_CASE].[CAS_PATSER],
[IPATHDBA_EMPMAS].[EMP_EMPNUM], [IPATHDBA_EMPMAS].
[EMP_LASTNM], [IPATHDBA_EMPMAS].[EMP_FIRSTNM],
[IPATHDBA_PATMAS].[PAT_SERIAL], [IPATHDBA_PATMAS].
[PAT_MEDREC], [IPATHDBA_PATMAS].[PAT_LAST],
[IPATHDBA_PATMAS].[PAT_FIRST], [IPATHDBA_PATMAS].
[PAT_SSN], [IPATHDBA_PATMAS].[PAT_DOB], [IPATHDBA_PATISR].
[PTI_SERIAL], [IPATHDBA_PATISR].[PTI_ISRCODE],
[IPATHDBA_INSURMAS].[ISR_CODE], [IPATHDBA_INSURMAS].
[ISR_NAME], [IPATHDBA_PROCEDURE].[PROC_DOCNUM],
[IPATHDBA_PROMAS].[PMF_DESCRP], [IPATHDBA_CASE].[CAS_SERV]
FROM ((((AS400 INNER JOIN IPATHDBA_CASE ON [AS400].
[acctnum]=[IPATHDBA_CASE].[CAS_ACCOUNT]) INNER JOIN
((IPATHDBA_PROCEDURE INNER JOIN IPATHDBA_PROMAS ON
[IPATHDBA_PROCEDURE].[PROC_NUM]=[IPATHDBA_PROMAS].
[PMF_NUM]) INNER JOIN IPATHDBA_EMPMAS ON
[IPATHDBA_PROCEDURE].[PROC_DOCNUM]=[IPATHDBA_EMPMAS].
[EMP_EMPNUM]) ON [IPATHDBA_CASE].[CAS_CASENUM]=
[IPATHDBA_PROCEDURE].[PROC_CASENUM]) INNER JOIN
IPATHDBA_PATMAS ON [IPATHDBA_CASE].[CAS_PATSER]=
[IPATHDBA_PATMAS].[PAT_SERIAL]) INNER JOIN IPATHDBA_PATISR
ON [IPATHDBA_PATMAS].[PAT_SERIAL]=[IPATHDBA_PATISR].
[PTI_SERIAL]) INNER JOIN IPATHDBA_INSURMAS ON
[IPATHDBA_PATISR].[PTI_ISRCODE]=[IPATHDBA_INSURMAS].
[ISR_CODE];


my problem field is: [IPATHDBA_CASE].[CAS_SERV] when i
added it to the SQL grid it caused this trouble...

when i originally made this report i designed it through
the wizard... doing it that way you choose which fields
you want to see and then later on - if you open your field
list the only fields you see are the ones you chose when
you first started the report... i dont know if that plays
a part in this or what... (i dont use the wizard options
when creating reports in Crystal, but since i am
practically brand new to Access i thought that would be
the better choice... i am now starting to wonder...)

thanks for taking all this time to help me...
 
That field is simply a select. It can't affect the results of your query.
The "join" can and a "where" statement could. If that were the only field
from that table, then I might start to wonder, but you are pulling
[CAS_CASENUM] from the same table.

Try taking the [CAS_SERV] out of your query and running it again. It should
not change which records are being pulled.

Any MVP's out there watching this post?

Any other ideas?

Rick B

ok - here is my query:

SELECT [AS400].[acctnum], [AS400].[mrn], [AS400].[ssn],
[AS400].[dob], [AS400].[patlstnm], [AS400].[patfstnm],
[AS400].[admitdate], [AS400].[admtype], [AS400].
[admsource], [IPATHDBA_CASE].[CAS_CASENUM],
[IPATHDBA_CASE].[CAS_ACCOUNT], [IPATHDBA_CASE].
[CAS_MEDREC], [IPATHDBA_CASE].[CAS_LNAME], [IPATHDBA_CASE].
[CAS_FNAME], [IPATHDBA_CASE].[CAS_DOB], [IPATHDBA_CASE].
[CAS_DATE], [IPATHDBA_CASE].[CAS_PATSER],
[IPATHDBA_EMPMAS].[EMP_EMPNUM], [IPATHDBA_EMPMAS].
[EMP_LASTNM], [IPATHDBA_EMPMAS].[EMP_FIRSTNM],
[IPATHDBA_PATMAS].[PAT_SERIAL], [IPATHDBA_PATMAS].
[PAT_MEDREC], [IPATHDBA_PATMAS].[PAT_LAST],
[IPATHDBA_PATMAS].[PAT_FIRST], [IPATHDBA_PATMAS].
[PAT_SSN], [IPATHDBA_PATMAS].[PAT_DOB], [IPATHDBA_PATISR].
[PTI_SERIAL], [IPATHDBA_PATISR].[PTI_ISRCODE],
[IPATHDBA_INSURMAS].[ISR_CODE], [IPATHDBA_INSURMAS].
[ISR_NAME], [IPATHDBA_PROCEDURE].[PROC_DOCNUM],
[IPATHDBA_PROMAS].[PMF_DESCRP], [IPATHDBA_CASE].[CAS_SERV]
FROM ((((AS400 INNER JOIN IPATHDBA_CASE ON [AS400].
[acctnum]=[IPATHDBA_CASE].[CAS_ACCOUNT]) INNER JOIN
((IPATHDBA_PROCEDURE INNER JOIN IPATHDBA_PROMAS ON
[IPATHDBA_PROCEDURE].[PROC_NUM]=[IPATHDBA_PROMAS].
[PMF_NUM]) INNER JOIN IPATHDBA_EMPMAS ON
[IPATHDBA_PROCEDURE].[PROC_DOCNUM]=[IPATHDBA_EMPMAS].
[EMP_EMPNUM]) ON [IPATHDBA_CASE].[CAS_CASENUM]=
[IPATHDBA_PROCEDURE].[PROC_CASENUM]) INNER JOIN
IPATHDBA_PATMAS ON [IPATHDBA_CASE].[CAS_PATSER]=
[IPATHDBA_PATMAS].[PAT_SERIAL]) INNER JOIN IPATHDBA_PATISR
ON [IPATHDBA_PATMAS].[PAT_SERIAL]=[IPATHDBA_PATISR].
[PTI_SERIAL]) INNER JOIN IPATHDBA_INSURMAS ON
[IPATHDBA_PATISR].[PTI_ISRCODE]=[IPATHDBA_INSURMAS].
[ISR_CODE];


my problem field is: [IPATHDBA_CASE].[CAS_SERV] when i
added it to the SQL grid it caused this trouble...

when i originally made this report i designed it through
the wizard... doing it that way you choose which fields
you want to see and then later on - if you open your field
list the only fields you see are the ones you chose when
you first started the report... i dont know if that plays
a part in this or what... (i dont use the wizard options
when creating reports in Crystal, but since i am
practically brand new to Access i thought that would be
the better choice... i am now starting to wonder...)

thanks for taking all this time to help me...
 
i didnt think it made sense either... i also noticed a
few minutes ago that if i open the design grid and close
it again without even clicking on the screen it does the
same thing... so i know its definitely not the field, but
still have no idea what is causing it...

thanks again for all your help on this - i still have at
least a month before they offer another Access class
here... most of the time i work in Crystal so i know the
basic concepts behind Access, but not the step-by-step
part of it...
 
Back
Top