Link to home
Start Free TrialLog in
Avatar of pdvsa
pdvsaFlag for United States of America

asked on

One to Many

Experts,

I have a defined one to many table relationship in the "table relationships"
In the qry, I will have duplicates because the relationship is one to many but in this qry I dont want to show the "many".
Can I limit the records?  Meaning dont show the duplicates?  


thank you.
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Use the DISTINCT keyword:

SELECT DISTINCT xyz
FROM tbl1 INNER JOIN tbl2 ON ... etc
Avatar of pdvsa

ASKER

oh yes I remember this DISTINCT word.  I have not used it before though.  It makes sense.  Will it always choose the first record?

thank you.  You only need DISTINCT in one location correct?
<<
 Will it always choose the first record?
>>

I believe so... If these records are truly duplicates, it should not matter.

However if some fields differ, you should consider a grouping query.  We'd need to see specific examples to help with that.
"dont show the duplicates"
1 to many relation holds data, like a father to children. In essence there are no duplicate records. In many table, each record has a unique composite key (FatherID + ChildID)

If you query the 1 table you get the Father with no duplicate records, and querying the many table you get his children, as well, with no duplicate records.

So what actually do you want to hide?
Avatar of pdvsa

ASKER

Well they are not true duplicates though.  
the relationship is between [LCID] and [LetterOfCreditID] but that is the only data that is a duplicate between the 2 tables.  I want to limit it to only one and not show if >1 on the Many side.  

I did put DISTINCT in the VBA but that did not limit the records.  It still showed duplicates on the LCID.
Avatar of pdvsa

ASKER

here is my VBA if that makes a difference.  
it is long

I put distinct

SELECT DISTINCT tblStatus.Status, tblBanks_Participating.BankType, tblLetterOfCredit.DateOfIssueSB, tblBusinessUnit.BusinessUnit, tblLetterOfCredit.amount, tblLCTypeDropbox.LCType, IIf([tblLCTypeDropbox.LCType] Like "*Parent*","Parent Guarantees to Issue","LC's Not Issued (No Issue Date Entered) **PCG's are below") AS MyGroup, tblLetterOfCredit.ExpectedIssuanceDate, IIf(IsNull([expectedIssuanceDate]),"No Expected Date ",Format$([tblLetterOfCredit].[ExpectedIssuanceDate],'\Qq yyyy')) AS [Expected Issue Date by Qtr], IIf([CurrencyID]=1,[tblLetterOfCredit].[Amount],([tblLetterOfCredit].[Amount]*[ExchangeRate])) AS [USD Equiv], tblCurrencyExchange.CurrencyName, Projects.ContractAmt, tblLetterOfCredit.Comments, tblLetterOfCredit.BankGTIssueDate, tblEndUser.[End User], Projects.ID, tblLetterOfCredit.LetterOfCreditID, tblCurrencyExchange.CurrencyID, tblCurrencyExchange.Currency, tblCurrencyExchange.ExchangeRate, tblLetterOfCredit.lcno, tblBanks_Participating.BankID, tblLetterOfCredit.FinalMaturity, tblLetterOfCredit.ExpiredYN, Projects.[Project Name]

FROM ((((((Projects RIGHT JOIN tblLetterOfCredit ON Projects.ID = tblLetterOfCredit.ProjectID) INNER JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID) LEFT JOIN tblCurrencyExchange ON tblLetterOfCredit.Currency = tblCurrencyExchange.CurrencyID) LEFT JOIN tblLCTypeDropbox ON tblLetterOfCredit.LCType = tblLCTypeDropbox.ID) LEFT JOIN tblStatus ON Projects.Status2 = tblStatus.ID) LEFT JOIN tblBusinessUnit ON Projects.BusinessUnit = tblBusinessUnit.BUID) LEFT JOIN (tblBankType_dropbox RIGHT JOIN tblBanks_Participating ON tblBankType_dropbox.BankTypeID = tblBanks_Participating.BankType) ON tblLetterOfCredit.LetterOfCreditID = tblBanks_Participating.LCID

WHERE (((tblStatus.Status) Not Like "*Dead*" And (tblStatus.Status) Not Like "*Lost*") AND ((tblLetterOfCredit.DateOfIssueSB) Is Null) AND ((tblBusinessUnit.BusinessUnit) Like "*Offshore" Or (tblBusinessUnit.BusinessUnit) Like "*Onshore*" Or (tblBusinessUnit.BusinessUnit) Like "*TOF*") AND ((tblLetterOfCredit.amount) Is Not Null) AND ((tblLCTypeDropbox.LCType) Not Like "*Incoming*" And (tblLCTypeDropbox.LCType) Not Like "*Payment Security*")) OR (((tblStatus.Status) Is Null) AND ((tblLetterOfCredit.DateOfIssueSB) Is Null) AND ((tblBusinessUnit.BusinessUnit) Like "*Offshore" Or (tblBusinessUnit.BusinessUnit) Like "*Onshore*" Or (tblBusinessUnit.BusinessUnit) Like "*TOF*") AND ((tblLetterOfCredit.amount) Is Not Null) AND ((tblLCTypeDropbox.LCType) Not Like "*Incoming*" And (tblLCTypeDropbox.LCType) Not Like "*Payment Security*"));
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yikes!  That is really complex.

I would suggest creating a table from the results of that query and working from there to reduce the duplicates.

You might also try the "sorting and grouping" in your report...

Can you post a sample database?
Avatar of pdvsa

ASKER

I thought there was a different way to do this.  Like a tick mark or something.  I see now that they are not true duplicates.  I will leave my condition on the field that I have now as this does limit the records.   thanks for that MAX tip.  I will write that down.

thanks....
Avatar of pdvsa

ASKER

Oh I see I could create a sort of temp table then maybe put a property on the LCID to NO DUPLICATES.   Then I wonder which ones would be removed.  Maybe I am not getting it.  

thanks..
You could use the same type of grouping query (Max)  on a temp table to pull the records you need.

The reason I suggested a separate table is that the query would be syntactically simpler...

But again, I think I'd be able to make better/more targeted suggestions if I could see a sample database.
Avatar of pdvsa

ASKER

thank you.  Need to get going....