Proper Query Summing

[dancole42@gmail.com]

So I have an invoicing database based on two main forms: Orders and
OrderLines. Orders has fields like:

OrderID
BillingMethod
OrderDate
CreditCard
CCExp
OrdSubTotal
ShippingCharge
SalesTax
OrdTotal

OrderLines has fields like:

LineID
ProductType
Product
Price
Quantity
LineSubtotal
OrderID (relates back to OrderID in orders)

The ProductType field is a combo box based on a table called TypeList
that lists all the different types of products we offer, along with
what Department sells each product. Each Department only sells their
own type of Products, so you won't ever see an invoice with products
from multiple departments.

So on the Orders form, users can enter the basic order info and in the
OrderLines subform they enter as many different products as they want
on the invoice.

The Order table is linked to a separate Batch table so that all the
invoices for a particular day are entered into that day's invoice
batch.

Access 101, right? My problem comes with a report I want to run.

At the end of the day, I want a report based on a query that will show
me how much money was brought in by each Department.

So I set up my query with the Orders, OrderLines, and TypeList tables.
I relate the OrderIDs between the two Order Tables, and the
ProductTypeIDs between the OrderLines and TypeList tables.

I set up a where statement so only Orders from the current day's batch
appear in the query. This works fine. The problem is, I tell it to
Group By the Department field in the TypeList table, and Sum the
OrdTotal field from the Orders table. However, it's clearly
calculating many of the orders TWICE. When I turn off the query's
TOTALS option, I get a Select query that looks something like this:

Department OrdTotal
Database Subscription $995.00
Online $49.95
Online $49.95
Research $151.69
Research $151.69
Research $151.69
Online $49.95
Online $49.95
Online $49.95
Research $200.44
Research $200.44
Research $200.44
Research $200.44
Research $200.44
Research $134.64
Research $134.64
Research $134.64

Clearly it doesn't like the fact that I'm grouping by a value in one
table and trying to sum the values in another table.

So how SHOULD I be doing this?

Thanks!

[salad]

Re: Proper Query Summing

dancole42@gmail .com wrote:

So I have an invoicing database based on two main forms: Orders and
OrderLines. Orders has fields like:
>
OrderID
BillingMethod
OrderDate
CreditCard
CCExp
OrdSubTotal
ShippingCharge
SalesTax
OrdTotal
>
OrderLines has fields like:
>
LineID
ProductType
Product
Price
Quantity
LineSubtotal
OrderID (relates back to OrderID in orders)
>
The ProductType field is a combo box based on a table called TypeList
that lists all the different types of products we offer, along with
what Department sells each product. Each Department only sells their
own type of Products, so you won't ever see an invoice with products
from multiple departments.
>
So on the Orders form, users can enter the basic order info and in the
OrderLines subform they enter as many different products as they want
on the invoice.
>
The Order table is linked to a separate Batch table so that all the
invoices for a particular day are entered into that day's invoice
batch.
>
Access 101, right? My problem comes with a report I want to run.
>
At the end of the day, I want a report based on a query that will show
me how much money was brought in by each Department.
>
So I set up my query with the Orders, OrderLines, and TypeList tables.
I relate the OrderIDs between the two Order Tables, and the
ProductTypeIDs between the OrderLines and TypeList tables.
>
I set up a where statement so only Orders from the current day's batch
appear in the query. This works fine. The problem is, I tell it to
Group By the Department field in the TypeList table, and Sum the
OrdTotal field from the Orders table. However, it's clearly
calculating many of the orders TWICE. When I turn off the query's
TOTALS option, I get a Select query that looks something like this:
>
Department OrdTotal
Database Subscription $995.00
Online $49.95
Online $49.95
Research $151.69
Research $151.69
Research $151.69
Online $49.95
Online $49.95
Online $49.95
Research $200.44
Research $200.44
Research $200.44
Research $200.44
Research $200.44
Research $134.64
Research $134.64
Research $134.64
>
Clearly it doesn't like the fact that I'm grouping by a value in one
table and trying to sum the values in another table.

Although you gave a detailed description of your problem it wasn't an
adequate description.

What is the above? Detail? Is it correct? Do you want to see detail?
Or summary in the report? Or both?

If you use a nonTotals query are the results correct? Or are there
duplicates?

If you build a report you can set it up to be a summary report or detail
report.

I think you'd be better off creating a Select query than a Totals query.

If you have incorrect results, perhaps you've designed the query wrong.
But then, we don't see the SQL statement so we'd just be guessing.

>
So how SHOULD I be doing this?
>
Thanks!
>

[dancole42@gmail.com]

Re: Proper Query Summing

Those are the results of the following query:

SELECT Typelist.Depart ment, Order.OrdTotal
FROM TypeList INNER JOIN ([Order] INNER JOIN qryOrdLines ON
Order.OrderID = qryOrdLines.Ord erID) ON TypeList.Produc tType =
qryOrdLines.Pro ductType
WHERE (((Order.OrdBat chID)=[Forms]![Batch]![OrdBatchID]));

The results represent six actual invoices (six records in the Orders
table), but as you can see, because Department is tied to a field in
the OrderLINES table, the OrdTotal field from each of the six invoices
is being repeated for each record in the OrderLines table.

There are a couple of less-than-perfect solutions, as I see it:

1) Instead of using the OrdTotal value from the Orders table, just sum
the SubTotal values from the OrdLines table. The problem is, this
leaves out shipping and sales tax. I could list shipping and sales tax
as actual line items on the invoice, but that could screw a lot of
things up.

2) I could tie each invoice to a Department, rather than each line
item to a Department, but there are occasions where multiple
departments contribute to one invoice.

I hope this helps clarify things. Thanks!

[salad]

Re: Proper Query Summing

dancole42@gmail .com wrote:

Those are the results of the following query:
>
SELECT Typelist.Depart ment, Order.OrdTotal
FROM TypeList INNER JOIN ([Order] INNER JOIN qryOrdLines ON
Order.OrderID = qryOrdLines.Ord erID) ON TypeList.Produc tType =
qryOrdLines.Pro ductType
WHERE (((Order.OrdBat chID)=[Forms]![Batch]![OrdBatchID]));
>
The results represent six actual invoices (six records in the Orders
table), but as you can see, because Department is tied to a field in
the OrderLINES table, the OrdTotal field from each of the six invoices
is being repeated for each record in the OrderLines table.
>
There are a couple of less-than-perfect solutions, as I see it:
>
1) Instead of using the OrdTotal value from the Orders table, just sum
the SubTotal values from the OrdLines table. The problem is, this
leaves out shipping and sales tax. I could list shipping and sales tax
as actual line items on the invoice, but that could screw a lot of
things up.
>
2) I could tie each invoice to a Department, rather than each line
item to a Department, but there are occasions where multiple
departments contribute to one invoice.
>
I hope this helps clarify things. Thanks!
>

I understand why you'd get multiple departments now.

Since your shipping and tax is rolled up per order and you can have
multiple departments in one order I'd say you are SOL. I suppose you
want to present the report by each department too.

It's easy enough using your scenario to get the tax. But the shipping
calc may not be as easy. I suppose you could get a percentage of the
shipping per department/orderitem and calc it out but due to rounding
you may be a few cents off.

I would do what I can to create fields for your report. And then do
your calculations inside the report. I really believe you are trying to
do too much in one query. Another avenue to explore is to divide the
"functions" of your query into sub queries. For example, create a query
that sums up values by department/product. Then link your Order file to
that query instead of the orderitems table.

[dancole42@gmail.com]

Re: Proper Query Summing

On Mar 24, 6:52 pm, salad <o...@vinegar.c omwrote:

dancol...@gmail .com wrote:

Those are the results of the following query:

>

SELECT Typelist.Depart ment, Order.OrdTotal
FROM TypeList INNER JOIN ([Order] INNER JOIN qryOrdLines ON
Order.OrderID = qryOrdLines.Ord erID) ON TypeList.Produc tType =
qryOrdLines.Pro ductType
WHERE (((Order.OrdBat chID)=[Forms]![Batch]![OrdBatchID]));

>

The results represent six actual invoices (six records in the Orders
table), but as you can see, because Department is tied to a field in
the OrderLINES table, the OrdTotal field from each of the six invoices
is being repeated for each record in the OrderLines table.

>

There are a couple of less-than-perfect solutions, as I see it:

>

1) Instead of using the OrdTotal value from the Orders table, just sum
the SubTotal values from the OrdLines table. The problem is, this
leaves out shipping and sales tax. I could list shipping and sales tax
as actual line items on the invoice, but that could screw a lot of
things up.

>

2) I could tie each invoice to a Department, rather than each line
item to a Department, but there are occasions where multiple
departments contribute to one invoice.

>

I hope this helps clarify things. Thanks!

>
I understand why you'd get multiple departments now.
>
Since your shipping and tax is rolled up per order and you can have
multiple departments in one order I'd say you are SOL. I suppose you
want to present the report by each department too.
>
It's easy enough using your scenario to get the tax. But the shipping
calc may not be as easy. I suppose you could get a percentage of the
shipping per department/orderitem and calc it out but due to rounding
you may be a few cents off.
>
I would do what I can to create fields for your report. And then do
your calculations inside the report. I really believe you are trying to
do too much in one query. Another avenue to explore is to divide the
"functions" of your query into sub queries. For example, create a query
that sums up values by department/product. Then link your Order file to
that query instead of the orderitems table.

Okay, I figured it out. I created separate queries. One gets the
Subtotal for each invoice by summing the OrdLines subtotal field -
because it's not based on the Orders table, it is able to properly
group by Department. The second query is based entirely on the Orders
table, and sums the shipping, tax, and gives the final total. The
third query joins this two together. The final report gives the
subtotals and departments in the Details, and the shipping, tax, and
final totals in the footer.

Thanks!