Combing 10 querries into One with Use of Start and End Date

[PharmGuyFl]

Hello,

I’m trying to run a query that displays the results of 10 separate queries on one query. I’m very new to access, and this forum has been very helpful in the past, so I’m hoping maybe I’ll get lucky again. Thanks guys and gals in advance.
The first query is displays the summary results of what a customer purchased across all contract types. The other 9 queries display the summary results of what a person bought dependent upon what contract type they were purchasing under using a WHERE clause under Contract_Type. The 9 contract types are DST, GPO, GVT, HOS, PBM, PHY, RTL, SLS, WSP.



I would like for my query to output everything together by showing Year, Customer_Id, DEA_Number, Customer_Type, Customer_Name, package_type_code, Sum Amt, DST Amt, GPO Amt, GVT Amt, HOS Amt, PBM Amt, PHY Amt, RTL Amt, SLS Amt, and WSP Amt.

I’m not really sure what I need to do this. Which fields need to be joined and what type of join needs to be used? Or do the fields even need to be joined at all? The first is the example of the sum query; the other is an example of the query with the WHERE clause is used for contract type.

Example of Summary Querry

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT "CARDCUST-2010" AS Name, Format([dbo_WS_Sale].[create_date],"yyyy ") AS [Year], dbo_WS_Customer.Customer_Id, dbo_WS_Customer.DEA_Number, dbo_WS_Customer.Customer_Type, dbo_WS_Customer.Customer_Name, dbo_INV_ItemPackage_M.package_type_code, Sum([Quantity]*[Vials_Per_Unit]) AS [Sum Vials], Sum([quantity]*[contract_price_per_unit]) AS [Sum Amt]
FROM ((((dbo_WS_Sale INNER JOIN dbo_Item ON dbo_WS_Sale.Item_Id = dbo_Item.Item_ID) INNER JOIN dbo_WS_Customer ON dbo_WS_Sale.ws_customer_id = dbo_WS_Customer.Customer_Id) INNER JOIN dbo_INV_ItemPackage_M ON dbo_Item.package_type_code = dbo_INV_ItemPackage_M.package_type_code) INNER JOIN dbo_WS_Contract ON dbo_WS_Sale.Contract_No = dbo_WS_Contract.Contract_No) INNER JOIN dbo_WS_ContractType ON dbo_WS_Contract.Contract_Type = dbo_WS_ContractType.Contract_Type
WHERE (((dbo_WS_Sale.create_date) Between [Enter Start Date] And [Enter End Date]) AND ((dbo_WS_Sale.Whole_Seller_Id)=202))
GROUP BY Format([dbo_WS_Sale].[create_date],"yyyy "), dbo_WS_Customer.Customer_Id, dbo_WS_Customer.DEA_Number, dbo_WS_Customer.Customer_Type, dbo_WS_Customer.Customer_Name, dbo_INV_ItemPackage_M.package_type_code;

Example of querry with where clause.

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT Format([dbo_WS_Sale].[create_date],"yyyy ") AS [Year], dbo_WS_ContractType.Contract_Type, dbo_WS_Customer.Customer_Id, dbo_WS_Customer.DEA_Number, dbo_WS_Customer.Customer_Type, dbo_WS_Customer.Customer_Name, dbo_INV_ItemPackage_M.package_type_code, Sum([Quantity]*[Vials_Per_Unit]) AS [WSP Vials], Sum([quantity]*[contract_price_per_unit]) AS [WSP Amt]
FROM ((((dbo_WS_Sale INNER JOIN dbo_Item ON dbo_WS_Sale.Item_Id = dbo_Item.Item_ID) INNER JOIN dbo_WS_Customer ON dbo_WS_Sale.ws_customer_id = dbo_WS_Customer.Customer_Id) INNER JOIN dbo_INV_ItemPackage_M ON dbo_Item.package_type_code = dbo_INV_ItemPackage_M.package_type_code) INNER JOIN dbo_WS_Contract ON dbo_WS_Sale.Contract_No = dbo_WS_Contract.Contract_No) INNER JOIN dbo_WS_ContractType ON dbo_WS_Contract.Contract_Type = dbo_WS_ContractType.Contract_Type
WHERE (((dbo_WS_Sale.create_date) Between [Enter Start Date] And [Enter End Date]) AND er 
((dbo_WS_Sale.Whole_Seller_Id)=202) AND ((dbo_WS_ContractType.Contract_Type)="WSP"))
GROUP BY Format([dbo_WS_Sale].[create_date],"yyyy "), dbo_WS_ContractType.Contract_Type, dbo_WS_Customer.Customer_Id, dbo_WS_Customer.DEA_Number, dbo_WS_Customer.Customer_Type, dbo_WS_Customer.Customer_Name, dbo_INV_ItemPackage_M.package_type_code;

[rpeare]

If the queries all show the same fields but the only thing that changes is the contract type why are you making them all separate queries to start with.

You could have a prompt in your query like:

[Enter the contract type] in the criteria

or create a form that runs your setup and on that form have a combo box listing the contract types then in the criteria of your query you'd have

[Forms]![Setup_Form]![ComboBox_Name]

if you wanted to do multiple contract types, again assuming they all show the same fields, you could do that with a list box and a little code.

Having a separate query for every possible variation of your data is really cumbersome and unnecessary.


The short way to get to what you want to do is to use a UNION query

Let's say your first query is called Query_WSP and your second query is called Query_XXX and your third is Query_YYY

where WSP and XXX are two contract types.

If they both have the same number of fields that show the same data you can combine them like:

SELECT * FROM Query_WSP
UNION ALL
SELECT * FROM Query_XXX
UNION ALL
SELECT * FROM Query_YYY

[PharmGuyFl]

Thanks Rpeare for the suggestion! I just ran the querry and grouped it by Contract_Type. I then used that data as the source data for pivot table in excel that used the year and then contract type as the column heading. This effectively allowed us to effectively visualize summary amounts for a customer by year and contract type. This allows the pivot table to display The Year 2007 with the summary of sales under the appropriate contract type, then 2008, 2009... to current. This spreadsheet t can be easily filtered using the other fields, so my manager can see which territories and accounts we need to focus on switching to a more profitable contract type. Sorry for the late reply back. I have been slammed at work. I just had a problem and was using the wrong tool at the end point of the process. Once again, Thanks Guys!