[AccessD] Would a crosstab Query work instead?

[David McAfee]

That's what I'm trying to do, eliminate the dlookups.

It's actually pretty fast right now, probably because there isn't a lot of
data.
I was thinking the query and the dlookups are all based on the same sub
query, so the sub query should be cached.
The base query also returns very little data, so I was thinking the
dlookups might not be that slow, but it is doing it this way with my "poor
man's" pivot table. :)

I'm making a time and attendance little scheduler.
I'm trying to show different versions/displays (grid, Gantt chart...) of
the same data that can be viewed by clicking on different tab pages.

Thanks,
David






On Thu, Feb 25, 2016 at 3:42 PM, Charlotte Foust <charlotte.foust at gmail.com>
wrote:

> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>