Repeating Data

[Aria]

Hi,
I have a school database that among other things tracks keys. I have a
number of key reports and created a new one called rptToday’sRequests. I was
asked to change it so that the room # and location (Stadium, W-101,
Cafeteria, etc.) would appear at the top (key code header) of each key
request. Placing the key code in the header, I think, is the problem.

There are different key codes that will let you gain access to one or a
multitude of sites on campus. If the key request is for a code that opens a
single location, there isn’t a problem. If the request is for a master key
which opens multiple locations, I see repeating data. It shows the first
instance of the location for that key code and then repeats the data for each
additional location without listing the location.

Here is the SQL for the report:
SELECT tblEmployees.EmpID, tblEmployees.EmployeeType, [FirstName] & " " &
[LastName] AS FullName, tblEmployees.FirstName, tblEmployees.LastName,
tblKeysRequests.KeyID, tblKeys.KeyCode, tblKeyLocks.LockID,
tblLocks.Location, tblKeysRequests.RqstDate, tblKeysRequests.QtyRequested,
tblKeysRequests.RcvdDate, tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
sqryRetBldgs.BuildingDescription, sqryRetBldgs.Active
FROM (tblLocks LEFT JOIN sqryRetBldgs ON tblLocks.BuildingID =
sqryRetBldgs.BuildingID) INNER JOIN ((tblKeys INNER JOIN tblKeyLocks ON
tblKeys.KeyID = tblKeyLocks.KeyID) INNER JOIN (tblEmployees INNER JOIN
tblKeysRequests ON tblEmployees.EmpID = tblKeysRequests.EmpID) ON
tblKeys.KeyID = tblKeysRequests.KeyID) ON tblLocks.LockID = tblKeyLocks.LockID
WHERE (((tblEmployees.EmployeeType)<>2) AND
((tblKeysRequests.RqstDate)=Date()) AND ((tblKeysRequests.RcvdDate) Is Null)
AND ((sqryRetBldgs.Active)=True))
ORDER BY tblEmployees.LastName, tblKeys.KeyCode;

Sorting & Grouping:
Keycode Ascending
Group Header Yes
Group Footer No
Group On Each Value
Group Interval 1
Keep Together With First Detail

RqstDate

I realize it’s because there are separate location instances and it’s
working the way it should *but*, I was hoping someone knew of a way I could
work around this. I don’t need to see 3 pages of the same information and
only 1 key requested. Thank you so much.

 

[Jeff Boyce]

Aria

If you are saying that the report, as you have it designed, repeats "key"
info for each location opened, one approach might be to open the report in
design view, find those fields that are "repeating", and set the "Show
Duplicates" property on each to No.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi,
I have a school database that among other things tracks keys. I have a
number of key reports and created a new one called rptToday'sRequests. I
was
asked to change it so that the room # and location (Stadium, W-101,
Cafeteria, etc.) would appear at the top (key code header) of each key
request. Placing the key code in the header, I think, is the problem.

There are different key codes that will let you gain access to one or a
multitude of sites on campus. If the key request is for a code that opens
a
single location, there isn't a problem. If the request is for a master key
which opens multiple locations, I see repeating data. It shows the first
instance of the location for that key code and then repeats the data for
each
additional location without listing the location.

Here is the SQL for the report:
SELECT tblEmployees.EmpID, tblEmployees.EmployeeType, [FirstName] & " " &
[LastName] AS FullName, tblEmployees.FirstName, tblEmployees.LastName,
tblKeysRequests.KeyID, tblKeys.KeyCode, tblKeyLocks.LockID,
tblLocks.Location, tblKeysRequests.RqstDate, tblKeysRequests.QtyRequested,
tblKeysRequests.RcvdDate, tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
sqryRetBldgs.BuildingDescription, sqryRetBldgs.Active
FROM (tblLocks LEFT JOIN sqryRetBldgs ON tblLocks.BuildingID =
sqryRetBldgs.BuildingID) INNER JOIN ((tblKeys INNER JOIN tblKeyLocks ON
tblKeys.KeyID = tblKeyLocks.KeyID) INNER JOIN (tblEmployees INNER JOIN
tblKeysRequests ON tblEmployees.EmpID = tblKeysRequests.EmpID) ON
tblKeys.KeyID = tblKeysRequests.KeyID) ON tblLocks.LockID =
tblKeyLocks.LockID
WHERE (((tblEmployees.EmployeeType)<>2) AND
((tblKeysRequests.RqstDate)=Date()) AND ((tblKeysRequests.RcvdDate) Is
Null)
AND ((sqryRetBldgs.Active)=True))
ORDER BY tblEmployees.LastName, tblKeys.KeyCode;

Sorting & Grouping:
Keycode Ascending
Group Header Yes
Group Footer No
Group On Each Value
Group Interval 1
Keep Together With First Detail

RqstDate

I realize it's because there are separate location instances and it's
working the way it should *but*, I was hoping someone knew of a way I
could
work around this. I don't need to see 3 pages of the same information and
only 1 key requested. Thank you so much.

 

[Aria via AccessMonster.com]

Hi Jeff,
I'm sorry. I didn't know anyone had responded. It was only another post I
read that mentioned only being able to see answers in Google groups, that I
was able to check elsewhere.
To address your reply:
Boy, do I feel dumb! I thought I looked for that. I set the property as you
suggested but I still have a problem. The labels still show duplicates
without other data. I don't see a hide duplicate format there. Do you know
how I can fix it? Thanks for your help.

Aria

If you are saying that the report, as you have it designed, repeats "key"
info for each location opened, one approach might be to open the report in
design view, find those fields that are "repeating", and set the "Show
Duplicates" property on each to No.

Regards

Jeff Boyce
Microsoft Access MVP

Hi,
I have a school database that among other things tracks keys. I have a

[quoted text clipped - 47 lines]

work around this. I don't need to see 3 pages of the same information and
only 1 key requested. Thank you so much.

 

[Jeff Boyce]

Is your report using GroupBy?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi Jeff,
I'm sorry. I didn't know anyone had responded. It was only another post I
read that mentioned only being able to see answers in Google groups, that
I
was able to check elsewhere.
To address your reply:
Boy, do I feel dumb! I thought I looked for that. I set the property as
you
suggested but I still have a problem. The labels still show duplicates
without other data. I don't see a hide duplicate format there. Do you know
how I can fix it? Thanks for your help.

Aria

If you are saying that the report, as you have it designed, repeats "key"
info for each location opened, one approach might be to open the report in
design view, find those fields that are "repeating", and set the "Show
Duplicates" property on each to No.

Regards

Jeff Boyce
Microsoft Access MVP

Hi,
I have a school database that among other things tracks keys. I have a

[quoted text clipped - 47 lines]

work around this. I don't need to see 3 pages of the same information
and
only 1 key requested. Thank you so much.

 

[Dale Fye]

Aria,

Without being able to see the report, it is a little difficult to guess what
you are doing. So let me ask a couple of questions, maybe they will help you
focus on the report format.

What is this report for? Is it basically a hard copy of the key request, so
that the user can sign the form acknowledging receipt of the key, or
something along those lines?

Do you run this report immediately after entering a key request into
tblKeysRequests, so there is probably only one key request that has RcvdDate
= NULL at a time?

Does it list all of the locks that are opened by the key that is being
requested?

I think I would start by grouping on the KeyRequestID (assumes autonumber),
and put all of the information about the person requesting the key, and the
date of the request, the quantity, and stuff like that in the group header.
Then, you can list the LockID and Location information in the details section.

----
HTH
Dale

Hi,
I have a school database that among other things tracks keys. I have a
number of key reports and created a new one called rptToday’sRequests. I was
asked to change it so that the room # and location (Stadium, W-101,
Cafeteria, etc.) would appear at the top (key code header) of each key
request. Placing the key code in the header, I think, is the problem.

There are different key codes that will let you gain access to one or a
multitude of sites on campus. If the key request is for a code that opens a
single location, there isn’t a problem. If the request is for a master key
which opens multiple locations, I see repeating data. It shows the first
instance of the location for that key code and then repeats the data for each
additional location without listing the location.

Here is the SQL for the report:
SELECT tblEmployees.EmpID, tblEmployees.EmployeeType, [FirstName] & " " &
[LastName] AS FullName, tblEmployees.FirstName, tblEmployees.LastName,
tblKeysRequests.KeyID, tblKeys.KeyCode, tblKeyLocks.LockID,
tblLocks.Location, tblKeysRequests.RqstDate, tblKeysRequests.QtyRequested,
tblKeysRequests.RcvdDate, tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
sqryRetBldgs.BuildingDescription, sqryRetBldgs.Active
FROM (tblLocks LEFT JOIN sqryRetBldgs ON tblLocks.BuildingID =
sqryRetBldgs.BuildingID) INNER JOIN ((tblKeys INNER JOIN tblKeyLocks ON
tblKeys.KeyID = tblKeyLocks.KeyID) INNER JOIN (tblEmployees INNER JOIN
tblKeysRequests ON tblEmployees.EmpID = tblKeysRequests.EmpID) ON
tblKeys.KeyID = tblKeysRequests.KeyID) ON tblLocks.LockID = tblKeyLocks.LockID
WHERE (((tblEmployees.EmployeeType)<>2) AND
((tblKeysRequests.RqstDate)=Date()) AND ((tblKeysRequests.RcvdDate) Is Null)
AND ((sqryRetBldgs.Active)=True))
ORDER BY tblEmployees.LastName, tblKeys.KeyCode;

Sorting & Grouping:
Keycode Ascending
Group Header Yes
Group Footer No
Group On Each Value
Group Interval 1
Keep Together With First Detail

RqstDate

I realize it’s because there are separate location instances and it’s
working the way it should *but*, I was hoping someone knew of a way I could
work around this. I don’t need to see 3 pages of the same information and
only 1 key requested. Thank you so much.

 

[Aria via AccessMonster.com]

Hmmm…strange that I can only see some answers through the site and not others.
Thank you Jeff and Dale for responding.

Jeff
No, my report is not using “Group Byâ€.

Dale
My posts tend to run long so I was just trying to stick to what I thought was
relevant information. I understand how it makes it hard to comprehend what
I’m doing so I’ll give a little background information. This is the 3rd
report I have that deals strictly with key requests.

As I started imputing information, certain shortcomings of the original
report became evident. The other reports, rptKeyRequests and
rptPendingRequests, have a date header. There isn’t a problem with repeating
data. This is the only report for key requests that has a key code header.
That’s why I thought that was the problem.

Report Set Up:
Report Header
Page Header
Key Code Header (Key Code, Location, Total Requested)

Detail Section

Top Row – Labels (Request Date, Requested By, Date Rcvd., Qty. Rcvd, Reason
for request)

Next Row – txtbox controls (RqstDate, Fullname, QtyRecd., Reason)

To answer your other questions:

What is this report for? Is it basically a hard copy of the key request, so
that the user can sign the form acknowledging receipt of the key, or
something along those lines?

Yes, it’s basically a hard copy of the requests. The orginal report listed
all key requests, both filled and unfilled; it was becoming more difficult to
spot the ones that were unfilled, hence the rptPendingRequests. As more and
more requests were pending, it became more difficult to spot the requests for
today only which need to be input into another system so that the request
could be logged and eventually filled by the district. Because the report is
rptToday’sKeyRequests, the key code is more important than the date.

Do you run this report immediately after entering a key request into
tblKeysRequests, so there is probably only one key request that has RcvdDate
= NULL at a time?
No, the report is run whenever I decide to run it. I don’t want to run it too
early as there may be other requests coming in for the day. All the requests
listed will have RecvdDate = Null.

Does it list all of the locks that are opened by the key that is being
requested?

Yes, and this is the problem. No problem with a regular key (one that opens
only one location). The problem is with a master key (one that can open 100
or more locations).

Since you both mention “group by or group on†I’ll try to do that and post
back with the results.
Thank you both for your time.

Aria,

Without being able to see the report, it is a little difficult to guess what
you are doing. So let me ask a couple of questions, maybe they will help you
focus on the report format.

What is this report for? Is it basically a hard copy of the key request, so
that the user can sign the form acknowledging receipt of the key, or
something along those lines?

Do you run this report immediately after entering a key request into
tblKeysRequests, so there is probably only one key request that has RcvdDate
= NULL at a time?

Does it list all of the locks that are opened by the key that is being
requested?

I think I would start by grouping on the KeyRequestID (assumes autonumber),
and put all of the information about the person requesting the key, and the
date of the request, the quantity, and stuff like that in the group header.
Then, you can list the LockID and Location information in the details section.

----
HTH
Dale

Hi,
I have a school database that among other things tracks keys. I have a

[quoted text clipped - 41 lines]

work around this. I don’t need to see 3 pages of the same information and
only 1 key requested. Thank you so much.

 

[Aria via AccessMonster.com]

I should have asked for clarification. I clicked the totals button, saw the
group by option for each field in the query and saved it. There was no change
in the report appearance. I had previously did "hide duplicates" for each of
the affected controls but the labels still show duplicate data.

Hmmm…strange that I can only see some answers through the site and not others.
Thank you Jeff and Dale for responding.

Jeff
No, my report is not using “Group Byâ€.

Dale
My posts tend to run long so I was just trying to stick to what I thought was
relevant information. I understand how it makes it hard to comprehend what
I’m doing so I’ll give a little background information. This is the 3rd
report I have that deals strictly with key requests.

As I started imputing information, certain shortcomings of the original
report became evident. The other reports, rptKeyRequests and
rptPendingRequests, have a date header. There isn’t a problem with repeating
data. This is the only report for key requests that has a key code header.
That’s why I thought that was the problem.

Report Set Up:
Report Header
Page Header
Key Code Header (Key Code, Location, Total Requested)

Detail Section

Top Row – Labels (Request Date, Requested By, Date Rcvd., Qty. Rcvd, Reason
for request)

Next Row – txtbox controls (RqstDate, Fullname, QtyRecd., Reason)

To answer your other questions:

What is this report for? Is it basically a hard copy of the key request, so
that the user can sign the form acknowledging receipt of the key, or
something along those lines?

Yes, it’s basically a hard copy of the requests. The orginal report listed
all key requests, both filled and unfilled; it was becoming more difficult to
spot the ones that were unfilled, hence the rptPendingRequests. As more and
more requests were pending, it became more difficult to spot the requests for
today only which need to be input into another system so that the request
could be logged and eventually filled by the district. Because the report is
rptToday’sKeyRequests, the key code is more important than the date.

Do you run this report immediately after entering a key request into
tblKeysRequests, so there is probably only one key request that has RcvdDate
= NULL at a time?
No, the report is run whenever I decide to run it. I don’t want to run it too
early as there may be other requests coming in for the day. All the requests
listed will have RecvdDate = Null.

Does it list all of the locks that are opened by the key that is being
requested?

Yes, and this is the problem. No problem with a regular key (one that opens
only one location). The problem is with a master key (one that can open 100
or more locations).

Since you both mention “group by or group on†I’ll try to do that and post
back with the results.
Thank you both for your time.

Aria,

[quoted text clipped - 27 lines]

 

[Larry Linson]

I'm reasonably certain that "hide duplicates" doesn't apply to labels, but I
am having a little difficulty understanding what you are saying about
labels. One thing: try moving the labels up into the group header (Key Code
Header) as its last line, rather than at the top line of the detail. There's
no need for the labels to be repeated for each detail line.

And, I am having a little difficulty understanding what it is that repeats
so many times -- can you clarify that? Remote debugging is difficult, at
best.

Larry Linson
Microsoft Office Access MVP

 

[Aria via AccessMonster.com]

I’m not sure where to post any more as I’m unsure we’re all seeing the same
things. I’ll try here and hope it all works out.

Larry, Jeff and Dale:
Thank you for responding. You have each given me something to think about.
I’m disappointed that I still tend to be narrowly focused; everything fades
to black as I concentrate on my ultimate goal. Maybe the ability you each
possess comes with experience…Anyway, I appreciate the requests for
clarification and the additional questions because it has me re-checking and
re-thinking the report set-up.

And, I am having a little difficulty understanding what it is that repeats so many times – >can you clarify that?

Absolutely…well, I’ll certainly *try* to be a little clearer.

There are 4 types of master keys -- storage, gate, wing and grand. I
anticipate the same problem with each of these as they open many locations on
campus. I have to pass this report along so that the requested keys can be
ordered through the district system.

What I see:
Report Header
Page Header

Key code header (below)

Key Code: TS-0 Location: M-109 Total Rqstd: 1

Request Date Requested by Date Rcvd. Qty. Rcvd. Reason for request
10/19/2009 Jane Doe Key lost by sub.

Request Date Date Rcvd. Qty. Rcvd.
Request Date Date Rcvd. Qty. Rcvd.

Request Date Date Rcvd. Qty. Rcvd.

Request Date Date Rcvd. Qty. Rcvd.

Etc
Etc.
Etc.

You get the idea. I don’t know why it doesn’t repeat “Requested by†or
“Reason for requestâ€. I had previously clicked the totals button so that
“group by†was under each field in the totals row. I saved the query but
didn’t notice any change in how the data was displayed in the report. I’m
still kind of new at all of this so I’m not sure if I was supposed to take
additional steps.

What I would like to see (Some of this may not display correctly due to line
wrapping.):

Key Code: TS-0 Location: M-109 Total Rqstd: 1

Request Date Requested by Date Rcvd. Qty. Rcvd. Reason for request
10/19/2009 Jane Doe Key lost by sub.


Key Code: TZ-48 Location: JK-04 Total Rqstd: 1

Request Date Requested by Date Rcvd. Qty. Rcvd. Reason for request
10/19/2009 John Smith Key broke in door.

Larry, I did as you suggested and moved the labels to the key code header.
Because of the questions asked, I tested another key that was sure to give me
problems, the grand master. The data repeats even after following the
suggestions. Another issue was the *huge* amount of empty space (almost a
page) for the wing master code that I couldn’t resolve. I remembered that
hide duplicates was set to “yes†which is also the reason I no longer had
repeating data for “Requested by†and “Reason for requestâ€. I changed this
property back to “no†to see what was happening. While the labels no longer
repeat, the data in the textbox controls do.

When I look at the query in datasheet view, I see a query that is pulling
data as expected. TS-0 is the key that unlocks M-109 and is the key that
unlocks all locations to the pool area and is the key that unlocks all
locations in wings C, D, E, etc. I remembered that there is a “Can Grow/Can
Shrink property which I set to “yes†but the spacing has not changed. Any
ideas?