My really bad db (1 Viewer)

[NADYAS]

Hello,

I'm new to access, did read some tutorials, but still facing some problems.
I'm making a simple database, so I think my questions are not that hard.

1.I have a Main form which I have in : ID, Fname, Mname, Lname, Age, Gender.
How can I create a drop down menu in second and third form, in which user can select the ID(from drop down) and the same data Entered in Main Menu can be entered in text fields in second and third form.
2. I have one huge form, the page is full about 225 fields, can I split to several forms ? should I create a button that takes me from first to second form so I can continue entering date ? should there be a relation between these forms ? If you please explain how, it would be great.
3. I also have form called screening, In it there is a session number, I have a form called progress note, I need to create several new progress notes in several dates for one session number, how can I do that ?
Thank you

As Asked by boblarson I'm uploading the database.

From your description, your database is not designed properly. I think you should post a copy of your design in the Theory and Practice of Database Design category here so people can help you get that correct before you do anything else.

I was trying to make each form as an A4 paper, but I couldn't so In assessment form for example I separated the pages but it's not a good design and I'm trying to create different forms for each part.

Thanks in advance for your help

View attachment New Microsoft Access Database.zip

 

[spikepl]

There are so many issues with your DB so I would not even know where to begin. But here are some suggestions, in ordered prioritised sequence:

1. Google database normalization and read
2. Do a tutorial on Access tables
3. Do a tutorial on Access queries
4. Download a working database (Microsoft has a bunch of templates, google Access templates) and have a look inside, see how it works. Especially, pay attention to how queries are used to extract required data, and how fields in tables are named and how these are used in queries.
5. Have a look at some suitable datamodels here http://www.databaseanswers.org/data_models/
6. After doing that, review and make changes in your db. Don't bother with the forms as yet - that is for later. The data structure needs to be fixed first
7. Then post you db again. But also, in plain English, write what it is to be used for. Describe the main tables. What is supposed to happen with the data? Are there to be any reports, or operations on data involving more than one record?.

For any readers - the Pottery Barn rule does not apply in the forum, so do feel free to jump in.

 

[Rabbie]

If you can post your DB in an earlier format you may get more replies. Not all of us have upto date versions of Access so it is helpful if you can save and post a copy in mdb format.

In general Spikepl has given you some very good advice which you should follow.

Form Design. Try to design your forms so they fit onto one screen. This makes it easier to to use. 225 fields is way too many to take in when you look at the form. Depending on what you are trying to do 20 or so should be your max.

 

[NADYAS]

Ok guys I'm starting with what you said now just one questions.

If I create main table with : ID(PK), Fname, Mname, Lname,
In my second and third table I need to insert the data from main(ID,Fname,Mname,Lname)

what should the PK be in my second and third table ? if I'm going to use the ID, should it be ID too ? The relation here should be one-to-many, right ?

Will fix table and upload a copy compatible with MS office 2000

 

[GinaWhipp]

Okay... well, Spikepl is correct you do need to read up on normalization. You also need to understand PK's (Primary Keys) and FK's (Foreign Keys) and what words a Reserved for use only with Access. To get you started...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page...
http://www.mvps.org/access/resources/index.html
A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html
MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials
Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/
Naming Conventions…
http://www.access-diva.com/d1.html
Other helpful tips…
http://www.access-diva.com/d11.html

 

[hnkor]

Okay, here is an example where a entity relationship diagram would work.

First of all do not attempt any forms until you have you database. First things first. Secondly, to start think of all the entities in you database. For example a student is an entity, the fields for student are first name, last name, street address, city, zip. Every record or row in the table needs to be unique. Name is not unique. Use a counter field as a pk.

What other entities can you think of? Okay a student takes many classes. So you create a table called class. Now a class has many sections this many signifies that you need another table for sections. Continue creating you entities i.e tables.

Each table has a pk key. This key becomes a join link to another table. For example Student takes many classes. The many side of the relationship class has a fk to student.

Now same example a class has many sections. So the section has a pk and a fk to class. On and on. This is how you link tables.

What else in section, teacher, time, building etc. Here are fields that can possibly be tables. For example to you want information about the build? Since a section is offered in a building at a particular time and a building can have many sections than the many side building has the foreign key to section.

Now teach is an entity too. A teacher has a name, address, depart, etc. Create a teacher table this teacher teaches many sections. Again a relation but a section has one teach. So the teacher has the foreign key to section. When I say foreign key it means the primary key in section becomes a foreign key in teacher.

So here are the possible relationships.
1:1 same table
1 to many the many table gets a foreign key to the 1 table
many to many is a little more complicated. You must create an intermediate table which holds two foreign keys. example product like cupcake has many ingredients and ingredients are in many products.

so ingredient table
product table
product ingredient table this is your intermediate and it has a fk to ingredient table and fk to product table. If you know your entities what you are tracking and how they are related 1:1, 1:m , M:M then you know where to put you foreign key. All tables have a primary key.

Now, when you pick an entity and decide on fields such as a student has a name, address, and so on. You might think aw a student has a term. Now a student may attend many terms. When you have a many field then you need to create a new entity for that field.

So in the beginning it's brain storming. Here now a student has a term and term has many students. M:M so create that intermediate table. etc....

 

[JLCantara]

Hi Nadias,

I made this very simple first step of your DB. The Main is now Clients and conteins only 'civil registry' information. I recommend you by the most expensive book on Acces (approx. $120): it will pay off.

Contrarily to what was said, I always, in classes, started with analysis/collection of significant data ( this is done). Creation of a table with proper field names and data types. Then creation of a general purpose query used by E/M/D (enty, modification and deletion) form: I name them ww_something. Finaly, creation of a form based on this query: note the 3 options (EMD) are programmable.

Tell me about this new approach...

JLCantara.