Summary Report from one Portal and other Database

[siou]

Hello and please help..I am so frustrated, I am in tears...really!

I cant figure this out and I have been working on it for a week and I have tried everything I know..please help. I am really at my wits end..

HELP??

INVOICES is a database that uses LINE ITEMS database to store all the information ordered on each individual serialized Invoice.

The LINE ITEMS database stores the styles ordered in the Portal In INVOICES, related record by related record.

In the INVOICES database, the totals for each invoice are summarized, such as tax totals and shipping totals.

I have to make a REPORT Database that summarizes the line items ordered by each invoice along with the totals for each invoice.

The report should

look like this and be sorted by invoice number and report on a group of invoices by date or by week or month.

INVOICE NUMBER

STYLE NUMBER DESCRIPTION SIZE COLOR QUANTITY RETAIL LINE TOTAL

TOTAL OF LINE TOTAL

TOTAL OF DISCOUNT

TOTAL OF SUBTOTAL

TOTAL OF TAX TOTAL

GRAND DAY TOTAL

The problem starts when I am trying to make this report in the LINE ITEMS database, and the information will only calculate correctly for the LINE ITEM TOTAL, which makes sense, because the calculation to add up the line items occurs in the LINE ITEMS database.

Basically, I cannot get the information to summarize correctly. I now after really going in some serious circles, that I cannot get it to summarize by each line item and then go to the Invoices database and add up all the information that is specific to each invoice.

I tried to make this Report in the Invoices database and it summarizes the totals correctly by the group of invoices but it will only show the first line of the portal in the body of the report.

I tried to make another third database that calculates the totals for each invoice and then another report that would call upon the information in both of the databases and then report on each, invoice by invoice, but It would not work.

It seems to be the problem that the calculations for the taxes and discounts and such are INVOICE based and so cannot seem to attach themselves correctly to the information stored for also each invoice.

I know I sound confused,...I am! Please help me?

[bruceR]

1. This report belongs in the Line Items file.

2. In the Invoices file you should have fields such as SumLineItems = sum(LineItems::LineTotal).

3. In Line Items there should be a relation back up to the invoice

4. Create a report in line items with parts:

subsummary by InvoiceID (print above)

body

subsummary by InvoiceID (print below)

In the subsummary (print above) display InvoiceID

In the subsummary (print below) display the related fields:

Invoices::SumLineItems

Invoices::Discount

Invoices::Subtotal

Invoices::Tax

Invoices::InvoiceTotal

etc.

[siou]

OK, I understand where you are going, and I incorporated this idea into my reports. The problem is this:

I can sub summarize this information for each invoice, by bringing the subtotals into the subsummary for each invoice, yes,

BUT;

what I really need is a daily report which is a total of all of those sums.

I need to have this summary information for each invoice but then at the end of the report have a grand summary of all of those totals for each day for any given group of invoices, by day or by week or by month.

I tried to make a Summary Field in the Invoices system that would be a total of the, for example Invoices totals, but when I put this into a grand summary, it just shows me the total of all of the invoices, not on the found invoices.

I think the problem is that the invoice infomation is not stored in the line items area, so that it cannot summarize this information.

Help?? Thanks so much for your time. I really really appreciate it! Ami

[bruceR]

You're right. They are two different reports.

[bruceR]

See my response to your other thread. This can be done with a date relationship going back up to the parent file.