Query providing duplicate data when combined

[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 to the Group By portion of the query.

(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

 

[dhookom]

I expect you have not joined all the fields that make records unique or have duplicates in a table/query. It might help if you provided your tables with their relationships and primary keys.

Duane
Hook'D on Access
MS Access MVP

 

[bshaw03]

Ok, because I'm at work and the site to upload is blocked I'll try it this way: (all ID are autoset by Access and noted as the primary key for their respective tables)

TblContract
Cont_#
DO_#
Program_Title
Award_Date
DO_PoP_Start_Date
DO_PoP_End_Date

TblCLIN
ID (Primary key)
Cont_#
DO_#
CLIN_Type
Mod_#

TblFunding
ID(Primary key)
Cont_#
DO_#
CLIN_#
Cost_Funding
COM_Funding
Fee_Funding
Total_Funding
Appropriation
ACRN
Mod_#

TblValue
ID (Primary key)
Cont_#
DO_#
CLIN_#
Cost_Value
COM_Value
Fee_Value
Price_Value
Award_Fee_Earned
Mod_#


The Cont_# and DO_# in TblContract and TblCLIN are related. The Cont_#, DO_#, CLIN_# and Mod_# in TblCLIN is then related to the same fields in both the Tblfunding and TblValue.

Best as I can tell they are all one to one relationships. I originally thought to try and do a one to many in TblCOntract (using the Cont_#) to the TblCLIN (mod_# field) but kept getting an error about setting the index to "Yes (no Duplicates)" would cause duplicate values in the index, primary key or relationship.

Thanks.

 

[bshaw03]

There is an ID field in the first table as well - looks like I forgot to type that one in there.

 

[dhookom]

I have never seen so many joins that exclude the primary key fields of the tables involved. Some of the join fields should have unique indexes. Your error message seems to point to some issue with your data that you don't understand.

Duane
Hook'D on Access
MS Access MVP