Problem linking tables for a Totals query

[Paul Fenton]

I have two tables that relate to client invoices. The Activiy table
has the list of items that we bill for.

Field 1 - ID - Auto Number
Field 2 - Name - Text field with name of service.

The second table is ClientInvoice and has the actual client invoice
records. One of the fields in this table is a text field "Services"
and uses Activity as a lookup table to select the item we're billing
for. The structure of the ClientInvoice table is:

ID - Auto Number
File Number - text - Customer identification #
Services - text - the item being billed
Rate - Currency - $$ being billed
DateEntered - Date

What I want to do is get a total for each Activity that's been billed
in the ClientInvoice table and display these in alpha order. The
problem is that Services field in the ClientInvoice table is sorting
on the ID field from the Activity table. I can get the totals for
each activity with totals query but I can't get it to sort the way I
want to.

If I could somehow link these two tables in the query, then I could
sort on the Name field of the Activities table, but I can't figure out
how do do that.


Paul Fenton
(e-mail address removed)

 

[[MVP] S.Clark]

Assuming that ServiceID exists in the Invoices table, something like the
following should get you close:

Select ActivityName, Count(InvoiceID) From Services LEFT JOIN invoices ON
invoices.serviceid = services.serviceid
GROUP BY ActivityName

(Unfortunately, you used Activities and Services interchangably, but I'm
hoping they're the same thing. Nomenclature is a stickler for me.)

 

[Paul Fenton]

Thank you for the response, Steve and I do apologize for the confusion
with the names.

After I posted that message, I had another go at it and figured out
that I could build a link between Activities and ClientInvoice by
using another table that had fields common to the original two.

SELECT Activities.Name, Sum(ClientInvoice.Rate) AS SumOfRate
FROM Activities INNER JOIN (Files INNER JOIN ClientInvoice ON
Files.FileNumber = ClientInvoice.FileNumber) ON Activities.ActivityID
= Files.Activity

That worked and got me the desired result.

Thanks again and I use your Access Analyzer all the time. Great
product.


Paul Fenton