Someone Pls Help!

[ENVY]

hey,

i am a beginner using Access and am currently trying to create a school trip database. Yet i am having some trouble setting up my tables and relationships. I have four tables - Student, Trip, Payment and Student Trip. I have the following relationship setup but cannot make it work.

how would i make this work and do i have the right tables etc? What is the relationship that is required from the payment table? hope someone can help

thanks

 

[ENVY]

pls - anyone?!

 

[Keith Nichols]

pls - anyone?!

Hi Envy,
I hear your pain. I'm a (relative) newbie and so I am of severely limited use, but can make the following observation. You haven't actually posted a question. we know you are frustrated with your DB, and we know what you have done, but we don't know what you are trying to do.

Advice:

1) Use a meaningful title to your query - "Please help"s tend to get ignored as the poster is assumed not to have tried to solve the problem themselves.

2) define what you are trying to achieve. If not, anyone who does respond may solve a problem you don't have.

A brief cursory look at the relationships shown did not throw up any flags to me (but I'm a practical virgin in these matters).

Tell us what you are trying to achieve, put it in the title, and ask an actual question.

On other threads Moderators have posted links to "how to post a question" documents. Have a rummage, find one of these, read and digest and post afresh.

I don't mean to discourage you and I'm sorry not to be able to answer your question directly, butI really don't know what you are trying to do. Also, I am severely limited in my ability to guide you due to my complete inexperience.

Regards,

Keith

 

[Keith Nichols]

pls - anyone?!

Hi Envy,
I hear your pain. I'm a (relative) newbie and so I am of severely limited use, but can make the following observation. You haven't actually posted a question. we know you are frustrated with your DB, and we know what you have done, but we don't know what you are trying to do.

Advice:

1) Use a meaningful title to your query - "Please help"s tend to get ignored as the poster is assumed not to have tried to solve the problem themselves.

2) define what you are trying to achieve. If not, anyone who does respond may solve a problem you don't have.

A brief cursory look at the relationships shown did not throw up any flags to me (but I'm a practical virgin in these matters).

Tell us what you are trying to achieve, put it in the title, and ask an actual question.

On other threads Moderators have posted links to "how to post a question" documents. Have a rummage, find one of these, read and digest and post afresh.

I don't mean to discourage you and I'm sorry not to be able to answer your question directly, butI really don't know what you are trying to do. Also, I am severely limited in my ability to guide you due to my complete inexperience.

Regards,

Keith

 

[ENVY]

hey, thankyou for your reply......

right, so i am creating a database for my ICT a level project based on a primary school. The database will be used for the school trips. The system is supposed to be able to:

store minimal informatation on the sudents
add student
delete student
search for student
create new trip - e.g. cost, location etc
delete old trips after 5 years
a way of payment for the student for a specific trip
create register for each trip
select students going on the trip
select students who have paid for the trip

then obviosly i would create some queries/reports like the register indicating succesful trips, trips within certain dates etc

i have been able to create a macro which enables me to search for a student by typing in the beginnings of their name, but i would like all the trips they have been on to come up once they are found and highlight if they have paid for the trip etc?

im having trouble with mutliple students on each trip, which is why i am wondering if my relationships are correct? for example, if student A pays for the trip, and then student B is marked as paid for trip, student A's payment seems to be overridden?

i would appreciate it if someone could point me in the right direction of how to achieve the best results for my problem. Do i have the enough tables? Do i have enough fields? how will the tables be related i.e. what fields to what?!

hope someone can help my problem

thanks

 

[neileg]

Your tblStudent, tblTrip, tblStudentTrip looks fine but you need a PK for tblStudentTrip. You could do away with tblPayment if each payment is for only one trip and is exactly the right amount. Otherwise you are going to need another many to many reealtionship between tblPayment and tblStudentTrip. If you want to retain tblPayment, you should be holding the PK from tblStudentTrip, not TripID and StudentID.

 

[GrahamT]

Your tblStudent, tblTrip, tblStudentTrip looks fine but you need a PK for tblStudentTrip. You could do away with tblPayment if each payment is for only one trip and is exactly the right amount. Otherwise you are going to need another many to many reealtionship between tblPayment and tblStudentTrip. If you want to retain tblPayment, you should be holding the PK from tblStudentTrip, not TripID and StudentID.

I agree! That's the way I would do it if it were my problem (i.e. another M-M relationship).

The resulting diagram my look confusing, but the trick is to concentrate on each part when designing your forms etc.. It helps a lot if you can print the diagram out so you can study the relationships at leisure - something Microsoft didn't seem to realise until Access 2000 (I think it was).

 

[ENVY]

ok. thankyou for your help......

attatched is what i have now done, but i am still unsure if it is correct because when i created the relationship between TblPayment and TblStudentTrip as shown, it is a 1-1 relationship.

is what i have now done correct? or should TblPayment PK go to pmntID in TblStudentTrip?

thanks

 

[GrahamT]

ok. thankyou for your help......

attatched is what i have now done, but i am still unsure if it is correct because when i created the relationship between TblPayment and TblStudentTrip as shown, it is a 1-1 relationship.

is what i have now done correct? or should TblPayment PK go to pmntID in TblStudentTrip?

thanks

Hi ENVY,

I've attached a couple of Dbs with suggested tables & relationships:

One is using a 1-1 type relationship, except there is little point in separating Payments from StudentTrips, so there is no separate payment table.

The other is using a M-M ralationship, since one StudentTrip could have many payments, and one Payment could apply to several StudentTrips.

I hope this helps.

 

[ENVY]

hey - thankyou very much for your help!

i have chosen the extra table option, as it is needed.
but now i load my student search table and it comes up with a small box saying 'enter parameter value' with student ID? why is this and how do i get rid of it?

also, if you have any tips on how it would be best to setup my forms or queries please let me know!

thanks once again for your help!

 

[GrahamT]

Hi ENVY,

Glad to help.

It sounds like you've got a spelling mistake in a table name, field name, query name or some such object. I suspect a query is looking for the value of Student ID when it sould be StudentID - or some such other finger trouble.

The built-in help for MS Access is generally very good, but you need to give yourself time to read it and experiment. For additional help, there are some good web-sites & I suggest you start with
http://www.kayodeok.btinternet.co.uk/favorites/kbofficeaccess.htm
which has several good links.

We are all individual, and the way one person likes to do their forms and stuff will be different to another person. Just have a go, but try to keep things simple for the basic functionality of the forms, subforms etc.. Once you have them in place and working, you can start to add some twiddly bits - which I prefer to do in code (I avoid macros like the plague) and most of my queries are built into the code also, but that's my preference. Another thing I avoid is the dreaded old-fashioned 'Switchboard' way of driving a Db. My preferred interface is via a floating toolbar or two containing buttons for forms & reports, for navigation, searching, editing, bookmarks and other useful functions. As I said, thats my preference and I'm sure others would disagree.

I hope this helps.

 

[ENVY]

It sounds like you've got a spelling mistake in a table name, field name, query name or some such object. I suspect a query is looking for the value of Student ID when it sould be StudentID - or some such other finger trouble.

but this only happened once i added the new table? well i will check my system for spelling errors!

thanks