If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Print all records on a report ONLY IF Invoice NO is same
How do I get all the detail records for the same invoice no. to appear on the
same page? |
#2
|
|||
|
|||
Print all records on a report ONLY IF Invoice NO is same
Presumably you have 2 tables, such as:
- tblInvoice (the header record), with fields: o InvoiceID primary key o ClientID relates to tblClient.Client o InvoiceDate Date/Time - tblInvoiceDetail (the line items), with fields: o InvoiceID relates to tblIvoice.InvoiceID o ProductID relates to tblProduct.ProductID o Quantity Number o PriceEach Currency etc. To create a report that looks like an invoice: 1. Create a query that uses both tables, along with tblClient and tblProduct. 2. Create a report based on this query. Output all the fields you will need for the report from the 4 tables. Also add a column for any calculated fields, e.g.: Amount: [Quantity] * [PriceEach] 3. In report design view, open the Sorting And Grouping dialog (View menu.) 4. In the first row of the dialog, choose the InvoiceID field, and in the lower pane of the dialog choose Yes for both Group Header and Group Footer. Access adds 2 new sections to the report - the InvoiceID Group Header (above the Detail), and the InvoiceID group Footer below. 5. Right-click the InvoiceID Group Header (the grey bar), and in the Properties box (Format tab), set the Force New Page property to Before. This ensures each invoice begins on a new page. 6. Increase the height of the InvoiceID Group Header to about 3 inches. In this section, add the fields for the invoice number, invoice number, and the address panel (customer name and address lines, placed where they will print correctly for a window-faced envelope.) 7. In the Detail section, add the fields for the line items. Cut their labels (Ctrl+X) and paste them into the InvoiceID Group Header. Place them in a row across the bottom of the section (below the address panel.) 8. Line up the fields in the Detail section side-by-side across the report, each one under its label. Access will print the InvoiceID header section once at the top of the invoice, and will then print as many Detail rows are needed for the invoice. 9. In the InvoiceID Group Footer (below the detail section), add a text box to show the total. For example: =Sum([Amount]) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Assistance" wrote in message ... How do I get all the detail records for the same invoice no. to appear on the same page? |
#3
|
|||
|
|||
Print all records on a report ONLY IF Invoice NO is same
Thank you very much Mr. Browne. This was very detailed and helpful. However,
my problem is that I have Invoice no. 8251 which has 3 detail records. Yet each record is appearing on a different page and the sum is totaling all the records for all the invoices. I need all the records for invoice 8251 or any other invoice which has multiple records to appear on the same page and sum for that invoice no. only. Is this possible? "Assistance" wrote: How do I get all the detail records for the same invoice no. to appear on the same page? |
#4
|
|||
|
|||
Print all records on a report ONLY IF Invoice NO is same
Okay, you have missed a step somewhere.
Possible examples: a) In the upper pane of query design, make sure the 2 tables are joined (i.e. there is a line from one to the other, on the matching fields (probably InvoiceID.)) b) In the report, make sure you have the InvoiceID header. c) If you are using a subreport, remove it and use tthe query with the 2 tables instead. d) Make sure your total is in the InvoiceID Group Footer, not in the Report Footer. e) Check that you are correctly applying the WhereCondition. and others. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Assistance" wrote in message ... Thank you very much Mr. Browne. This was very detailed and helpful. However, my problem is that I have Invoice no. 8251 which has 3 detail records. Yet each record is appearing on a different page and the sum is totaling all the records for all the invoices. I need all the records for invoice 8251 or any other invoice which has multiple records to appear on the same page and sum for that invoice no. only. Is this possible? "Assistance" wrote: How do I get all the detail records for the same invoice no. to appear on the same page? |
#5
|
|||
|
|||
Print all records on a report ONLY IF Invoice NO is same
Thank you!!! I have reviewed my steps and applied them per instructions. I
even have a nice looking Form with a subform for the Details. But it seems I do not know where to apply the WHERE conditions and others you mention below in e). Do I apply the WHERE condition in the Report Design on a print - event procedure to get all details for the same invoice number whether it's 806221 or 8251? And would this syntax be appropriate? Select * (all the records ) From Invoice detail table, Where invoice DetailID = invoice Id.invoice header table? "Allen Browne" wrote: Okay, you have missed a step somewhere. Possible examples: a) In the upper pane of query design, make sure the 2 tables are joined (i.e. there is a line from one to the other, on the matching fields (probably InvoiceID.)) b) In the report, make sure you have the InvoiceID header. c) If you are using a subreport, remove it and use tthe query with the 2 tables instead. d) Make sure your total is in the InvoiceID Group Footer, not in the Report Footer. e) Check that you are correctly applying the WhereCondition. and others. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Assistance" wrote in message ... Thank you very much Mr. Browne. This was very detailed and helpful. However, my problem is that I have Invoice no. 8251 which has 3 detail records. Yet each record is appearing on a different page and the sum is totaling all the records for all the invoices. I need all the records for invoice 8251 or any other invoice which has multiple records to appear on the same page and sum for that invoice no. only. Is this possible? "Assistance" wrote: How do I get all the detail records for the same invoice no. to appear on the same page? |
#6
|
|||
|
|||
Print all records on a report ONLY IF Invoice NO is same
The suggestion is to leave the report so that it shows all records when
opened from the Database window. Instead, the Click event of the button on your form sets the WhereCondition. So if the button is opened from the form where you have a record selected, it shows only that record. But if it is opened from the Database window, it shows all records. The linked article shows how to set up the WhereCondition for OpenReport in the Click event of your button. Here it is again: http://allenbrowne.com/casu-15.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Assistance" wrote in message ... Thank you!!! I have reviewed my steps and applied them per instructions. I even have a nice looking Form with a subform for the Details. But it seems I do not know where to apply the WHERE conditions and others you mention below in e). Do I apply the WHERE condition in the Report Design on a print - event procedure to get all details for the same invoice number whether it's 806221 or 8251? And would this syntax be appropriate? Select * (all the records ) From Invoice detail table, Where invoice DetailID = invoice Id.invoice header table? "Allen Browne" wrote: Okay, you have missed a step somewhere. Possible examples: a) In the upper pane of query design, make sure the 2 tables are joined (i.e. there is a line from one to the other, on the matching fields (probably InvoiceID.)) b) In the report, make sure you have the InvoiceID header. c) If you are using a subreport, remove it and use tthe query with the 2 tables instead. d) Make sure your total is in the InvoiceID Group Footer, not in the Report Footer. e) Check that you are correctly applying the WhereCondition. and others. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Assistance" wrote in message ... Thank you very much Mr. Browne. This was very detailed and helpful. However, my problem is that I have Invoice no. 8251 which has 3 detail records. Yet each record is appearing on a different page and the sum is totaling all the records for all the invoices. I need all the records for invoice 8251 or any other invoice which has multiple records to appear on the same page and sum for that invoice no. only. Is this possible? "Assistance" wrote: How do I get all the detail records for the same invoice no. to appear on the same page? |
Thread Tools | |
Display Modes | |
|
|