10 Replies Latest reply on Jul 31, 2015 12:47 PM by willrollo

    Really struggling to copy an Access Report

      Title

      Really struggling to copy an Access Report

      Post

      I am in the last stages of completing a database based on an old MS Access (97!) DB that is nearly defunct.

      I am currently designing the reports part of the solution and have completed most except the overall order comparison report. I have attached a screen grab. It is designed to show the total orders from each month and year since the company started. It shows the year, month name, Nett total and order count. 

      I have no idea where to start as unsure of what layout mode would be best to show this data, or the use of portals etc. Or is there a better way to report this?

      My layout that has the required fields is called Invoice details. I am here to provide more info where necessary

      Thank you for tor time

        • 1. Re: Really struggling to copy an Access Report
          SteveMartino

          I don't think a portal will work because the number of columns would continue to grow.  Same for a cross-tab report.  You're best option may be to generate the report in the web viewer with HTML, or java script and using CSS to control the display.

          htttp://htmlhelp.com/reference/css/

           

          • 2. Re: Really struggling to copy an Access Report
            philmodjunk

            You can use portals to get columns of data for this if you can work from a fixed number of columns-- a set of 12, 1 row portals--one for each month would seem to serve here on a list view to get the rows of data. The layout would be based on a table where you create one new record for each year.

            The remaining details will depend on your basic data model. Do you have the typical data model of Invoices to line items to products?

            Is this report showing totals over all products sold or do you need to select a product and then the report lists the totals for that selected product?

            • 3. Re: Really struggling to copy an Access Report

              Hi Phil

              Thank you. That is more or less how far I had got - creating 1 field portals, 1o rows high (for ten years) and 12 of these columns in a row, depicting months. 

              I have created a new  direct relationship from INVOICEDETAILS::ID_Invoice_pk to (x) INVOICEDETAILS::ID_Invoice_pk  to help the portal work  (I think this is correct?)

              I was stuck populating these with correct data. My invoice details table does contain fields I have created for other reports, such as Invoice totals - these are calculated from related fields in tables Invoice items and products as usual. The totals I need are just totals of an order - regardless or the product type. So it is total quantity of orders in a month (doesn't matter who the order is for, how much or what the order consists of) and the total value of orders in the month. 

              How would I create one new record for each year as you suggest? 

               

              • 4. Re: Really struggling to copy an Access Report
                philmodjunk

                What happened to the screen shot?

                Yes, but I am specifying 1 row portals, not 10 row portals. You would use a new table for your report with one record for each year. You would then set up a relationship between this new table and one of your other tables that matches by year.

                And you never answered my other questions, which can make a significant difference in the details of this design as one option is to summarize data from the invoice details table and another would summarize data from the Invoices table. I'd double check some of the details of what you want here in the screen shot but can no longer see it...

                • 5. Re: Really struggling to copy an Access Report
                  /files/e9f9085614/2015-07-31_10.16.03_am.png 793x455
                  • 6. Re: Really struggling to copy an Access Report
                    philmodjunk

                    So your blue columns appear to be total invoice amounts for the year in Euros. What is the green column? The number of invoices? That seems too small a figure. And what "Quantity" is represented by your last column?

                    Which leads to me repeating a previous question: Is this a total of all invoices for a given year or all Invoice Data records for a specific product? both options are possible.

                    Also, given that these are invoices and thus not data that is modified over time, there are ways to set up a summary table so that producing such a report from pre-summarized data is much faster than from the original invoice (or invoice data) records.

                    • 7. Re: Really struggling to copy an Access Report

                      Sorry - I deleted it as thought you got the idea...

                      Anyway, I think I understand. Are you suggesting that I could create two tables (related) to show a comparison between two years' results? I would like to see as many years as I wish as per the attached image. Or is there a better way to show this report?

                      The invoice details table is in essence the invoices table. In my solution the invoice details table is the main table for all the order information. This data is used whether the record is a quote,order or invoice. The record gets a quote number or order number or invoice number from the related quote/order/invoice tables. A quote can be converted to an order, which can then be converted to invoice. Quite confusing but there we go! I hope this answers your query...Thank you

                      • 8. Re: Really struggling to copy an Access Report

                        Sorry sent last reply just as you sent yours! IN answer to your questions about the attachment.The green columns are total amount of Orders for the month for that year. The blue figures are the amount invoiced (total) for that month/year. The purple column at the end is the total number of orders for that year. There are total annual invoiced amounts too but off screen. It is a rather confusing report but I suppose not much choice in ways to show this sort of data

                        • 9. Re: Really struggling to copy an Access Report
                          philmodjunk

                          What I am describing would list summaries for all years for which you have invoices. Since I used to work for a small business that generated 300-1000 invoices a day, your monthly total numbers of invoices seem far too small so I wanted to be sure of what I was looking at.

                          I've also rethought the basic approach. One row portals can do this, but displaying the data would be very slow as each time that you bring up the report, it would have to recalculate all the aggregate values from the original invoices and invoice data tables.

                          A faster way (that I have used for a similar report), is to set up a nightly script that computes summary totals for the day's invoices and stores them in a simple number field for use in this report.

                          So your YearlyTable can simple by a series of Number fields, one for each total value or total count. Summary fields in your Invoices table can provide the total value of invoices for that month or year and the total count of invoices. (This only needs two summary fields.)

                          The relationship can be:

                          YearlySummaries------<Invoices
                          YearlySummaries::Year = Invoices::cYear    (Define cYear as Year ( InvoiceDate ). Enable "allow creation..." for Yearly Summaries.

                          The nightly summarize script can work like this:

                          #Update Yearly Totals
                          Enter Find Mode []
                          Set Field [Invoices::Date ; Year ( Get ( CurrentDate ) ) ]
                          Set Error Capture [on]
                          Perform Find []
                          If [ Get ( FoundCount ) // records were found ]
                             Set Field [YearlySummaries::YrlyQty ; Invoices::sInvoiceCount ]
                             Set Field [YearlySummaries::YrlyTotal ; Invoices::sInvoiceTotal ]
                             #Compute Monthly Totals
                             Enter Find Mode []
                             Set Field [Invoices::Date ; Month ( Get ( CurrentDate ) ) & "/" & Year ( Get ( CurrentDate ) ) ]
                             Perform Find []
                             If [ Get ( FoundCount ) // records were found ]
                                Set Variable [$MonthName ; value: Monthname ( Get ( CurrentDate ) ) ]
                                SetFieldByName [ "YearlySummaries::" & $MontName & "Total" ; Invoices::sInvoiceTotal ]
                                SetFieldByName [ "YearlySummaries::" & $MontName & "Count" ; Invoices::sInvoiceCount ]
                             End If
                          End IF

                          Data dictionary:
                          Invoices table:
                          cYear    Calculation field with number result type. Defined as year ( Date ) where "Date" is the name of your Invoice date field.
                          sInvoiceTotal: Summary field. Computes the total of your invoice total field in Invoices. (Same field gives you totals for month or year
                          sInvoiceCount: Summary field. Computes the count of InvoiceID or any never empty field in the table.
                          YearlySummaries table:
                          Year: Number field automatically loaded with the year the when created by the first Set Field step to execute for that year's summarized data.
                          YrlyQty: Number field. Store the total count of invoices for the specified year
                          YrlyTotal:  Number field. Store yearly Euro value of all invoices for the record's specified year.
                          JanuaryTotal: Number field. Stores January total Euro value for all January invoices for the specified year.
                          JanuaryCount: Number field. Stores January total Invoice count for all January invoices for the specified year.
                          FebruaryTotal: Number field. Stores February total value
                          FeburaryCount: Number field: Stores Feburary invoice count
                          And so forth....

                          Note that the fields with month names have to be very carefully and consistently named such that the Set Field By Name expressions can correctly calculate the names given. If you change how these fields are named, you have to make corresponding changes to the set field by name calculations.
                            

                           

                          • 10. Re: Really struggling to copy an Access Report

                            thank you- i shall attempt to have a go over the next few days