Database setup problem

[cpocpo]

Hello all!

Still having a fundamental problem making my database work. (I
received alot of help in earlier posts, and this is the last thing to
make this thing workable.)


Basically, I have 40 employees, some military, some civilian. As well
as tracking their basic personal information (SSN, address, spouse
name, etc.), I am keeping track of their "recurring
items" (birthdays,
anniversaries, medical exams, dental exams, EEO training, HR
training,
etc.).Some of these are every 1 year, some are every 3 years, etc.
When the database is opened, a report should pop up with "The
following events are due this month: Mr. X dental exam due ________,
Mrs. J has a birthday on _______. Also, the following items are
overdue: Mr. P's dental exam was due on _________."


So, each employee can have many different "events", each having their
own date of accomplishment.


Setup:
1) tblEmployee with 3 fields: EmployeeID (autonumber, PK), LName
(text), and FName (text).
2) tblEventType with 3 fields: EventTypeID (autonumber, PK), EventType
(Text) and Periodicity (Number) [This would have the list of events:
dental exam, physical exam, EO training, etc.]
3) tblEvents with 4 fields: EmployeeID (number), EventTypeID (Number),
EventID (autonumber, PK)
and EventDate (Date/Time). [This would bring together the person, the
event they participated in, and the date they did it]


Relationships:
1) EmployeeID (from tblEmployee) to EmployeeID (from tblEvents)
one-to-many, no referential integrity, no cascades, join type 1
2) EventTypeID (from tblEventType) to EventTypeID (from tblEvents)
one-to-many, no referential integrity, no cascades, join type 1

Created a query with the wizard pulling LName and FName from the
first
table, EventType from the second table, and EventDate from the third.
This
gives me a form with the FName and LName and a subform with the other
two.


Now, I can get the name to update but when I enter the date in the
subform, I get an error "ding" with every keystroke. It seems that the
tables aren't "talking" to each other. And this probably has to work
for the popup reminder report to work (which I
will need help with also.)


??


Lost for sure Guy

 

[Guest]

Add to tblEvents a field EventDone (Date/Time) to record completion.

Add to tblEventType a field to indicate if the follow on event should be
based on last scheduled plus Periodicity OR EventDone plus Periodicity.

Have an append query to generate a new tblEvents record when EventDone is
filled in.
For a one-to-many you need referential integrity and cascades updates.

Open the subform in design view, click on menu VIEW - Properties. Make sure
the Master/Child links are set to the field names used in the one-to-many
relationship.
You should not update the name, only add new names. If you update it
changes the record in the employee table.
With cascade updates they should talk to each other.
--
KARL DEWEY
Build a little - Test a little

Hello all!

Still having a fundamental problem making my database work. (I
received alot of help in earlier posts, and this is the last thing to
make this thing workable.)


Basically, I have 40 employees, some military, some civilian. As well
as tracking their basic personal information (SSN, address, spouse
name, etc.), I am keeping track of their "recurring
items" (birthdays,
anniversaries, medical exams, dental exams, EEO training, HR
training,
etc.).Some of these are every 1 year, some are every 3 years, etc.
When the database is opened, a report should pop up with "The
following events are due this month: Mr. X dental exam due ________,
Mrs. J has a birthday on _______. Also, the following items are
overdue: Mr. P's dental exam was due on _________."


So, each employee can have many different "events", each having their
own date of accomplishment.


Setup:
1) tblEmployee with 3 fields: EmployeeID (autonumber, PK), LName
(text), and FName (text).
2) tblEventType with 3 fields: EventTypeID (autonumber, PK), EventType
(Text) and Periodicity (Number) [This would have the list of events:
dental exam, physical exam, EO training, etc.]
3) tblEvents with 4 fields: EmployeeID (number), EventTypeID (Number),
EventID (autonumber, PK)
and EventDate (Date/Time). [This would bring together the person, the
event they participated in, and the date they did it]


Relationships:
1) EmployeeID (from tblEmployee) to EmployeeID (from tblEvents)
one-to-many, no referential integrity, no cascades, join type 1
2) EventTypeID (from tblEventType) to EventTypeID (from tblEvents)
one-to-many, no referential integrity, no cascades, join type 1

Created a query with the wizard pulling LName and FName from the
first
table, EventType from the second table, and EventDate from the third.
This
gives me a form with the FName and LName and a subform with the other
two.


Now, I can get the name to update but when I enter the date in the
subform, I get an error "ding" with every keystroke. It seems that the
tables aren't "talking" to each other. And this probably has to work
for the popup reminder report to work (which I
will need help with also.)


??


Lost for sure Guy

 

[rolaaus]

Sometimes, and I'm not 100% sure if this applies in your situation, but when
your sub-form's recordsource query is based on multiple tables, it will
display your information just fine, but if you try to modify anything it
behaves just like what you describe (getting a ding tone with each keystroke).
Making any modification when you are pulling queries from multiple tables is
tricky at best, and I'm not sure if it is even possible (though I might be
wrong, I *DO* know there are limitations and can cause headaches in at least
some situations).

Hello all!

Still having a fundamental problem making my database work. (I
received alot of help in earlier posts, and this is the last thing to
make this thing workable.)

Basically, I have 40 employees, some military, some civilian. As well
as tracking their basic personal information (SSN, address, spouse
name, etc.), I am keeping track of their "recurring
items" (birthdays,
anniversaries, medical exams, dental exams, EEO training, HR
training,
etc.).Some of these are every 1 year, some are every 3 years, etc.
When the database is opened, a report should pop up with "The
following events are due this month: Mr. X dental exam due ________,
Mrs. J has a birthday on _______. Also, the following items are
overdue: Mr. P's dental exam was due on _________."

So, each employee can have many different "events", each having their
own date of accomplishment.

Setup:
1) tblEmployee with 3 fields: EmployeeID (autonumber, PK), LName
(text), and FName (text).
2) tblEventType with 3 fields: EventTypeID (autonumber, PK), EventType
(Text) and Periodicity (Number) [This would have the list of events:
dental exam, physical exam, EO training, etc.]
3) tblEvents with 4 fields: EmployeeID (number), EventTypeID (Number),
EventID (autonumber, PK)
and EventDate (Date/Time). [This would bring together the person, the
event they participated in, and the date they did it]

Relationships:
1) EmployeeID (from tblEmployee) to EmployeeID (from tblEvents)
one-to-many, no referential integrity, no cascades, join type 1
2) EventTypeID (from tblEventType) to EventTypeID (from tblEvents)
one-to-many, no referential integrity, no cascades, join type 1

Created a query with the wizard pulling LName and FName from the
first
table, EventType from the second table, and EventDate from the third.
This
gives me a form with the FName and LName and a subform with the other
two.

Now, I can get the name to update but when I enter the date in the
subform, I get an error "ding" with every keystroke. It seems that the
tables aren't "talking" to each other. And this probably has to work
for the popup reminder report to work (which I
will need help with also.)

??

Lost for sure Guy

 

[Jamie Collins]

For a one-to-many you need referential integrity and cascades updates.

"Need"? There is no causation between relationship type (1:1, 1:m,
etc) and CASCADE referential actions.

Jamie.

--