Page 1 of 4 123 ... LastLast
Results 1 to 15 of 50
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Referenial Integrity (Access 2000 SP2)

    I have a table for timesheets that looks back to three tables to pull names. The look backs are for "Client Name", "Employee Name", and "Acccount Name". Each of these three tables has an "ID" set to autonumber that corresponds to the name. When I go to establish the relationships, I can only set the referential integrity for "Employee Name". When I go to set it for the other two I get an error message that there is no unique index. I'm assuming its because there is already a unique index that is a number set when the referential integrity was set for "Employee Name", however I'm not sure if this is the case. When I originally set up the Name tables to look back to, I didn't establish an ID as an autonumber, but instead set the name as the primary key as I didn't want duplicates. This became an issue with similar names like "John Smith", etc. I was going to use SS#'s however the end user doesn't always obtain those so that was an issue. My reason for going back and adding the ID's to each table was due to problems I was having with reports. The report issue is now clear, however I'd like to set the referential integrity back again. Any thoughts???

    Thank you,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referenial Integrity (Access 2000 SP2)

    An AutoNumber field is always unique, but it's not automatically the primary key of a table.

    Open your "Client Name" table in design view.
    Click in the ID field.
    Then click the "Primary Key" button in the toolbar (it looks like a key). This makes the ID field into the primary key field of the table; you will see a tiny key symbol to the left of the field name.
    Close the table and save the design changes.
    Open the Relationships window.
    You should now be able to set referential integrity for the relationship between the Timesheets table and Clients table.

    Repeat these steps for the "Account Name" table.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referenial Integrity (Access 2000 SP2)

    Morning Hans!

    I already did the steps you recommended multiple times. I've tried making the primary key the name vs the ID number and that doesn't work either. I've never had this happen before so its stumped me to no end which was I thought maybe it had to do with the three name tables that the Timesheet table references, each having fields called ID. It doesn't help that this is an already established database with tons of data in it (which of course I have an original saved of), vs one I'm developing from scratch. To make matters worse I've already set up multiple reports using your help from the other day to my post referencing using list boxes to open reports, so pulling the ID catagory will reduce me insanity!!! Ugh. Now what??

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referenial Integrity (Access 2000 SP2)

    Leesha,

    This kind of problem can be very frustrating, and it's hard to investigate form a distance. Could you post a screenshot of the Relationships window as it is now?

    (If you don't know how to make screenshots, open the Relationships window and type Alt+PrintScreen. This will place a picture of the active window on the clipboard. Then, open Word and paste the picture into a blank document. Save the document. If it is less than 100 KB, you can attach it directly to a reply; if it is too big, zip it and attach the zip file. For later, you might do a search in the Software Finds and Wants forum to search for free screen capture programs.)

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referenial Integrity (Access 2000 SP2)

    I've attached the preview shot you requested.

    Thanks,
    Alicia
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referenial Integrity (Access 2000 SP2)

    Wow! This is really going to take a lot of work to get right. It's up to you to decide whether you want to do it. In the end, your database structure will have been improved, but a lot of things will get broken before you are there...

    tblTimesheet now has a field Client Name, which is a text field, linked to Client Name in tblClientDemographics. It should be a numeric field (Long Integer) linked to the ID field, but you can't just change it into a numeric field.

    Start by adding a new field to tblTimesheet.
    Name: ClientID
    Type: Numeric (Long Integer)

    Save & close the table.

    Next, create a new query in design view. Add tblTimesheet and tblClientDemographics. They should be linked on Client Name.
    Make the query into an update query (Query menu).
    Add the ClientID field from tblTimesheet to the query grid.
    Set the Change To to [tblClientDemographics].[ID]
    Run the query (menu option Query/Run). If you get an error message, there are problems with your data. Otherwise, Access will ask for confirmation to save ... records. Click OK.

    Switch to the database window, and open tblTimesheet. Check that the ClientID field has been filled. Close the table again.
    Open the Relationships window, and delete the existing relationship between tblTimesheet and tblClientDemographics.
    Add a new one from ID in tblClientDemographics to ClientID in tblTimesheet, and try to set referential integrity.

    If all is well up to now, you can open tblTimesheet in design view again, and delete the Client Name field.
    Set the Caption property of ClientID to Client Name.
    In the Lookup tab, change the display type of ClientID to Combo Box.
    Set the Row Source to tblClientDemographics.
    Set the number of columns to 3
    Set column widths to 0";1";1".
    Move the ClientID field up to the position of the former Cliebnt Name field.
    Save the table design. The table should be OK now, but...

    You will have to modify everything that uses the Client Name field in tblTimesheet: queries, forms and reports.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referenial Integrity (Access 2000 SP2)

    Well, I'm a glutton for punishment! That's what I get for helping a friend out!!! I appreciate the input and of course the perfectionist in me will make me fix it so I'll be getting to work on it tonight.

    Thanks!
    Leesha

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referenial Integrity (Access 2000 SP2)

    Hi Hans,

    Well, I followed your steps for all three tables (Client, Account, Employee) and the referential integrity is set and all the forms etc. changed and running fine. My ONLY issue is that in all three tables (client, account and employee)the name field changes to the ID number on a whim, only one at a time in each database. IE, if I'm running account related reports, the account name will change to the ID number that is associated with it. Any idea where that is coming from?

    Leesha

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referenial Integrity (Access 2000 SP2)

    Do you mean that after you have run an account-related report, the Account Name field in tblAccount has actually been modified to contain ID's instead of names? Or is it a matter of ID's being displayed instead of names? Or something else?

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referenial Integrity (Access 2000 SP2)

    Morning!

    The Account Name Field in tble Account has actually been modified to contai ID's instead of names. I went back and rechecked all queries and found 2 that didn't actually have links between the tables and thought that might of been it, and yet it is still happening. I'm not sure where to begin to look. I'm about ready to trash the whole database and rework it but the owner has sooooooooooooo much data in it!

    Thank you
    Leesha

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referenial Integrity (Access 2000 SP2)

    Frankly, I don't see how this can happen unless you have an incorrect update query. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referenial Integrity (Access 2000 SP2)

    Nope, the update query went fine. Not one error message and the I went back and checked the data and it was valid. To make matters worse, it only changes the the data in one line item at a time and it varies. It doesn't go back and replace the ID number for every cell in the database. Sigh, gonna be a long day.

    Thanks!
    Leesha

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referenial Integrity (Access 2000 SP2)

    Next problem.................I'm slowly, very slowly working through the form, query and report changes that are a result of the tblClient, tblAccount and tblEmployee ID changes I made for referential integrity. I'm stuck on list that when clicked on it would go to the line item for that row in the database. Now when the list opens, the ID numbers vs the actual names show. I've tried adding the associated name fields from the corresponding tables and although that helps, for some reason it limits the amount of data that shows up. There are no parameters placed on the query such as date range etc. When I run the actual query in design view it shows the name. It's only in the list box view that only the numbers show. Is there a way for me to change this???

    Thank you,
    Alicia

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referenial Integrity (Access 2000 SP2)

    You must include the ID and name in the Row Source of the list box, and set the Column Widths property to 0";1" (the second number isn't important, you can change it if you like, but the first must be 0). This will hide the ID column, while still using it as the "value" of the list box.

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referenial Integrity (Access 2000 SP2)

    God I appreciate you quick replies. The rowsource data comes from tblTimesheets which no longer has ClientID, AccountID, and EmployeeID now in it as numeric values which lookback to the names in the tblClient, tblEmployee, and tblAccount so that the names show in the combo boxes on the form. I removed the actual Client Name, Employee Name, and Account Name from tblTimesheet after the referential links were set as per your instructions yesterday (thanks for the detail). Should I have kept these?

    Thanks,
    Leesha

Page 1 of 4 123 ... LastLast

Posting Permissions

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