Improving append query speed

[HKKS]

Hello,

I have an update query that works well and at good speed ONLY IF I use a
single field in the append query, such as "LastName". But my pull down combo
boxes have the follwing expression: Expr1: [LastName] & ", " & [FirstName] &
", " & [Position], which allows the user to choose the right student (many
same last names). So, I have also put the same expression in the append query
as well. This slows down the append query dramatically.

Any way to have a single field in both combo boxes and the append query AND
still have the LastName, FirstName & Position appear on the combo boxes?

The append query SQL coding just in case it helps:

SELECT DISTINCTROW [LastName] & ", " & [FirstName] & ", " & [Position] AS
Expr1, [Course Details].CourseID, [Course Details].Material1, [Course
Details].Material2, [Course Details].Material3, [Course Details].Material4,
[Course Details].Material5, [Student DataBase].Company, *
FROM [Student DataBase] INNER JOIN [Course Details] ON [Student
DataBase].Company = [Course Details].Company
WHERE ((([LastName] & ", " & [FirstName] & ", " &
[Position])=[Forms]![Search Students for New Class]![cmdLastName1] Or...

Thanks very much in advance.
Ken

 

[Dale_Fye via AccessMonster.com]

Ken,

The best way to do this is to have two fields in the combo box (PersID, and
then the composite Lastname, firstname, position. You set the combo box up
with 2 columns, set the bound column to 1, then set the column widths to 0"
and 3" (or whatever you need to display the name stuff).

Then use the PersID in your query.

HTH
Dale

Hello,

I have an update query that works well and at good speed ONLY IF I use a
single field in the append query, such as "LastName". But my pull down combo
boxes have the follwing expression: Expr1: [LastName] & ", " & [FirstName] &
", " & [Position], which allows the user to choose the right student (many
same last names). So, I have also put the same expression in the append query
as well. This slows down the append query dramatically.

Any way to have a single field in both combo boxes and the append query AND
still have the LastName, FirstName & Position appear on the combo boxes?

The append query SQL coding just in case it helps:

SELECT DISTINCTROW [LastName] & ", " & [FirstName] & ", " & [Position] AS
Expr1, [Course Details].CourseID, [Course Details].Material1, [Course
Details].Material2, [Course Details].Material3, [Course Details].Material4,
[Course Details].Material5, [Student DataBase].Company, *
FROM [Student DataBase] INNER JOIN [Course Details] ON [Student
DataBase].Company = [Course Details].Company
WHERE ((([LastName] & ", " & [FirstName] & ", " &
[Position])=[Forms]![Search Students for New Class]![cmdLastName1] Or...

Thanks very much in advance.
Ken

 

[HKKS]

Brilliant, absolutely brilliant Dale. Much quicker queries. Thanks millions.