Query doubling results

[bshaw03]

Hello,



I am fairly new to access but thought I was getting the hang of it until I ran into this roadblock. I have 4 tables that I'm attempting to pull data from and put into a query. When I create the queries on an individual basis, they work fine (1 query for 3 of the tables). However, when I create one for all four tables at the same time, I am getting duplicate lines of data when certain elements are added to the "Group By" portion of the query. I have placed 3 queries SQL below - the first two work perfectly fine while the last one works up until the point that I add Funding and any associated elements from that table to the Group By portion of the query - then it doubles certain values under the cost part of the query as well as the funding portion. I know there is no duplicate data in the tables because it pulls correctly for the first two queries.

(1) QryValue - Designed to pull the value of given efforts

SELECT TblContract.[Cont_#], TblContract.[DO_#], TblCLIN.[CLIN_#], TblCLIN.CLIN_Type, TblCLIN.[Mod_#], TblValue.Cost_Value, TblValue.COM_Value, TblValue.Fee_Value, TblValue.Price_Value
FROM (TblContract INNER JOIN TblCLIN ON (TblContract.[Cont_#] = TblCLIN.[Cont_#]) AND (TblContract.[DO_#] = TblCLIN.[DO_#])) INNER JOIN TblValue ON (TblCLIN.[Cont_#] = TblValue.[Cont_#]) AND (TblCLIN.[DO_#] = TblValue.[DO_#]) AND (TblCLIN.[CLIN_#] = TblValue.[CLIN_#]) AND (TblCLIN.[Mod_#] = TblValue.[Mod_#])
GROUP BY TblContract.[Cont_#], TblContract.[DO_#], TblCLIN.[CLIN_#], TblCLIN.CLIN_Type, TblCLIN.[Mod_#], TblValue.Cost_Value, TblValue.COM_Value, TblValue.Fee_Value, TblValue.Price_Value;

(2) QryFunding - Designed to pull the funding of given efforts

SELECT TblContract.[Cont_#], TblContract.[DO_#], TblCLIN.[CLIN_#], TblCLIN.CLIN_Type, TblCLIN.[Mod_#], TblFunding.Cost_Funding, TblFunding.COM_Funding, TblFunding.Fee_Funding, TblFunding.Total_Funding, TblFunding.ACRN, TblFunding.Appropriation
FROM (TblContract INNER JOIN TblCLIN ON (TblContract.[DO_#] = TblCLIN.[DO_#]) AND (TblContract.[Cont_#] = TblCLIN.[Cont_#])) INNER JOIN TblFunding ON (TblCLIN.[Mod_#] = TblFunding.[Mod_#]) AND (TblCLIN.[DO_#] = TblFunding.[DO_#]) AND (TblCLIN.[CLIN_#] = TblFunding.[CLIN_#]) AND (TblCLIN.[Cont_#] = TblFunding.[Cont_#])
GROUP BY TblContract.[Cont_#], TblContract.[DO_#], TblCLIN.[CLIN_#], TblCLIN.CLIN_Type, TblCLIN.[Mod_#], TblFunding.Cost_Funding, TblFunding.COM_Funding, TblFunding.Fee_Funding, TblFunding.Total_Funding, TblFunding.ACRN, TblFunding.Appropriation;

(3) Qry1 - Designed (or trying to at least) to show the value and funding for given efforts at the same time

SELECT TblContract.[Cont_#], TblContract.[DO_#], TblCLIN.[CLIN_#], TblCLIN.CLIN_Type, TblCLIN.[Mod_#], TblValue.Cost_Value, TblValue.COM_Value, TblValue.Fee_Value, TblValue.Price_Value, TblFunding.Cost_Funding, TblFunding.COM_Funding, TblFunding.Fee_Funding, TblFunding.Total_Funding, TblFunding.ACRN, TblFunding.Appropriation
FROM ((TblContract INNER JOIN TblCLIN ON (TblContract.[Cont_#] = TblCLIN.[Cont_#]) AND (TblContract.[DO_#] = TblCLIN.[DO_#])) INNER JOIN TblValue ON (TblCLIN.[CLIN_#] = TblValue.[CLIN_#]) AND (TblCLIN.[Cont_#] = TblValue.[Cont_#]) AND (TblCLIN.[DO_#] = TblValue.[DO_#]) AND (TblCLIN.[Mod_#] = TblValue.[Mod_#])) INNER JOIN TblFunding ON (TblCLIN.[CLIN_#] = TblFunding.[CLIN_#]) AND (TblCLIN.[Cont_#] = TblFunding.[Cont_#]) AND (TblCLIN.[DO_#] = TblFunding.[DO_#]) AND (TblCLIN.[Mod_#] = TblFunding.[Mod_#])
GROUP BY TblContract.[Cont_#], TblContract.[DO_#], TblCLIN.[CLIN_#], TblCLIN.CLIN_Type, TblCLIN.[Mod_#], TblValue.Cost_Value, TblValue.COM_Value, TblValue.Fee_Value, TblValue.Price_Value, TblFunding.Cost_Funding, TblFunding.COM_Funding, TblFunding.Fee_Funding, TblFunding.Total_Funding, TblFunding.ACRN, TblFunding.Appropriation


Any help would be greatly appreciated. Thanks so much.

Billy

[June7]

I suspect 2 or more tables in this query are the 'many' side of relationship with another table.

Want to provide db for analysis? Follow instructions at bottom of my post.