Can anyone help with this access exercise?

[harrywillson21]

Define functional requirements, i.e. develop a functional model in the form of an data flow
diagrams and a data model in the form of an entity relationship diagram for the system
described below. Implement the data model in Access database management system.
Prepare a query as required in the text.

A student tourist club wants to develop a system thanks to which students can join the club
and sign up for trips organized by the club.
When enrolling in the club, a student must provide basic information such as first name and
surname, student's ID, age and gender. These data must be stored in the system.
Of course, students can opt out of the club at any time. Then, information about them is
deleted by the system.
The club's board inputs information about new trips in the system. Information about a trip
should include its title, start date, end date, limit of participants, and type of trip (for
example: hiking, biking, horseback riding or canoeing).
Students can request information on trips, and the system should provide such information.
Students are signed up on trips on a first come, first served basis. During registration the
number of signed up students is checked. If the limit is exceeded, a student will not be
signed up. Then respectively, a student should receive information about signing up for a trip
or about rejection.
At the request of the club's board, the system should prepare a list of current club members
in alphabetical order. Prepare a query that will perform this function in Access.

Thanks for any help

[Minty]

Yes - certainly can.

But I'd like to see what you have done first and where you are stuck, so that we're not simply going to become LetUsDoYourHomeWork.com

[harrywillson21]

So, i made relations, but doesn't know how to made query and how to connect this tables with each other

[Minty]

Relationships are the connections between the different tables.

Can you post up a picture of your tables in the relationship window.
Then someone can help in pointing you in the right direction.

[harrywillson21]

That's what I did

[Minty]

Lot's of errors in that I'm afraid.

I definitely think you need to learn a little about Primary Keys and Foreign Keys before going any further.

They are well demonstrated here http://www.databaseanswers.org/data_...ents/index.htm
There are many examples on that site that may help you further down the correct path.

Have a read here as well http://www.databaseanswers.org/approach2db_design.htm

Come back once you have some improved table designs.

[orange]

Define functional requirements, i.e. develop a functional model in the form of :
-a data flow diagrams and
-a data model in the form of an entity relationship diagram for the system described below

-Implement the data model in Access database management system.
-Prepare a query as required in the text.


Did you create a dfd? If so,can you post it?

In addition to Minty's comment, I suggest you work through this tutorial from RogersAccessLibrary. It will take about 45-60 minutes, but you will learn a process that you can use with any database. You will experience the process by working through his sample. Probably the best 1 hour for learning about entities, relationships.....

Good luck.

[harrywillson21]

This is my dfd diagram

[harrywillson21]

Thanks for help to everyone

[ssanfu]

Looking at the relationship window in Post #5, I see several issues.
You shouldn't use spaces in object names.
Instead of "First Name", use "First_Name" or better yet "FirstName".
Instead of "Start Date", use "Start_Date" or better yet "StartDate".

Beware of Reserved Words.. "Type" is a reserved word in Access, plus it is not very descriptive. "Type" of what??? Wouldn't "TripType" be a better field name?
Problem names and reserved words in Access

"Limit"??? Wouldn't "SignUpLimit" be a better field name?


Why is there a 1-to-1 relationship between table "Students" and table "Registrations"? How many times does a student have to sign up? Wouldn't you think the "RegistrationDate" should be in the "Students" table?

I would rename the "Club" table to "TripInfo". Why do you have a 1-to-Many relationship between "StudentsStudentID" and "Club.Title"?
One student can sign up for many trips and one trip can have many students. Therefore, it is a Many-to-Many relationship. So maybe you need another table?? (a Junction table)


=============================
In my tables, the PK field is ALWAYS an Autonumber type field. That meany all FK fields MUST be Long Integer type fields.



Minty and orange have given you lots of studying to do...
I'll be interested to see what you come up with in your redesign.

[ZooTycoon]

How do you plan on getting the info from the students? Or how will they rigister? Will they send or hand it in and then the Admin person captures all on the system you creating?
Or would you want the student to submit all data themselves on an online system. Maybe since you only starting out now you need to make that choice.
Having a Access/Web solution or fully web might be the way to go depending on your plan.

[harrywillson21]

Can you tell me if it is correct now? What else should I change?

[kd2017]

Your relationships look good!

1) I would get rid of the forward slash in the [student/member] table. Try not to have any special characters or spaces in table names, field names, any object names, but underscores are fine.

2) Not required but on junction tables like your [enrollment] table I like to still have an autonumber field as the primary key. That said, you would still create a unique index on the student_id and trip_id pair. Re: https://stackoverflow.com/questions/...cess-databases

3) Instead of a trip_type field I would create a trip_type lookup table and the trips_info table would reference this new lookup table with an foreign key trip_type_id field. This is more flexible and makes adding new trip types in the future much easier. DON'T be tempted by lookup fields.

[CarlettoFed]

To give a complete judgment on your work you should also see how you managed the various properties of the fields and the indexes in the tables so it would be more effective if you attached a sample file.

[CarlettoFed]

Sorry but there was an involuntary sending.