Microsoft Access Report with dates

[kimvandersteen]

Hello,

I've some problems with the creation of a report in Access. I'll first brief describe the problem.

For our planning we have a table that contains the id of the employee, the id of the project, the date and the time (AM/PM). When I create a report for employee X, I only see the date (and time) when they are occupied. It is the intention to print also the period when they are available (for other project).

Example of the lay-out:
-----------------------

Employee: Date: AM/PM: Project:
--------- ----- ------ --------
DOE 1-1-2007 AM a
DOE 1-1-2007 PM a
DOE 2-1-2007 AM b
DOE 2-1-2007 PM
DOE 3-1-2007 AM d
DOE 3-1-2007 PM a
DOE 4-1-2007 AM b
DOE 4-1-2007 PM

Can somebody help me with this?

Thx in advance,
Kim

 

[dhookom]

You need to create a table or derive a recordset of all possible dates and times. I generally have a tblDates with a single field [TheDate] in most applications I create. Create records for all your dates (use code or copy from Excel). Create a table tblAMPM and with a single field [AMPM] and add two record "AM" and "PM".

Create a cartesian query of all dates and AMPMs
=== qcartDateAP =======
SELECT [TheDate], [AMPM]
FROM tblDates, tblAMPM;
========================

Add this query to your original query and join the date and ampm fields. Change the join to include all records from the qcartDateAP records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MallardVHS]

Hi Kim,

Any chance a PivotTable would help? One with dual polarity (I am positive that's not the right term!! - Sorry!)

Random thoughts are seldom, er, MS'd

Don

[green]Tis far easier to keep your duck in a row if you just have the one.[/green]

 

[kimvandersteen]

Thank you all for your usefull comments!

Finally this was the solution:
------------------------------
SELECT * FROM tbl_planning AS p
RIGHT JOIN
(SELECT date, ampm FROM tbl_date, tbl_ampm) AS d ON (p.planningdate = d.date AND p.planningampm = d.ampm)

Now I only need to get information about our employees and the projects in the query. Simple on the first sight, but I receive always an error. It should be a simple inner join I guess.

This is what I've tried:
------------------------
(SELECT * FROM tbl_planning AS p
INNER JOIN tbl_employee AS e
ON p.planningemployee=e.empid)
RIGHT JOIN
(SELECT date, ampm FROM tbl_date, tbl_ampm) AS d ON (p.planningdate = d.date AND p.planningampm = d.ampm)

Can someone tell me where I'm wrong?

Thx

 

[dhookom]

Why don't you simplify this by creating multiple queries?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]