$ Complex Access Query

[david.barbetta]

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!

 

[David Glienna]

Need a Left-Join.

SELECT Persons.LastName, Persons.FirstName, Persons.GenderID,
Genders.GenderID, Genders.Gender
FROM Persons
INNER JOIN Genders
ON Persons.GenderID = Genders.GenderIDwhich will return columns that have
the same GenderIDEMAIL ME for PayPal info...

 

[david.barbetta]

Thanks, I wasn't stumped by the join, do you have any answers for any
of

- multiplying cost by quantity,
- selecting only entries that match the given criteria,
- grouping rows by drug_id,
- summarizing columns by date/time period?

Thanks.

 

[John Spencer]

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

 

[david.barbetta]

Haha, done (see below). Thanks!

Thank you for your generosity to the American Heart Association. You
can learn more about the impact this contribution makes by visiting
http://www.americanheart.org.

Donation Confirmation ID o11941500
Donation Date May 13, 2008 5:00 PM, CDT
Amount $50.00