Solved: Reports - Displaying Parent Category Info

[ibgreat]

Hello All,

I am just starting to play with Access's reports, so this please help with noobie question.

I have the following table and fields:

tblClientBx_D
BxIncID
PersonID_Client
BxCatID
BxCatSubID
BxDate

The table data might look like this:
BxIncID...PersonID_Client...BxCatID...BxCatSubID...BxDate
1............1......................1...........1.................1/1/09
2............2......................2..............................1/3/09
3............1......................1...........2.................1/4/09


The report is categorized by:
1. PersonID_Cient
2. BxCatID
3. BxCatSubID

Within the detail section on the form is the BxDate. The problem is that not all records include a BxCatSubID. The report should include this data as well. I initially thought I would be able to add an additional detail section after the BxCatID section on the form and sort via a query where BxCatSubID value was null. I don't see the ability to add the detail. Assuming this is the case, I believe I would need to use a subreport.

I've looked through a few tutorials, a simple Access book, and a couple BBs and am having difficulty finding anything any info. Any help is appreciated!!

[OBP]

Do you mean that you want to display the BxCatSubID or that you want those values separated out in some way?

[ibgreat]

Do you mean that you want to display the BxCatSubID or that you want those values separated out in some way?

The report groups the data by the PersonID, then By BxCatID, then by BxCatSubID. Therefore, the report already displays and groups the data from the BxCatSubID field.

If you take a look at the example table data you will note that not all records include data in the BxCatSubID field. If there is no data in BxCatSubID field I still need the BxCatID data to show on the report. In the simplified example above it would be the BxDate. On the form side this would look like a cascading combo box with no value placed into the second box.

I am attaching a screenshot from the design view so you can see the grouping order.

Regards

[OBP]

So in your Report, which looks OK, if you have no BxCatSubID field data don't you still get the Details shown for that overall record?
Can you post a zipped copy of the database with those dummy records in the table?

[ibgreat]

So in your Report, which looks OK, if you have no BxCatSubID field data don't you still get the Details shown for that overall record?
Can you post a zipped copy of the database with those dummy records in the table?

You got it if there is no BxCatSubID, that record is not shown in the report.

I assume this is because the lowest level grouping is based on the BxCatSubID so the report doesn't know where to put info that doesn't include that field.

Please note that I was incorrect in stating that BxCatSubID does not have a value. It actually has is 0. I did try to change the 0 to null, but without any change in the results.

If necessary I can try to post the DB, but would have to write over confidential data and even zipped, I think it would be too big as there is the front and back-end. It will probably be easier to replicate the problem in a new file.

Regards.

[OBP]

Just take a copy of the Database with a new name Delete all of the records except a couple and then overwrite just those with things like 1 2 3 etc
Compact & Repair it and then Zip it.
You may find Compacting and repairing ir will make the origianl much smaller as well.

ps I am in the UK and can't work on here in the evenings (7.20pm here) so I will have to try and pick up with you again tomorrow, unless Cregan Tur (Randy) can pick this up now.

[CreganTur]

I'm more of an applications Dev with VBA, so I only dabble in reports, but I can definately take a look if you will upload a database that replicates the problem.

[ibgreat]

Randy,

Thanks for taking a look. I am just starting to play with this end of things. Thus far (I have also played with graphs/charts a bit), I am finding the formatting options more limiting than I expected.

Anyhow, when I stripped the DB of everything that wasn't relevant it was small enough. As you will note, records #5 and #69 don't show in the report. I don't even get the heading for their BxCatID??

[OBP]

ibgreat, you appear to be using a select SQL statement for the Report's Record Source rather than a dedicated Query.
I prefer to use a Query to ensure that all the data is available from the tables before manipulating it in the Report.
So in the Report's SQL query I have removed the Join between the tblBxCategory_L table and the tblBxCategory_subL table.
I then set the Joins from the tblClientBx_D table to those tables as "Include all records from tblClientBx_D and only those matching from the other 2 tables.
I think that gives what you want.

[ibgreat]

Because, I thought this was an issue with the design of the report, I hadn't thought to double check this in the datasheet view. I do this all the time with forms. It's funny how restricted our thinking can be sometimes.

Thanks for the advise, and yes that was exactly what I was looking for.

Warm Regards and Many Thanks!