Need help with a report

[jzwp11]

I received your e-mail regarding the additional data you want in your report. First, I would create a new query that has all of the fields you want and then base the report on that query. I created the query called qryForOrderSummaryReport in the attached DB. It brings in all of the fields from the RTP, Order Details, Employee and Suppliers tables. I would rebuild the totals you mention within the report itself. You could do it in the query, but the report should be able to do the same.

[Tari]

Thanks again

So I've been playing with the report and here are the last speed bumps:

- I need a drop down as part of the supplier information that let's me choose whether the currency is USD or CAD, this information then has to be displayed on the report. I added the table with the entry, but I have no clue how to add it to the supplier form or to the report query

- I don't know how to calculate on a report. Therefore I'm missing 3 sums. I attached a scan of the printed report

- In the description part of the report is the product field. it always shows the same one, even if the order has 2 or 3 different product entries. I need it to show each one of them in it's own line with a max of 10

- The G/L Code does not display

- when you print it, it prints the report on one page with lots of white pages behind it. Can you set it to only print the first page?


Those should be the last speed bumps I ran into. I tried to do as much as I can by myself

[jzwp11]

I need a drop down as part of the supplier information that let's me choose whether the currency is USD or CAD, this information then has to be displayed on the report. I added the table with the entry, but I have no clue how to add it to the supplier form or to the report query

Does the currency type relate to the supplier or to certain RTP's (but not all RTPs) of a supplier? If it relates to the supplier, then you would add the field to that table (or a foreign key that relates back to the table you created). Does the currency choice impact the actual values shown in your forms and reports? In other words, do you have to apply a conversion factor to convert from one currency to another? If so that calculation will have to be dealt with either in the query or the report.

Once you add it to the appropriate table, the query should just bring it in as another field. From there you can add it to the report/form.

I don't know how to calculate on a report. Therefore I'm missing 3 sums. I attached a scan of the printed report

You have to add additional controls and set the control source to an expression. To do totals you have to do those in either a group header or footer. I've added the footer and the appropriate controls.

In the description part of the report is the product field. it always shows the same one, even if the order has 2 or 3 different product entries. I need it to show each one of them in it's own line with a max of 10

You added the controls multiple times in the detail section, you only add the controls once. The number of detail records, determines the number of lines that will show. I do not know how to limit the number of detail lines to 10 to a page (reports are not my strong suit). You might ask that specific question via a new thread on the forum.

The G/L Code does not display

You were actually seeing the primary key related to the code. I had to add the gl code table to the query and add the actual code field to the underlying query. I did the same for the department rather than having a drop down on the report.

- when you print it, it prints the report on one page with lots of white pages behind it. Can you set it to only print the first page?

The amount of white space is governed by the area of the various sections. If you close up the sections such that only the controls are visible in design view, you close up the white space in the report. The number of pages you get depends on the amount of extra space on your report as well as the number of detail records. If you have a lot of detail records, you will go beyond 1 page unless you find a solution to your question about limiting the number of detail records per page.

[Tari]

Hey,

I added the currency field to the suppliers table and added that field to the query. thanks for guideing

Rest works and looks good. The auto expanding product lines faszinate me

Thank you!

[jzwp11]

You're welcome. Good luck with your project.