[AccessD] Would a crosstab Query work instead?

[Charlotte Foust]

What is it that you're accomplishing with this?  Is it to produce some kind
of schedule for each person?  Those dlookups have to be awfully slow, and
you might be better off designing a report and using grouping to partition
the data.​

Charlotte Foust
(916) 206-4336

On Thu, Feb 25, 2016 at 3:27 PM, David McAfee <davidmcafee at gmail.com> wrote:

> Experiencing a bit of cerebal Falttulence...
>
> I created the following query, which works, and gives the expected results,
> but I'm thinking to myself "there must be an easier way to do this..."
>
> I was looking at cross tabs, but they all want to sum/count/average the
> data, I just want to display it.
>
>
> SELECT qrySchedules.FirstName,
>
> DLookUp("Start","qrySchedules","WeekdayNo =1 And [FirstName] ='" &
> [FirstName] & "'") AS SatStart,
>
> DLookUp("End","qrySchedules","WeekdayNo =1 And [FirstName] ='" &
> [FirstName] & "'") AS SatEnd,
>
> DLookUp("Start","qrySchedules","WeekdayNo =2 And [FirstName] ='" &
> [FirstName] & "'") AS SunStart,
>
> DLookUp("End","qrySchedules","WeekdayNo =2 And [FirstName] ='" &
> [FirstName] & "'") AS SunEnd,
>
> DLookUp("Start","qrySchedules","WeekdayNo =3 And [FirstName] ='" &
> [FirstName] & "'") AS MonStart,
>
> DLookUp("End","qrySchedules","WeekdayNo =3 And [FirstName] ='" &
> [FirstName] & "'") AS MonEnd,
>
> DLookUp("Start","qrySchedules","WeekdayNo =4 And [FirstName] ='" &
> [FirstName] & "'") AS TueStart,
>
> DLookUp("End","qrySchedules","WeekdayNo =4 And [FirstName] ='" &
> [FirstName] & "'") AS TueEnd,
>
> DLookUp("Start","qrySchedules","WeekdayNo =5 And [FirstName] ='" &
> [FirstName] & "'") AS WedStart,
>
> DLookUp("End","qrySchedules","WeekdayNo =5 And [FirstName] ='" &
> [FirstName] & "'") AS WedEnd,
>
> DLookUp("Start","qrySchedules","WeekdayNo =6 And [FirstName] ='" &
> [FirstName] & "'") AS ThuStart,
>
> DLookUp("End","qrySchedules","WeekdayNo =6 And [FirstName] ='" &
> [FirstName] & "'") AS ThuEnd,
>
> DLookUp("Start","qrySchedules","WeekdayNo =7 And [FirstName] ='" &
> [FirstName] & "'") AS FriStart,
>
> DLookUp("End","qrySchedules","WeekdayNo =7 And [FirstName] ='" &
> [FirstName] & "'") AS FriEnd
>
> FROM qrySchedules
>
> GROUP BY
>
> qrySchedules.FirstName,
>
> DLookUp("Start","qrySchedules","WeekdayNo =1 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("End","qrySchedules","WeekdayNo =1 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("Start","qrySchedules","WeekdayNo =2 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("End","qrySchedules","WeekdayNo =2 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("Start","qrySchedules","WeekdayNo =3 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("End","qrySchedules","WeekdayNo =3 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("Start","qrySchedules","WeekdayNo =4 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("End","qrySchedules","WeekdayNo =4 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("Start","qrySchedules","WeekdayNo =5 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("End","qrySchedules","WeekdayNo =5 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("Start","qrySchedules","WeekdayNo =6 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("End","qrySchedules","WeekdayNo =6 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("Start","qrySchedules","WeekdayNo =7 And [FirstName] ='" &
> [FirstName] & "'"),
>
> DLookUp("End","qrySchedules","WeekdayNo =7 And [FirstName] ='" &
> [FirstName]
> & "'");
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>