Report Query with select distinct question

[d9pierce1]

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

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?

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

[Welshgasman]

Ever heard of a zip file?

[madpiet]

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!

I don't think there's enough information here to answer your question. If you want to force the business name to show up only once in your report, create a report grouping on the Business Name.

In theory, you could base the report on the entire query (where the child records would cause duplicates to appear in the Business Name column), and put the Business Name column in the header and group it, and it would only show up once.

As for the file being too big, did you try zipping it?

[June7]

Zip limit is 2mb, non-zip is 500kb

Have you run Compact & Repair? Make a copy and delete some data or objects?

Have you used report Sorting & Grouping design features?

Textbox has HideDuplicates property.

[d9pierce1]

Hi all,
I took everything out of it I could, it is 10.4mb unzipped and 2.49mb zipped. I will try to take someother things out but ran into issues with this earlier today and got errors galore on opening it.
But I will try some more.

[June7]

I doubt viewing your db will change advice.

Options:

1. report Grouping and/or textbox Hide Duplicates

2. report/subreport arrangement

[Micron]

I took everything out of it I could, it is 10.4mb unzipped

I imagine you use embedded images on forms, command buttons and such?

[d9pierce1]

Hi Micron,
No images embedded in forms. Its just a lot
But thanks for that

[Micron]

OK, that would have been my suspicion because these images are usually hidden in system tables. When someone says they can't get the db down in size even though they've compacted it and removed a lot of forms/reports/queries/macros/tables, it's usually because the images are still there.

There are probably those here who would download your file from a file share and take a look.

[madpiet]

What's a lot?
One way of doing it is to create a new blank database and maybe create a few linked tables to the original that you want to get data from and select a subset of the entire table. Do the same for the child tables required for the report. Then import the report.
yeah, it's some work, but how badly do you need an answer?

[ArviLaanemets]

Based on your query, unless you have some serious problems with your DB structure, there is no need for DISTINCT clause in report's source query. Probably the query you need will be like:

SELECT
    bs.BusinessID, 
    bs.BusinessName,
    qenp.BusinessEntityID,
    qenp.CboDataValue AS EntityType,
    qenp.SystemValue,
    qap.CboDataValue AS AddressType,
    ...
FROM
    (((tblBusiness bs
        INNER JOIN tblBusiness2BusinessPrimaryFunctionNew bspf2 ON bs.BusinessID = bspf2.BusinessID)
        INNER JOIN qryEntityNotPrimary qenp ON bs.BusinessID = qenp.BusinessID)
            LEFT JOIN qryPhonePrimary qpp ON qenp.BusinessEntityID = qpp.BusinessEntityID)
            LEFT JOIN qryAddressPrimary qap ON qenp.BusinessEntityID = qap.BusinessEntityID
WHERE [Some condition to ensure only primary businesses from tblBusiness are selected]

You group your report on BusinessID, and you will have BusinessName (and BusinessID in case you want to display this too) in header of report group displayed (i.e. into header you put control(s) for [bs.BusinessID and] bs.BusinessName. The rest of info is displayed row-wise in detail part of group (i.e. into detail part you enter controls for [qenp.BusinessEntityID,] qenp.CboDataValue, qenp.SystemValue, ... etc.). And into footer part of group, you can put some statistics about this business, like the count of sub-businesses it has, or the total of yearly income when you have it for sub-businesses, etc.
.

[Welshgasman]

Would be easier to create a new empty DB and import just what you need to show the issue. Test it first and if that can still show the error, then upload that.

[d9pierce1]

Blank Profile Business - Copy.zip

Hi all,
I sized it down so could zip up and attached.
Will open to frmBusiness
Hit report tab, I only left one report in it and removed most all records
So, when I select the report, it opens showing 3 of the same business (which I only left one business in it)
If I select from report filter, Construction from the Industry, then it still shows up 3 of the same business, If I then select General Contractor from the Primary Role list,
and run report, then it will show 2 of the same business. If I select Then select Construction Management from the Functions as list, then it will only show 1 business.
This does what it is being told but what I want to tell it is no matter what is selected, if its the same business, then only show one business no matter how many Industry, roles, or functions it may have.
Hope thaat makes sense. I just would like it to show one business on the report, not 2 or 3 or however many insustries, roles, or functions it may have!

I may need to remove the BusinessPrimaryIndustryID, BusinessPrimaryRoleID, BusinessPrimaryFunctionID and table from that query and on the report, reference those txtboxes on report to the actual lstbox on the report form?

=[Forms]![frmBusiness]![sfrmBusinessContact].[Form]![frmBusinessReport]![LstIndustry.Column(0)]
=[Forms]![frmBusiness]![sfrmBusinessContact].[Form]![frmBusinessReport]![LstCategory.Column(0)]
=[Forms]![frmBusiness]![sfrmBusinessContact].[Form]![frmBusinessReport]![LstFunction.Column(0)]

I tried putting above into the fields on report form but they dont work, most likely I am not referencing the lstbox correctly?
Any suggestions would be greatly appreciated!

Thanks
Dave

[Micron]

open the report query and scan across the fields and you should be able to see why you get more than one record, then the solution should be obvious.

[d9pierce1]

Hi Micron,
I know why its getting the multi records, what I dont know is how to not show duplucates and still use my report filter?
Thanks