Create Report from one column in different tables

[Snowman88]

Hi guys!

Here is the scenario:

I have several tables which all have a common column (client_name) and I want to do a report, which gives me all entries from all the tables which have a specific client name. I have, for example, a report with client details, a report with client activities, but those reports list ALL clients details and ALL clients activities.

Can this be done?

Any help is very much appreciated!

 

[boblarson]

Create a main report made up of subreports that pull the associated data you want and then you can open the report using a standard where clause that limits it to the client you want.

 

[ColinEssex]

1) you should only store the names in one main table with an Autonumber ID for the record. Then after the relationships have been done to the other tables, they'll store the ID number and link back. The only thing that gets duplicated into different tables is the ID number. That way, a name change will be no problem as it affects nothing

2) Using the whole name is bound to lead to problems as they'll all need changing if a name is wrong.

Col

 

[boblarson]

1) you should only store the names in one main table with an Autonumber ID for the record. Then after the relationships have been done to the other tables, they'll store the ID number and link back. The only thing that gets duplicated into different tables is the ID number. That way, a name change will be no problem as it affects nothing

2) Using the whole name is bound to lead to problems as they'll all need changing if a name is wrong.

Col

Ditto -

In my post I was assuming that you had followed Colin's way of doing things. I overlooked that you had stored client_name, which is not good.

 

[Snowman88]

Well, I understand this ID and name thing, but i have never worked with Access before so I am doing a lot of things wrong or bad, that's for sure But i try to fix this one.

But I have never done a subreport...how do i do this? Sorry for being so noobish

 

[boblarson]

You create a normal report that shows what you want, how you want it, when it is going to be displayed. Then, click the sub form/report button (on the controls tool bar)

and follow the wizard, selecting an existing report and taking each one (you do this one at a time) that you want on the main report.

 

[Snowman88]

Argh! I don't have the wizard installed here and can't install it....that's bad

 

[boblarson]

So, you never get any wizards when adding controls? Have you tried just making sure that this is depressed:

 

[Snowman88]

No, when it looks like in your screenshot, i get a message saying:
"Microsoft Access can't start this wizard right now. currently not installed..."

I can use the report design wizard for example, but not this one.

 

[Snowman88]

Ah, forget that, it updated to 2003 and now i can install the missing wizard. Could not do this last week with the older access....

I will try it out now, thanks

 

[Snowman88]

Where do I put the WHERE clause?

 

[boblarson]

In the code for opening the report from the form:

DoCmd.OpenReport "YourReportNameHere", acPreview, , "[YourFieldNameHere]=" & Me.YourFormControlNameHere

 

[Snowman88]

Now I get run-time error 2491 saying, that the action or method is invalid because the form or report is not bound to a table or a query



I feel dumb