Procedural subreport question

[cpberg1]

Hey all,

Still new to access and working out a report issue. I believe I need to use subreports to display the data I want and am trying to ensure I have a clear(ish) understanding before getting really deep into it.

I have my employee table with personal info which included employee ID primary key.

I have other tables for various authorizations and expirations related to each employee with foreign key employee id.

I want to display one report showing each employee and all their various authorizations and expirations. I use a report based on table employees with subreports for each authorization type correct?

I've built a query(s) based on expirations table to calculate the last expiration date for each employee. Is it necessary to include employee ID field on each query so that I may eventually relate the expiration back to the proper parent record on the final report?

Thanks all, I read about sub-forms on the faq's or general (forget which) and I think a lot of this same logic should apply.

Chris

 

[vbaInet]

I have other tables for various authorizations and expirations related to each employee with foreign key employee id.

I want to display one report showing each employee and all their various authorizations and expirations. I use a report based on table employees with subreports for each authorization type correct?

It depends on the layout you wish to achieve but in some cases it's one table per subreport if it's a one-to-many mapping. So think about your layout and see if it can be combined in one query or not. If it can't then you need subreports.

I've built a query(s) based on expirations table to calculate the last expiration date for each employee. Is it necessary to include employee ID field on each query so that I may eventually relate the expiration back to the proper parent record on the final report?

Thanks all, I read about sub-forms on the faq's or general (forget which) and I think a lot of this same logic should apply.

Correct (if you're going to use subreports) and correct.

 

[cpberg1]

Very cool. I spent a bunch of time to set it up and they worked great!

Followup question not totally related to subreport.

Is it possible to show report output of 1 field laterally instead of vertically? I'm using a continous form and the format makes me question if it's possible.

For example I have my employee ID #5 who has 3 authorizations listed on a subreport.
I get (ignore the "...........")

John smith Auth#1
.................... Auth#2
.....................Auth#3

Next employee auth #1
......................auth#2

Is it possible to create?
John smith auth#1, auth#2, auth#3
next employee auth#1, auth#2

All authorizations in this example comes from one field in a table or query.

 

[vbaInet]

If you want to do that sort of thing then you could look into a Crosstab query then you can base your subreport on this query. But bear three things in mind:

1. The fields of the crosstab query will (in most cases) not be availble to you in the subreport unless you've already pre-defined what fields the Column Headers should display in the query. You pre-define the column headers using the PIVOT statement in a Crosstab query.

2. What will happen if there are 50 authorisations (for example)? Will it fit? Perhaps not.

3. If you didn't pre-define the column headers in the query and all you want to do is display the query in a subreport as-is, then you will not be able to perform Totals on those fields.

But then again, the whole idea is for the column headers to be dynamically created so pre-defining the column headers will defeat this purpose. There are ways of programatically overcoming this but it is involving and requires good coding skills.