Access Report Question

[nighteagle]

Hi,

I have some ugent issues about creating an Access report:

(1) I need create a report to display a table like structure on a report - multiple rows need be displayed and aligned on a single page. How do I achieve this?

Example like this:

Emp_Name Emp_Date Is_Manager
---------- ---------- -----------
Mary 06/2004 No
Bob 01/2002 Yes
Frank 10/2005 No
...

Can an Access Report display all these rows of data on a single page?

(2) how do I apply a filter (or a where clause) to display a specific row/column in a text control from a query result?

For instance, in above example, how do I display Emp_Name "Bob" in a textbox? (...where emp_name = "bob")

(3) I assume one report can have only one data source?

Thanks a lot!

 

[nighteagle]

Can anyone help me please?

 

[Moniker]

Have you tried to do this yet? It's quite simple, really. Have you attempted it at all and are you running across errors or unexpected results? Can you post it?

Briefly, all you do is set your group headers (Emp_Name, Emp_Date, Is_Manager) to be your "column headers" (they're just labels on a report), and then in the detail, you put the actual fields for each column header. Just put them in once -- Access will handle the rest. For filters, just use a where clause in the report's record source (SELECT * FROM Employees WHERE Is_Manager=True, for example).

 

[nighteagle]

Have you tried to do this yet? It's quite simple, really. Have you attempted it at all and are you running across errors or unexpected results? Can you post it?

Briefly, all you do is set your group headers (Emp_Name, Emp_Date, Is_Manager) to be your "column headers" (they're just labels on a report), and then in the detail, you put the actual fields for each column header. Just put them in once -- Access will handle the rest. For filters, just use a where clause in the report's record source (SELECT * FROM Employees WHERE Is_Manager=True, for example).

Yeah - I tried and every record is displayed on a seperate page, which is not I wanted...after playing around for a while after your reply, I noticed that I need make the detail section small enough so all records will be displayed like a table structure...otherwise it simply goes to the next page. It took me a while to find out :-(. And more to this issue, what I actually need is to display some structure like the following:

Dept------Emp----Emp_Date
------------------------------------------------
sales------Mary---04/2006
-----------Bob----01/2002
------------------------------------------------
marketing--Frank--10/2005
-----------Bill-----03/2003
------------------------------------------------
IT---------Mike---01/2000
-----------Sam---02/1999
-----------Jason--04/2001
...
and so on... (the dashes within each section is just for display formatting purpose here)

How to do that?

In terms of the filter, what I mean is for an exisiting query or table (as data source), I need the row1/column1 ("Mary") goes to ControlBox1 and row2/column1 ("Bob") goes to ControlBox2... I don't think you can add a where clause to the sql statement to get this done. Any suggestions?

 

[Moniker]

Consider the structure of the report. You initially asked if a report can have more than one record source, and that's a "no". However, you can base a report on a query that has numerous data sources.

As for Mary in Row 1, ControlBox1, Bob is Row 2, ControlBox 2, you're not quite getting how reports work. You only have one control as it were. For example, it might look like this:

-----Report Header-----
-----Group Header-----
Employee_Name           Employee_Date           Is_Manager
-----Detail-----
[Employee_Name_Field]   [Employee_Name_Field]   [Is_Manager_Field]
-----Group Footer-----
-----Report Footer-----

You only have one control for each field. To control what appears there, you would use filters or calculated conditions in the field. Look at the example I did (but please, don't base anything on it -- I wrote it in five minutes as an example without normalization, etc.).

UPDATE: Give me another 5 minutes to update my example with your new requirements.

UPDATE2: OK, I'm now separating by departments. Look at how I did the Sorting and Grouping (View -> Sorting and Grouping).

Much more of this, and the hourly charge will start ticking.