Repeating Data

  • Thread starter Thread starter Aria
  • Start date Start date
A

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

Aria said:
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.
Click to expand...
 
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.

Aria via AccessMonster.com said:
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.

Jeff said:
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
Click to expand...
[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.
Click to expand...
Click to expand...
Click to expand...
 
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



Aria said:
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.
Click to expand...
 
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
 
Back
Top