SELECT td_Drug_ID
, Format(inv_DateBilled,"yyyy-q") as Quarter
, SUM(td_cost_ea * td_quantity) as TheCost
FROM Invoice as I INNER JOIN Ticket As T
ON I.inv_BillNo = T.t_BillNo
WHERE inv_sitename = "Site 1"
and inv_payor_type = "Payor 1"
and td_therapy_type = "Therapy 1"
and inv_DateBilled Between #1/1/2007# and #12/31/2007#
GROUP BY td_Drug_ID,
Format(inv_DateBilled,"yyyy-q")
A crosstab query would allow you to reorient that, but I don't know if you can
execute the crosstab query from Excel. The crosstab would look something like
the following
TRANSFORM SUM(td_cost_ea * td_quantity) as TheCost
SELECT td_Drug_ID
, SUM(td_cost_ea * td_quantity) as TheTotalPeriodCost
FROM Invoice as I INNER JOIN Ticket As T
ON I.inv_BillNo = T.t_BillNo
WHERE inv_sitename = "Site 1"
and inv_payor_type = "Payor 1"
and td_therapy_type = "Therapy 1"
and inv_DateBilled Between #1/1/2007# and #12/31/2007#
GROUP BY td_Drug_ID
PIVOT Format(inv_DateBilled,"yyyy-q")
If this solves your problem and you are still willing to pay, send the money
to a local charity or to the American Heart Association.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
(E-Mail Removed) wrote:
> I would be happy to pay $50 via Paypal to the first person to
> successfully answer my question.
>
> I am using Access 2003 and Excel 2003 (I'm hoping to be able to pull
> the output of the following query into Excel). I have two tables and
> I'm trying to create a complex query. These table represent invoices
> (Invoice) and delivery records (Ticket). Table structure:
>
> Table 1: Invoice
> inv_datebilled
> inv_billno
> inv_payor_type
> inv_expected
> inv_sitename
>
> Table 2: Ticket
> t_billno
> td_therapy_type
> td_drug_id
> td_cost_ea
> td_quantity
>
> The tables are not linked via a relationship but the "inv_billno" &
> "t_billno" items match up, and each invoice could have several tickets
> (1 to many).
>
> I'm trying to get the following info:
>
> Select sum of (td_cost_ea * td_quantity)
> from (union of the tables)
> where inv_sitename = "Site 1"
> and inv_payor_type = "Payor 1"
> and td_therapy_type = "Therapy 1"
> group rows by td_drug_id
> group columns by quarters (Q1 2007 is 1/1/2007 to 3/31/2007, Q2 is
> 4/1/2007 to 6/30/2007, etc) based on inv_datebilled
>
> So the output would be a table with a row for each drug_id and a
> column for each calendar quarter. The data values would be a total
> amount spent (td_cost_ea times td_quantity), which would sum all
> entries where the conditions (e.g. inv_sitename = "Site 1") were
> satisfied.
>
> This could be done in one query or a query of a query. If you can't
> do the entire thing or it can't be done, then if you could get me most
> of the way along (e.g. columns are individual dates instead of
> quarters) I would pay you something. Note that I'm hoping to send the
> SQL programmatically from MS Excel so I think there's a limit on the
> length of the SQL string.
>
> Thanks!
Similar Threads
