Design question about buttons

[Emma]

Hello I have a button which brings up information to discharge the client, it
has an automatic date which is populated back onto the first page to show the
date the discharge happened.

Now, we would like to expand and have several date fields thus the client
can be discharged, come back and be discharged again. The only problem is I
have to create a new table for the discharge to happen more then once. I've
tried using the table wizard but it still uses the old table. I know now this
was an intial design error on my part. Can anyone offer any advice?

Thanks Emma

 

[Arvin Meyer [MVP]]

Any table can be opened in Design View to add fields. A new table can be
easily created also without a wizard. Which version of Access are you using?

 

[Jeff Boyce]

Emma

Are you saying that you are working with a one-to-many relationship? That
is, one client can be discharged many times?

If so, you'd need to use a "child" table to store each
"incident"/"participation"/(whatever leads up to a discharge).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Emma]

Yes I'm using 2007. I would like a one to many relationship, however there's
nothing that needs to be done before the user discharges a client just click
the button and enter the data in the form that opens up.

 

[Arvin Meyer [MVP]]

Build a second table with the PatientID as the join key (Primary Key in the
Patients table and Foreign Key in the new table. Do it something like:

tblPatientVisits
VisitID PK autonumber
PatientID FK long integer (will join with PatientID in Patients table
VisitDate Date/Time
DischargeDate Date/Time
VisitDetails Memo

and any fields specific to that visit.

Now use you existing patients table to append data any of the fields which
now belong in the PatientVisits table. The autonumber will fill in by itself
so you need not worry about it for now.

Create a query, something like:

Select * from tblPatientVisits;

Now create a datasheet or continuous form with the query as a recordsource.
After joining the relationship between tables, use the continuous form as a
subform in the Patients form.

Once you are satisfied that the data is correct, go back and delete the
redundant fields from the Patients table and the controls on the main
Patients form. You should now have everything properly setup. Pat yourself
on the back and tell your boss that the problem is solved.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com