Results 1 to 9 of 9
  1. #1
    LiverEatnJohnson is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    17

    Tough Append Query!!!!!!

    Ok this is a tough one.



    I have classes with a date, site, type, upto 3 teachers, and an id right. ( this tabel is filled )
    and those classes have many evaluations which is now empty. (evals maped to classes by class_id field)
    I also have a table that has imported eval data from excel.
    the tabel imported from excel has the class data for each eval in the first few columns and the rest are the eval answers.
    What I need to have happen is to import import data from the excel table to the evals tabel and have it line up with the correct class.

    Any ideas??

    Let me know if you need more information.

    Thanks alot this one is tough.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    The underlying issue with your set up, as I understand it, is table design and relationships.
    Here is a tutorial that should help put the pieces together.
    http://www.rogersaccesslibrary.com/T...lationship.zip

    Good luck.

  3. #3
    LiverEatnJohnson is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    17
    Ok got a lead on this one

    I think I can use a Dlookup to get the id of a class if i know the site, type, date, and coach1(those fields combined are unique).
    but I am having trouble getting the Dlookup right. any ideas or and example of a Dlookup in an append querry would be great.

  4. #4
    LiverEatnJohnson is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    17
    or somthing like this

    INSERT INTO Evals(class_id)
    SELECT Class.ID WHERE Class.SITE = Both.Site, Class.TYPE = Both.Type, Class.DATE = Both.Date, Class.coach1 = Both.RiderCoach1
    FROM[Both]
    INSERT INTO Evals (c1a, c2a, c3a, c4a, c5a, c6a, i1a, i2a, i3a, i4a, i5a, i6a, i7a, i8a, i9a, class_comments, taken, year_taken, recommended, dealer, which_one, family, friend, licensing, school, [work], other_source, what_other_source, experience, cruiser, touring, sport_bike, dual_sport, off_road, other_bike, what_other_bike, age, ethnnicity )
    SELECT DISTINCT Both.CC1, Both.CC2, Both.CC3, Both.CC4, Both.CC5, Both.CC6, Both.I1, Both.I2, Both.I3, Both.I4, Both.I5, Both.I6, Both.I7, Both.I8, Both.I9, Both.Comments, Both.Taken, Both.Year, Both.Recommend, Both.Dealer, Both.What_Dealer, Both.Family, Both.Friend, Both.Licensing, Both.School, Both.Work, Both.Other, Both.WhereElse, Both.Experience, Both.Cruiser, Both.Touring, Both.Sport_Bike, Both.Dual_Sport, Both.Off_Road AS Expr1, Both.Other_Bike, Both.What_Bike, Both.Age, Both.Ethnicity
    FROM [Both];

  5. #5
    LiverEatnJohnson is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    17
    or

    INSERT INTO Evals (class_id, c1a, c2a, c3a, c4a, c5a, c6a, i1a, i2a, i3a, i4a, i5a, i6a, i7a, i8a, i9a, class_comments, taken, year_taken, recommended, dealer, which_one, family, friend, licensing, school, [work], other_source, what_other_source, experience, cruiser, touring, sport_bike, dual_sport, off_road, other_bike, what_other_bike, age, ethnnicity)
    SELECT DISTINCT Class.ID WHERE Class.site = Both.Site And Class.type = Both.Type And Class.start_date = Both.Date And Class.coach1 = Both.RiderCoach1
    Both.CC1, Both.CC2, Both.CC3, Both.CC4, Both.CC5, Both.CC6, Both.I1, Both.I2, Both.I3, Both.I4, Both.I5, Both.I6, Both.I7, Both.I8, Both.I9, Both.Comments, Both.Taken, Both.Year, Both.Recommend, Both.Dealer, Both.What_Dealer, Both.Family, Both.Friend, Both.Licensing, Both.School, Both.Work, Both.Other, Both.WhereElse, Both.Experience, Both.Cruiser, Both.Touring, Both.Sport_Bike, Both.Dual_Sport, Both.Off_Road AS Expr1, Both.Other_Bike, Both.What_Bike, Both.Age, Both.Ethnicity
    FROM [Both];

    This doesn't work but I think you get the idea or using the Dlookup like this

    INSERT INTO Evals (class_id, c1a, c2a, c3a, c4a, c5a, c6a, i1a, i2a, i3a, i4a, i5a, i6a, i7a, i8a, i9a, class_comments, taken, year_taken, recommended, dealer, which_one, family, friend, licensing, school, [work], other_source, what_other_source, experience, cruiser, touring, sport_bike, dual_sport, off_road, other_bike, what_other_bike, age, ethnnicity)
    SELECT DISTINCT DLookUp("[ID]","[Class]","[Both.Site]" & "[Both.Type]" & "[Both.Date]" & "[Both.RiderCoach1]")
    Both.CC1, Both.CC2, Both.CC3, Both.CC4, Both.CC5, Both.CC6, Both.I1, Both.I2, Both.I3, Both.I4, Both.I5, Both.I6, Both.I7, Both.I8, Both.I9, Both.Comments, Both.Taken, Both.Year, Both.Recommend, Both.Dealer, Both.What_Dealer, Both.Family, Both.Friend, Both.Licensing, Both.School, Both.Work, Both.Other, Both.WhereElse, Both.Experience, Both.Cruiser, Both.Touring, Both.Sport_Bike, Both.Dual_Sport, Both.Off_Road AS Expr1, Both.Other_Bike, Both.What_Bike, Both.Age, Both.Ethnicity
    FROM [Both];

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    It might be easier for the readers if you posted a jpg of your tables and relationships.

    Failing that I would suggest techonthenet as a good reference for Access/vba functions and syntax.
    http://www.techonthenet.com/access/f...in/dlookup.php

  7. #7
    LiverEatnJohnson is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    17
    Click image for larger version. 

Name:	tableStructure.png 
Views:	10 
Size:	66.0 KB 
ID:	11914 There is a picture of my table structure. The problem has nothing to do with that though. I may be able to use a union with a nested select here is a sudo code try so you can get an idea of what I mean

    INSERT INTO Evals(class_id)
    SELECT ID
    FROM Class
    WHERE Class.site = Both.site AND Class.type = Both.type AND Class.start_date = Both.Date AND Class.coach1 = Both.RiderCoach1
    UNION
    INSERT INTO Evals( allTheEvalAnswerFields)
    SELECT DISTINCT Both.allTheAnswerFields
    FROM(both)

  8. #8
    LiverEatnJohnson is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    17
    ok I made some progress

    my professor said i should do it in steps
    like first import the evals and then get the classes for it
    well the field was too small to do all the eval answers at once so first i did the second half of the eval and am now trying to do an update query to add in the first half but it comes up blank

    here is the insert query i did
    INSERT INTO Evals ( taken, year_taken, recommended, dealer, which_one, family, friend, licensing, school, work, other_source, what_other_source, experinece, cruiser, touring, sport_bike, dual_sport, off_road, other_bike, what_other_bike, age, ethnnicity )
    SELECT Both.Taken, Both.Year, Both.Recommend, Both.Dealer, Both.What_Dealer, Both.Family, Both.Friend, Both.Licensing, Both.School, Both.Work, Both.Other, Both.WhereElse, Both.Experience, Both.Cruiser, Both.Touring, Both.Sport_Bike, Both.Dual_Sport, Both.[Off-Road], Both.Other_Bike, Both.What_Bike, Both.Age, Both.Ethnicity
    FROM [Both];

  9. #9
    LiverEatnJohnson is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    17
    got the second half in just did a copy and past after making sure they were in the same order on both the Both table and Eval table
    now i just need to match all the evals to a class

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA SQL - append query
    By dualvba in forum Programming
    Replies: 5
    Last Post: 07-30-2012, 02:23 AM
  2. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  3. Replies: 7
    Last Post: 07-21-2011, 01:01 PM
  4. Tough form issue that uses DLookup's
    By Solola in forum Forms
    Replies: 3
    Last Post: 03-15-2011, 01:25 PM
  5. Append query won't append
    By yelkenli in forum Queries
    Replies: 5
    Last Post: 02-12-2010, 11:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums