Please Help! Access Rookie

[kewlgolf]

Hi,

I'm new to the forums and seeking help in creating an Access Form / Report...

We have an Access database created with about 8 tables of Municipal information (contact info, council info, address, meeting dates, etc.). We have relationships created for all tables (a unique Municipal Code for each of the 74 municipalities is the primary key).

We would like to create reports with our information in Access since things are easily automated and less time consuming when you get going, but do not have the proper training or experience to create some of the more complex functions necessary to get a proper report.

(We currently create the reports in Word, and it is painstaking making sure things are lined up, and editing information)



What I would like to know is: what do I need to create? a Form, or a report? I know a form is used to edit the information for specific fields. One example where I ran into trouble is this:

I want to have a field (textbox?) on the report called "Mayor"... but the information in the "council" table is organized in such a way that First and Last names are separate, along with councillor type (Mayor, Deputy Mayor, Chairperson, etc...). I realize I have to create an expression of some type to automate this. So I need to combine First and Last names where councillor type equals mayor, and this needs to be grouped by each municipality (so the mayor of Springfield, gets assigned to the Springfield record on the form).

Any help is greatly appreciated!

[Robeen]

I think the first thing you need to do is create a query that contains ALL the data you want to see.

To combine Data from different fields, you can do something like this in your query design:

In a new column in your query design view type something like:
FullName: [FirstName] & " " & [LastName]
. . . that will combine the first and last names for you and put them in a column named 'FullName'.

Once you've got all the data you need in your query - create your report using the Query as your Data Source.

When you're creating your report, if you use the Report Wizard, you will be given the option to add Grouping and sorting. You can get most of your stuff done in the Report Wizard.

Let me know if you need more help!

[jzwp11]

It sounds like you need a report.

Assuming that you want the output to look like the following, you can do it in the report or a query (and then base the report on the query):

Public, Joe, Mayor

In the query it would look like this:

SELECT LastNameField & ", " & FirstNameField & ", " & councillortypeField as Councillor
FROM...

Now with respect to having the mayor always show up first in the report that is a little trickier. I can think of 2 ways to do it.

1. Create a query that brings in the city info, the people information but only if the councillortype="Mayor". Create a report based on this query. Create another query that references the city but excludes the mayor (councillortype<>"Mayor"). Create another report based on this query. Drag and drop this report into the detail section of the first report and link via the city key field (I assume). This would then be a subreport in the main report.

2. Add a field to your councillor type table that would hold a sequence or rank number. Assign the mayor as rank 1 and the others with higher numbers. Create a query that brings the relevant tables together and sort by the new rank field. the mayor will just be listed first in a list of people. I'm not sure if this is what you were after.

[kewlgolf]

thanks for the help... I am slowly but surely getting it put together with the help of developing the queries beforehand!

It's a great way to brush up on my SQL skills

[jzwp11]

You're welcome.