You will need a table of dates, so you can create a query that has a record
for each date in the date range. You can the create a crosstab query to show
the dates in the format you want. Note that the form will be read-only, i.e.
you will not be able to enter anything in that form (unless you handle the
entries programmatically.)
1. Create a table of dates.
Just one date/time field named (say) TheDate, and marked as primary key.
Save the table with the name tblDate.
2. Create the query using both tables (your existing table, and tblDate.)
There must be no line joining the 2 tables in the upper pane of query
design.
In the Criteria under tblDate.TheDate, enter:
Between COMPANY_HOLIDAY.FROM_DATE And COMPANY_HOLIDAY.TO_DATE
This gives you are record for every date in the date range.
The query in SQL View (View menu) will look something like this.
SELECT COMPANY_HOLIDAY.COMH_ID,
COMPANY_HOLIDAY.FROM_DATE,
COMPANY_HOLIDAY.TO_DATE
tblDate.TheDate
FROM COMPANY_HOLIDAY, tblDate
WHERE tblDate.TheDate Between COMPANY_HOLIDAY.FROM_DATE And
COMPANY_HOLIDAY.TO_DATE
3. Add your Company table to the query.
I'm assuming that COMH_ID is the field that indicates which company has this
booking? And that you want to show the company name in the query instead of
the COMH_ID? So add the company table to the query. Add the Compnay name to
the output grid.
4. Save that query with a name such as Query1. Close.
5. Create a new query, using Query1 as an input "table".
In query design view, change it to a Crosstab query (Crosstab on Query
menu.)
Access adds 2 rows to the design grid: Total, and Crosstab.
6. Enter these fields as columns in query design.
(The 3 rows refer to the Field, Group By, and Crosstab rows respectively.)
- Company
Group By
Row Header
- TheYear: Year(TheDate)
Group By
Row Header
- TheMonth: Month(TheDate)
Group By
RowHeader
- TheDay:Day(TheDate)
Group By
Column Header
- ComH_ID
First
Value
That gives you the basis to build a form or report. You probably want to
enter the numbers 1 to 31 (separated by commas) in the query's Column
Headings property, to guarantee that all days show.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Filips Benoit" <be***********@pandora.bewrote in message
news:4W**********************@phobos.telenet-ops.be...
Dear All,
I don't know how to start !
I have a table named COMPANY_HOLIDAY having fields: COMH_ID,
COMH_HOL_FROM_DATE and COMH_HOL_TO_DATE
ID FROM_DATE TO_DATE
1 13/12/2006 14/12/2006
2 12/12/2006 12/12/2006
2 21/12/2006 22/12/2006
3 1/12/2006 21/12/2006
Must build a form having:
COLUMNS: CompanyName + the days of selected month/year ( February 2007)
like
ROWS: companies ( all or a selection)
Microsoft feb1 feb2 feb3 feb4 >>feb28
ABC-news feb1 feb2 feb3 feb4 >>feb28
Flash-TV feb1 feb2 feb3 feb4 >>feb28
(Working days in white-color, weekend-days in gray and holidays in red.)
I think I need a recordset ( table, query) for the form's Recordsource !
Thanks,
Filip
