Hello
. I am working on an Access 2010 WEB-enabled database.
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 specific dates' apscence. 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.
To avoid duplicates i have to use the SELECT DISTINCTand this is what i do. It actually works locally, but when i publish the database on my sharepoint site, it returns the following:
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.



can you please advice me?? thank you

Hum, post #2 for you? Welcome to UA!

(IMG:style_emoticons/default/welcome2UA.gif)


You don't have distinct for Web query.

Several workarounds:

Normalize the data further. So you have a SINGLE date row. That row could be the person who entered in the data for that "batch run". It also means that if you made a mistake and entered 10 names for a given day, then you just edit the ONE record with the wrong date, and presto! All 10 names move to the correct date (so the absence people are thus "attached" to this main record. Since the date is only being entered one time, then you can drive the combo box.

Another idea is to not use a combo box, but use a date field + date picker. Depending on your setup, this may or may not work for reports. In other words, they could pick dates that don't have people, but for date range, this might be ok anyway.

Another idea is to maintain a date table that is distinct. You thus use a table trigger (after update). These triggers would thus write out automatic to that table and maintain a distinct atomic list of dates.

My first choice would be the normalized data idea. I often find that additional normalizing not only means less code, but also gives you more functionally. So for example without code we would thus have that single row and bonus feature is that without code you can move 10 names to another day if wrong date was used during data entry time. This works since as noted the 10 incorrect entered names are all child records of the date of entry. And as noted often the person who entered the 10 names, the time of day, the last edit/change time etc. are often needed anyway (and again it makes no sense to repeat these values over and over for each record). So who did the data entry, the date of abscence etc. would be entered into this main reocrd, and then all people are entered into a sub-form.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Dear Sir, since I first faced the issue I described you, I have posted more than 20 post in several Forums. Not many users replied me, because it has to do with something very specific, but from all off them, you are the only one that described me so well, and most of all, explained me.

This is the main reason I will say thank you, no matter the result.


now, about the issue.
The whole project so far is like this:
SHAREPOINT SITES (a1,a2,a3,b1,b2,b3) ...these sites are each classroom's site.
the teacher logs in and the Access web database shows up (using navigation form as initial page)
The teacher has the options (tabs) to : 1: store details about what the class was taught this day in specific courses.

example

21/02/2011 MATHS : page 21,23,45
Foreign Language : Test No4, page 5,6,7,8

22/02/2011 MATHS : page 24-38,46
FOREIGN LANGUAGE :Test No5, page 9,10,11,12
and this is being stored in access table.it works ok

2: Lookup the previous stored data by either choosing as a criteria a)date or
b)specific course (to see all the history in a single course)

3: Store the daily apcences by entering a form, choosing a date, and under that fillup the name of the student and the hours he missed

so here we have DATES and NAMES.
4: Lookup the previous stored data by either choosing as criteria
a)Name of apcent student from a combobox (ok i made it work well)
b)Date that picks up from a combobox.
here is the problem...
when in step 3 (before) two students are apcent in the same day, there will be 2 records with the same date, in the storing table.
if 6 studens where apcent, there would be 6 records with the same date, in the table. So if he would like to see who was apcent that date, the combobox will show 6 duplicates..this is the problem.

as a result it is ok, i mean choosing any of the 6 results to the combobox, and pressing the button, the result will be the same , and working well.. the point is the duplicates in the button and only.

I would really like to know how to make the combobox do not show duplicates, without changing the structure of the whole apcence input form...

(sorry for writing you all that but i want you to have the full picture)

Thank you

and sorry for my english...i am not a native speaker, even if I'd love to!!

There nothing really much more new to add here.

The advice and possible solution(s) remains un-changed.

As noted the ideal solution would be to further normalize your data. That means creating ONE record for the given day with the given date. This then means you attach the courses taught (or whatever information you tracking here) to the ONE date record. So in effect those records become child records of this one date record. And with only one date record, you can fill the combo box with that one date and not have repeating dates.

In fact anytime when you have "repeating" data in a relational system means that your data is not normalized. The MORE normalized your data is, the LESS of the problem "repeating" data becomes. With normalized data you don't need distinct since your not repeating the date over and over. So you are currently stuck with an less than ideal design and you don't have a distinct in SQL to "cover up" the repeating dates. So the less ideal your tables, the MORE likly you are to have repeating values.

However as suggested in place of the above further normalizing of your data (and to reduce the amount of changes to your "existing" tables and software) then a suggested alternative here is to create a table of dates based on database triggers. This would thus require you to create an additional table, but would be the least disruption to your existing software and the existing forms and existing tables. So the current work you done would not have be changed, but only adding more work.

Writing some table triggers to maintain that table is not too hard and is not a lot of code. However such code has to deal with a date being changed to another date does require close attention to fine details. So if there are no courses left on the date after changing the date to another day, then that old date reocrd will have to be deleted.

So this type of code is not too difficult if you ARE comfortable writing data macros. If you are not comfortable writing data macros, then this task will be more challenging to you.

It is likely less work to write that macro code to maintain this list of dates then that of normalizing your existing design. However, you can likely further normalize your designs and not have to write macro code. This is just a trade off as to the two suggestions here. (so less changes, but you need to write that macro, OR you don't have to write code, but then you have to modify your forms and current desings).

As noted, another simple approach would be just allowing the user to enter a date and use the date picker. This would allow them to pick a date without any courses, but as noted, this might not matter too much, or you could inform them that no courses occurred on that date before you attempt to run that report.

So there are a good number of suggestions here, but they are not new suggestions from my original answer.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

well i will start from the most practical one.

to create a text box>controling the date field(this automatically transforms the simple text box to date picker , and it's the only way)

as you correctly said, if a user chooses a date that has no record, the report, will simply be empty.. its ok with me.

if this solution you gave me , will be the constant one, i will try to find how to inform the user that this date choise has no records , so the report will not be executed...


also, now i am facing another issue about some fields are being read-only when published on sharepoint. (if it persists, i will post an individual thread)

>i will try to find how to inform the user that this date choise has no records

Sure, you can most certainly do the above. You can either check when they select the data (probably a good idea). You can also check again if/when they click on the button to launch/view the report.

Assuming that you placed a un-bound control on a form, set the format as date and thus allow the date picker to work. Now when the user selects a date, the after update event of that text box will run. So in the after update event, you can thus run some code to check if a date exists for that day. The code in the after update event will look like this:

(IMG:https://public.bay.livefilestore.com/y1p-kPXmOz5E2a8dtrqFpQGPVbO-DnqK8DvRzIelWGzfmPi6v484Oh2E3v2-dyNZ-apt_KWqzPzTXhdLSyKr25ikQ/datecheck.png)

Note how in above I BLANK OUT the date if it is incorrect with the SetProperty
And the code in the table (named macro) will look like this:

(IMG:https://public.bay.livefilestore.com/y1pL0ckdzV4OxGnNqeeMpZTmzMX5Czqqu1TSExnWFXmu7EWH09f_gecabXlfEGsQXgSW4gvS4GJgY67oxhEwryC6Q/checkDate.png)

Of course in above you have to change the column names to whatever you tables and data structures look like.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

perfect. I will definately use this. I have no idea about creating If clauses. I can understand the whole idea but dont know how to create it.. the above reply was the best way for me to create and understand on.

i have a question.

now i am creating the report that, after the teacher pickes a name from a combobox (with the students names form a single column table) runs the query that shows all THAT studens history of apcents.
i set the criteria on the SURNAME field : [name]

then go to parameters and set as PARAMETER: [name] and Data Type: Text --(the only thing that changed here is that the parameter is text instead of date)

i create the button ,that runs the report, and as report parameter in the button's macro i set Parameter: [combobox*] ----the name of my combobox containing the names..

(in the zip i have the pictures)

but when i run the button it asks for a parameter....as if it doesnt recognize the combobox's value as text.....

why could this be happening???

Name is a reserved word. Do NOT use it.

Setup the parameter to use something like MySirName.

The query with parameters will look something like this:

(IMG:https://public.bay.livefilestore.com/y1plE14nbdG4caCBwaB3LQoXblwPUZlMCgyZHGsu6bYqpnTSyU9w47p5f0Mbp71WpaWU9sfRHJJQxE8OdWpnRv7zw/parms2.png)

Note how the paramter is placed as the condition for LastName (or whatever column you want).

The code to open the report from your form will thus look like:

(IMG:https://public.bay.livefilestore.com/y1pphC2OhbPbb8Yqhd5S_kyJmB009MVX4fufbe7C4s0b2NF-oKTCwGEacsEzUMBXxqv5bEkhKycuiFbLaP7o7nH8A/lreport.png)

In above, my form has a txtBox called txtFull. Do make sure your combo box is returning the SAME type of column and check the FIRST column you are using for that combo box.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Name is a reserved word. Do NOT use it.<<<< This suggestion was very right, and it was the reason that my database was working while being published (on sharepoint site) and NOT working locally.
I was running thebutton for the report, and nothing..when i used another text for the criteria and parameter, it work locally too...

The query with parameters will look something like this<<<< Excactly like this, is the way my query looks. Except for the windows mode.. has to be dialog, so no cancelation of data entering, while the
filling up procedure, causes the updating with orphan or hald records..

make sure your combo box is returning the SAME type of column and check the FIRST column<<< how can i do this? the combobox takes the values from a simple-single-columned query

I would also like to ask you somethig else.

I have a table with students' names. I also have the form where the teacher will submit the students that are apcent, every day.. is it possible to have in that form, a combobox that will have the names from the table, and every value (name) i choose, to be submitted to the table that it (the form) reffers to??

If the combo box is based on a single column from a table, then to determine what the data type is you have to open up the table and look at the column.

>>How can I do this?

Ok, this is not too difficult.

You need to open up the table and look at the column.

So when I open up a table, and click on the ID column, you see this:

(IMG:https://public.bay.livefilestore.com/y1pjT7YRWt5FOUCgWkK73CPzY1IaVonvllGB_xGTTsebAitzBU1RH506OKJZQ7-m_sYve9Yyy9PWKMvJ9CnX7uulA/rib1%201.png)

In the above I drew a blue line to show you where to look to determine the data type.
So in above you can clearly see the column is a autonumber (or number) type of column.

If I hit tab or click on another column such as LastName, then to determine the "type" of column that lastname is, then you get this:

(IMG:https://public.bay.livefilestore.com/y1pe2hHyaYVSZvGvJqmNC_gNej6qcbQbhX9q50MskvOR3tqi4edGqJHG4SAy49Cujc6fYdUiQQYUYhgbQdnBTjA3w/rib2%201.png)

Note in above how you can see that the column is a TEXT or what we often call a string.

And if we move to a date column in above, then we get this:

(IMG:https://public.bay.livefilestore.com/y1pR_WLqIjn-cQzGaUaua5_Zj7m5scgT_aHTm0sG6udsKFN9zgjYwT0ywnnSos-e4u5FxDn_RLem0WpUfxSlSy2kg/rib43.png)

So you "how" to do this is to open up the table in question and go look at the column type.

Make sure you click on the "Fields" tab. You THEN can click on any column. After you click on a column, then you can look at the Date Type setting in the ribbon.

You are obvislity new to this, so don't forget to click on the Fields tab after opening up the table.

As noted, I drew some lines to show where to "look" after you click on the column in question.

So the above should quite much give you the information on how to determine the column type.

So for your question you have to go and open up the one table and check the data type of that column.

So if the column you are to going filter on is of TEXT (string), then make sure the value your combo box (or text box) is returning for the parameter is ALSO text type. (so you do not want to use a date or some number).

If the column you are going to filter on is of NUMBER type, then make sure the value your combo box (or text box) is returning for the parameter is ALSO a number type (so you do not want to use a date or some text string).

If the column you are going to filter on is a DATE type, then make sure the value your combo box (or text box) is returning a date type. (so you do not want to use a number or text string here).

So the basic skill being suggested here that you need to acquire is how to open up a table, then make sure the fields tab is selected on the ribbon. You can then can click on a column. You then can look at the data type box on the ribbon, and it will display/show the type of the column in question.

Without this basic skill and ability to open up a table and look at the data type, then you will never know what kind of "types" to parameters you have. So this is a good question on your part and you will need to do the above often until you grasp this concept of different data types being used.

Not much development in Access can occur without the ability determine and ensure that you are passing the correct "type" to a filter or in your case a query parmater.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

well i do know how to check the type of the data is being entered or store in a table. You are right , without this skill, nothing can be done!

My date field had date-type data stored anyway, because whenever you click on a cell the "pickup date" embedded graphic box, appears. Also in web databases, when you want to fill up a date field, it will not accept simple string, or anything in a non date format..

I understand you have to mention it, because my level is low, but not that low!

Besides that, the whole web application is working well on sharepoint. no error messages or something.. i tested it .

the database is about 12MB . so i must have used at least some advance features.

now the only thing left to do is

1) to use the "check if the picked date reffers to a record or not" (the way you shawn me) and,

2) to have combobox's values (that already contains names from a table) as potential values to another table (using it in the other table's form..)

so far you have helped more than a lot.
The best combination is to have the knowlegde and have the ability to pass it..
you do this very good

Ah, ok, no problem at all. I had simply noted that you should make sure the type is the same in these cases.

In fact I simply stated this:

make sure your combo box is returning the SAME type of column

And you THEN asked how you can do this- eg:


So I stated you can go look at the type by looking at the table. Perhaps I miss understood the above question and you were asking about something else? If you were not asking about how to get the type, then I am unable to figure out what in the above you were asking for or did not understand (you can expand on what it was you were asking for).

Anyway, not a huge deal, as I often am unable to figure out what is being asked!

Regardless, it sounds like you making good process here.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

I thought, that after your advice, to check about what the combobox returns, i had to do something more that the one i knew (which is the one you discribed!) thats all .

anyway, it was a misunderstanding from my position, its my english also!...you understand..

about the combobox, can you explain me what should i do to make it work??


well yes i must admin there is some progress in general...
within a month i set a server, creating a database (almost done), made the graphic part (as much as sharepoint allows),created domain etc...

I like this project very much no matter how long will it take me to accomplish it.

about the combobox, can you explain me what should i do to make it work??

cancel my previous question! i found it. thanks a lot.

i hope you will be the one who will solve my next issue.

thank you again