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

    Access query question (Access2003)

    Hi everyone, I usually pop up with questions as I start into the new Christmas program. We run the Christmas project using ACCESS-- the table is the same format each year but I need to hold the information about each year so that we can go back to compare some things (this is the reason I dont use related tables as much as I could if I didnt need to keep the whole project intact.) Our non profit offers a lot of services and for most of the others, a table of services is linked to the ID of the family and the ID of the person. For Christmas, we still use some information from the family table and from the person table, but nearly 30 fields are unique to each Christmas year. About 5 years ago, I finally had a form for data entry that really fit and was easy to use so have been making a copy of the form to use each year. (have left the copies of each year in the computer so that it is easy to view the past years) Every year for the past 4, I have been able to make a new query by taking the previous one and changing the fields for the large table. (I am sure all of you know an easier way to do that) This year, I followed the same procedure (well I think I did--cant find the error) but the query wont work in the form. It has always pulled in the info from the family table and from the person table but this year when the ID number is put itn--nothing happens BUT if I close the form and reopen it--there is the data! Now, I know many of you know exactly what I did wrong--but I cant find it. So, my two questions are-- is there an easy way to take a query and copy it but update it to use another table with exactly the same fields and can you tell me why a query would only populate fields after the form is closed and reopened.

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

    Re: Access query question (Access2003)

    I don't know if it's really easy, but you could do the following:
    - Open the query in design view.
    - Use File | Save As to save it with a different name.
    - Select View | SQL.
    - Press Ctrl+C to copy the text string (it should already be selected).
    - Start Notepad (or another text editor).
    - Press Ctrl+V to paste the SQL string.
    - Use Edit | Replace to replace all instances of the name of the "old" table with the name of the "new" table.
    - Press Ctl+A to select all text.
    - Press Ctrl+C to copy the text.
    - Switch back to the query in Access.
    - Press Ctrl+V to paste the text (it should overwrite the original version).
    - Select View | Design.

    I can't answer your other question without having much more information, or seeing (a stripped down copy of) your database.

  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 question (Access2003)

    Hi Hans-- yep, that is basically what I do but usually in the grid view so that is is easier to see where changes need to be made and so that I dont miss a field... ok, was hoping there would be an easier way but I can do that 30+ field change once a year -- maybe I should copy and paste the two queries and you see if you can see an error on my part-- why would one query pull in the needed info into the form and the other not? I simply cant find the problem-- here is another clue-- when I try to delete the test children from the NEW 2008 data base, I am told that I cant because there is a reference to the 2004 Christmas data--- I have searched and can not find one referenceto 2004-- plus, it would seem that I would have had that problem for more than this year since I do this the same way every year.

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

    Re: Access query question (Access2003)

    If you wish you can paste the SQL text of the queries into a reply.

  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 question (Access2003)

    Here are the two SQL-- the first one works perfectly-- even if I stick it in the new form (so I dont think it is something I did in the new frm) The second one does not bring up the info from the person table or the family table until I close the form and reopen it. Any help you can give me will be appreciated-- I could of course copy the old SQL and change to 2008--but then I would not know what I had done wrong and would have learned nothing.


    SELECT tblFamilyData.Family_Name, tbl2007Christmas.LegalName, tblFamilyData.Update_Date, tblFamilyData.[Family ID], tbl2007Christmas.PersonalID, tblPerson.DOB, tblPerson.Race, tblPerson.Sex, tbl2007Christmas.ShirtSize, tbl2007Christmas.PantSize, tbl2007Christmas.ShoeSize, tblPerson.AKA, tbl2007Christmas.AgencyName, tbl2007Christmas.School, tbl2007Christmas.Priority, tbl2007Christmas.EntryDate, tbl2007Christmas.Gift1, tbl2007Christmas.Gift2, tbl2007Christmas.Gift3, tbl2007Christmas.FavoriteColor, tbl2007Christmas.[Team/hobby], tbl2007Christmas.[Book suggestion], tbl2007Christmas.Music, tbl2007Christmas.GreatestNeed, tbl2007Christmas.[Special Wish], tbl2007Christmas.[#children], tbl2007Christmas.[Need of Family], tbl2007Christmas.Caseworker, tbl2007Christmas.[Ready for pick up], tbl2007Christmas.[#clothes], tbl2007Christmas.[#toys], tbl2007Christmas.[holding for], tbl2007Christmas.[holding box], tbl2007Christmas.[Additional Referrals], tbl2007Christmas.[Food needed], tbl2007Christmas.[Picked up By], tbl2007Christmas.Memo, tbl2007Christmas.[confidential memo], tbl2007Christmas.[T-food sponsor], tbl2007Christmas.Height, tbl2007Christmas.Pounds, tbl2007Christmas.[C-food Sponsor], tbl2007Christmas.GiftSponsor, tbl2007Christmas.DatePickedUp, Year(Now())-Year(tblPerson!DOB) AS Age, tbl2007Christmas.biketagged, tbl2007Christmas.bikepickedup, tbl2007Christmas.[In House], tbl2007Christmas.GotSpecialWish, tbl2007Christmas.GotNeed, tbl2007Christmas.Gotgift1, tbl2007Christmas.Gotgift2, tbl2007Christmas.Gotgift3, tbl2007Christmas.Corrections, tbl2007Christmas.CDplayer, tbl2007Christmas.DVDplayer, tbl2007Christmas.VCR, tbl2007Christmas.[Bike Request]
    FROM tblFamilyData INNER JOIN (tblPerson INNER JOIN tbl2007Christmas ON tblPerson.[Personal ID] = tbl2007Christmas.PersonalID) ON tblFamilyData.[Family ID] = tblPerson.[Family ID];


    SELECT tblFamilyData.Family_Name, tbl2008Christmas.LegalName, tblFamilyData.Update_Date, tblFamilyData.[Family ID], tbl2008Christmas.PersonalID, tblPerson.DOB, tblPerson.Race, tblPerson.Sex, tbl2008Christmas.ShirtSize, tbl2008Christmas.PantSize, tbl2008Christmas.ShoeSize, tblPerson.AKA, tbl2008Christmas.School, tbl2008Christmas.Priority, tbl2008Christmas.EntryDate, tbl2008Christmas.Gift1, tbl2008Christmas.Gift2, tbl2008Christmas.Gift3, tbl2008Christmas.FavoriteColor, tbl2008Christmas.[Team/hobby], tbl2008Christmas.[Book suggestion], tbl2008Christmas.Music, tbl2008Christmas.GreatestNeed, tbl2008Christmas.[Special Wish], tbl2008Christmas.[#children], tbl2008Christmas.[Need of Family], tbl2008Christmas.Caseworker, tbl2008Christmas.[Ready for pick up], tbl2008Christmas.AgencyName, tbl2008Christmas.[#clothes], tbl2008Christmas.[#toys], tbl2008Christmas.[holding for], tbl2008Christmas.[Additional Referrals], tbl2008Christmas.[Food needed], tbl2008Christmas.[Picked up By], tbl2008Christmas.Memo, tbl2008Christmas.[confidential memo], tbl2008Christmas.Corrections, tbl2008Christmas.[T-food sponsor], tbl2008Christmas.[C-food Sponsor], tbl2008Christmas.DatePickedUp, tbl2008Christmas.GiftSponsor, tbl2008Christmas.biketagged, tbl2008Christmas.bikepickedup, tbl2008Christmas.Height, tbl2008Christmas.Responsibility, tbl2008Christmas.Pounds, Year(Now())-Year(tblPerson!DOB) AS Age, tbl2008Christmas.[In House], tbl2008Christmas.GotSpecialWish, tbl2008Christmas.GotNeed, tbl2008Christmas.Gotgift1, tbl2008Christmas.Gotgift2, tbl2008Christmas.Gotgift3, tbl2008Christmas.CDplayer, tbl2008Christmas.DVDplayer, tbl2008Christmas.[holding box], tbl2008Christmas.VCR
    FROM (tblFamilyData INNER JOIN tblPerson ON tblFamilyData.[Family ID] = tblPerson.[Family ID]) INNER JOIN tbl2008Christmas ON tblPerson.[Personal ID] = tbl2008Christmas.PersonalID;

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

    Re: Access query question (Access2003)

    There's no reference to 2004 here, so it must be elsewhere. As I noted higher up in this thread, I'd need 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 question (Access2003)

    I will try to do that-- the database is very large even without data-what is the total size allowed for posting-- and since it is normally in a front end /back end database-- should I just combine the two for the purpose of you reviewing it?

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

    Re: Access query question (Access2003)

    The maximum size for an attachment is 100 KB, but you can't attach an .mdb file directly, you should zip it and attach the zip file.
    See <post#=401925>post 401925</post#> for instructions on how to prepare the databases.
    If you can't get the zip file with frontend and backend together under 100 KB, you can zip them separately and attach the zip files to two consecutive replies.

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

    Re: Access query question (Access2003)

    was not successful getting it down to 100-- zipped it is 900--- so how about I make a new database with only the tables needed for Christmas? I will try that and get back to you. Thank you for trying to help here.

  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 question (Access2003)

    Since I could not get the table down to the right size, I tried a few other things-- first, I totally redid the work-- made a new form (based on the 2007 form) and did a new query step by step being sure I did not leave anything out. When I used the new query in the form, I could add children but the fields would not populate without closing the form-- I did try refresh but it did not help. Then I used the 2007 query and the form worked perfectly-- and when I added the exact same child it DID populate the fields. THEN I went back to the AQL version of 2007-- copied into a document-- changed all the 2007 references to 2008 and saved the query. Used that query for the form and SAME problem! Any suggestions? I cant use this if i HAVE TO CLOSE THE FORM for every entry. Some days we enter 100 or more children. I think I will stop thinking about this for tonight and get a fresh start in the morning. Thank you for any suggestions.

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

    Re: Access query question (Access2003)

    I don't have any suggestions for you. Please read <post#=401925>post 401925</post#>.

Posting Permissions

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