$ Complex Access Query

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 structu

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!

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 Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008
wrote in message
...
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 structu

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!

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.

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

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 structu

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!

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