Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    13

    Unanswered: Basic report problem

    Pretty basic here but it's been kicking my tail for a while. I created a db that consists of a linked table off my work server. This linked table simply provides first and last names of employees. Second I created another table to store these peoples input (since you can't save data to a linked table). Anyway I linked the employees to 10 fields in my created table. All ten of these fields are simple yes/no checkboxes. The entry form works great, people select their name then choose the appropriate check boxes. However my report does not show the "unchecked" check boxes for people who have not done anything yet. The report simply shows their name. Once somebody goes in and checks (or even unchecks if checked previsouly) then the report works just great showing the check boxes, whether checked or not. For what ever reason my created table full of checkboxes doesn't create a record for people until they check a box for the first time. Any way to get around this? Basically I want my report to show all check boxes regardless if the user even knows this db exists or has done nothing.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ranger3484
    Pretty basic here but it's been kicking my tail for a while. I created a db that consists of a linked table off my work server. This linked table simply provides first and last names of employees. Second I created another table to store these peoples input (since you can't save data to a linked table). Anyway I linked the employees to 10 fields in my created table. All ten of these fields are simple yes/no checkboxes. The entry form works great, people select their name then choose the appropriate check boxes. However my report does not show the "unchecked" check boxes for people who have not done anything yet. The report simply shows their name. Once somebody goes in and checks (or even unchecks if checked previsouly) then the report works just great showing the check boxes, whether checked or not. For what ever reason my created table full of checkboxes doesn't create a record for people until they check a box for the first time. Any way to get around this? Basically I want my report to show all check boxes regardless if the user even knows this db exists or has done nothing.
    Hi Ranger

    Welcome to the forum.
    you can't save data to a linked table
    Yup - you can. There are a few simple requirements but there would be little point to linked tables if you couldn't update data.

    The report simply shows their name. Once somebody goes in and checks (or even unchecks if checked previsouly) then the report works just great showing the check boxes, whether checked or not.
    Sounds like an outer join to a table with no linked records. Do your users click these checkboxes in a subform? If not, please detail how you enter new rows in your local checkbox table. Please also post the sql on which the report is based

    Cheers
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2005
    Posts
    13
    The sql table is a huge file that resides on our server. It contains every single item about our employees from name, ssn, birth date, address, spouse info....just about everything. Many of our scheduling, vacation, assigments, etc. programs on our server all link into this sql table for whatever information that particular program needs. All I need is first name and last name.

    Being new to Access I want to be extremely careful with this sql table. I really don't want users to have any ability whatsoever to update or change the main file source. If something were to get screwed up, many programs could be affected and the result could be disasterious. Since I didn't think you could update a linked sql table I created a "Make-a-table Query" pulling only the EmployeeID, First Name, and Last name. Once I had this queried table I created a form. My intent was to make a single form using this new queried table, then add fields yes/no for my checkboxes. But since my "Make-a-Table Query" needs to run every session, adding yes/no checkboxes would be cleared upon every startup. With this I decided to build a totally seperate table to save the yes/no inputs. I did this, then inserted it in as a subform. In other words, my main form has 3 fields (employee ID, First Name, and Last Name). My subform (which writes to it's own table) has 11 fields (10 yes/no, and 1 personal ID as the primary key). With this, the form worked great.

    When I open up the 11 field, checkbox table in datasheet view it only shows the people that have indeed made some yes/no selection in the form. After all, this table knows nothing about anything until one checks a box. Once you check a box, it saves the record and records the Employee ID, First Name, and Last Name. As a result, my report is frequently missing checkboxes on many people who have not done anything. Their name still shows up, but no checkbox next to it. I've gone in and "checked" a box on some of these people, just to immediately uncheck it. When I do, the report shows their name and a bunch of unchecked boxes, which is just what I want. But doing so for 10,000+ people, it would be a nightmare. Also, when new employees show up I'd have to do that individually.

    Just thought, maybe I should build a simple query from my original linked table to my checkbox table. Hmm. Maybe that's the answer. I'm sure I'll run into problems as running queries on this particular sql table has been less than easy. The program that makes this sql table does not assign a primary key which has made things even more difficult.

    Sorry this is so long. That being said I'd appreciate any help one could offer. Though I probably won't use it now, how do you "update" a "linked table." Access says you can't write to it?



    KML
    Last edited by ranger3484; 11-07-05 at 12:31.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Starting at the end, Access needs to know the primary key (field(s) that uniquely identify each record) to make updates - you may have been asked for this when you linked the table but didn't select any in which case access will not perform updates. I take it this huge file is in SQL Server (there is no such thing, BTW, as a SQL table)?

    The report is correctly showing the data - a Null (as it is "showing") is different to a False\ True entry in a table. However, you want these nulls to come up false. You can a) pre-populate your local table with all the staff (all 10k) ready for their checkbox input, adding the remaining records periodically. b) Create a query like the below and use as your record source:

    Code:
    SELECT [StaffName], NZ(MyCheckbox1, False) AS MyCheckbox1, NZ(MyCheckbox2, False) AS MyCheckbox2, NZ(MyCheckbox2, False) AS MyCheckbox2
    FROM MyHUGETable LEFT OUTER JOIN MyLocalTable ON
    MyHUGETable.StaffID = MyLocalTable.StaffID
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Apologies if this seems to be off the topic

    ....if all you need is a very limited subset of the parent table, and you are concerned about data corruption then I'd suggest you find a way limting the data your application needs. No idea about your companies policies but your organisation may have a responsibility to keep private information private.

    I have seen several solutions to the problems

    one is to get the DBA's controlling the main system to provide a view with just the fields you require. this can be doen by them createing a view or accessing the data via a query.

    another is to take a snapshot of the data (say overnight) and populate a local system with only the data you require.

    of the 2 the former is far preferable. It may be that your DBA's already have such a requirement. it may be that they will coalesce your requirements with others including other information. I used a 2 or 3 level query (one level full access, 2nd level restricted, 3rd level very limited). OK some of it can be done via security permissions but in practise I've found it easier to explicitly define what is available to the applciation.

    HTH

  6. #6
    Join Date
    Nov 2005
    Posts
    13
    Thanks for the inputs. Here's the thing. This table I refer to is actually a *.xls file on the server. When I linked to it, Access never asked or made any attempt (to my knowledge) of defining a primary key. Actually, once the table was linked and visible in Access I looked at the table via design view. I tried to set the primary key to a particular field but was quickly told by Access that I can't do this since it's a linked table. Hmmm. Second I wanted to add some yes/no fields to this table such that I could use my checkboxes here. Well, again, since it's linked I was denied. So, I went to the server, and pulled up the actual *.xls file, added the yes/no fileds and saved. Next problem. Excel does not give you the option of yes/no in a particular field. So when I linked back into the *.xls table all my yes/no fields were there but shown as "Text". I tried every validation that Excel offered. No yes/no option in access was the result. I did notice that the previously generated coulum in the linked table called "Active" was showing up as a yes/no field. I simply copied this column into my checkbox yes/no fields the deleted all data. Somehow the Excel/Access programs decided to retain the field properties yes/no for my new fields. Eureka I have a table with yes/no fields that is linked.

    KML

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •