Grouping Multiple Entries Into One Report Field (1 Viewer)

[ysquared86]

I am a total access novice, but I have been designing an order database for my company, and am trying to implement an automatic invoice-generating report based on my query from the tables.

The problem is that each item is entered as separate entries, and sometimes a bunch of them are supposed to go on the same invoice page. Of course, there is an [Invoice Number] field cuz I foresaw that I'm gonna have to group them eventually. But I'm at a loss as to how to do so.

Basically what I want is something that takes every entry with the same invoice number, pulls out the item number, price, quantity etc from each entry, then stacks them neatly into a table. So kind of like concatenating strings, but with tabular data...

Is this possible? I'd have to imagine it is. Please help.

 

[ysquared86]

Nobody?! really?

 

[WayPay]

Nobody?! really?

Really. Your question is really vague. Are you having a problem with a report you're trying to create, or do you not know how to create a report at all?

As an Access novice: Read up on Database Normalization.
As a forum novice: Read the sticky post http://www.access-programmers.co.uk/forums/showthread.php?t=85042on asking questions the smart way in the General forum.

In short, be clear on what exactly you want help on, and lots of people will be willing to help you .

 

[datAdrenaline]

I hate to answer this briefly but ... you can use Report "Grouping and Sorting", or you can use a Sub-Report control on your main report.

 

[ysquared86]

I know I said I'm an Access novice, but i'm not that much of a novice...I've worked with php and mySQL enough to know about databases.

I have my order history in "Orders" table. What this question concerns with are the product information inside the order (product name, unit price, quantity, etc.) and grouping them together. Every entry in "Orders" table has an [Invoice Number] field, because not every order has the same number of items sold, and I knew I was going to have to group them later on the same page of an invoice.

To automatically generate the invoice, I created a report, and the basic layout of the order info section of the invoice page is, a table with product name, unit price, quantity etc going across in the column headings on top. I want each item sold in that invoice to display under the headings, in a row all to itself. There is no set number of items in each invoice, and I want EVERY item with the same invoice number to display in a tabular form on the same page. I fiddled around with the group and sort functions but I can't seem to get it right. I'd imagine that's what I'd have to use like datAdrenaline said...

 

[WayPay]

Maybe this example will give you an idea.

 

[ysquared86]

Thanks, waypay. That helped, though I swear that's what I was doing before... I wonder where I went wrong...oh well, who cares now.

A couple more questions for you, if you don't mind...

1. is there a way to modify individual borders on a report table? as in, top/bottom/left/right border styles in each cell. i can't find anything like that yet can't believe they'd leave out something like that.

2. is there a way to "fill the page" automatically? as in, when the contents of the table don't fill up the page, something will push the footer all the way down to the bottom.

much appreciated.

 

[Rabbie]

Thanks, waypay. That helped, though I swear that's what I was doing before... I wonder where I went wrong...oh well, who cares now.

A couple more questions for you, if you don't mind...

1. is there a way to modify individual borders on a report table? as in, top/bottom/left/right border styles in each cell. i can't find anything like that yet can't believe they'd leave out something like that.

2. is there a way to "fill the page" automatically? as in, when the contents of the table don't fill up the page, something will push the footer all the way down to the bottom.

much appreciated.

Q1. Access doesn't have cells. Are you referring to text boxes or what?

Q2. The Page Footer wll always be at the bottom of the page even if there are only a few lines of data.

 

[ysquared86]

Thanks for your quick response.

Yeah, sorry. I meant text boxes.

As far as the footer goes:
As it stands right now, the report is generating a page for each order (each entry in the "Orders" table), and in report view they are all connected. I want to be able to print any of them on demand, with the same footer on each page and each starting with new page numbers, and that's why I thought i should put the footer in the group footer section instead of the page footer, but that doesn't allow me to put it at the bottom of the page. But in print preview they show up at the bottom, so I think I'm set about that. However, I feel like I'm using the report function the wrong way, yet I don't see how/why I would have to create individual reports or queries for each order. Is there an easier way to navigate through each invoice / organize them?

 

[WayPay]

Is there an easier way to navigate through each invoice / organize them?

Have a look at the VBA in the report. I don't know if it fully works (don't have enough test data to get a 2-page invoice) but it should help you along.

I can't find anything like that yet can't believe they'd leave out something like that.

I couldn't find it either, but I can believe it. Never met a report builder I liked.

Normally I'd suggest you consider doing the report in Word or Excel, but this might get more complicated than a simple Mail Merge.

 

[ysquared86]

I really do appreciate all your help.
That said, if at all possible, definitely leaning more toward just doing it in Word due to design limitations...is there a way to export a page into a word file? I saw that you can do an rtf export, but that leaves out all the formatting. Is there no way to export it while controlling where each element of the report goes in the word file?

 

[WayPay]

is there a way to export a page into a word file?

As far as I know, no.

Is there no way to export it while controlling where each element of the report goes in the word file?

I know of no way, but then I'm not a Word expert at all.