report total problem

[acepayne]

Hello,

I suspect there *should* be an easy way to do what I want, but I am having difficulty getting it right. Here goes...

Let's say I have the basic tables ORDERS and ORDERLINES. Each order can have many orderlines.

Now, I've created a query that gets the following:

From ORDERS: orderID, shipping
From ORDERLINES: product, qty, and linetotal (qty * price)

(I've simplified this somewhat)

I want a report that shows all orders (grouped by orderID), as well as all of the order lines for each order.

This is all fine, so far. At the end of the report, however, I would like the following:

1. The grand total of [linetotal] - OK
2. The grand total of shipping - this is where my problem lies.

I cannot figure out how to get a sum of the shipping. The shipping charge is part of each detail record as of now, but I just need to add one shipping amount per order.

Does this make sense? It should be easy enough to do, shouldn't it??!

Thanks for reading,
Gord

 

[WayneRyan]

Gord,

Are you saying that the shipping charge is replicated on each
record of ORDERLINES?

If so, then you should move it to the Orders table. This will
make your life a lot easier. With this configuration, I don't
even know (or want to know) what would happen if you
just moved the Detail field to the Order Section of the report.

Wayne

 

[acepayne]

Hi Wayne,

No, the shipping charge field is in the ORDERS table. Each record of my query, however, includes the shipping charge (it queries both the orders and orderlines tables).

I think I may have found a solution, however. By creating a text box in the footer for the order ID group, and setting it's source to the shipping charge AND setting the running sum property to Yes, I can then display whatever ends up there in the report's footer section.

If there is a better method, please let me know!

Thanks,
Gord

 

[Pat Hartman]

The usual way to do this is with a subreport. The order information including the shipping amount goes on the main report and the details go on the subreport. This is the same concept as your data entry form setup and is used whenever you have to report "one" side data and "many" side data on the same report/form. If you do this, don't forget to make separate queries for the main and subreports. The main query should only get data from the order table and the subreport query should only get data from the details table.

 

[acepayne]

Hey Pat,

This is probably the best way to do this, it would definitely be more organized. I just dislike using sub-reports, as I find them difficult to line up with the main report stuff at times.

Thanks for the help!

Cheers,
Gord

 

[KevinM]

I've always found that it's better to include things like shipping/Delivery/Service charge as a line item.
i.e. store it in the OrderDetails table.

Treat it just like another product and it makes totalling up a whole lot easier.

 

[acepayne]

Thanks, Kevin.

This is a good idea that I will keep in mind for future projects. For this one, though, the products are displayed in a web page. How would you go about "hiding" the shipping charge item(s). Would you add a field to the products table (a flag to indicate whether to show or not), or just hard code the query (select everything except shipping charge products). Probable the former, I'm guessing?

Thanks again,
Gord

 

[KevinM]

In my method there is no need to 'hide' or 'unhide' it.
It's either added as an 'item' or not.