are reports the primary end files not forms?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

ok Ive been having alot of problems with a monster data base first I had over
800 fields can't make tables that big had 4 tables, 20 forms and needed
approximatly 40 reports. each person working at the command needs one of each
forms assigned to them. was thinking that I needed all of their personal
information 20 fields in each form. Thinking that forms was the primary
viewing or end result, but Im wrong reports are the end result and primary
view right? So I think I need alot more tables witht the related information
corolated in each. But I still need a way to identify each form page to an
individual, is that done through sub-forms, quieres or relationships?
 
NOOOOOOO

You don't need a separate form or report for each person.

What are you trying to accomplish?

The forms can be designed (based on a query) that will only return SOME
records. For example, If you have five comanders and each one has twenty
people under him, then just have the form pull the records for the person
signed into the database. In other words, in the query you would include
the person's comander. Under that column, you would simply include criter
that says only pull records where this field is equal to the user that is
currently signed into the database. So, if Comander Jones is signed on and
opens the form, only employees with Comander Jones in their "supervisor"
field will display. If Comander Smith signs on and opens the SAME form,
then only Smith's people will display.

Reports are for printing. Anything you want to print (or have look like a
printed report on the screen should be a report.

Forms are for viewing, adding, editing, and sometimes deleting, records. In
many cases, a form can pull results similar to a report, but a form should
not be printed.


Hope that helps.

Keep posting questions. Try to give us specifics though. "I need to list
our entire company sorted and page-breaks by comander" or "I need to
allow each comander to view, modify, add, and delete employee records, but
they should not be allowed to touch any other comander's people".

Rick B
 
In general, I would say the Forms are really for collecting data from users.
Reports are for providing output from the database.

It sounds like you need to create queries to correlate your data from
various tables, then use the queries as data sources for your forms and
reports.

Of course, I'm not familiar with the database you are working on, but it's
possible that better Normalization needs to be performed. If you're not
familiar with it, you may want to do some research on Normalization and
Relational Databases. (e.g. a Google search on : "Relational Database" +
Normalization)

I hope that helps,

Marvin
 
Ok I didn't phrase it right, each person needs a PAGE in each form assigned
to them if I break everything up waht is the best way to ensure that when
entering data by the end user that he is entering the specifics about the
right person on that page of the form?
 
Outside of the tabs (in your form header maybe?) put the person's ID number,
name, etc. Don't put every field on a tab, use the top part of the form for
the more common fields. If it were me, I'd also have a "find" box or a
drop-down box in the form header to allow me to move to the appropriate
record. This would mean that no matter what tab you were on, you would be
able to see what record you were editting.



Have you looked at any of the sample databases that are out there? You
should take a few minutes and explore the Northwinds database.

You could also use the built-in templates and see how they handle employee
databases in the template.

Rick B
 
Ok my report main has 9 Tabs, I dont want the persons ID on every tab or each
tab page, but forms hold multiple peoples information on different pages or
are the called record entries?. Now when I go to the second Page (record) on
that form I want to know who Im entering data on. when I have 6 forms I would
Like all page/record #1 to be smith john, record, page/record 2# to be
williams Paul etc.
So basically that brings it all around I want to input all that data on my
main form thats for general informartion for Smith John, then when I open Up
Forms-Medical it alrady hasSmith John as record #1 etc
 
ok Ive been having alot of problems with a monster data base first I had over
800 fields can't make tables that big had 4 tables, 20 forms and needed
approximatly 40 reports. each person working at the command needs one of each
forms assigned to them. was thinking that I needed all of their personal
information 20 fields in each form. Thinking that forms was the primary
viewing or end result, but Im wrong reports are the end result and primary
view right? So I think I need alot more tables witht the related information
corolated in each. But I still need a way to identify each form page to an
individual, is that done through sub-forms, quieres or relationships?
Click to expand...

I just want to agree with the other responses here.

YOU ARE ON THE WRONG TRACK.

If you are trying to store 800 fields in each record (which you cannot
do in one table, of course, and should not do in multiple one-to-one
tables); or if you're trying to combine multiple tables to create an
800 field report - *you're not using Access correctly*.

Stop. Step back. Go to http://www.mvps.org/access and look for the
tutorials on Normalization. Perhaps get one of the many good books on
Access, such as John Viescas' _Running Access <version>_ or the
_Access Bible_. It really sounds like you're confusing data
*presentation* - forms and reports - with data *storage*. They are not
the same; it's very easy when you're first getting into Access to look
at the desired final output appearance and use it to design your
tables, but that will almost surely give you a BADLY non-normalized
database.

You've posted a lot of problems here but you have not (that I have
seen) described the nature of the data which you are storing. What are
the Entities (real-life people, things, or events) of importance to
your application? (Each type of Entity should have its own table).
What are their Attributes (distinct, non-repeating, atomic bits of
information about each entity)? How are the entities related?

If you get the Table structures right to begin with (hint: a 30 field
table is about as wide as you'll ever need to get), and do some study
on using Forms, Subforms, combo boxes, and the other tools Access
provides, then this may become a lot simpler to implement. If you have
the data *stored* based on its logical structure, rather than on the
layont of one particular type of printout, you'll have the flexibility
to produce that printout *or any other desired printout*.


John W. Vinson[MVP]
 
A table is the place to store information. You should be able to describe
the function of a single table without using the word "and" (except to say
"name and address" or some such). Personal information is often one table.
It would contain a person's last name, first name, address, employee ID
number, etc. It contains only information that is specific to that person.
All of the information for one person in that table is a record. It is
represented by a row in the table, but it functions very differently from a
row in a spreadsheet.
Each record in each table has a primary key, or unique identifier. If you
have 800 fields, does that mean you have a record with 800 unique pieces of
information? You really need to post your table structure and relationships
if folks in this forum are to offer assistance.
 
In the past month I have bought and read to my dismay 3 books gone to 5 or so
web pages and finally endedup here.
The data base I am making is a comprehensive individual/personnel one to be
used at my command. It will have alot of feild because in the Navy you don't
just do one thing. example- qualifications - at current command there are
watch station quals-10, firefighting quals- 13, safety quals-4, 3-M
quals-5possible, equiptment quals-10, (in differnt shops it varies), gun
quals-4, various (planner, plant quals etc-10) ok thats just the quals they
need a yes/no field and a date qualified field. Just Quals aprox 70 fields.
Now with education fields, previous and in Navy educations aprox 100 fields
for schools and scores,thats just two areas. We also have recall information
address, phone basic stuff then TAD assignments, Leave tracking shop manning
and ll of this information will be used in many reports. I can build tables
and I can Layout forms for entering the data in, not a problem. The only
thing Im trying to do because there are so many forms because of size limit
is coralate the forms from each section, so when I enter the name rate rank
ssn (basic Identity) into the first form that it automatically fills in the
feild in the same record of every form, so when the person entering data
opens any form to update data the can go right to that persons record and
they know for sure where to enter stuff.


Or should I be using the tables to enter all of my data in but that seems to
defeat the purpose of a GUI based data base. I Hope this clears up what I am
trying to do.
 
JimmyD said:
In the past month I have bought and read to my dismay 3 books gone to 5 or so
web pages and finally endedup here.
The data base I am making is a comprehensive individual/personnel one to be
used at my command. It will have alot of feild because in the Navy you don't
just do one thing. example- qualifications - at current command there are
watch station quals-10, firefighting quals- 13, safety quals-4, 3-M
quals-5possible, equiptment quals-10, (in differnt shops it varies), gun
quals-4, various (planner, plant quals etc-10) ok thats just the quals they
need a yes/no field and a date qualified field. Just Quals aprox 70 fields.
Now with education fields, previous and in Navy educations aprox 100 fields
for schools and scores,thats just two areas. We also have recall information
address, phone basic stuff then TAD assignments, Leave tracking shop manning
and ll of this information will be used in many reports. I can build tables
and I can Layout forms for entering the data in, not a problem. The only
thing Im trying to do because there are so many forms because of size limit
is coralate the forms from each section, so when I enter the name rate rank
ssn (basic Identity) into the first form that it automatically fills in the
feild in the same record of every form, so when the person entering data
opens any form to update data the can go right to that persons record and
they know for sure where to enter stuff.


Or should I be using the tables to enter all of my data in but that seems to
defeat the purpose of a GUI based data base. I Hope this clears up what I am
trying to do.
Click to expand...
 
Do you have a personnel table? If not, you need one. It will contain a
primary key, name, ID number, rank, and other things specific to each person.
Each person's information is one record. You will not be storing that
person's name in any other tables unless you have a very good reason.
Examples of a good reason would include needing to know a person's name at
the time they signed a document, even if it is different form their name now.
However, if you are trying to keep track of a person's training you will
want to know about that training regardless of their name at the time.
Now you need an education (or training) table. It will contain a primary
key, a foreign key (you will establish a relationship between the foreign key
in the education table and the primary key in the personnel table), the name
of the course, where it took place, the instructor's name, etc. In other
words, each record will consist of information specific to a particular
training session or course. Once you have established a relationship between
these tables, every record you enter into the training table will be
associated with one and only one record in the personnel table.
If the training occurs at schools, maybe you will need a schools table, with
a course table related to that.
If this is not how you need to do it, please post your table structure, and
describe the relationships. Without that kind of information there is really
nothing anybody here can do.
 
You would open a form (one form). When opened, you would be looking at one
person (John Smith). In the form header (common area above all your tabs)
you would have his id number, his first name, his last name, maybe a search
field, etc. Below that you would have a big sqare with several tabs. Tab1
might be called Personal Data, tab 2 might be called Medical Data, tab 3
might be called Awards, tab 4 might be called training, etc. You could
click on each tab and edit different information You would still be doing
all this for John Smith. when you press page down, the form would pull ip
the next person's iformation.

Again, go look at Northwinds. Look at the Employee form.

Until you see some examples of what's out there, you are probably building a
mess that you will have to go back and clean up later.

Rick B
 
Whoaaaaa. You are not using normalization. Let's say you have 150
training classes you offer. You WOULD NOT add a field to a table for each
class.

This is called a One-to-many relationship. One table contains the person.
One table contains the classes they have taken. If they take five classes,
they will have five records in that second table. If they take twenty
classes, they will have twenty records. You would NOT add a field to your
Person table for each class and try to maintain that.

This could also become a many to many relationship if you want to have a
separate table that stores the names of all the available classes


TABLE1
IDNumber
FirstName
LastName
etc.


TABLE2
ClassNumber
ClassName
InactiveY/N


TABLE3
IDNumber
ClassNumber
Date
Grade



Each person would have a record in table1.

Each available class would have a record in table 2.

Table three would contain one record for each person/class combination.




Hope that gets you going.

Please STOP and look at the samples provided. Look at NORTHWINDS. Learn
how it works and asks us questions from there (the advantage is that we will
be looking at the same thing.)


Rick B
 
In the past month I have bought and read to my dismay 3 books gone to 5 or so
web pages and finally endedup here.
Click to expand...

Well, maybe they were the wrong books, or you were jumping in to form
design without getting the basics of table design first. You're
designing spreadsheets, not tables! Tables grow DOWN, not ACROSS. See
below for examples.
The data base I am making is a comprehensive individual/personnel one to be
used at my command. It will have alot of feild because in the Navy you don't
just do one thing. example- qualifications - at current command there are
watch station quals-10, firefighting quals- 13, safety quals-4, 3-M
quals-5possible, equiptment quals-10, (in differnt shops it varies), gun
quals-4, various (planner, plant quals etc-10) ok thats just the quals they
need a yes/no field and a date qualified field. Just Quals aprox 70 fields.
Click to expand...

No. It's THREE TABLES.

Personnel
ID <the person's unique military ID>
LastName
FirstName
Rank
<etc>

Qualifications
QualID
QualificationType <e.g. "firefighting">
Qualificaition <e.g. "SCOBA">

PersonnelQualifications
ID <who got the qualification>
QualID <which of the 70 or so qualifications they got>
QualDate <when they got it>
<other fields about this sailor's performance on this particular
qualification>

If a sailor qualifies on 36 qualifications, they would have 36 records
in PersonnelQualifications.
Now with education fields, previous and in Navy educations aprox 100 fields
for schools and scores,thats just two areas. We also have recall information
address, phone basic stuff then TAD assignments, Leave tracking shop manning
and ll of this information will be used in many reports.
Click to expand...

Similar. If you have one (sailor) to many (scores), you need a Scores
table with *multiple records per sailor* rather than a wide flat table
with a different *field* for each score.
I can build tables
and I can Layout forms for entering the data in, not a problem. The only
thing Im trying to do because there are so many forms because of size limit
is coralate the forms from each section, so when I enter the name rate rank
ssn (basic Identity) into the first form that it automatically fills in the
feild in the same record of every form, so when the person entering data
opens any form to update data the can go right to that persons record and
they know for sure where to enter stuff.
Click to expand...

If you have a Form based on Personnel with Subforms based on these
multiple related tables, Access *takes care of that for you*. You do
NOT need to, or want to, store the personnel data in multiple tables;
you do NOT want to copy that data into ANY other table, and you do not
need to create a different main form for every different category of
data.
Or should I be using the tables to enter all of my data in but that seems to
defeat the purpose of a GUI based data base. I Hope this clears up what I am
trying to do.
Click to expand...

You *will* be entering data into tables - tables are the only place
Access actually stores data - but you should *never* need to see a
table datasheet when you're actually using the database. Forms are
your interface to the tables; and the tools that Forms provide -
subforms, combo boxes, listboxes, etc. - make that process easier.

It appears that you have made the very common mistake of letting the
Forms, or the Reports, drive your table design. It's easy to do,
especially if you've worked with spreadsheets! But in a relational
database IT IS SIMPLY WRONG, and will get you into all of the hassles
that you're currently experiencing. Get your books back out, and look
in the Index for the terms "Normalization" or "Table design" - they
should cover the subject in an early chapter (and if they don't, burn
the book <g>). The foundation of any database is its table structure;
Forms and Reports are added later. If you're building a ship, you want
to be sure that the hull is designed right before you start adding
decks and masts!

John W. Vinson[MVP]
 
Back
Top