Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access query (xp)

    I am embarrassed to send this to the whole group but am ready to pull out my hair-- was getting the new Christmas table-and query and form ready for Christmas 2006. Always base it on the form and query from the previous year as there are usually only a few changes. This time, the query seems to be the problem but I can NOT see my error. The query is based on three tables all connected by the personal ID of each child. When the ID is typed in, many of the fields should be populated but they dont. After working for several hours, I went back to look at the 2005 form and query and it won't populate the fields although it worked fine during the entire Christmas season. Happily, I have some months to fix this problem, but have compaired the SQLfrom both the query that still works (2004) and the ones that wont work and can't find anything different. The ONLY thing I can think that is different is that I am cleaning up the data base and now have all the major tables linked in relationships with referential integrity assured. If someone has time to look it over, I will send both the SQL views (the one that works and the one that does not) and would be very appreciative if you can set me straight.

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

    Re: Access query (xp)

    It would be useful if you could post the SQL (or a stripped down copy of the database - see <post#=401925>post 401925</post#> for instructions.)

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access query (xp)

    will attach document with both SQL-- the one for 2004 works fine-- the one for 2005 does not (but did work fine throughout Christmas and I really do not remember changing it at all.)

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

    Re: Access query (xp)

    The major difference seems to be that the ShirtSize, PantSize and ShoeSize fields have shifted from tblPerson to tbl2005Christmas. Are those the fields you're having problems with?

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access query (xp)

    nope--those fields have to be filled in on the form--the ones that should fill automatically are race, sex, DOB, Family ID, Family Name, and AKA We changed the sizes from storage in "person table" to storage in each year's Christmas table because the sizes change each year and are only used at Christmas. I can not see any other reason for it to not work--it is making me crazy--have used basically the same query for 4 years-- just cant figure it out.

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

    Re: Access query (xp)

    I think we'd have to see (a stripped down copy of) the database...

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access query (xp)

    I will try again tonight to make one small enough to fit . Thank you for trying.

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access query (xp)

    Here is a very small version-- the forms will only partially work because so many tables are missing--but the thing I need is to fix the query-- I changed the names and addresses of the persons/families that I put in the data part so that it can be open to anyone. YOu will see that if you enter a personal ID into the 2004 query, it populates the needed fields--but will not do the same in 2006 (did not include the table for 2005 so it cant work--sorry--was not paying attention)

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

    Re: Access query (xp)

    You have made PersonalID a unique index in tbl2006Christmas; it wasn't unique in tbl2004Christmas. Do the following:

    1) Open tbl2006Christmas in design view.
    2) Activate the Indexes window.
    3) Set the Unique property of the PersonalID index to No.
    4) Close and save the table.
    5) Activate the Relationships window.
    6) Delete the relationship between tblPerson and tbl2006Christmas.
    7) Create the relationship (on Person ID vs PersonalID) anew.
    8) Double click the relationship and tick the Enforce Referential Integrity check box.
    9) Click OK.
    10) Close the Relationships window.

    The 2006 query should now work OK.

  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access query (xp)

    will fix it tonight--thanks so much--I knew it would be something small but it was making me crazy--had tried to do the query over and over for two days. Since I have your ear [img]/forums/images/smilies/smile.gif[/img] could you answer one more question? Last year you told me that the reason the replica was failing was due to me making copies of the replica (although after I transferred tham to another hard drive I deleted the copy) --that the replica had to be made ON the drive where it would reside. My reason for having replication is so I can work on the data at home and not need to worry about others also working on it at the office or at their homes-- How do I get a copy of the replica to several different sites unless I happen to have a laptop that is attached to a network? Maybe I cant. One thought is to use an external hard drive and take it home with me. Even if I were to stop letting two people work at the same time, back up becomes a problem. During the two months of the year that I work nearly 20 hours a day 7 days, I get rather tired and worry that I will copy over the days work rather than backing up. And suggestions? I still have 6 months.

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

    Re: Access query (xp)

    That must have been someone else - I don't know anything about replication.

  12. #12
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access query (xp)

    ok may just post that question to the whole group--thanks for your help-- I may have a good weekend afterall.

Posting Permissions

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