Report / Sub-Report Problerm

[iTISTIC]

Posted a previous message about setting a sub-reports record source
property using VBA, but I think I'll be a little clearer as there may
be a better solution I am just not aware of.

Here my situation. I have 2 reports. Lets call them Parent and
Sub-Report to keep things easy. The parent report shows customer detail
information (phone numbers, mailing addresses, etc.). In this parent
report I have the sub-report. I want the sub-report to list all of the
piece of equipment available in the system with checkboxes, and have
the checkbox checked for each piece of equipment the customer owns.
Table structur is as follows:

tblCustomers
==
account_number - PK
first_name
last_name
.... etc

tblCustomerEquipment
==
rec_id - PK
account_number - FK
product_generic_id

tblProductGenerics
==
product_generic_id - PK
product_name
.... etc

Due to what appears to be a bug in Access, I can't construct a query
that will list ALL records in tblProductGenerics and the associated
values for tblCustomerEquipment where there is a related record in
tblCustomerEquipment. Access treats the queries I create as INNER JOINS
even though they are LEFT JOINS. Here is a description of the problem I
have:

http://support.microsoft.com/kb/275058/en-us

Although it says the Jet Service Pack will fix the issue, when I
download the proper service pack for Windows XP and execute it I am
told the service pack is older than the version I currently have and
that the update is exiting and not necessary.

Any idea how I can accomplish this?

 

[Allen Browne]

The issue isn't a bug: it's that the subreport is linked on the customer's
account number, which shows only those records in the subreport that match
the customer account in the mainform, so equipment that does not match the
customer does not show.

It might be easier to do this with just one report instead of a report and
subreport.

Try this:

1. Create a query using just tblCustomers and tblProductGenerics. If you see
any line joining the 2 tables in the upper pane of query design, delete the
line. The lack of any join (a Cartesian product) gives you every possible
combination (i.e. every product for every customer.)

2. Drag the fields you need for your report into the output grid.

3. In a fresh column in the Field row, type a subquery to indicate whether
this combination of customer and product exists in the tblCustomerEquipment
table:

CustHasEquip: EXISTS (SELECT rec_id
FROM tblCustomerEquipment
WHERE (tblCustomerEquipment.account_number
= tblCustomers.account_number)
AND (tblCustomerEquipment.product_generic_id
= tblProductGenics.product_generid_id))

4. Save the query.

You now use this query as the source for your report. Create a Group Header
for the customer info. In the Detail section, you can list every piece of
equipment, with a check box bound to the CustHasEquip field.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[iTISTIC]

This is a good suggestion. I haven't tried it, but the section this
data needs to reside in on the report is not a section that can take up
the entire width of the report. This data goes in a small section on
the left side of the report and has other data to the right of it.
Because of this the solution you have provided will not fit well with
my client's needs.

Any other suggestions how this could work with a sub-report? I
appreciate your time and assistance.

 

[Allen Browne]

The suggested query lists every combination of customer and equipment.

If necessary, you could use that query as the source for a subreport, and
using a different table/query for the main report.

 

[iTISTIC]

Allen,

Thank you very much for your assistance. Your solution has improved
solved my problem and I am now able to deliver this database to my
client with the functionality they have requested. I greatly appreciate
your time and willingness to share your knowledge.

Most of my experience during the past few years has been solely with
SQL Server, with which this problem would've been tackled a little
differently.

Thanks again!

Shawn