combobox rowsource "All" if more than one in list
in [Form]
|
Prev: Requerying data on differnet tab
Next: Link two subforms in datasheet view (one sub-form, one sub-subform
From: deb on 23 Apr 2010 15:49 access 2003 I have a combobox with the below row source. It contains a Union to add "All units". How can I edit this to only show "All Units" if there are more than one selection in the combobox? i.e. if combobox rowsource has 3 units then "All Units" would be included. if combobox rowsource has only 1 unitthen only list the one unit. SELECT t000GFacts.UnitID, t000GFacts.Unit, t000GFacts.UnitType, t000GFacts.AEIC, t000GFacts.PlantName, t000GFacts.Unit AS UnitOrder, t000GFacts.SiteName, t000GFacts.OperatingCompany AS Customer, t000GFacts.ProjectNo, t000GFacts.SiteID, t000GFacts.PlantID, t040Project.ProjectID FROM t040Project INNER JOIN (t000GFacts INNER JOIN t041ProjectDetails ON t000GFacts.UnitID = t041ProjectDetails.UnitID) ON t040Project.ProjectID = t041ProjectDetails.ProjectID WHERE (((t040Project.ProjectID) Like [Forms]![f001ProjectReview]![ProjectID])) UNION SELECT 0 AS UnitID, "All Units" AS Unit, "" AS UnitType, "" AS AEIC, "" AS PlantName, "" AS UnitOrder, "" AS SiteName, "" AS Customer, "" AS ProjectNo, "" AS SiteID, "" AS PlantID, t040Project.ProjectID FROM t040Project INNER JOIN (t000GFacts INNER JOIN t041ProjectDetails ON t000GFacts.UnitID = t041ProjectDetails.UnitID) ON t040Project.ProjectID = t041ProjectDetails.ProjectID WHERE (((t040Project.ProjectID) Like [Forms]![f001ProjectReview]![ProjectID])) ORDER BY t000GFacts.PlantName, t000GFacts.Unit; Thanks bunches, -- deb
From: Dirk Goldgar on 23 Apr 2010 16:36 "deb" <deb(a)discussions.microsoft.com> wrote in message news:203751D4-1E7C-4177-A1A2-4FB45B2A1F00(a)microsoft.com... > access 2003 > > I have a combobox with the below row source. It contains a Union to add > "All units". > > How can I edit this to only show "All Units" if there are more than one > selection in the combobox? > > i.e. > if combobox rowsource has 3 units then "All Units" would be included. > if combobox rowsource has only 1 unitthen only list the one unit. > > SELECT t000GFacts.UnitID, t000GFacts.Unit, t000GFacts.UnitType, > t000GFacts.AEIC, t000GFacts.PlantName, t000GFacts.Unit AS UnitOrder, > t000GFacts.SiteName, t000GFacts.OperatingCompany AS Customer, > t000GFacts.ProjectNo, t000GFacts.SiteID, t000GFacts.PlantID, > t040Project.ProjectID FROM t040Project INNER JOIN (t000GFacts INNER JOIN > t041ProjectDetails ON t000GFacts.UnitID = t041ProjectDetails.UnitID) ON > t040Project.ProjectID = t041ProjectDetails.ProjectID WHERE > (((t040Project.ProjectID) Like [Forms]![f001ProjectReview]![ProjectID])) > > UNION SELECT 0 AS UnitID, "All Units" AS Unit, "" AS UnitType, "" AS AEIC, > "" AS PlantName, "" AS UnitOrder, "" AS SiteName, "" AS Customer, "" AS > ProjectNo, "" AS SiteID, "" AS PlantID, t040Project.ProjectID FROM > t040Project INNER JOIN (t000GFacts INNER JOIN t041ProjectDetails ON > t000GFacts.UnitID = t041ProjectDetails.UnitID) ON t040Project.ProjectID = > t041ProjectDetails.ProjectID WHERE (((t040Project.ProjectID) Like > [Forms]![f001ProjectReview]![ProjectID])) > ORDER BY t000GFacts.PlantName, t000GFacts.Unit; I'm not sure I'm going to get this right without setting up tables to test with, but try this: SELECT t000GFacts.UnitID, t000GFacts.Unit, t000GFacts.UnitType, t000GFacts.AEIC, t000GFacts.PlantName, t000GFacts.Unit AS UnitOrder, t000GFacts.SiteName, t000GFacts.OperatingCompany AS Customer, t000GFacts.ProjectNo, t000GFacts.SiteID, t000GFacts.PlantID, t040Project.ProjectID FROM t040Project INNER JOIN (t000GFacts INNER JOIN t041ProjectDetails ON t000GFacts.UnitID = t041ProjectDetails.UnitID) ON t040Project.ProjectID = t041ProjectDetails.ProjectID WHERE (((t040Project.ProjectID) Like [Forms]![f001ProjectReview]![ProjectID])) UNION ALL SELECT 0 AS UnitID, "All Units" AS Unit, "" AS UnitType, "" AS AEIC, "" AS PlantName, "" AS UnitOrder, "" AS SiteName, "" AS Customer, "" AS ProjectNo, "" AS SiteID, "" AS PlantID, t040Project.ProjectID FROM t040Project INNER JOIN (t000GFacts INNER JOIN t041ProjectDetails ON t000GFacts.UnitID = t041ProjectDetails.UnitID) ON t040Project.ProjectID = t041ProjectDetails.ProjectID WHERE (((t040Project.ProjectID) Like [Forms]![f001ProjectReview]![ProjectID])) GROUP BY 0, t040Project.ProjectID HAVING Count(*) > 0 ORDER BY t000GFacts.PlantName, t000GFacts.Unit; -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: Dirk Goldgar on 23 Apr 2010 16:48 "Dirk Goldgar" <dg(a)NOdataSPAMgnostics.com.invalid> wrote in message news:8705B008-7DD9-4D82-942A-CF95C3161EE7(a)microsoft.com... > > I'm not sure I'm going to get this right without setting up tables to test > with, but try this: > > SELECT t000GFacts.UnitID, t000GFacts.Unit, t000GFacts.UnitType, > t000GFacts.AEIC, t000GFacts.PlantName, t000GFacts.Unit AS UnitOrder, > t000GFacts.SiteName, t000GFacts.OperatingCompany AS Customer, > t000GFacts.ProjectNo, t000GFacts.SiteID, t000GFacts.PlantID, > t040Project.ProjectID FROM t040Project INNER JOIN (t000GFacts INNER JOIN > t041ProjectDetails ON t000GFacts.UnitID = t041ProjectDetails.UnitID) ON > t040Project.ProjectID = t041ProjectDetails.ProjectID WHERE > (((t040Project.ProjectID) Like [Forms]![f001ProjectReview]![ProjectID])) > UNION ALL > SELECT 0 AS UnitID, "All Units" AS Unit, "" AS UnitType, "" AS AEIC, > "" AS PlantName, "" AS UnitOrder, "" AS SiteName, "" AS Customer, "" AS > ProjectNo, "" AS SiteID, "" AS PlantID, t040Project.ProjectID FROM > t040Project INNER JOIN (t000GFacts INNER JOIN t041ProjectDetails ON > t000GFacts.UnitID = t041ProjectDetails.UnitID) ON t040Project.ProjectID = > t041ProjectDetails.ProjectID WHERE (((t040Project.ProjectID) Like > [Forms]![f001ProjectReview]![ProjectID])) > GROUP BY 0, t040Project.ProjectID > HAVING Count(*) > 0 > ORDER BY t000GFacts.PlantName, t000GFacts.Unit; Correction: HAVING Count(*) > 1 -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: deb on 23 Apr 2010 17:01
works like a dream!!! -- deb "Dirk Goldgar" wrote: > "Dirk Goldgar" <dg(a)NOdataSPAMgnostics.com.invalid> wrote in message > news:8705B008-7DD9-4D82-942A-CF95C3161EE7(a)microsoft.com... > > > > I'm not sure I'm going to get this right without setting up tables to test > > with, but try this: > > > > SELECT t000GFacts.UnitID, t000GFacts.Unit, t000GFacts.UnitType, > > t000GFacts.AEIC, t000GFacts.PlantName, t000GFacts.Unit AS UnitOrder, > > t000GFacts.SiteName, t000GFacts.OperatingCompany AS Customer, > > t000GFacts.ProjectNo, t000GFacts.SiteID, t000GFacts.PlantID, > > t040Project.ProjectID FROM t040Project INNER JOIN (t000GFacts INNER JOIN > > t041ProjectDetails ON t000GFacts.UnitID = t041ProjectDetails.UnitID) ON > > t040Project.ProjectID = t041ProjectDetails.ProjectID WHERE > > (((t040Project.ProjectID) Like [Forms]![f001ProjectReview]![ProjectID])) > > UNION ALL > > SELECT 0 AS UnitID, "All Units" AS Unit, "" AS UnitType, "" AS AEIC, > > "" AS PlantName, "" AS UnitOrder, "" AS SiteName, "" AS Customer, "" AS > > ProjectNo, "" AS SiteID, "" AS PlantID, t040Project.ProjectID FROM > > t040Project INNER JOIN (t000GFacts INNER JOIN t041ProjectDetails ON > > t000GFacts.UnitID = t041ProjectDetails.UnitID) ON t040Project.ProjectID = > > t041ProjectDetails.ProjectID WHERE (((t040Project.ProjectID) Like > > [Forms]![f001ProjectReview]![ProjectID])) > > GROUP BY 0, t040Project.ProjectID > > HAVING Count(*) > 0 > > ORDER BY t000GFacts.PlantName, t000GFacts.Unit; > > > Correction: > > HAVING Count(*) > 1 > > -- > Dirk Goldgar, MS Access MVP > Access tips: www.datagnostics.com/tips.html > > (please reply to the newsgroup) > |