transform query

  • Thread starter Thread starter Mario Krsnic
  • Start date Start date
M

Mario Krsnic

Hello everybody,
I have in a table the saved absences of persons. So:
name, BeginAbs, EndAbs
John, 1.5.2005, 12.5.2005

To get the absences for a certain date I use this query:
SELECT name FROM absences
WHERE MyDate Between BeginAbs And EndAbs;

This works fine. Now I would like to transform the query using pivot feature
to get a month view of
absences for every day and every person.
How to do it?
Thanks
Mario
 
What I have done in the past is create a table with all dates (tblAllDates
and field TheDate). Consider starting with a query that creates separate
records for each date.
Something like:
SELECT Name, BeginAbs, EndAbs,
tblAllDates.TheDate
FROM tblAllDates, tblAbss
WHERE (((tblAllDates.TheDate) Between [BeginAbs] And [EndAbs]));
 
What I have done in the past is create a table with all dates (tblAllDates
and field TheDate). Consider starting with a query that creates separate
records for each date.
Click to expand...

Sorry. I did not understand. How do you mean the dates enter the table
tblAllDates?
Now I have BeginAbs, EndAbs for a certain person and I have an empty table
tblAllDates. I need the dates in the table tblAllDates to be able to perform
your query?!
Cheers
Mario
Something like:
SELECT Name, BeginAbs, EndAbs,
tblAllDates.TheDate
FROM tblAllDates, tblAbss
WHERE (((tblAllDates.TheDate) Between [BeginAbs] And [EndAbs]));

--
Duane Hookom
MS Access MVP


Mario Krsnic said:
Hello everybody,
I have in a table the saved absences of persons. So:
name, BeginAbs, EndAbs
John, 1.5.2005, 12.5.2005

To get the absences for a certain date I use this query:
SELECT name FROM absences
WHERE MyDate Between BeginAbs And EndAbs;

This works fine. Now I would like to transform the query using pivot
feature
to get a month view of
absences for every day and every person.
How to do it?
Thanks
Mario
Click to expand...
Click to expand...
 
You need to fill the table tblAllDates with every possible date in your
application. You can use code to append the records or possibly create them
in Excel and paste them into the table.

--
Duane Hookom
MS Access MVP


Mario Krsnic said:
What I have done in the past is create a table with all dates
(tblAllDates
and field TheDate). Consider starting with a query that creates separate
records for each date.
Click to expand...

Sorry. I did not understand. How do you mean the dates enter the table
tblAllDates?
Now I have BeginAbs, EndAbs for a certain person and I have an empty table
tblAllDates. I need the dates in the table tblAllDates to be able to
perform
your query?!
Cheers
Mario
Something like:
SELECT Name, BeginAbs, EndAbs,
tblAllDates.TheDate
FROM tblAllDates, tblAbss
WHERE (((tblAllDates.TheDate) Between [BeginAbs] And [EndAbs]));

--
Duane Hookom
MS Access MVP


Mario Krsnic said:
Hello everybody,
I have in a table the saved absences of persons. So:
name, BeginAbs, EndAbs
John, 1.5.2005, 12.5.2005

To get the absences for a certain date I use this query:
SELECT name FROM absences
WHERE MyDate Between BeginAbs And EndAbs;

This works fine. Now I would like to transform the query using pivot
feature
to get a month view of
absences for every day and every person.
How to do it?
Thanks
Mario
Click to expand...
Click to expand...
Click to expand...
 
Back
Top