Hi Ruth,
Yes, it's possible to do what you want to do. Why do you want to do
it? It crams your data down into a tight jumble rather than breaking
it out and 'properly' displaying it. Why do you want it so tight?
First, the tools in Access assume that, since we've gone through all
of the machinations to whip the data into at least third normal form,
we'd like to keep it that way. Your expressed desire is to display it
as a flat file cluster in three instances: hires, transfers,
terminations.
Second, your (miss)use of relational database nomenclature and your
reference to an Excel model cause me to believe that you're early in
the transition to Access and that you have been doing your list
management in Excel.
Whether my assumptions are true or not, Welcome to the wonderful world
of Access! It can be a lot of fun and if you persevere you'll be able
to do some awesome things with it. That's the good feeling part of
it.
The down side is that Access is the most difficult of the MS Office
applications to learn. That learning curve is long and steep.
Relational database management systems are based on SET Theory from
mathematics. There is an awful lot of it that is simply
counterintuitive. You read things and just know that the author is a
fraud. Word and Excel are particularly easy to learn: You learn some
of the basics and can apply them immediately. In Access you have to
learn quite a bit before you can do anything useful. Word and Excel
have evolved from early word processors and spreadsheets and were user
level applications from the 'git go'. Under the hood they are both
very powerful but you're protected from the complexities unless you
need the real power. In Access you're exposed to things breaking and
going wrong from the very start.. In addition, the error messages
that get thrown up (pun intended) usually have nothing to do with the
issues at hand and nothing at all to do with what you're trying to
accomplish. I'm not trying to discourage you, far from it. I want
you to know that when things go wrong you should be patient with
yourself. It isn't because you are stupid! It's tough getting up to
speed with Access.
There are lots of rules to be learned and observed in the pursuit of
Access. Learn them and observe them. Plan to buy books appropriate
to your current level of understanding. Google the Access newsgroups
for recommended books. These newsgroups are excellent resources. For
newbies to Access I recommend both microsoft.public.access.tables
design and microsoft.public.access.gettingstarted. Lurk them daily
for an hour or so. Get to know the issues being posted and understand
the responses. Keep lurking those two groups until there are no more
surprises. Get so you can post some responses. Another great
resource is
www.mvps.org/access Visit the site and poke around a
whole bunch. It is filled with Access lore and continually grows and
increases its value.
The heart and soul of any database application is the data design.
Analysis of your application will flush out the entities involved.
Entities in your application are captured in tables named for them.
Good candidate names for tables might be Person, Employee, Company,
Order, Manufacturer, etc. Not so good are things like Main, Master,
etc. It doesn't mater whether names are singular or plural. If your
data design is good you'll have an application you can enhance
indefinitely. If the data design is screwed up your application will
become increasingly difficult or impossible to maintain and enhance.
I know, I know. I haven't addressed your issues directly. I was
addressing some of the more important stuff first.
Using the standard tools provided by Access your sample data would
fall out something like:
==================================================
Terminations for the period ended November 20, 2005
Name: EmployeeID:
Jones, Rose 123456
Smith, Irwin 234234
Brown, Butch 345345
Total
terminations 3
----------------------------------------------------------------------
--------------------
Transfers out for the period ended November 20, 2005
Name: EmployeeID: Destination:
Black, Ruth 456456 Training Services
White, Harold 567567 Human Resources
Total
transfers out 3
----------------------------------------------------------------------
--------------------
Hires for the period ended November 20, 2005
Name: EmployeeID: Department:
Lincoln, Abraham 000111 Statesmanship
Eisenhower, Dwight 000199 Strategic Execution
Regan, Ronald 000299 World Affairs
Bush, George H.W. 000300 Statesmanship
Bush, George W. 000302 Operations
Total Hires 5
----------------------------------------------------------------------
--------------------
==================================================
Be aware that you have special Sorting and Grouping tools in the
Report Designer that supplement the filtering and sorting you may have
done in the underlying queries. There are also lines and graphic
elements that can be used in either mode to separate or emphasize
data.
to make it fall out like your example:
Terminations (3): Jones, Smith, Brown
Transfers (2): Black, White
Hires (5) Lincoln, Eisenhower, Regan, Bush,
Bush
would require writing three Static Function Procedures in a standard
module and then calling those procedures appropriately from the
Report's underlying query. Actually, since the three functions will
be almost identical, it will be possible to write one and to copy and
modify the first one
It is not an Herculean effort to do that
but the application becomes more difficult to do and to later
maintain.
While my preferences are clear, I've tried to present a balanced
comparison.
If you are really, really sure you want it with the special
procedures, etc. post back with your table names, with their field
names and relationships. Someone, maybe even I, will pick up the
thread.
HTH
