HI all,
I am sorry but I cant upload due to 2 mb restrictions and I cant get my db any less then 2.49
I have a report query that I need to some how get to show me disticnt business names and I have tried everything.
What happens is if a business has more then one Industry or Role, or Function it gives a record for each one and I only want to show the business name once wither it has one or more Industry, role, or function!
Here is the origional query in sql
Code:
SELECT tblBusiness.BusinessID, tblBusiness.BusinessName, qryEntityNotPrimary.BusinessEntityID, qryEntityNotPrimary.CboDataValue AS EntityType, qryEntityNotPrimary.SystemValue, qryAddressPrimary.CboDataValue AS AddressType, qryAddressPrimary.Address, qryAddressPrimary.AddressCont, qryAddressPrimary.City, qryAddressPrimary.State, qryAddressPrimary.ZipCode, qryAddressPrimary.County, qryPhonePrimary.CboDataValue AS PhoneType, qryPhonePrimary.Description, qryPhonePrimary.PhoneNumber, IIf(IsNull([DBA]),[BusinessName],[BusinessName] & ", DBA: " & [DBA]) AS FullBusinessName, Trim([Address] & " " & [AddressCont]) AS FullAddress, qryEntityNotPrimary.Reference, qryEntityNotPrimary.DBA, tblBusiness2BusinessPrimaryFunctionNew.BusinessPrimaryFunctionID, tblBusiness2BusinessPrimaryFunctionNew.BusinessPrimaryRoleID, tblBusiness2BusinessPrimaryFunctionNew.BusinessPrimaryIndustryID
FROM (((tblBusiness INNER JOIN qryEntityNotPrimary ON tblBusiness.BusinessID = qryEntityNotPrimary.BusinessID) LEFT JOIN qryPhonePrimary ON qryEntityNotPrimary.BusinessEntityID = qryPhonePrimary.BusinessEntityID) LEFT JOIN qryAddressPrimary ON qryEntityNotPrimary.BusinessEntityID = qryAddressPrimary.BusinessEntityID) INNER JOIN tblBusiness2BusinessPrimaryFunctionNew ON tblBusiness.BusinessID = tblBusiness2BusinessPrimaryFunctionNew.BusinessID;
And I tried this below but got syntax error in the FROM?
Code:
SELECT DISTINCT tblBusiness.BusinessID, tblBusiness.BusinessName FROM (SELECT DISTINCT tblBusiness.BusinessID, tblBusiness.BusinessName, qryEntityNotPrimary.BusinessEntityID, qryEntityNotPrimary.CboDataValue AS EntityType, qryEntityNotPrimary.SystemValue, qryAddressPrimary.CboDataValue AS AddressType, qryAddressPrimary.Address, qryAddressPrimary.AddressCont, qryAddressPrimary.City, qryAddressPrimary.State, qryAddressPrimary.ZipCode, qryAddressPrimary.County, qryPhonePrimary.CboDataValue AS PhoneType, qryPhonePrimary.Description, qryPhonePrimary.PhoneNumber, IIf(IsNull([DBA]),[BusinessName],[BusinessName] & ", DBA: " & [DBA]) AS FullBusinessName, Trim([Address] & " " & [AddressCont]) AS FullAddress, qryEntityNotPrimary.Reference, qryEntityNotPrimary.DBA, tblBusiness2BusinessPrimaryFunctionNew.BusinessPrimaryFunctionID, tblBusiness2BusinessPrimaryFunctionNew.BusinessPrimaryRoleID, tblBusiness2BusinessPrimaryFunctionNew.BusinessPrimaryIndustryID)
FROM (((tblBusiness INNER JOIN qryEntityNotPrimary ON tblBusiness.BusinessID = qryEntityNotPrimary.BusinessID) LEFT JOIN qryPhonePrimary ON qryEntityNotPrimary.BusinessEntityID = qryPhonePrimary.BusinessEntityID) LEFT JOIN qryAddressPrimary ON qryEntityNotPrimary.BusinessEntityID = qryAddressPrimary.BusinessEntityID) INNER JOIN tblBusiness2BusinessPrimaryFunctionNew ON tblBusiness.BusinessID = tblBusiness2BusinessPrimaryFunctionNew.BusinessID;
Thank you for any assistance on that
Dave