Access Database Design

[davidjearly]

Hi,

I'm trying to create an Access 2003 database, which I can use to produce reports on data inported from excel spreadsheets. I am relatively new to Access, but I know the rest of MS Office very well.

I have been using the design wizard to create tables in Access based on the information contained within the spreadsheets. I have 5 tables, the first of which has fields 'ID', 'FIRST NAME', 'LAST NAME', 'DOB', 'AGE', 'TITLE'. The other 4 tables have information about the status of those individuals, and share the common field 'ID'. My aim here is to be able to keep data based on the above in one database, but be able to produce reports on it based on the contents of the 5 tables. These reports would ideally contain graphs.

For example, using the field 'ID' from the first table, I would like to be able to produce a report (chart) with the number of individuals who are waiting on a product being received, which is taken from the second table. The second table would have the fields 'ID', & 'WAITING'.

Can anyone help get me started with this?

Thanks!

 

[reclusivemonkey]

Hi David,

What you have just described sounds fine; where is it you have got stuck?

Your best bet would be to create a query for your report and link the two tables by the ID field. You can then bring together the data from the two tables.

 

[davidjearly]

Hi David,

What you have just described sounds fine; where is it you have got stuck?

Your best bet would be to create a query for your report and link the two tables by the ID field. You can then bring together the data from the two tables.

Thanks for your reply and sorry for my late response, but just back from Christmas break.

Well, where do I start? I have created a database using Access 2003. I have created the five tables as explained previously. I haven't imported any data and I haven't set up any queries. How do I go about this?

I would like to be able to import the data from excel spreadsheets and generate charts based on the information in the tables.

Thanks,
David

 

[johngordon]

If you've gotten as far as creating your tables, what you want to do is, for example :

1. Create a new query, choosing design view.
2. You'll see a box listing your tables - just select the ones you want for your query, and click on Add.
3. So if you've added two tables, they'll appear at the top, with the fields listed. I think typically you'd have ID set to autonumber for your main table, and just as number in the other tables.
4. The query should link the ID fields.
5. You can then add the fields from each table into the query, by dragging then into the bottom part (or double clicking).
6. You would then create a new report, and base it on the query you've created.

Not sure about graphs, as I've never done that.

Hope that helps.

 

[exegete77]

Howdy. A couple of questions to help further in this process. Perhaps you already have this done.

Have you normalized your data/structure? (1st normal, 2nd normal, etc.)

What about relationships between the tables? Have you made them?

 

[davidjearly]

Right guys. Thanks for your replies this far. I've managed to get a barebones solution up and running and I am generating chart reports. However, a few more tips would be great.

I know that I can customise the look of my chart report by entering 'design view' in Access and selecting 'chart options'. What I haven't been able to do is centre the chart in the middle of my report. It appears in the top left hand corner when I run the report.

Secondly, I would like to design a form that opens when the database is launched and allows users to run any one of the chart reports I have created.

Is this possible?

Thanks,
David

 

[reclusivemonkey]

I know that I can customise the look of my chart report by entering 'design view' in Access and selecting 'chart options'. What I haven't been able to do is centre the chart in the middle of my report. It appears in the top left hand corner when I run the report.

Stretch the chart object to the width of the report and it should remain in the centre.

Secondly, I would like to design a form that opens when the database is launched and allows users to run any one of the chart reports I have created.

Somewhere in Tools --> Options (I don't have a copy of access at home) is an option to choose a form to automatically load when you open the database. You can use the Switchboard Manager menu option to create this form for you.

Is this possible?

Thanks,
David[/QUOTE]