Database design for transactions

[Mark RPh]

I am having a mental block and need help. I am designing a database to track
issues at various sites. The sites are already listed for demo information. I
want to utilize the existing database 'Main Information' to find the site,
then enter the information about the issue, using other databases. And then I
want to store this as a transaction.

I think the correct approach would be to have a Issuedb and then
issuedetaildb.

Here is what I have in mind on a form:
Pull up the site by choosing the site cost center number and that fills in
the next field, which is the site name (included in the drop down as the
second column.
Then enter things such as the date, reported (y or n), regional manager
(from a drop down), administrator, director of nursing, etc.
Then go down and enter the items of the issue (missing med name, qty, reason).
There is usually only one drug per issue, but there can be more.

I am struggling with the transaction part of the db.

Any suggestions concerning my rambling?

 

[Carl Rapson]

I am having a mental block and need help. I am designing a database to
track
issues at various sites. The sites are already listed for demo
information. I
want to utilize the existing database 'Main Information' to find the site,
then enter the information about the issue, using other databases. And
then I
want to store this as a transaction.

I think the correct approach would be to have a Issuedb and then
issuedetaildb.

Here is what I have in mind on a form:
Pull up the site by choosing the site cost center number and that fills in
the next field, which is the site name (included in the drop down as the
second column.
Then enter things such as the date, reported (y or n), regional manager
(from a drop down), administrator, director of nursing, etc.
Then go down and enter the items of the issue (missing med name, qty,
reason).
There is usually only one drug per issue, but there can be more.

I am struggling with the transaction part of the db.

Any suggestions concerning my rambling?

It sounds like you need three tables (not "databases", btw) - IssueMaster,
IssueDetail, and IssueDrugs. IssueMaster would be just like you've described
it. IssueDetail would contain information relevant to the entire issue,
minus information about the drugs. IssueDrugs would contain information
about each drug relevant to the issue. The IssueMaster primary key would be
a foreign key in the IssueDetail table, and the IssueDetail primary key
would be a foreign key in the IssueDrugs table. Each entry in IssueMaster
would have one or more entries in IssueDetail, and each entry in IssueDetail
would have one or more entries in IssueDrugs.


Carl Rapson

 

[Mark RPh]

It sounds like you need three tables (not "databases", btw) - IssueMaster,
IssueDetail, and IssueDrugs. IssueMaster would be just like you've described
it. IssueDetail would contain information relevant to the entire issue,
minus information about the drugs. IssueDrugs would contain information
about each drug relevant to the issue. The IssueMaster primary key would be
a foreign key in the IssueDetail table, and the IssueDetail primary key
would be a foreign key in the IssueDrugs table. Each entry in IssueMaster
would have one or more entries in IssueDetail, and each entry in IssueDetail
would have one or more entries in IssueDrugs.


Carl Rapson

Thanks for the clarification. I think I have been confusing the table
designs and the form designs which doesn't help. I have been trying to mix
fields from different tables and haven't had much luck. Do different tables
require the use of subforms?

Thanks for the help by the way.

 

[Steve]

To answer your question ---

When designing the table structure for a database, you need to distinguish
where one-to-many relationships exist. You may have many of these in a
database. For each one-to-many relationship, you need a form/subform system
to enter the data. The data for the one side is entered in the main form and
the data for the many side is entered in the subform.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Keith Wilby]

<sarcasm>
Hey Steve, been away a few days ??
Well... welcome back !!
</sarcasm>

Every week lots of pagehits and new first-time visitors at
http://home.tiscali.nl/arracom/whoissteve.html

Pity, I was enjoying the respite.

 

[John Marshall, MVP]

Do you want to modify your sigline you use in the Access newsgroups to be
truthful? At least you do not use that sigline in the Excel newsgroups.
What kind of an Excel resource are you if last night you posted:

:What is the code to move down the cells in a column one by one?
:
:Thanks!
:
:Steve

and

:What is the code to move through the sheets of a workbook one by one and
:after processing the last sheet, stop?
:
:Thanks!
:
:Steve

to the Excel Programming newsgroup?

The "Thanks!" is a nice touch, but we all know you do not mean it.

John... Visio MVP

 

[Keith Wilby]

At least you do not use that sigline in the Excel newsgroups.
What kind of an Excel resource are you if last night you posted:

<snip>

LMAO, thanks John, I didn't see those. Fairly elementary stuff for a
"resource".

 

[Carl Rapson]

Thanks for the clarification. I think I have been confusing the table
designs and the form designs which doesn't help. I have been trying to mix
fields from different tables and haven't had much luck. Do different
tables
require the use of subforms?

Thanks for the help by the way.

Generally, you would want to base each form on a single table (or on a query
based on a single table). When joining tables for display on a single form,
it's easy to wind up with a non-updatable query. In the design described,
your main form would be based on IssueMaster. Create another form based on
IssueDetail and embed that form into the main form, setting the link field
to be the primary key of IssueMaster (which would be a foreign key in
IssueDetail). On the IssueDetail form, repeat the process to embed a subform
based on IssueDrugs, setting the link field to be the primary key of
IssueDetail. With this design, whenever a record is displayed in the main
form, the subforms will automatically display the related information from
the other tables. And, you can add new records to all three tables.

Carl Rapson