By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,480 Members | 1,656 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,480 IT Pros & Developers. It's quick & easy.

webDB combobox shows duplicated values - Query works fine

George Tekos
P: 47
Good evening.
i have a table that stores dates, names (of the students) and courses (that the particular student missed, because of apscence)
i created a query, so i can recall a specific dates' aapscence. i choose the available dates form a combobox, but the only thing i dont want is the duplicate values in that combobox.
for example if, on 12/23 there where 3 students not present, the combobox will show three times 12/23.

I am working on an Access 2010 WEB-enabled database.



Thank you very much

i am attaching the database so you can see what i mean
Attached Files
File Type: zip Database21.zip (41.2 KB, 66 views)
Dec 30 '11 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,709
Please post the current SQL you are using to populate the ComboBox. The answer will probably involve the DISTINCT predicate of the SELECT clause, but for a more helpful answer we really need to see more detail in the question.
Dec 30 '11 #2

George Tekos
P: 47
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Field1 FROM Table1;
this is in COMBOBOX>PROPERTY SHEET> DATA> ROWSOURSE

is this what you mean?
Dec 30 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
That is exactly what I mean assuming your date field is called [Field1] and your table is actually called [Table1].

This example is shown on multiple lines but can be used in a single line if preferred, as long as you separate each element (Ensure at least one space replaces any new line) :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT
  2.          [Field1]
  3. FROM     [Table1]
  4. ORDER BY [Field1]
The ORDER BY clause is not necessary but may be a good idea nevertheless.
Dec 30 '11 #4

George Tekos
P: 47
and ofcourse this is it!!!
i really thank you

so in one line should be like this?
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Field1] FROM [Table1] ORDER BY [Field1]
???

no dots?
Dec 30 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
George Tekos:
no dots?
Indeed. No dots.

Dots are there in this type of situation only for qualifying references. These are needed only where any ambiguity exists (which is rarely the case when only one source - or table - is used).

So, The following both make sense :
Expand|Select|Wrap|Line Numbers
  1. SELECT A.X
  2.        Z.X
  3. FROM   [A]
  4.        INNER JOIN
  5.        [Z]
  6.   ON   A.ID=Z.ID
and :
Expand|Select|Wrap|Line Numbers
  1. SELECT [X]
  2. FROM   [A]
Dec 30 '11 #6

George Tekos
P: 47
it works ok when i test it. but when i publish it it returns the following error message

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.
Dec 30 '11 #7

George Tekos
P: 47
i would liek to give you one more information.
if i just let the combobox work as before (showing duplicates )
it takes the values from a query, and the SQL of the combobox, in this ocassion is
Expand|Select|Wrap|Line Numbers
  1. SELECT Table.Field1 FROM Table ORDER BY Table.Field1 DESC; 
THIS is working, but without DISTINCT . and it is working uploaded..
i think it helps
Dec 30 '11 #8

NeoPa
Expert Mod 15k+
P: 31,709
I have no experience working with SQL within a web-based environment. Maybe the rules that need to be followed are different there from what I'm used to working within Access. The only real difference is in the use of the DISTINCT predicate, so why not try :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Table.Field1 FROM Table ORDER BY Table.Field1 DESC;
Personally I would never use a term such as "Table" in SQL without the brackets ([]) to ensure the reference is clear and unambiguous, but if it works then it works.
Dec 30 '11 #9

George Tekos
P: 47
i tried that too, after you told me but still nothing.

I found a demo db, made by someone else who supposed to have solved that issue. but i cannot understand much. i will attach it here, if it is possible to see it and tell me how it works.
I think it's, using a specific macro or something like that.I do not know enough programming or SQL that's why i can't "Decode" the solution in the database i found.

i need a "tran-Santa-lator"

thank you
Attached Files
File Type: zip SelectDistinctWebExample.zip (65.4 KB, 91 views)
Dec 30 '11 #10

NeoPa
Expert Mod 15k+
P: 31,709
Sorry George, but No.

I only barely understand your question. Working out what some random person provided for some other reason and translating that into what you need (which I don't understand too well anyway) is not something I'd look to take on.
Dec 31 '11 #11

George Tekos
P: 47
ok. i respect it.
the random person is not that random, and the reason he made this database is the same reason you all the experts post back to our threads, by giving examples. His example is a simple database. i just asked your help because i dont get very well the way it works.. (which would be the answer to my question-thread!)..thats all.
Dec 31 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.