Problem with the report or sub report??

[ttellis]

I haven't posted in a while, but this one has me going crazy!

I have a table that has a dynamic link to a list on Sharepoint which provides the data for my database. I have created a report that will serve as an inventory list. I created a a form with an unbound combo box that is used to select a box number, a query will run on that box number to select all records, and the report will open with the contents inventory of selected box. The contents control on the form is a subreport that combines all records into one report. I want 1 report for each box number:

Box Number 1234
Account Number 7777
Contents Owner Jane Doe
Contents Item 1 from record 1
Item 2 from record 2
Item 3 from record 3
Item 4 from record 4
Item 5 from record 5

The problem is that when the report opens, it has created a seperate report for each record. For example: If box number 1234 has 2 items, it will create 2 identical reports. If box number 4567 has 12 items, it will create 12 identical reports. In the example above, I will have that report....5 times!



Tiffany

 

[GingerR]

The problem is that when the report opens, it has created a seperate report for each record.

Do you mean it's created a separate PAGE for each record? Group on BOX NUMBER and make it a heading.

If I'm off base, please post your Recordsource for the report and any special report formatting such as Groups and Headers. Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

I expect your main report has a level of detail that should only be found in the subreport. For instance if you have a table of Orders and a table of OrderDetails. Your main report would include only the Orders table and the subreport would contain the OrderDetails table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ttellis]

Do you mean it's created a separate PAGE for each record? Group on BOX NUMBER and make it a heading.

It is grouped on Box Number. No, there's not a seperate page for each record. Continuing with the example I used in my original post, My report looks like this:

Box Number 1234
Account Number 7777
Contents Owner Jane Doe
Contents Item 1 from record 1
Item 2 from record 2
Item 3 from record 3
Item 4 from record 4
Item 5 from record 5

Box Number 1234
Account Number 7777
Contents Owner Jane Doe
Contents Item 1 from record 1
Item 2 from record 2
Item 3 from record 3
Item 4 from record 4
Item 5 from record 5

Box Number 1234
Account Number 7777
Contents Owner Jane Doe
Contents Item 1 from record 1
Item 2 from record 2
Item 3 from record 3
Item 4 from record 4
Item 5 from record 5

Box Number 1234
Account Number 7777
Contents Owner Jane Doe
Contents Item 1 from record 1
Item 2 from record 2
Item 3 from record 3
Item 4 from record 4
Item 5 from record 5

Box Number 1234
Account Number 7777
Contents Owner Jane Doe
Contents Item 1 from record 1
Item 2 from record 2
Item 3 from record 3
Item 4 from record 4
Item 5 from record 5

 

[ttellis]

I expect your main report has a level of detail that should only be found in the subreport. For instance if you have a table of Orders and a table of OrderDetails. Your main report would include only the Orders table and the subreport would contain the OrderDetails table.

All of my data on the report is coming from 1 table. The structure of the database is jacked...but I have to work with it as is because of how it's linked to Sharepoint. I can modify/create/delete everything BUT the table structure.

Table properties:
Field name Data Type
ID AutoNumber
Edit Hyperlink
Attachments Yes/No
Box Number text
Acct Number text
Contents Memo
Contents Owner Text
Ship To Text

 

[dhookom]

If the data is all coming from one report then either
[li]Don't use a subreport[/li]
[li]change your main report's record source to a totals query[/li]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GingerR]

So it should have read:

it has created multiple records for each Box Number

You said everything comes from one table, but I don't see fields for Item (Item 1, Item 2, etc) in your table field list?


Again, have you made a HEADER for BOX NUMBER? Do that, and put BOX NUMBER, Contents Owner and Account Number in it. Leave only the detail records in the DETAIL section.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[ttellis]

but I don't see fields for Item (Item 1, Item 2, etc) in your table field list?

There is a seperate record for each item. 'Item 1' is essentially record 1; 'Item 2' is record 2...so on and so forth.

In my report, there is both a BOX NUMBER header (contains box number & account number) and footer (contains a page break).

 

[GingerR]

I just made a table, with these records:

BoxNumber	AccountNumber	Item
7	             88	     item1
7	             88	     item2
7	             88	     item3
7	             88	     item4
8	             888        item1
8	             888        item2

In a report, i went to SORTING AND GROUPING, and on the first line picked BOX NUMBER. below, i picked GROUP HEADER = YES. On the report, i put BoxNumber and AccountNumber inside the BoxNumber header. I put only the Item in the detail section. It comes out like this:

Box Number: 7
Account Number: 88
Item1
Item2
Item3
Item4

Box Number: 8
Account Number: 888
Item1
Item2



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[ttellis]

I moved everything into the Box Number header exception for the Contents control and it works great!!!

Thanks...stars for GingerR and dhookom!!