Having a problem with a subreport

M

martinmike2

Hello,

I am having an issue with a subreport that only works part of the
time.

I have a report that shows all personnel assigned a BSC and a sub
report that shows all personnel not assigned a BSC.

BSC's are broken down into UIC's. The main report's query asks for
the UIC as criteria and filters the data to display only that UIC's
BSC's. The subreport is linked to the main report via the UIC. now
when I enter a UIC of 46965, the subreport works, but when I enter in
any other UIC (there are three different UICs all together) the
subreport dosn't populate.

When I run the query for the subreport, it shows all three UIC's but
the subreport wont populate when included into the main report. Out
side the main report the subreport works fine,

Any help would be greatly appreciated.

Sincerely,
Michael Martin
 
T

Tom Wickerath

Hi Michael,

So you apparently have a table with UIC as the primary key, and another
table with UIC as a related many side (foreign key) field. Have you
established relationships with enforced referential integrity (RI)? The
reason I ask is that you *may* have a situation where the UIC field in one
table contains one or more trailing spaces, or perhaps a carriage return,
such that the field *appears* to include the same UIC when visually
inspected, but in fact it is not the same. If this is true, then you will not
have success establishing a relationship with enforced RI. I once helped a
friend with an identical situation, and strangely enough, his database
included a UIC field as well. However, his UIC values were text-based and
always 6 characters.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
M

martinmike2

Here's what I have. I have one table, called tblAMD. This table
contains all of the information for every BSC (each record contains
BSC, UIC, and several other fields that contain data relevant to that
specific BSC. By the way, BSC = Billet Sequence Code, and UIC = Unit
Identification Code. Now, there is a field called SSN, where we
assign a member to their BSC.

There is also a Field called WCCODE that assigns each BSC to a Work
Center. I have a query that provides data to a report that shows all
of the BSC's broken down by Work Center and the personnel assigned to
them. Invariably we have extra people and I need them to show in the
report footer.

Let me clarify. The query that provides the data for the main report
is:
"SELECT qryAMDsubqry.*, EDVR.*, qryAMDsubqry.auic
FROM qryAMDsubqry LEFT JOIN EDVR ON qryAMDsubqry.PERS.SSN = EDVR.SSN
WHERE (((qryAMDsubqry.auic) Like [Which UIC?]));"

qryAMDsubqry is:
"SELECT PERS.*, tblDIV.WC, tblDIV.TITLE, tblAMD.*
FROM (tblAMD LEFT JOIN PERS ON tblAMD.SSN = PERS.SSN) INNER JOIN
tblDIV ON tblAMD.WCCODE= tblDIV.WCCODE
WHERE (((tblAMD.WCCODE) Is Not Null));"

The query for the subreport is:
"SELECT PERS.WC, tblAMD.SSN, (EDVR.A_RATE_ABR & " " & PERS.[NAME LAST]
& ", " & PERS.[NAME FIRST]) AS exp, EDVR.PNEC, EDVR.SNEC, EDVR.UIC
FROM EDVR INNER JOIN (PERS LEFT JOIN tblAMD ON PERS.SSN = tblAMD.SSN)
ON EDVR.SSN = PERS.SSN
WHERE (((tblAMD.SSN) Is Null));"

I placed the subreport in the report footer, and linked the UIC
fields. When I went to run the report, the subreport only populated
for UIC 46965, and not for either 44319 or 3824A. Now, the other two
UIC's are by no means completly assigned, so I should have loads of
extra bodies. This is the case when I run the subreport on its own,
but included into the main report, it only works on 46965, as stated
above.

Hope I didn't rant on too long.
 
T

Tom Wickerath

Hi Michael,
Hope I didn't rant on too long.
Click to expand...

No, that's fine.....however, you did not answer my question. Have you
established relationships with enforced referential integrity (RI)?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

martinmike2 said:
Here's what I have. I have one table, called tblAMD. This table
contains all of the information for every BSC (each record contains
BSC, UIC, and several other fields that contain data relevant to that
specific BSC. By the way, BSC = Billet Sequence Code, and UIC = Unit
Identification Code. Now, there is a field called SSN, where we
assign a member to their BSC.

There is also a Field called WCCODE that assigns each BSC to a Work
Center. I have a query that provides data to a report that shows all
of the BSC's broken down by Work Center and the personnel assigned to
them. Invariably we have extra people and I need them to show in the
report footer.

Let me clarify. The query that provides the data for the main report
is:
"SELECT qryAMDsubqry.*, EDVR.*, qryAMDsubqry.auic
FROM qryAMDsubqry LEFT JOIN EDVR ON qryAMDsubqry.PERS.SSN = EDVR.SSN
WHERE (((qryAMDsubqry.auic) Like [Which UIC?]));"

qryAMDsubqry is:
"SELECT PERS.*, tblDIV.WC, tblDIV.TITLE, tblAMD.*
FROM (tblAMD LEFT JOIN PERS ON tblAMD.SSN = PERS.SSN) INNER JOIN
tblDIV ON tblAMD.WCCODE= tblDIV.WCCODE
WHERE (((tblAMD.WCCODE) Is Not Null));"

The query for the subreport is:
"SELECT PERS.WC, tblAMD.SSN, (EDVR.A_RATE_ABR & " " & PERS.[NAME LAST]
& ", " & PERS.[NAME FIRST]) AS exp, EDVR.PNEC, EDVR.SNEC, EDVR.UIC
FROM EDVR INNER JOIN (PERS LEFT JOIN tblAMD ON PERS.SSN = tblAMD.SSN)
ON EDVR.SSN = PERS.SSN
WHERE (((tblAMD.SSN) Is Null));"

I placed the subreport in the report footer, and linked the UIC
fields. When I went to run the report, the subreport only populated
for UIC 46965, and not for either 44319 or 3824A. Now, the other two
UIC's are by no means completly assigned, so I should have loads of
extra bodies. This is the case when I run the subreport on its own,
but included into the main report, it only works on 46965, as stated
above.

Hope I didn't rant on too long.
Click to expand...
 
T

Tom Wickerath

PS. Here is a Microsoft KB (Knowledge Base) article that you might find
helpful:

Defining relationships between tables
http://support.microsoft.com/?id=304467

In your case, the key fields appear to be the SSN (Social Security?) fields:
PERS.SSN = EDVR.SSN

Do you have a relationship with enforced referential integrity (RI) that
involves the SSN field in PERS and the SSN field in EDVR? How about a
relationship with enforced RI between the SSN field in tblAMD and the SSN
field in PERS? Are you able to create a relationship with enforced RI between
tblAMD.WCCODE and tblDIV.WCCODE?

It looks like the UIC field is found in the EDVR table. Is this field
present in any other tables (even if it's not the same name)?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
 
M

martinmike2

Tom,

I was unable to enforce RI between the SSN fields of any of the tables
because EDVR and tblAMD have extra records waiting to be assigned to a
SSN. I was able to enforce RI between the WCCODE fields of tblAMD and
tblDIV though.
 
T

Tom Wickerath

Hi Mike,

Until you are able to enforce RI between all key fields used in the report's
recordsources, you really have no way of knowing if the error might be caused
due to mismatched SSN values. The mismatching can occur with non-printing
characters, so visual inspection may not be enough to show that the values
are indeed equal in related tables. For example, if the SSN value in one
table is 123456789, and the SSN number in a related table is 123456789space,
the values will appear as if they are the same, but in fact they are not.
Thus, any join that attempts to join such values would not return any
records. A trailing space is only one example. You could even have something
like a trailing carriage return line feed, with or without addtional
characters that you might not see unless the row height was adjusted to a
higher value.

For the present time, can you add the missing SSN numbers to one of the
tables, such that you are able to enforce RI? Another option might be to make
a test copy of your database, and then delete the non-matching SSN numbers
from the affected table. Then attempt to enforce RI. Finally, test your
report with sub-report. It should be showing all related UIC's at that point.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

Any luck on solving your subreport problem yet?

If you are unable to solve it, and you are willing to send me a copy of your
database, I'll try to help you more. If any of the data is considered
sensitive, then just make a copy of your database, and remove/replace any
sensitive data with fictional data. If you are interested, send me a private
e-mail message with a valid reply-to address, and attach a copy of your
compacted & zipped file. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
M

martinmike2

Tom,
Sorry about the lack of reply. I was re-tasked on a different area of
the database for a while.

I would have no problem sending you a copy with dummy data in it, i
just need to figure out why my front-end is 60MB. I think its in my
code right now. Need to scrub it clean before I can send it.
 
M

martinmike2

Tom, I appreciate your offer, I can send you a copy of the files with
dummy data if you like.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

report with 'standard' subreport 4
Help! i cant get Nz function to work in report! 1
report using a subreport based on a crosstab query 5
Multi based criteria 1
Subreport On No Data 1
Blank records in SubReport to Total in Main Report 2
SubReport Sorting Problem 4
Guidance required for report design 4

Top