Query to look Excel-like

  • Thread starter Thread starter Anonymous
  • Start date Start date
A

Anonymous

I'm creating a read-only form or any other way to pop up a window that
shows the records in my table in an Excel-like format, so I'm working on
the underlying query for it. Example follows:

Source data table is tbl_Absences:
----------------------------------------------------
fld_Clock# fld_StartDate fld_EndDate fld_AbsenceType
7 01/05/06 01/05/06 "L"
40 01/02/06 01/03/06 "V"
----------------------------------------------------

qry_Absences_Schedule end-result, I hope:

----------------------------------------------------
Emp# Emp#
7 40
01/01/06
01/02/06 V
01/03/06 V
01/04/06
01/05/06 L
01/06/06
----------------------------------------------------

I'm not even sure how to start... how do I get a query to kick out all the
dates within a date-range? A month at a time would be ideal...

I'm guessing eventually it'll have to be a crosstab query but 'ell if I
know at which point I have to make it be a crosstab query.... this'll
probably be the most compicated query I've had to make yet.

Jeff
 
Crosstab query for sure. There is a Crosstab query Wizard, but even then it
might take a couple of tries. Two tips on crosstabs:

First create a query that returns the records that you want. This query
could have a parameter in it in the criteria under fld_StartDate. Next
create the crosstab based on this query instead of the tables directly. In a
nutshell a query based on a query.

For a report you need to know the column headings or it will bomb out. There
are two ways of doing this. The first is to prepopulate the Column Headings
property of the crosstab query. That way the text boxes on the reports will
always know where to find the data. It has other benefits such as putting the
columns in a order that you like (such as months in chronological order
instead of April, August, etc.). It will also hold open a field such as for a
month that had no data or exclude a month if you don't want to see June for
example.

Also your sample data suggests that you need all the dates for a period and
not just those in the table. If this is so, you might need a table of dates
and left join it with tbl_Absences to show dates where tbl_Absences doesn't
have one.
 
I don't suppose there's a way to have a function or something create the
list of dates for, say, a month, instead of creating a table of dates,
that you can think of?

Thanks for the guidance - I'll be trying it again this morning.

Jeff
 
I don't have time to give you a detailed example, but you can define a
Table with 31 records (containing integer values 1-31) and write a Query
based on this Table that creates a date value for the day of the month
with each of these values. You might need to redefine the Query for the
next month, but these date values you could then attach to the Query on
which you're basing your Crosstab Query, and the dates could become the
column headers in the Crosstab.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top