Someone to lend a helping hand...

[bleyden]

I have been at this for a couple weeks now, and have read tutorials, watched videos, etc., but for whatever reason something is just not clicking. Is there anyone on here willing to help create what is probably a relatively easy database.



The basic description of this database is to allow me to keep track of individuals and their contact information, and then allow me to add registrations of different programs to their "account". Ultimately, I want to be able to generate reports outlining all of a specific individual's information (contact info, registrations), all of the individuals who are registered for one specific thing, etc.

I am quite honestly at a loss and about to go to an excel sheet which would take away a lot for what I need, so if anybody is feeling helpful, I would be immensely grateful.

Thank you all for your help and for just reading this.

[orange]

Is this a desktop database application?
What exactly have you done so far?
Which tutorials did you do/watch? What was the problem with the videos?
Do you have a list of requirements? If so , please post a copy.

Tell us in plain simple English about

the individuals, the contact info
Where does account fit? How does it relate to individuals?
Registration(s) to what?

[bleyden]

Thanks for your reply Orange!
The main issue I have had with the tutorials and videos is that my brain is having trouble relating the examples in the tutorial to specifically what I have and need done.

As far as requirements go for individuals, I want to see a unique 8-digit ID number and basic contact info (name, address including country, 2 phone numbers, 2 email addresses) and I want to also be able to add and see registrations for these individuals with the programs that I would have in another table/list. For each program in this list, I want to see the course code (autonumber?), department (EDU, REC), course name, mode (online, in person, hybrid) and duration. To add a registration, I want to be able to see the course they are registered for, a registration number, and be able to set an expiration date.

By account, I think I just meant the registrations that are tied to an individual.

I hope this helps, and if anything else is needed let me know!

[bleyden]

And yes, sorry, it is a desktop database!

[orange]

Here's the best help I can offer at the moment.
Work through this tutorial

It will take from 30-50 minutes or so. Read the instructions, then proceed along his procedure.
Focus on the process involved --what it is trying to achieve and the steps involved.
You have to do some work, but that's how you will learn.
There is a solution included, but work on your own.

As for tutorials generally, you will not find one that does exactly what your database does. The tutorials teach you concepts and methods. You have to apply them to your situation.
Focus on the tutorial , then get back.

good luck.

[bleyden]

I had tried that tutorial once before, but upon trying it again, for some reason, it it simply not clicking with me as most tutorials dont. If anyone is interested in taking a more hands-on approach to helping I would love any support I could get.

Thank you for trying to help, Orange. I wish the outcome on my end was better.

[orange]

I'll help you.

Start with a description of what the business is about. Here are samples of the level of detail required.

Narrative
ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

#2
Roger Carlson's CIS 253 class wants to create a database to store information about the students in the class. Information will include student demographic information, contact information and course information and history.

Information about the students will include name, address, city, state, zip, phone, email, fax, college major, Social Security Number, and gender. Each student can have more than one phone number, email address or fax number.

Course Information includes course name, course number, number of credits, and grade received. Each student will take many courses in their college career. And naturally, each course will have many students enrolled.

Good luck.

[ssanfu]

Read up on normalization concepts.

Do you understand PK/FK fields for creating relationships between tables?
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

I always use Autonumber type field for the PK field.
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

-----------------------------------------------------------------------------------------------
Start by using paper/whiteboard/cardboard/sticky notes/etc to design your tables. I have even used Excel to design tables; easy to move field names around in a spreadsheet.

Dos and Don'ts:
Do not use spaces in object names
Do not use punctuation in object names
Do not use special characters (exception is the underscore) in object names
Do not begin an object name with a number

USE only letters and numbers (exception is the underscore) in object names


Do not use Look up FIELDS in tables (see http://access.mvps.org/access/lookupfields.htm and http://access.mvps.org/access/tencommandments.htm)
Do not use multi-value fields in tables
Do not use Calculated fields in tables.


These are my conventions in designing a dB......... above are just suggestions. You get to develop your own conventions.

-----------------------------------------------------------------------------------------------
So for the individuals table, you might have:
tblIndivContact
-----------------
IndivContact_PK - Autonumber
IndividualID - Text ( a unique 8-digit ID number)
FirstName - Text
LastName - Text
address1 - Text
address2 - Text
city - Text
ST - Text
Zip - Text
country - Text
Phone1 - Text
phone2 - Text
email1 - Text
email2 - Text

tblRegistrations
------------------
Regist_PK - Autonumber
IndivContact_FK - Number/Long (link to tblIndivContact)
Department_FK - Number/Long (link to tblDepartments
RegistMode_FK - Number/Long (link to tblRegistModes)
CourseCode_FK - Number/Long (link totblCourseCodes)
ExpireDate - Date/Time


tblDepartments
--------------------
Deptartment_PK - Autonumber
DeptDesc - Text (EDU, REC)

tblCourseCodes
---------------------
CourseCode_PK - Autonumber
CourseCode - Text
CourseName - Text

tblRegistModes
--------------------
RegistMode_PK - Autonumber
Desc - Text (online, in person, hybrid)



This is a really rough layout based on the info you have provided.
Add/subtract fields to meet your needs and post the table structures for feedback on your design.

[orange]

bleyden,

Steve (ssanfu) has given you guidelines for do's and don't and has given you some structure ideas based on his interpretation of the facts you provided.

So it seems you are associated with some sort of learning/education facility. As I see/interpret your post, you want:

- to uniquely identify individuals (8-digit ID number) and their basic contact info (name, address including country, 2 phone numbers, 2 email addresses)

- to be able to add and see the programs for which they are registered

- for each program to see the course code , the department
, department (EDU, REC), course name, mode (online, in person, hybrid) and duration.

-when adding a registration, be able to see the course they are registered for, a registration number, and be able to set an expiration date.

Need more info on what a Program is; where does this fit with Department; and Course.
It would seem that a Course or Course sessions would have a start and end date, and these dates would be related to a registration. Further, it seems that Courses would be offered during a semester (school term) and a Registration would be related for a Semester or School year??

I am trying to get you to describe/tell us what you know about these things and how they relate to each other.

Good luck.

[bleyden]

Hi Steve and Orange,

Thank you both for sticking with me through this and dumbing everything down!

I followed Steve's example (he hit it right on the head) with tables and fields. To clarify, because I think I got a little carried away with nicknames, and just to sure things up:
--For each individual, I want to see their unique ID, F/M/L names, address, phone1 and 2 and email1 and 2.
--I also want to see which courses/programs (interchangeable, so let's stick with course) each individual is registered for, the version number of the course they are taking, which department that course falls under (EDU, REC), and when the course expires for that individual.

Given that, does Steve's sample tables/fields seem to be what I would need? If so, when I do the linking of the fields in tblRegistrations to the other tables/fields, would I be doing this in the relationship view? And would I drag the field from tblRegistrations to the corresponding field in the other tables, or would I drag from the other tables to the appropriate field in tblRegistrations?

Thank you so much for sticking with me!

Best,
Brendan

[ssanfu]

does Steve's sample tables/fields seem to be what I would need?

That depends on your requirements. I suggested the fields/tables based on your Post #3. Do you have other requirements/data?

when I do the linking of the fields in tblRegistrations to the other tables/fields, would I be doing this in the relationship view?

If you set the relationships in the relationship window, the joins will automatically made when you create a query and add tables.
And, yes, that is where I create the relationships.

would I drag the field from tblRegistrations to the corresponding field in the other tables, or would I drag from the other tables to the appropriate field in tblRegistrations?

I don't think it matters. But I drag from the 1 field (PK) to the many (FK) field. Then I click the check box to "Enforce Referential Integrity".