SQL Distinct not supported on Web Database

[ehabo]

Hello, I have been using an sql statement to populate ''distinct'' values for a combobox row source:

SELECT DISTINCT Contacts.[Batch] FROM Contacts WHERE Contacts.[Batch] IS NOT NULL 
ORDER BY Contacts.[Batch];

I am using access 2010 and this simple sql statement tested perfectly but is not compatible with web databases. After trying to publish my db i get an error "ACCWeb103902" explaining "Access was unable to convert the query for use on the Web because it uses an unsupported query type, unsupported expressions, unsupported criteria, or other features that are not supported on the Web".

Is there another way that I can use to remove duplicate values from my row source??

[NTC]

you are ahead of me. as you're doing something I haven't yet needed to do and so haven't banged my forehead against this issue. not sure if Distinct will not work....might have to check around on that point.

how about that WHERE clause ; WHERE Contacts.[Batch] IS NOT NULL..... as a sanity check have you tried leaving that out and just isolating this down to Distinct only?

[TheShabz]

Also try removing the DISTINCT and using GROUP BY at the end. Your result is only one field so it wont make a difference.

[ehabo]

Must admit i was not a happy bunny upon realizing certain sql statement were not supported by access 2010 and spent most of the day looking for a work-around as there always seems to be one with flexible applications such as access, but no luck.

I only have 1 contacts table with a field ''batch'' which i use to group my contacts, usually around 15 each batch. I have tried using the where clause and the expression builder but in vain. What happens when i remove the distinct clause is that my row source displays batch numbers for each record (eg. 1111111, 22222, 33333333333 as opposed to 1, 2, 3, 4).

I am simply looking for an equivalent to the SQL Distinct statement, but suspect that maybe it should be done using macro or a separate table to hold unique values, any suggestions??

[Illinger]

Did you ever find a solution?

[TheShabz]

EDIT: Oops, wrong thread.

[giorgospsk]

i am experiencing the same problem. i dont just want to show the not null values, i additionaly want to prevent the combobox from showing duplicates...

did you find any solution?

[kpo]

I have run into the same problem. Has anyone found a solution?

My database is for inventory management. We deal with four main manufactures and different models for each manufacturer and each of those models has their own distinct sizes. Aside from our main table [tblActuators] I created a separate table to list all the different type and size units we have [tblManuTypeSize]. It has three fields, manufacturer [Manu], type [Type] and size [Size].

In an effort to "idiot proof" our record entry, my entry form has cascading combo boxes for these three fields (their names on the form are cmboManu, cmboType and cmboSize, respectively). The combo boxes pull from tblManuTypeSize. From the property sheet, here are the SQL statements for the row sources for these boxes...

cmboManu Row Source: SELECT DISTINCT [Manu] FROM tblManuTypeSize;

cmboType Row Source: SELECT DISTINCT [Type] FROM tblManuTypeSize WHERE [Manu]=cmboManu;

cmboSize Row Source: SELECT DISTINCT [Size] FROM tblManuTypeSize WHERE [Type]=cmboType;

In the client version it works flawlessly, however when building the webDB and then attempting to publish to Sharepoint Online I receive an error because the DISTINCT aspect can't transfer to the web. I have searched the internet to no avail.

Any help is greatly greatly appreciated!
Thanks in advance!
KP

[mmandel1]

Yeah, neither DISTINCT nor GROUP BY work in Access web databases in SharePoint. I'm trying to figure out how to do the same thing. No ;luck so far.

[NTC]

I will add this little bit of information:

* there is a difference between sharepoint lists and web database tables - - so be sure in the posts you make & read that it is clear which is being discussed; both reside on sharepoint (365) and both can involve web forms - so it can be difficult to parse the dialog as to which is being discussed.

* web database tables on 365 are platformed fundamentally on SQL Server. SQL Server is capable of distinct queries. The key structural difference between classic Access and SQL Server is that classic Access puts queries in the front end while SQL Server puts queries in the back end. So that leads me to believe that web databases are capable of having distinct queries - so while perhaps attempting to auto 'transform' a classic Access database to a web database it may not be able to transform this element - one would think it should be able to be recreated working directly with the web database - - not sure as I haven't had the need to find out yet.....

* one of the key aspects of Access 2013 is its inherent 'cloud awareness' (and particularly Office 365). I would recommend that if you are going to the effort to create a new Access Web Application that you do upgrade to Access 2013; although 2010 version puts forth the ability to make web apps is very true - I would still upgrade....and I rarely urge a user to upgrade under normal circumstances....

* I will go off the beaten path in putting forth an opinion that ASP.net is Microsoft's premier web development environment. Access Web Application development needs to be understood for what it is in terms of being less robust a web development environment compared with ASP.net - unlike classic Access which is extremely robust in terms of VBA customization. On the web side of Access you get what you get and one needs to work with the feature set that comes out-of-the-box without the expectation to customize to the degree one is accustomed to on the classic/VBA side.

maybe a little helpful as a perspective - certainly subject to differing opinions.....