how many fields are allowed in a table?

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

Guest

New user doesnt come close to describing me. ( I Have several issues) I am
apush button learner, tried to read several books did not help only
confussedme. I am currently building a data base for my command (Navy),
basically its an all encompassing personnel tracker, tobe used all the way up
the chain. tracking assignments, leave, recalls/musters, education, etc etc..
I first jumped rightin and figured so muchout it was really amazing how if
you keep pushing buttons you can trip across an answer (always back up prior
to using this method, highly reccomended..Anyway, now im stuck, my
databaselayed out on paper has a monster Switchboard, 10+ forms, about 40+
reports, and with over 600+ data I need at least 3 tables. Ok this site
reccomends lower field tables that would be fine but heres my problem. when I
build a form it only lets me use one table. In every form therewill be some
of the same information NAME, first, midlle, Last, SSN etc. And every
individual will need to be listed on each form to produce each report. How
do I either use one table for the reoccuring information or link the tables
toauto coppy that information into all of that individuals forms. other wise
I have to at leastrenter thesameinformation for a personin every form that
has a diffent table and every table will have atleast 10 fields exactly
alike.
I hope that someone has an answer for me, because Im really at a loss. If
for every new form that has the same thing in it buton a difffernt table, it
will reduce theeffectiveness ofthis data base. Ioriginally started thisto
decrease the number of times a persons information had to be hand entered. If
I had only one table then I could use that field on every form and it auto
fillsi in.
Sorry so long of anexsplanation but hopeing to get the issue across so
someone may understand.
Thanks
Jim
 
First, you should not put more than about 20 or 30 fields in a table. Each
table should contain similar data. For example, you might have a
name/address type table, a phiscal characteristics, medical data, etc.

You CAN have multiple tables on one form. The whole point of Access is that
it is a relational database. You have to "relate" the tabels to each other.
Build relationships to link the tables together by common fields (some
unique "employee number" type of field).

Base your forms on a query, not on a table. You can base them on a saved
pre-built query, or, if you will only use it with one form, just imbed the
query int he form...

Create a new form in design-view. double-click the black square in the
upper left corner to open the form's property box. Click the data tab, then
go to the Record source field. Leave that field blank, but click the query
builder button (...). Add any tables with data you need and apply any
criteria to limit what you will see. Then close the query. Note that forms
based on more than one table may not be updatable if the tables have more
than a one-to-one relationship. In the examples above (personal data,
medical data, etc.) you should only have one record in each table for each
person, so you will not run into this.

I'm sure you will have lots more questions, but this should get you started.

Note that your forms can have tab controls to hold more data, bt still look
nice and organized.


Good Luck


Rick B
 
Also, your last few paragraphs make a good point. You do not want to
duplicate effort. You can help accomplish this by maintianing those
relationships and linking tables to each other using those key values.

Rick B
 
Ill give that a try, butQueries confuss me lots, So I guess morebutton
pushing in that area. printing out your directions. So now I at least know
where to look. The TABs areon 3 of my current forms. one has 14 tabs in the
main table formfor all purpose information. Fields, Tables, Switchboards
special tools I have been practicing with. Queries have yet to really
touchwas actually hopeing that wasnt the area I needed to go. Still not sure
about Relationships nor Queries
thank you
 
Did a test form said used quieres insteadad of table , seemed simple was able
to pull information out of several tables into a querybox, then went to the
field list and everything was thee I needed,placed fields into a form on a
tab, looked good in design, but when I went o preview or working view, it
justshowed the back ground no fields were visible. ?
 
JimmyD said:
Did a test form said used quieres insteadad of table , seemed simple was able
to pull information out of several tables into a querybox, then went to the
field list and everything was thee I needed,placed fields into a form on a
tab, looked good in design, but when I went o preview or working view, it
justshowed the back ground no fields were visible. ?
Click to expand...

Multi-table queries often return non-editable result sets. If your query is
non-editable and also returns zero records then your form will go completely
blank.

Check the help topic named "When can I update data in a query?"
 
First Off,
THANK YOU, for pointing me in that direction, got those problems fixed.
You ever notice how when you get through one obstacle another is waiting
around the next corner, building this database has more problems for me that
an onion has layers, and to be honest both have me wanting to shed tears. ok
found out how to view them got that fix.
Now theproblem is this , I can creat the forms and I can see the
fields and I can enter data into those fields but they are still stand alone,
the Queries are not cross-updateing the other reports with the same fields
pulled from the exact same small table I created for just those items
relevent to all form.
 
Back
Top