Re: Proper Query Summing

dancole42@xxxxxxxxx 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!

.

Relevant Pages

  • Counting records :-(
    ... I have a report that is linked to a query. ... the customer has ordered 5 items) the orderID is listed down the column 5 ... total number of individual orders. ...
    (microsoft.public.access.reports)
  • Re: Counting records :-(
    ... You need to add an OrderID group header section. ... > I have a report that is linked to a query. ...
    (microsoft.public.access.reports)
  • RE: How do I eliminate rows where field value count is =>10?
    ... let's assume you want to query the OrderDetails table in the sample ... want to return records with OrderID values with less than 3 similar OrderIDs. ... SELECT UniqueIdentifier ... I am building a report in Access 2003 from a table. ...
    (microsoft.public.access.reports)
  • Proper Query Summing
    ... OrderLines has fields like: ... OrderID ... so you won't ever see an invoice with products ... So I set up my query with the Orders, OrderLines, and TypeList tables. ...
    (comp.databases.ms-access)
  • Re: OrderID or DataTable
    ... It really depends on how your partner is building the report. ... Reporting Services, it makes sense to send him the OrderID as the query is ...
    (microsoft.public.dotnet.framework.adonet)