normalized tables, ok... normalized reports? 1

[misscrf]

I am having a hard time figuring out how to report my data from a normalized table structure. I tried having a

main report for the main table, and then using sub-reports for the sub data. This had problems, because one of

the subreports had to have a subreport, which is fine, exept that it put the sub-sub report on a new page, and

made a huge space, even if there wasn't anything there.

I tried a query, but any I came up with were way to complex and couldn't even be executed/previewed.

Here are a list of the tables below. I need a report for a candidate, which the user will choose from a form ( I

understand how to set that up with the query builder for the control source). I also need all of that person's

address, phone numbers, and legal info if they are a type of candidate that has legal info. I also need all of the

person's applications with all of the activity history for each application.

Whew! I can put it all in the entry form. Just can't figure out how to print it in a nice uniform view to give to a

boss.

Can anyone help?

Tables:

tblActivity: Activities that happen during an application process
tblActivityType: Type of activity
tblAddress: Address of the candidates
tblAddressType: Type of address
tblApplications: Applications that a candidate submits
tblAppSource: Source of the application- agency, write-in etc
tblCandidate: Main candidate table
tblCandidateType: Type of candidate
tblGroup: Practice group that a job would pertain to
tblJobs: Job listings
tblLegalInfo: Information that is needed if the candidate is an attorney type
tblOffer: Result of an offer extended activity – accept, pending, reject
tblOffice: Office that a job would pertain to
tblPhone: Phone numbers of the candidates
tblPhoneType: Type of phone number
tblSalutation: Salutation of the candidate- mr. mrs.
tblYNP: Admitted to the bar- yes, no, pending


If you would like to see what fields are in the tables, I have my database fully documented at:

http://www.geocities.com/misscrf/CandidateHiring.htm

Thank you.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Can't your report and subreports mirror exactly your form and subforms?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

Thank you for responding, dhookom.

the problem is how it breaks on the page. On my forms, for example, I have a main candidate form. One of it's subforms is applications. That has a subform for activities. Works great on forms, not so much on reports. It leaves a big gap for the subform's subform. That seems to be the big issue right now.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Can you combine the Applications and Activities into a single subreport?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

hmmm, that might do the trick. how would I display the column headers, without them appearing per record?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Maybe put your column headers in the report or a group header section.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

Thanks, i will try that out monday.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I have spent the morning working on this, and I am at a road block.

I have one issue that makes this a bit difficult. I have the application and activities all on the same query. That is fine. Problem is that I have a lot of foreign keys. In the application table, I have a foreign key to the job table. That has a foreign key to the office table, which specifies which office the job listing is for.

Thing is, that I have an activity type in the activities table for materials forwarded. This brings along a field for office, a foreign key to the same office table, but to tell which office the materials were forwarded to as a part of this activity.

I can explain my struggle to make dlookups work, but I thought I would put this out there, and see if someone understands the method I should use to display this madness.

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You can link/join to multiple copies of the the office table. Just add the office table to the query multiple times and link each copy to the appropriate field from the application or activities table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

and then do I put the office field in twice, once from each table linked to each appropriate foreign table?

I will try that out...

Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

As I bring in the foreign key tables, I don't get my records. I do not understand what I am doing wrong. Is there something that I may be doing wrong? Is there a certain way to execute normalized tables with many foreign keys into a solid report?

Even before I add the office tables, I am still not getting all my records. If I start with just the applications table, I can see all of its information. Problem is that the foreign keys display column 2 in the query, report column 1 on the report. So I bring in the foreign table. Simply by bringing in any of the foreign tables, I lose some of the records that are otherwise in the application table.

If I add the foreign tables and make the relationships so that all of applications and where the foreign = It says the joins are bad and can't execute the sql statement.

:-(

Any help would be great... :-D



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I actually found that I did not have all of the joins defined in the relationships. I need to have ALL of them defined. Then I split up the query. I have 1 query for applications, and one for activities. Then I put those together into 1. Now my report is great!

misscrf

It is never too late to become what you could have been ~ George Eliot

misscrf

It is never too late to become what you could have been ~ George Eliot