Access Problem

watsgone

Hi all,

I am try to update an access db. I am out of practise with Access
My question is how do you make a multi rowed field. for example I want to link a person to the many different course they have done.

Any help would be great.

FindingNemo

watsgone wrote: »

Hi all,

I am try to update an access db. I am out of practise with Access
My question is how do you make a multi rowed field. for example I want to link a person to the many different course they have done.

Any help would be great.

Hi,
You should have three tables for starts
A users table and a courses table and a userscourse

example

usersTBL

user_id user_name
1.............john
2.............steven
3.............mark

coursesTBL

course_id course
1....................IT
2.....................Engineering
3...................Snorkelling

UsersCourseTBL

User_id Courise_id
1 ................ 1
1..................2
2..................1
3...................3

tables have a relationship

hope that helps

watsgone

Thanks,

i have a user table, no primary key as the prefect key is their id number but many have none, so I am working as the firstname,lastname as the primary key.
In the userCourseTBL I would like to add a date completed and a section option to each user- so I know who did what when.

The course table is nearly complete, I am unsure now to make and link these relantionships properly.
Confuse in regards to master and child?

FindingNemo

watsgone wrote: »

Thanks,

i have a user table, no primary key as the prefect key is their id number but many have none, so I am working as the firstname,lastname as the primary key.
In the userCourseTBL I would like to add a date completed and a section option to each user- so I know who did what when.

The course table is nearly complete, I am unsure now to make and link these relantionships properly.
Confuse in regards to master and child?

ok, not having a primary key in the usersTBL is not a good idea.
put one in,
it's no problem adding in the date/time field into the usercourseTBL.

when you're in tables, you can right click, and then click relationships, have you done that ?

watsgone

What do you suggest as a primary key, autonumber is not a good option IMO,
Should I create a login name for them? 521 format eg 5 letters of last name, 2 letters of first name, and middle inital?

FindingNemo

watsgone wrote: »

What do you suggest as a primary key, autonumber is not a good option IMO,
Should I create a login name for them? 521 format eg 5 letters of last name, 2 letters of first name, and middle inital?

That seems far too much overhead for this IMO.
I would suggest just a user_id, with autonumber,
why do you feel this is no good for a primary key, it's standard practice.

http://office.microsoft.com/en-us/access/HA100140991033.aspx#2 (recommended from Microsoft also)

is_that_so

I would agree. Autonumber is quick and convenient unless your system absolutely requires something more complex. Seeing as you are using Access I doubt if this is the case. The use of autonumber effectively negates the need for you to come up with a very complex identification system for a field that Access can set up as unique anyway. You could create a username field instead. I'd imagine that user or system validation will be based on some other fields like a password and username field combination.

watsgone

FindingNemo wrote: »

I would suggest just a user_id, with autonumber,
why do you feel this is no good for a primary key, it's standard practice.

there is no standard user id in this system, only firstname,lastname.
after a certain lenght of time a user is removed to another table past users, I am afraid that the autonumber may conflict between the two tables

FindingNemo

watsgone wrote: »

there is no standard user id in this system, only firstname,lastname.
after a certain lenght of time a user is removed to another table past users, I am afraid that the autonumber may conflict between the two tables

hmm...not been negative but just trying to help here.
It sounding more and more like this isn't the most effect and best designed system ?
what you use as a primary key shouldn't really have an effect on something else if you know what I mean, primary keys should not be visible to a user in reality, they're only for internal use only really

watsgone

oh I know and I totally agree.
I am trying to make the existing one better, I dont want to scrap the old completely.
The primary key is a worry. there is an ideal primary key that would cause no conflict as in a user-id but there is no formal record for each member and I dont want null values

FindingNemo

cool,
so you sorted now then :-)

AccessQuery

watsgone wrote: »

there is no standard user id in this system, only firstname,lastname.
after a certain lenght of time a user is removed to another table past users, I am afraid that the autonumber may conflict between the two tables

Evening,
You should have something more unique than just first/surname to id your students. How many Paddy O'Reilly's are there in Ireland for example? To manage your system/process efficiently you'll need a unique Id for each. What if in the future you want to store grades etc all of the "Paddy O'Reilly" grades will be completely mixed up. An Id number field set as auto number will work fine.

Also instead of holding past students in a different table why not create another [yes/no] field in your master student table called "Past Pupil" or "Current Pupil" or such like. You'll have instant access to all pupil records and should a past pupil come back you'll just have to "click" them back on.

If you do decide to store past pupils on a different table only the master table Id field should be auto number and there'll be no conflicts.

watsgone

thanks that sounds much better.

Seth Brundle

AccessQuery wrote: »

How many Paddy O'Reilly's are there in Ireland for example?

267 according to the last census IIRC!

AccessQuery

watsgone wrote: »

thanks that sounds much better.

Are you planning to store other info on students i.e. address, dob etc? If you are you should probably checkout the data protection requirements (www.dataprotection.ie).

The application process is simple and the fees are very low, that's assuming you're a small operation.

If you're running courses for the general public or school kids it wouldn't look good having to spend a day in court compliments of the Data Protection Commissioner. Better safe than sorry!

watsgone

this is for a non- profit club. The head office deals with the data protection fees side of it.
I have decided to go with these tables-
courseInfo TBL
members TBL
pastMembers TBL
courseCompleted TBL

I hope to create a form to make it easier for the user to input the courses completed and by who.
Inside in there if I can figure it out, I want to put a look up column that the user can check the code
against the name.

This sound approaite?

Some day a front end will have to be put on this but for now if I can this sorted I will be happy.

AccessQuery

watsgone wrote: »

this is for a non- profit club. The head office deals with the data protection fees side of it.
I have decided to go with these tables-
courseInfo TBL
members TBL
pastMembers TBL
courseCompleted TBL

I hope to create a form to make it easier for the user to input the courses completed and by who.
Inside in there if I can figure it out, I want to put a look up column that the user can check the code
against the name.

This sound approaite?

Some day a front end will have to be put on this but for now if I can this sorted I will be happy.

For what it's worth I wouldn't run with the pastMembers Tbl. The data transfers will have to be managed, they'll need controls put together. The [Yes/No] field I mentioned previously would save you an awful lot of needless work and from a user point of view it'd make life simpler.

I'd suggest the following table layouts

CourseInfo TBL
Course Code
Course Description
Course Start Date
Course End Date

Members TBL
Member ID
Member First Name
Member Surname
Member Past Pupil

CourseCompleted TBL
Member ID
Course ID
Course Finished by Member (Y/N - Optional)
Course Score of Member (Optional)

As a simple lookup mechanism you could use the "DLookup" function to get info from any table and then display the returned value in a non-entry/display field.

You can also have a lookup on the [CourseCompletedTBL]![Course ID] field and when that field is used anywhere on a form you'll have an automatic drop down list of courses available as held on the [CourseInfo TBL].

I hope this is helpful.

watsgone

Thanks its a great help,

i think I will remove the past members details altogether I can run query to show only current ones in their own separte form for data entry for the user. I was thinking of putting a switchboard on it though I am afraid it will make things needlessly complex and the user does really look for one any opinions?

AccessQuery

watsgone wrote: »

Thanks its a great help,

i think I will remove the past members details altogether I can run query to show only current ones in their own separte form for data entry for the user. I was thinking of putting a switchboard on it though I am afraid it will make things needlessly complex and the user does really look for one any opinions?

Evening,
Your users only need 1 menu and two forms

1) Menu to give simple access to forms below...
2) Form 1 for Entry/Update of Course details
3) Form 2 for Entry/Update Member details and Courses Taken

Don't waste your time fiddling with queries on multiple forms to display various data sets - Past Pupils / Current Pupils. The tickable field on form 2 will save you all that.

Anything else is needless over kill. Where users are concerned apply the K.I.S.S principle - Keep It Simple Stupid.

Oh by the way when you've finished, I'll be surprised if you don't get the line "oh that's great but can I have it do [this/or that] as well?"

Good luck

watsgone

Thanks for all your help, maybe you could help me out with one more thing.
I wanto to read many rows into a textbox on a form eg a user has done say three course, In a form with the users name and a text box that would contain the 3 rows of the course names completed?

Beano

you wouldnt use a text box for that (well you could but your wouldnt). Better to use a listbox or a subform. Havent written anything in access in years so I cant give specifics off the top of my head. SOme sort of grid control could be used as well.

AccessQuery

watsgone wrote: »

In a form with the users name and a text box that would contain the 3 rows of the course names completed?

I'd advise you to leave the screens as flexible as possible so don't restrict yourself to just displaying 3 course records. Have something like this on the form....

Display Student data on top 3/4 of form and on bottom 1/3 display all courses attended by that student. You could use a sub form to display a line of the course data for the student. As the sub form fills it should allow you scroll through the students course details.

If the screen data is cramped split it between TABed sub forms....
Top of the main/master form will display Student Name details
TAB 1 could display dob, address etc
TAB 2 could display all the courses attended by that student.

The TAB effect will make it look and feel very professional and allow you store and display more data per student moving forward.

Hope this helps.

AccessQuery

How goes it?