Help with multiple tables

[Melinda]

I am trying to make a form/database for weekend volunteers. I need to be able
to input their Ham Radio Call Sign, Last Name, First Name, Phone number and
E-mail address. None of this info would change except maybe their phone
number.
When they show up the first time, all of this info would be entered and
stored in a table called volunteers. Every time they show up after that, only
their call sign (which is different for each person) would be used to
log/sign them in. On another line, in another table, would be a date field
which could be automatically filled in with the day's date - or, if need be,
the date could be entered manually, along with time in, time out and
area/zone worked. Then, a third table called Activity which would used if
anything happens during the "shift" that needs to be reported or discussed
later.
I can't figure out how to set up the tables so that the info from the date,
time in, time out, area/zone and activity are all associated with a
particular person. I also am not sure about how to set up the report pages,
but I figure one thing at a time for my poor brain to wrap itself around.
Any and all help would be greatly appreciated - especially the "dummies"
version.

Thanks in advance,
Melinda

ps - I've read all the tutorials and did the "training" for Access 2003 here
on Microsoft, but this is the first time I've ever tried to make anything
with Access and just can't seem to make my brain comprehend how to do it
correctly. Again, thanks. Mickey

 

[Wayne-I-M]

Hi Melinda,

The 1st thing to do (before you start creating forms, reports, etc) is to
get the tables right. ithout these nothing will very well if at all.

So you need to create a number of tables and "link" them together so you
application knows which bit of data is related to which other bits.
Something like - in your case - which person was one the radio at which time
(there could have been more than one) and on whih date was which person on
the radio and what happends during that time, etc etc etc.

The 1st table you need is to store the details of the people.

So create a table called tblVolunteers
in this table put the details of the people who will use the radios. Very
important, only put one "bit" in each field. So you would not have a field
called name, but you would have a field called 1stname and another called
2ndname (note there are no spaces in the field names - makes it simpler later
on)

So put in these fields into tbleVolunteers
UserID this is an autonumber field an the primary field
User1stName text
User2ndName text
UserTelephoneNumber text (even though you are imutting numbers it
really text
UserEmailAdress Hyperlink
UserHamName text
UserAddress1 text
UserAddress2 text
UserAddress3 text
UserAddress4 text
UserAddressZipPostCode text
etc
etc
Basically anything that you need to about that user.

Note on field names. Everyone has thier own ideas so I may be wrong.....I
tend to use very simple to understand names (like User1stName,
UserEmailAdress) as this make it simple later on when you start running
codes. But this is up to you.

Next you need another table maybe called tblLogRecord which these fields
LogID this is an autonumber field an the primary field
UserID Number (this is important that it's number)
LogTimeIn The is a time/date field
LogTimeOut The is a time/date field


Your last table for now will be tblActivity
This is your linking table between the other 2 tables.
ActivityID this is an autonumber field an the primary field
UserID Number field
LogID Number field
ActivityMemo Memofield
ActivityComplete Yes/No field
ActivityAction Text

I have added in ActivityComplete which would be a simple tick box that you
could use to show the activity has been sorted out (any problems discussed,
etc).
ActivityMemo would be where you would record the items that happend during
the users time on the radoi.

Of course you can add other field to the tables but you would now this and I
wouldn't.

The next thing to do is to link all these tables together (it's simpler
somtimes if you do this before you start adding data o the records).

Open the relations ships window and show the 3 tables
Drage the UserID and LogID into the tblActivity and drop them over the
same field in this table. This will create the link.

Right click the thin lines and select Enforce Refential Integeraty - this
will stop a record being create in a table if there is not a correpssponding
record in the other table.
In other words you can not have a reocrd in ActivityLog for a user who des
not exist, etc.


Once you have your tables sorted then post back wth other question about
queries, forms, reports, etc if you have any other problems and I am sure
someone will be able to answer you if they have time


Good luck - hope this helps a little

 

[Wayne-I-M]

Oh, I forgot to say.

I have use 3 tables as there may be more than one "thing to talk about"
during each time/date. So please don't be tempted to just put a memo field
in tblLogRecord. This is a common mostake (that many people not used to
referential databases make) and it would be you would end up with the same
data in different tables - BIG mistake.

So please do use the Many to Many setup I gave it really will make it work
better (eve if it take a few mins more to create)

 

[Melinda]

Wayne,
Thank you very much for your quick response. I followed your instructions
and got the databases all set up. I had a little "snag" because I couldn't
remember where to find the relationships window - then looked at the "table
that data" training to remind myself that it's under the tools.
One question, though, the relationships window only showed one-to-many, not
a many-to-many, but it seemed to do it pretty much automatically. Is that
correct, or did I misunderstand what I was seeing?
Now, off I go to work on setting up the form to enter all the information
that will be needed from/for each database. After that, set up the reports to
have them all ready to "spit out" after this volunteer season is over with.
Amazing how simple it seems once you have someone who knows what they are
doing.
The worst (?) thing about this is that I was asked to do this with less than
2 weeks before our season starts. The person "in charge" was tired of having
to deal with all the "hard" data (i.e. paper, paper and more paper). To give
you a little better idea of what we do; every year between Thanksgiving and
Christmas, we Hams volunteer at a local Shopping Mall on the weekends. We
need to keep track of who shows up when, which section they work and what if
anything happened.
Again, Thanks so very much,
Mickey

ps - now lets see what lovely words I have to say to my computer as I work
on the forms and reports. All stations, please stand by for more crazy
questions from someone who doesn't know 1 - what to do, 2 - how to do it or 3
- why she's doing it! He He. Mickey

 

[Wayne-I-M]

Hi Melinda

In the relationships window you will set up a 1 to many relationship but
when you do this to 3 or more tables its called a many to many

Think about the standard example - a school

There are man classes and many pupils.
Each class can have many pupils and each pupil can go to many classes.

In this case you would have

ClassID
PupilID

and a linking table
TableID
ClassID
PupilID

so it is (like your situation) a Many to Many relationship.

I would NOT base the forms on any tables
I "would" create a query based on the tabels and then base your forms,
reports on this (or these) queries.

There are lots of reasons for this such as you can sort a query. You can add
calculations, etc

DON'T do you reports yet. They are the end result. You should get the
database up and running 1st then then just look at the reports as a snapshot
of what you have on your database - or course reports can do more than this
but I thik that is what you are looking for

2 weeks should be OK as long as you keep it very simple - you can always add
to the application later. ust get it running first, change anything you find
is not working and then later your can look at improving it.

Good luck


you

 

[Melinda]

Thanks again Wayne.
Now, I'm going to sleep before I go to my "real" job. I'm going to work on
it some more tomorrow morning after I get home from work and eat
supper/breakfast.
I will post how it's going and if I have any hair left, and post any more
questions I will probably have.
Mickey

 

[Rich Wonneberger]

Wayne,

Where would you put the memo field?
If in another table, how would you link it to what?

TIA
Rich W.

 

[Wayne-I-M]

Hi

If it were me I would put it (as in my 1st answer - and explained in my 2nd)
in the linking table which in my example would be tblActivity

The reason for this is that there "may" be more than 1 "thing to talk about"
on each shift (or whatever it called).

If you were to put it in the in the tblLogRecord then you would need to
record the event details more than once and I have always found (and been
told) that you should only store unquie data once. If say John and Jill were
in the shopping center they could have more than incedence and in this case
it would be an entry into tblActivity not the log (as the log is a reocrd of
the whole period).

Of course there are times that you may want to store the same details more
than once but this case is not one of these

 

[Melinda]

I would like to have it set up, so that after a person has their info put
into the database on the first day - if they come back, say 3 days later, I
just put in their call sign and it brings up their user info but leaves the
time and activity areas blank. That way I can put the new time/date, area
worked, etc. in there for the next time they show up. Is that possible? Or
should I just leave well enough alone?
Mickey

 

[Rich Wonneberger]

Wayne,

Makes sense. I have to re-read the messages. I didn't catch the use
for the 2 tables.

Thanks
Rich W.

 

[Melinda]

ok. I have the database tables all set up and saved. I have made forms to
input the information. I have it where it's saving the info in the
"Volunteers" form, but when I try to put in the date, time in, time out and
area worked in a sub-form, it won't save that info.
What am I doing wrong?
I have to have this all done by Thursday and there are 2 days this week I
won't get anything done because of my schedule. My poor brain feels like
mush!!
Thanks,
Mickey

 

[Melinda]

Wayne,
just wondering, you had said not to base my forms on the databases, but to
use queries instead. That being the case, how can I get my information put
into my databases? Isn't that the reason for the forms?
Maybe I just missed something in between my eyes reading and my brain
understanding it all.

Thanks,
Mickey

 

[Melinda]

well, I found the problem - it was the memo field in the user activity
report. I changed that to a text field and it saved the info that it let me
enter. Any idea why that would be a problem? I didn't play with it to see if
it's a problem just with the subform or not, but I'll be trying that in the
next couple of days just to check.

Thanks again,
Mickey

 

[John W. Vinson]

just wondering, you had said not to base my forms on the databases, but to
use queries instead. That being the case, how can I get my information put
into my databases? Isn't that the reason for the forms?

He did not say "databases" - he said "tables". A Database (in Access jargon)
is a container for multiple forms, reports, queries, tables, and other
objects.

Maybe I just missed something in between my eyes reading and my brain
understanding it all.

A Query has no independent existance. It's just a tool for selecting,
combining, and arranging data from a Table. If you update a Query you're
updating the Table upon which the query is based.