Recent content by dbasch

Eureka! Eureka! That is perfect Pat! Finally it works! Thank you so much! Directormac: Yes, technically test #3 would not be required if the records were only in one table. However they are in two, so I must check that a record from the [Web And Referral Leads] table does not exist in the ACT...

Almost. You are correct that rule #2 is redundent. Rule #3 however, is still needed because ( && !Y) must also be satisfied. So revised it would be: 1: X if (Y) 2: !X if (Z && !Y) A company is considered qualified if: 1) It is in the ACT table A company is considered unqualified if: 1)...

I see what you mean. How can Jet know which company records field values to include with a grouped company record. It cant. I guess this would explain why Jet only allows for grouping by all fields included in the SELECT statement. Here you go. Here is an example original recordset: Company...

No Go Joe Looks like everyone got sick of dealing with this one. Bummer :( Moderator: could you please rename this thread to 'Grouping duplicate field data' and move it to the 'queries' section? Derek

Pat, I am using it. A company is considered qualified if: 1) It is in the ACT table 2) It is in the ACT table and in the [Web And Referral Leads] table FROM ACT LEFT JOIN [Web And Referral Leads] ON [ACT].[Company]=[Web And Referral Leads].[Company] The above LEFT JOIN from the...

Pat, Thanks for the reply. That makes sense. So, I still can't use the GROUP BY statement to group the 1 side data that is represented multiple times. This is because the GROUP BY statement requires that I include every field declared in the SELECT statement. I have multiple fields so...

Nevermind, scratch the below! :mad: Upon further investigation I discovered these gems: All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function. groupfieldlist - The names of up to 10 fields used to group...

AHAH! GROUP BY! That is what I needed. Thanks FoFa. GROUP BY Clause Combines records with identical values in the specified field list into a single record. A summary value is created for each record if you include an SQL aggregate function, such as Sum or Count, in the SELECT statement. :D

Ok, this brings me back to my original problem. I was unable to utilize the DISTINCT function to remove duplicates because: If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results. So, the...

Hello, How do I remove duplicate records from a report accross the entire report? According to the Access help I can only remove duplicate records from each page using 'Hide Duplicates'. Any suggestions? Thanks, Derek Basch

Hello, I am trying to do a query such as: SELECT DISTINCT [Qualified Leads].[Company], [Qualified Leads].[Record Creator] FROM [Qualified Leads]; This returns all of the records that have fields taht are a combination of unique values. However, what I really need is just the company field to...

Thanks!

Hello, I am using a summed Iif statement to count the number of occurences of a specific field value in a report. In this case Yes/No. I am using the below statements in 2 text boxes to total the Yes and No answers: =Sum(IIf([Qualified]=No,0,1)) =Sum(IIf([Qualified]=Yes,1,0)) The totals...