Report from multiple tables (1 Viewer)

andreab13

New member
Local time
Today, 02:13
Joined
Mar 21, 2019
Messages
3
Hi everyone,

I am not a complete Access newbie, but for my company it is stretching and growing my skills.

Here is what I need to do:

Create a report for one client with info from multiple tables.

I understand creating a query would be my first step, but I have many fields, and it's not coming out how I would like.

I have separate tables for:
Clients
Training 1
Training 2
Training 3

In my main page for the Client, I have tabs, and one of them includes a sub form for Training 1 - 3. I would ideally like to print that tab but that doesn't turn out pretty.

I had the 3 trainings combined in the Clients table previously, but it did not work out well, so I had to split them up. Each Training table contains dates and information relating to that specific training.

What I need is a report that gives me info from all 4 tables...but because of the different dates...it's over 20 fields. I need a handful from Client, then everything from Training 1 - 3.

Anything I try gives me duplicates or seems overly complicated:banghead:

Any suggestions?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:13
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to the forum. Have you considered using subreports?
 

Mark_

Longboard on the internet
Local time
Today, 02:13
Joined
Sep 12, 2017
Messages
2,111
If you have three training tables, what is so different between them that they couldn't be one with an extra field that defines each type of training?

Also, what are you table definitions / relationships for each table?

theDBguy has given one solution, two others would be
1) Recreate your "Training" table to hold all types of training.
OR
2) Create matching queries for each of your training tables for what you need to report then put them together in a UNION query for reporting.
 

andreab13

New member
Local time
Today, 02:13
Joined
Mar 21, 2019
Messages
3
If you have three training tables, what is so different between them that they couldn't be one with an extra field that defines each type of training?

I said training to make it easier, but one is a training, one is a safety committee, and the other is an inspection. So they are very different.

Also, what are you table definitions / relationships for each table?
The only relationship they have (because nothing else would make sense) is the Client

theDBguy has given one solution, two others would be
1) Recreate your "Training" table to hold all types of training.
OR
2) Create matching queries for each of your training tables for what you need to report then put them together in a UNION query for reporting.

As I described, #1 won't work because they are very different, and #2 is something I have not heard of so I will look that up!
 

Mark_

Longboard on the internet
Local time
Today, 02:13
Joined
Sep 12, 2017
Messages
2,111
andreab13,

Ah, then you'd probably want theDBGuy's solution. One subreport for each of the different tables.

We do see a lot of posts where someone is actually using the same table structure for different "Trainings" (or what have you), so knowing they are different tables for differing purposes does mean my original suggestions probably won't work.

P.S. welcome to the forum! Hope you can get a satisfactory solution quickly!
 

Bullschmidt

Freelance DB Developer
Local time
Today, 04:13
Joined
May 9, 2019
Messages
40
And to be sure to show data for just the one client on the report page:
In each subreport control...

Link Child Fields: ClientID
Link Master Fields: ClientID
 

Users who are viewing this thread

Top Bottom