Force Blank Lines

[jnew]

Greetings again,

This community is a life saver!

I think there is a simple solution to this problem, but
I'm unable to come up with it.

Each page of my report lists one employee. Each employee
MAY be assigned to one or more of five committees:

1. Benefits Review
2. Community Service
3. Custom Training
etc.

The database includes a look-up table listing the five
committees, and a bridge table for employee-committee
assignments (composite key: empid, cmteeid). The bridge
table includes additional data, such as the date the
employee was assigned to the committee and compensation.

Here is my problem. I would like the report to always
display all five committees and then show the date and
compensation next to the committees to which the specific
employee is assigned.

I've created an outer-join query, but it's not working for
me.

I've come up with a solution, but it's much more
cumbersome than I think it needs to be. I've created five
subreports, each filtered for an individual committee.

Is there another, more efficient way to pull this off.

jn

 

[Allen Browne]

You want to show every possible combination of employee and committee,
regardless of whether there is an entry in the junction table or not.

1. Create a query based on the Employee table and the Committe table. The
junction table is not in this query.

2. If you see any line joining the 2 tables, delete it. It is the lack of a
join that gives you every possible combination (a Cartesian Product).

3. Save the query. Close.

4. Create another query using the first one as an input "table", along with
the junction table, joined to the first query on EmpID and CmteeId.

5. Double-click the join lines, and make them outer joins.

6. Save and close.

7. Open your report in design view, and noninate this last query as the
Record Source for the report.

 

[Michael Noblet]

I have a report that shows utilization of a room in
calendar format. I was wondering if I could use this
method or something like it to creat a record for a room
with no utilization. We want all rooms to show up
everytime we run the report regardsless if there is any
utilization.

Can you give me a little direction?

Mike

 

[Allen Browne]

Yes.

1. Create a query based on:
- a table of rooms, and
- a table of all the timeslots,
and there is no join between the 2 tables, you will get every possible
combination of room + timeslot.
Save.

2. Create another query that contains the first one as an input "table", and
your appointments table. Join them as appropriate (e.g. on rooom and on
timeslot). Double-click the join line. Access offers a dialog with 3
choices. Choose, "All records from the query, and any matches from the
table." (This gives you an outer join query.) Use this query as the source
for your report.

Hopefully you will be able to apply those general principlies to your
particular data structure.

 

[Michael Noblet]

Allen,

I used your sugestion and I am not getting what I am
really looking for. I am sure I am just entering a
perameter incorectly.

Here is what is going on:

I have a table with OR room, Case Start date, Case Start
Time, Case Stop time, or Number. I have a report with vb
script to format the report below:
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim lngDuration As Long 'Length of Usage
Dim lngStart As Long 'Time Start
Dim lngLMarg As Long 'Left Margin of Graph Line
Dim dblFactor As Double 'Denotes Graph Line length
lngLMarg = Me.BoxGraphLine.Left
dblFactor = Me.BoxGraphLine.Width / 1440
lngStart = DateDiff("n", #12:00:00 AM#, Me.
[CalcStartTime])
lngDuration = DateDiff("n", Me.[CalcStartTime], Me.
[CalcEndTime])
Me.txtProcedure.BackColor = 8421504
Me.txtProcedure.BorderColor = 16777215
Me.txtProcedure.Width = 10
Me.txtProcedure.Left = (lngStart * dblFactor) +
lngLMarg
Me.txtProcedure.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

This draws a line on a grid of hours from 0 to 23 to show
graphically when a room was used. on each day. For each
day I want to show all ors not just the ones used. When I
tried the Cartesian Product solution you sugested the
report output did not work correctly. Aby ideas would be
greatly appreciated.

Mike

 

[Allen Browne]

Mike, there are many facets to this, but I think you will need a table of
dates and a table of OR rooms. Put both into a query, with no join. That
gives you a query showing every OR for every date. Save.

Now use that query as in input "table" for another query.
Add the actual bookings table.
Join the table and the query on the Start date.
Double-click the join line, and choose:
All records from the query, and any matches from ...
Use that as the source for your report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I used your sugestion and I am not getting what I am
really looking for. I am sure I am just entering a
perameter incorectly.

Here is what is going on:

I have a table with OR room, Case Start date, Case Start
Time, Case Stop time, or Number. I have a report with vb
script to format the report below:
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim lngDuration As Long 'Length of Usage
Dim lngStart As Long 'Time Start
Dim lngLMarg As Long 'Left Margin of Graph Line
Dim dblFactor As Double 'Denotes Graph Line length
lngLMarg = Me.BoxGraphLine.Left
dblFactor = Me.BoxGraphLine.Width / 1440
lngStart = DateDiff("n", #12:00:00 AM#, Me.
[CalcStartTime])
lngDuration = DateDiff("n", Me.[CalcStartTime], Me.
[CalcEndTime])
Me.txtProcedure.BackColor = 8421504
Me.txtProcedure.BorderColor = 16777215
Me.txtProcedure.Width = 10
Me.txtProcedure.Left = (lngStart * dblFactor) +
lngLMarg
Me.txtProcedure.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

This draws a line on a grid of hours from 0 to 23 to show
graphically when a room was used. on each day. For each
day I want to show all ors not just the ones used. When I
tried the Cartesian Product solution you sugested the
report output did not work correctly. Aby ideas would be
greatly appreciated.

Mike

-----Original Message-----
Yes.

1. Create a query based on:
- a table of rooms, and
- a table of all the timeslots,
and there is no join between the 2 tables, you will get every possible
combination of room + timeslot.
Save.

2. Create another query that contains the first one as an input "table", and
your appointments table. Join them as appropriate (e.g. on rooom and on
timeslot). Double-click the join line. Access offers a dialog with 3
choices. Choose, "All records from the query, and any matches from the
table." (This gives you an outer join query.) Use this query as the source
for your report.

Hopefully you will be able to apply those general principlies to your
particular data structure.

 

[Michael Noblet]

Allen,

I swear I am doing everything you have laid out here, but
I am still not getting usefull data.

My SQL statement looks like this:

SELECT qryORUtilizationCalcs.ScheduleDate,
qryORUtilizationCalcs.[Start Time], qryORUtilizationCalcs.
[Stop Time1], qryORUtilizationCalcs.CalcStartTime,
qryORUtilizationCalcs.CalcEndTime, qryORUtilizationCalcs.
[OR Rm], qryORUtilizationCalcs.[OR Case Number],
qryORUtilizationCalcs.DayNum
FROM qryORScheduledDateList LEFT JOIN
qryORUtilizationCalcs ON
qryORScheduledDateList.ScheduleDate =
qryORUtilizationCalcs.ScheduleDate;

qryUtilizationCals is the "Table" for the actual
historical usage of the room and qryORScheduledDateList is
the query that creates the cartesian product.

the output of the SQL statement above does not give the
new lines where the or would have a schedule dat but no
time as I am looking for. It gives 35 times the records
back. 35 happens to be the # of OR rooms.

If I make it a right join it gives me duplicate records
over and over.

Any ideas? I promise I won't bother you any more if I can
get a handle on this.

Mike

-----Original Message-----
Mike, there are many facets to this, but I think you will need a table of
dates and a table of OR rooms. Put both into a query, with no join. That
gives you a query showing every OR for every date. Save.

Now use that query as in input "table" for another query.
Add the actual bookings table.
Join the table and the query on the Start date.
Double-click the join line, and choose:
All records from the query, and any matches from ...
Use that as the source for your report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I used your sugestion and I am not getting what I am
really looking for. I am sure I am just entering a
perameter incorectly.

Here is what is going on:

I have a table with OR room, Case Start date, Case Start
Time, Case Stop time, or Number. I have a report with vb
script to format the report below:
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim lngDuration As Long 'Length of Usage
Dim lngStart As Long 'Time Start
Dim lngLMarg As Long 'Left Margin of Graph Line
Dim dblFactor As Double 'Denotes Graph Line length
lngLMarg = Me.BoxGraphLine.Left
dblFactor = Me.BoxGraphLine.Width / 1440
lngStart = DateDiff("n", #12:00:00 AM#, Me.
[CalcStartTime])
lngDuration = DateDiff("n", Me.[CalcStartTime], Me.
[CalcEndTime])
Me.txtProcedure.BackColor = 8421504
Me.txtProcedure.BorderColor = 16777215
Me.txtProcedure.Width = 10
Me.txtProcedure.Left = (lngStart * dblFactor) +
lngLMarg
Me.txtProcedure.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

This draws a line on a grid of hours from 0 to 23 to show
graphically when a room was used. on each day. For each
day I want to show all ors not just the ones used. When I
tried the Cartesian Product solution you sugested the
report output did not work correctly. Aby ideas would be
greatly appreciated.

Mike

-----Original Message-----
Yes.

1. Create a query based on:
- a table of rooms, and
- a table of all the timeslots,
and there is no join between the 2 tables, you will get every possible
combination of room + timeslot.
Save.

2. Create another query that contains the first one as

an
input "table", and

your appointments table. Join them as appropriate (e.g. on rooom and on
timeslot). Double-click the join line. Access offers a dialog with 3
choices. Choose, "All records from the query, and any matches from the
table." (This gives you an outer join query.) Use this query as the source
for your report.

Hopefully you will be able to apply those general principlies to your
particular data structure.

--
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.

I have a report that shows utilization of a room in
calendar format. I was wondering if I could use this
method or something like it to creat a record for a room
with no utilization. We want all rooms to show up
everytime we run the report regardsless if there is any
utilization.

Can you give me a little direction?

Mike
-----Original Message-----
You want to show every possible combination of employee
and committee,
regardless of whether there is an entry in the junction
table or not.

1. Create a query based on the Employee table and the
Committe table. The
junction table is not in this query.

2. If you see any line joining the 2 tables, delete it.
It is the lack of a
join that gives you every possible combination (a
Cartesian Product).

3. Save the query. Close.

4. Create another query using the first one as an
input "table", along with
the junction table, joined to the first query on EmpID
and CmteeId.

5. Double-click the join lines, and make them outer joins.

6. Save and close.

7. Open your report in design view, and noninate this
last query as the
Record Source for the report.


Greetings again,

This community is a life saver!

I think there is a simple solution to this

problem,
but

I'm unable to come up with it.

Each page of my report lists one employee. Each employee
MAY be assigned to one or more of five committees:

1. Benefits Review
2. Community Service
3. Custom Training
etc.

The database includes a look-up table listing the five
committees, and a bridge table for employee- committee
assignments (composite key: empid, cmteeid). The bridge
table includes additional data, such as the date the
employee was assigned to the committee and compensation.

Here is my problem. I would like the report to always
display all five committees and then show the date and
compensation next to the committees to which the
specific
employee is assigned.

I've created an outer-join query, but it's not working
for
me.

I've come up with a solution, but it's much more
cumbersome than I think it needs to be. I've created
five
subreports, each filtered for an individual committee.

Is there another, more efficient way to pull this off.

jn

.

 

[Allen Browne]

Hi Mike

So qryORScheduledDateList gives you every combination of OR + Date. Good.

The final query probably needs to be joined on 2 fields: the date (as you
have) and also to [OR Rm]. In query design view, try dragging the [OR Rm]
field from qryORScheduledDateList onto the [OR Rm] in qryORUtilizationCalcs.
Then double-click the join line to turn it into an outer join also. You will
end up with two lines joining the source tables, with the arrow-heads both
pointing the same direction.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I swear I am doing everything you have laid out here, but
I am still not getting usefull data.

My SQL statement looks like this:

SELECT qryORUtilizationCalcs.ScheduleDate,
qryORUtilizationCalcs.[Start Time], qryORUtilizationCalcs.
[Stop Time1], qryORUtilizationCalcs.CalcStartTime,
qryORUtilizationCalcs.CalcEndTime, qryORUtilizationCalcs.
[OR Rm], qryORUtilizationCalcs.[OR Case Number],
qryORUtilizationCalcs.DayNum
FROM qryORScheduledDateList LEFT JOIN
qryORUtilizationCalcs ON
qryORScheduledDateList.ScheduleDate =
qryORUtilizationCalcs.ScheduleDate;

qryUtilizationCals is the "Table" for the actual
historical usage of the room and qryORScheduledDateList is
the query that creates the cartesian product.

the output of the SQL statement above does not give the
new lines where the or would have a schedule dat but no
time as I am looking for. It gives 35 times the records
back. 35 happens to be the # of OR rooms.

If I make it a right join it gives me duplicate records
over and over.

Any ideas? I promise I won't bother you any more if I can
get a handle on this.

Mike

-----Original Message-----
Mike, there are many facets to this, but I think you will need a table of
dates and a table of OR rooms. Put both into a query, with no join. That
gives you a query showing every OR for every date. Save.

Now use that query as in input "table" for another query.
Add the actual bookings table.
Join the table and the query on the Start date.
Double-click the join line, and choose:
All records from the query, and any matches from ...
Use that as the source for your report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I used your sugestion and I am not getting what I am
really looking for. I am sure I am just entering a
perameter incorectly.

Here is what is going on:

I have a table with OR room, Case Start date, Case Start
Time, Case Stop time, or Number. I have a report with vb
script to format the report below:
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim lngDuration As Long 'Length of Usage
Dim lngStart As Long 'Time Start
Dim lngLMarg As Long 'Left Margin of Graph Line
Dim dblFactor As Double 'Denotes Graph Line length
lngLMarg = Me.BoxGraphLine.Left
dblFactor = Me.BoxGraphLine.Width / 1440
lngStart = DateDiff("n", #12:00:00 AM#, Me.
[CalcStartTime])
lngDuration = DateDiff("n", Me.[CalcStartTime], Me.
[CalcEndTime])
Me.txtProcedure.BackColor = 8421504
Me.txtProcedure.BorderColor = 16777215
Me.txtProcedure.Width = 10
Me.txtProcedure.Left = (lngStart * dblFactor) +
lngLMarg
Me.txtProcedure.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

This draws a line on a grid of hours from 0 to 23 to show
graphically when a room was used. on each day. For each
day I want to show all ors not just the ones used. When I
tried the Cartesian Product solution you sugested the
report output did not work correctly. Aby ideas would be
greatly appreciated.

Mike

-----Original Message-----
Yes.

1. Create a query based on:
- a table of rooms, and
- a table of all the timeslots,
and there is no join between the 2 tables, you will get
every possible
combination of room + timeslot.
Save.

2. Create another query that contains the first one as an
input "table", and
your appointments table. Join them as appropriate (e.g.
on rooom and on
timeslot). Double-click the join line. Access offers a
dialog with 3
choices. Choose, "All records from the query, and any
matches from the
table." (This gives you an outer join query.) Use this
query as the source
for your report.

Hopefully you will be able to apply those general
principlies to your
particular data structure.

--
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.

"Michael Noblet" <[email protected]>
wrote in message
I have a report that shows utilization of a room in
calendar format. I was wondering if I could use this
method or something like it to creat a record for a room
with no utilization. We want all rooms to show up
everytime we run the report regardsless if there is any
utilization.

Can you give me a little direction?

Mike
-----Original Message-----
You want to show every possible combination of employee
and committee,
regardless of whether there is an entry in the junction
table or not.

1. Create a query based on the Employee table and the
Committe table. The
junction table is not in this query.

2. If you see any line joining the 2 tables, delete it.
It is the lack of a
join that gives you every possible combination (a
Cartesian Product).

3. Save the query. Close.

4. Create another query using the first one as an
input "table", along with
the junction table, joined to the first query on EmpID
and CmteeId.

5. Double-click the join lines, and make them outer
joins.

6. Save and close.

7. Open your report in design view, and noninate this
last query as the
Record Source for the report.


Greetings again,

This community is a life saver!

I think there is a simple solution to this problem,
but
I'm unable to come up with it.

Each page of my report lists one employee. Each
employee
MAY be assigned to one or more of five committees:

1. Benefits Review
2. Community Service
3. Custom Training
etc.

The database includes a look-up table listing the
five
committees, and a bridge table for employee- committee
assignments (composite key: empid, cmteeid). The
bridge
table includes additional data, such as the date the
employee was assigned to the committee and
compensation.

Here is my problem. I would like the report to always
display all five committees and then show the date
and
compensation next to the committees to which the
specific
employee is assigned.

I've created an outer-join query, but it's not
working
for
me.

I've come up with a solution, but it's much more
cumbersome than I think it needs to be. I've created
five
subreports, each filtered for an individual
committee.

Is there another, more efficient way to pull this
off.

jn

 

[Michael Noblet]

The join on both fields was the problem. The report has
come out very nice. Thank you very much for your help.

Mike

-----Original Message-----
Hi Mike

So qryORScheduledDateList gives you every combination of OR + Date. Good.

The final query probably needs to be joined on 2 fields: the date (as you
have) and also to [OR Rm]. In query design view, try dragging the [OR Rm]
field from qryORScheduledDateList onto the [OR Rm] in qryORUtilizationCalcs.
Then double-click the join line to turn it into an outer join also. You will
end up with two lines joining the source tables, with the arrow-heads both
pointing the same direction.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I swear I am doing everything you have laid out here, but
I am still not getting usefull data.

My SQL statement looks like this:

SELECT qryORUtilizationCalcs.ScheduleDate,
qryORUtilizationCalcs.[Start Time], qryORUtilizationCalcs.
[Stop Time1], qryORUtilizationCalcs.CalcStartTime,
qryORUtilizationCalcs.CalcEndTime, qryORUtilizationCalcs.
[OR Rm], qryORUtilizationCalcs.[OR Case Number],
qryORUtilizationCalcs.DayNum
FROM qryORScheduledDateList LEFT JOIN
qryORUtilizationCalcs ON
qryORScheduledDateList.ScheduleDate =
qryORUtilizationCalcs.ScheduleDate;

qryUtilizationCals is the "Table" for the actual
historical usage of the room and qryORScheduledDateList is
the query that creates the cartesian product.

the output of the SQL statement above does not give the
new lines where the or would have a schedule dat but no
time as I am looking for. It gives 35 times the records
back. 35 happens to be the # of OR rooms.

If I make it a right join it gives me duplicate records
over and over.

Any ideas? I promise I won't bother you any more if I can
get a handle on this.

Mike

-----Original Message-----
Mike, there are many facets to this, but I think you

will
need a table of

dates and a table of OR rooms. Put both into a query, with no join. That
gives you a query showing every OR for every date. Save.

Now use that query as in input "table" for another query.
Add the actual bookings table.
Join the table and the query on the Start date.
Double-click the join line, and choose:
All records from the query, and any matches from ...
Use that as the source for your report.

--
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.

Allen,

I used your sugestion and I am not getting what I am
really looking for. I am sure I am just entering a
perameter incorectly.

Here is what is going on:

I have a table with OR room, Case Start date, Case Start
Time, Case Stop time, or Number. I have a report

with
vb

script to format the report below:
Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
Dim lngDuration As Long 'Length of Usage
Dim lngStart As Long 'Time Start
Dim lngLMarg As Long 'Left Margin of Graph Line
Dim dblFactor As Double 'Denotes Graph Line length
lngLMarg = Me.BoxGraphLine.Left
dblFactor = Me.BoxGraphLine.Width / 1440
lngStart = DateDiff("n", #12:00:00 AM#, Me.
[CalcStartTime])
lngDuration = DateDiff("n", Me.[CalcStartTime], Me.
[CalcEndTime])
Me.txtProcedure.BackColor = 8421504
Me.txtProcedure.BorderColor = 16777215
Me.txtProcedure.Width = 10
Me.txtProcedure.Left = (lngStart * dblFactor) +
lngLMarg
Me.txtProcedure.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

This draws a line on a grid of hours from 0 to 23 to show
graphically when a room was used. on each day. For each
day I want to show all ors not just the ones used. When I
tried the Cartesian Product solution you sugested the
report output did not work correctly. Aby ideas

would
be

greatly appreciated.

Mike

-----Original Message-----
Yes.

1. Create a query based on:
- a table of rooms, and
- a table of all the timeslots,
and there is no join between the 2 tables, you will get
every possible
combination of room + timeslot.
Save.

2. Create another query that contains the first one

as
an

input "table", and
your appointments table. Join them as appropriate (e.g.
on rooom and on
timeslot). Double-click the join line. Access offers a
dialog with 3
choices. Choose, "All records from the query, and any
matches from the
table." (This gives you an outer join query.) Use this
query as the source
for your report.

Hopefully you will be able to apply those general
principlies to your
particular data structure.

--
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.

"Michael Noblet"

wrote in message
I have a report that shows utilization of a room in
calendar format. I was wondering if I could use this
method or something like it to creat a record for

a
room

with no utilization. We want all rooms to show up
everytime we run the report regardsless if there

is
any

utilization.

Can you give me a little direction?

Mike
-----Original Message-----
You want to show every possible combination of employee
and committee,
regardless of whether there is an entry in the junction
table or not.

1. Create a query based on the Employee table and the
Committe table. The
junction table is not in this query.

2. If you see any line joining the 2 tables,

delete
it.

It is the lack of a
join that gives you every possible combination (a
Cartesian Product).

3. Save the query. Close.

4. Create another query using the first one as an
input "table", along with
the junction table, joined to the first query on EmpID
and CmteeId.

5. Double-click the join lines, and make them outer
joins.

6. Save and close.

7. Open your report in design view, and noninate this
last query as the
Record Source for the report.


Greetings again,

This community is a life saver!

I think there is a simple solution to this problem,
but
I'm unable to come up with it.

Each page of my report lists one employee. Each
employee
MAY be assigned to one or more of five committees:

1. Benefits Review
2. Community Service
3. Custom Training
etc.

The database includes a look-up table listing the
five
committees, and a bridge table for employee- committee
assignments (composite key: empid, cmteeid). The
bridge
table includes additional data, such as the

date
the

employee was assigned to the committee and
compensation.

Here is my problem. I would like the report to always
display all five committees and then show the date
and
compensation next to the committees to which the
specific
employee is assigned.

I've created an outer-join query, but it's not
working
for
me.

I've come up with a solution, but it's much more
cumbersome than I think it needs to be. I've created
five
subreports, each filtered for an individual
committee.

Is there another, more efficient way to pull this
off.

jn

.