Is Access the correct app to do this...

[Guest]

Hi

I need to create a database (I think) that is basically a calendar, where
each record represents a person, but the data is chronological. ie. In the
'event' field (text), I can enter different data for each day.

This problem is most perplexing as each record seems to be linear in nature
and I can't figure out how to make it dynamic to include the extra dimension.

Any help would be most appriciated

Regards

 

[Guest]

Have you tried Excel? Try dates as column headers, and persons as rows -- the
intersection cells will be your data fields.

Send me an email if you need additional information, and I can send you a
sample.

Best,
Rajesh
(e-mail address removed)

 

[Guest]

Tried Excel?

Rows: People
Columns: Dates
Intesection Cells : Data

Example:
1 2 3 4 5 ...
Sarah Do 1 Soccer
Joe Piano Rugby
Michelle Cello Cook

I can send you a sample excel document if you want. Email me if you want me
to..

Rajesh
(e-mail address removed)

 

[Nick Coe \(UK\)]

I'm not wholly clear on what you're trying to do from your
post... :-/

That said, it looks to me as if you need two tables, one for
people with a unique autonumber key as PrimaryKey, a second
with dates with the PK from the first table as a foreign key
as a one to many from tblPeople to tblDates.

Perhaps table two should be Events. Perhaps you could
explain a little more.

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/ Download Free Demo Copy

In ear-lution typed:

 

[Guest]

sorry for being unclear,

the database is to track info for people attending an art therapy centre.
each member would require a separate record with their personal info. also we
need a calendar that can track their attendance, so we can search date ranges
to find out how many times members have attended. so where a date field can
hold - a date, i need to be able to hold a matrix so for every date i have a
boolean (attended/absent), and also a note.

I hope this makes sense and thank you for your time and help

regards

R. Mears

where I'm stumped (and i'm a complete access novice) is to be able to
incorporate a matrix-like field that can support

 

[Guest]

Access would be fine for this.

As Nick said, you will create a table for persons (with a personid primary
key autonumber), and another for attendance (with a personid refernce, and
date field, and yes/no flag).

You will need 2 forms, one for entering and maintaining people and one for
entering attendance, and all the reports that you need.

Before you start building anything, check if you can use any of the database
wizard template databases. if i remember right, there is one for T&E which
might suit your purpose.

Rajesh

 

[Joseph Meehan]

sorry for being unclear,

the database is to track info for people attending an art therapy
centre. each member would require a separate record with their
personal info. also we need a calendar that can track their
attendance, so we can search date ranges to find out how many times
members have attended. so where a date field can hold - a date, i
need to be able to hold a matrix so for every date i have a boolean
(attended/absent), and also a note.

I hope this makes sense and thank you for your time and help

regards

R. Mears

As noted, that means two tables.

One table is for the person and facts are are unique to them, Name
address, phone number etc

Table two, linked to table one includes activities etc. That may be
date and time of attendance, so each record may represent a separate
meeting, or another may be the names and birthdays of each of their children
may call for table three. Anything that may have more than one discrete
data for the individual in the first table.

This can extend further. For example if the meetings may cover many
different subjects, you may want a table linked to table two listing each
subject that was discussed and maybe additional comments like an indication
of some satisfactory resolution of that subject.

Sort of like this

Individuals:
ID: 1
Name: John Smith
Address
Phone
Date of birth (don't use age, age changes you can compute it from the
DOB)

Meetings
ID: 1
Date: 1/1/05
Subject: Acme Corp.

ID: 1
Date: 1/15/05
Subject General Printer Corp.

ID: 2
Name: Mary Smith
Address
Phone
Date of birth (don't use age, age changes you can compute it from the
DOB)

Meetings
ID: 2
Date: 1/2/05
Subject: Wild Cats Corp.

ID: 2
Date: 1/15/05
Subject General Printer Corp.

etc.

 

[Guest]

thank you both so much for the help, but can I just clarify 1 point.

the 2nd table with the date field, how do i get the date field to hold 356
separete values (yes/no for each day)

TIA

 

[Joseph Meehan]

thank you both so much for the help, but can I just clarify 1 point.

the 2nd table with the date field, how do i get the date field to
hold 356 separete values (yes/no for each day)

You have a separate record in the second table for each individual for
each date used. If you have 12 people and they each attended 10 meetings
the second table would have 120 records of meetings each with a data field
indicating the date.

It is a bit of a jump to database logic from the way we record all this
on paper. Think of it as each individual having a folder in your file and
you add a piece of paper (record) for each meeting they attend. So in the
end you would have 12 folders each with 10 sheets with each sheet indicating
the date of the meeting and any details about that meeting that you need to
record.

 

[Guest]

the calendar table will have 3 columns, and this is how the sample data will
look:

person_id date attendance
1 2/28/05 Y (this can actually be a check mark)
2 2/28/05 N
3 2/28/05 Y
1 3/1/05 N
2 3/1/05 N
3 3/1/05 Y
etc..

while reporting, you can get the information in the matrix way you need..

Rajesh

 

[Guest]

Thanks loads people, this really helps, just 1 final thing, do i have to
create 356 dated records in the second table, or can i get access to create
them from a form with say a calendar object on it if they click a date on the
calendar?

thank you all

 

[Joseph Meehan]

Thanks loads people, this really helps, just 1 final thing, do i have
to create 356 dated records in the second table, or can i get access
to create them from a form with say a calendar object on it if they
click a date on the calendar?

If you are entering data for a given date, you can enter the first one
then you can just use a control ' (single quote character) to copy the data
you just entered for that field in the prior record.

 

[Guest]

well, you can either load it all up front (problem is how far out would you
want to go?), or you can create it one month at a time, at the beginning of
the month.

Best,
Rajesh

 

[Guest]

Thanks Rajesh, I appriciate the help and advice

well, you can either load it all up front (problem is how far out would you
want to go?), or you can create it one month at a time, at the beginning of
the month.

Best,
Rajesh

 

[Guest]

Thanks Joseph, I appriciate the help and advice

If you are entering data for a given date, you can enter the first one
then you can just use a control ' (single quote character) to copy the data
you just entered for that field in the prior record.