If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
$ 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! |
#2
|
|||
|
|||
$ Complex Access Query
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! |
#3
|
|||
|
|||
$ Complex Access Query
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. |
#5
|
|||
|
|||
$ Complex Access Query
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 |
Thread Tools | |
Display Modes | |
|
|