Feeling Lost

[David]

Hi. Let me start off by saying that I've never used Access before. I have been reading through a few books though and trying to learn. What I have been tasked to do is create a database for some budget programs and budget figures. There are 3 main main categories, and each has several programs under it; with each program having a identifcation number assigned to it already. What we want to be able to do, as far as I knew (so this all needs to be very flexable), is to look up different programs and compare their budget numbers. I would think this would be simple enough, by creating relationships to enable me to develop a simple query, but the solution seems to be elluding me. Also, a small wrinkle, is that some prgrams (with the same identification number) are present under different categories because that program will be changing hands in the future.

I would appreciate any tips and/or guidance on this issue. Access seems very powerful, but at the same time a steap learning curve.

 

[Kevin Sprinkel]

-----Original Message-----
Hi. Let me start off by saying that I've never used

Access before. I have been reading through a few books
though and trying to learn. What I have been tasked to do
is create a database for some budget programs and budget
figures. There are 3 main main categories, and each has
several programs under it; with each program having a
identifcation number assigned to it already. What we want
to be able to do, as far as I knew (so this all needs to
be very flexable), is to look up different programs and
compare their budget numbers. I would think this would be
simple enough, by creating relationships to enable me to
develop a simple query, but the solution seems to be
elluding me. Also, a small wrinkle, is that some prgrams
(with the same identification number) are present under
different categories because that program will be changing
hands in the future.

I would appreciate any tips and/or guidance on this

issue. Access seems very powerful, but at the same time a
steap learning curve.

Yes, it can be steep...but rewarding. First principles to
understand, in my opinion are:

- Divide and Conquer
- Fields should describe "attributes" of a single "thing".

So, since a program can be in multiple categories,
category is not an attribute of the Programs table below,
it's an attribute of each unique program/category
combination.

At a minimum, you will need:

Categories
CategoryID Autonumber
Category Text

Programs
ProgramID Number
Program Text
Budget Currency

Budgets
Program Foreign Key to Programs (Number)
Category Foreign Key to Categories (Number)

Your query should contain the fields you wish to display
or print, probably:

Categories->Category
Programs->Program
Programs->Budget

In query design view, show all three tables. Establish a
relationship between each foreign key field in Budgets and
its corresponding field in the lookup tables Categories
and Programs. You may also do this in Relationship design
view prior to creating the query, in which case, the
relationships will already be established when you show
the tables in design view.

Select the fields above, and save your query. You can
then use the wizard to create a report, grouping by
Category, sorting by Category, then Program.

HTH
Kevin Sprinkel
Becker & Frondorf