Tough Append Query!!!!!!

[LiverEatnJohnson]

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.

[orange]

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.

[LiverEatnJohnson]

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.

[LiverEatnJohnson]

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];

[LiverEatnJohnson]

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];

[orange]

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

[LiverEatnJohnson]

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)

[LiverEatnJohnson]

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];

[LiverEatnJohnson]

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