All records not showing on report

[Guest]

Hello,

I have a sub form in my main form for an invoice database. The sub form is
set up in datasheet view and is for inputting cost center and dollar amounts
associated with an invoice number in the mian form. Everything works fine
except, when I pull a report based on a query, I can't seem to show all the
records unless I include all the information from the sub form. So if one
record has five cost centers, the report will show five lines associated with
the one invoice number. I only want to pull one line and then in the cost
center field show all the cost centers associated to that record. When I run
the query, everything shows. What am I doing wrong? Please help me and
simplify answer as I am not familar with sub forms. This is my first one.

Thanks!!!

 

[John Vinson]

Hello,

I have a sub form in my main form for an invoice database. The sub form is
set up in datasheet view and is for inputting cost center and dollar amounts
associated with an invoice number in the mian form. Everything works fine
except, when I pull a report based on a query, I can't seem to show all the
records unless I include all the information from the sub form. So if one
record has five cost centers, the report will show five lines associated with
the one invoice number. I only want to pull one line and then in the cost
center field show all the cost centers associated to that record. When I run
the query, everything shows. What am I doing wrong? Please help me and
simplify answer as I am not familar with sub forms. This is my first one.

Thanks!!!

The first thing to realize is that the Form is *JUST A WINDOW*. Your
report isnot based on the for, or on the subform; it's based on
Tables, and the Form is a tool for updating those tables. The Form
should be based on a Query.

If you want to take the five cost centers in the five records and
concatenate their names into a text string in a single textbox on the
report, see the code at

http://www.mvps.org/access/modules/mdl0004.htm


John W. Vinson[MVP]

 

[Guest]

Hi John,

My tables are updating and when I put together a query of the information I
want on the report and run the query, everything is there. The problem is,
viewing the report, I do not want five lines of the same information showing
with only the cost center being different. I just want to see one line (the
main information) and show the five cost centers. Otherwise, the report looks
very confusing.

Also, when I create the report in the report wizard, it creates a header for
me automatically. Everything will show on the report. If I move the fields
out of the header into details, I can only view some of the records, not all.
Example, I created 3 record using three different vendor names. When I moved
the fields out of the header, I created three more records one of each vendor
again giving me two reords for each vendor. My three originals showed and one
one of the new ones not the other two. My query seems to be working fine but
I think I am missing a step somewhere.

I would greatly appreciate any help you can give!!

 

[John Vinson]

Hi John,

My tables are updating and when I put together a query of the information I
want on the report and run the query, everything is there. The problem is,
viewing the report, I do not want five lines of the same information showing
with only the cost center being different. I just want to see one line (the
main information) and show the five cost centers. Otherwise, the report looks
very confusing.

Also, when I create the report in the report wizard, it creates a header for
me automatically. Everything will show on the report. If I move the fields
out of the header into details, I can only view some of the records, not all.
Example, I created 3 record using three different vendor names. When I moved
the fields out of the header, I created three more records one of each vendor
again giving me two reords for each vendor. My three originals showed and one
one of the new ones not the other two. My query seems to be working fine but
I think I am missing a step somewhere.

I have NO idea what the Recordsource query for your report might be;
all I can suggest is that you use the Report's Sorting and Grouping
dialog (right mouseclick the little square at the upper left
intersection of the rulers in design view) to group by your main table
information; put the controls you don't want repeated on the Header
for this group, and the cost center information (only) in the Detail
section. You'll get five lines of it.

I don't know what the wizard did with the report, but it may indeed
not be what you want. You may need to work on the design mode
yourself, and go beyond what the wizard (which of course knows nothing
about what you *want*, just what its designers expected that you might
want) can do.

John W. Vinson[MVP]

 

[Guest]

Hi John,

That was it! That's the step I was missing. I had everything either in Group
Header or Details and everything was messed up. I am still a novice with this
but find Access to be a fascinating tool and want to keep learning more.
Thank you for staying with me on this. Your help was greatly appreciated!!

Thanks!!