SIMPLE quiery Help

[dperry]

There is only a few simple things that I need from my queries...
I'm very sorry for the ease of these questions, but I seem to be a
"catch-all" I.T. specialist and I've never had to make a table that did
anything more than hold information

The database I'm creating is for finding out who can and cannot take
two different medications. First on the form is the employee and all of
his and his agency information. Then on a separte table is that
employee's name again, along with all of his household members, and
whether they cannot take either of the two medications. If the check
box is checked, then they can NOT take that medication. Then I need to
have a print out of all kinds of different reports from that
information... Once Again, I'm sorry for the ease of this, but google
searching didnt' really help me, and I need this database made up
quickly, so I dont have time to order a book to learn. The outputs I
put down include what I call a header (which is just some information
at the top of the page to divide up the reports). Anything that isn't
next to Header, is in a table type print out.

Some of the Inputs:

(Table One):
Name
County (By Look Up from Municipality, which I also dont know how to do)
Municipality (Drop Down List)
Agency Name
Agency Type (Drop Down List)
Agency Address

(Table Two):
HouseHold Member Name(which Includes Name)
CIPRO (checkbox)
DOXY (checkbox)

There are a few reports that I need from this, and to get them I need
to make queries.
(C) = Checked, (U) = Unchecked. Sorry if the needs see

First One:
Header:Municipality
Agency Type
Total Number of HouseHold Members
Total Of: CIPRO(C) and DOXY(U)
Total Of: CIPRO(U) and DOXY(C)
Total Of: CIPRO(C) and DOXY(C)
Then I need grand totals of all the totals...

Second:
Header: County
Municipality
Total Of: CIPRO(U) and DOXY(U)
Total Of: CIPRO(U) and DOXY(C)
Total Of: CIPRO(C) and DOXY(U)
Total Of: CIPRO(C) and DOXY(C)
Then I need grand totals of all the totals...

Third:
Header: Agency Name, Agency Address, Municipality, Agency Type
Name
Total number of household members
Total Of: CIPRO(C) and DOXY(U)
Total Of: CIPRO(U) and DOXY(C)
Total Of: CIPRO(C) and DOXY(C)
Then I need grand totals of all the totals...

Talk to me as if I'm 3 years old, because I'm extremely new to this,
and have been only self taught Access, but I dont have time to learn it
all because this database needs to be done FAST.

 

[BruceM]

I will touch on this since nobody else has. First, Access tables hold data.
That's all. They can perform a few tricks such as sorting and filtering,
but they are still data containers first and foremost.

You should not store the same name in separate tables except under special
circumstances such as needing to preserve the person's name as it was when
the record was created, and not have that record reflect a name that has
changed since then. Your situation does not seem to fall into that sort of
specialized category. Also, a table is for storing one category of
information, or one real-world entity, to borrow a term from somebody else
in this forum. In other words, Employee information is in one table, and
Agency information in the other.

It seems rather a curious thing that you are keeping track of personal
employee information regarding medication. Also, since the consequences of
taking medication incorrectly can be dire, are you prepared to take on the
responsibility of setting up a reliable system to monitor that? If so, be
aware that the learning curve for Access can be on the steep side, so
putting this together in a hurry is not something on which you can rely.

You will need at least an Employee table and an Agency table. Whether you
would use a household table may depend on whether another member of the
household could also be an employee.

In order for anybody here to assist you effectively you will need to specify
in plain language just what you need to accomplish. When you do so you will
need to spell out, for instance, the relationship between Employee and
Agency. Why do you need Employee and Agency information to appear together?
Things like that.

 

[dperry]

I work for a government agency that takes care of Anti-Bioterrorism.
These medications are a couple of the many medications that are used
when a bioterrorism attack occurs. Since our employees will be working
with the situation, they will need the medications, and their families
too. This is a database that will acknowledge whether or not the
employee or their household members can or cannot take those particular
medications. Also, we are not directly distributing the medication, we
have doctors and hospitals to do that for us, but we supply them with
the medication.

The reason for the two tables, and the employee being on both, is
because the database already existed with the single database that
included the information of the main table. We are now adding the
second table and information to include their medication needs, and
their household members too. So, I basically need to know who can and
cannot take the medications. I know that there is a huge learning curve
to Access, but since I've never had to use it to this extent, I need
to do it, and fast. The Gov. is really riding my back and I'm not
allowed to contract out the work. So, any little help toward getting me
finished is a HUGE help.

Thank you.

 

[BruceM]

It would be a great help if in replying you included the rest of the thread
in the reply. It is really a nuisance to flip back and forth between
messages to remember just what has been said.

Do employees ever get married and change their names? Do you intend to
change their names in all related records? If you use a middle initial with
a period in one record, and without a period in the other, then they are
different people as far as Access is concerned. The point here is that you
do not store data redundantly unless there is a compelling reason such as
what I mentioned earlier in the thread. You have presented no such reason.

You wrote that "The reason for the two tables, and the employee being on
both, is because the database already existed with the single database that
included the information of the main table". What was the single database?
What data are in the main table?

Again, does it EVER happen, or could it ever happen, that two family members
work at the agency? Why is there an agency field in the employee record in
your original post? You seem to be saying that this is for employees at
your agency, but then you say that there needs to be an agency field in the
employee record. Are other agencies involved? Storing the Agency in the
employee record is an appropriate choice, but better yet would be to link to
an Agency table and store the Agency ID. Agency names do change, after all.

tblEmployee
EmployeeID (primary key, or PK)
AgencyID (foreign key, or FK)
MemberID (FK)
FirstName
LastName
Agency
Other information specific to the employee

tblAgency
AgencyID (PK)
AgencyName
AgencyType
Other information specific to that agency

Assuming that family members will never be co-employees at the agency, you
will need a Family Member table:

tblFamilyMember
MemberID (PK)
FirstName
Other information specific to that family member

If family members could also be employees, you will need to make provision
for that

You will also probably need a medications table. I know you say that there
are just two medications, but that could change. Also, an EmployeeMeds
table, containing EmployeeID as a foreign key, for storing the list of OK
medications for each employee, and a MemberMeds table for the same purpose
with family members. These two could probably be the same table, as long as
MemberID is the same data type (in table design) as is EmployeeID.

I suppose you could just have two medications fields in tblEmployee and in
tblFamilyMember. Be aware that reports will be more difficult and less
flexible if you take this approach, and that updating could be a headache if
a third medication is ever added, or if one medication replaces another.

There may be other valid approaches to the design, if anybody else cares to
weigh in. I repeat that I waited a while to address this question, and
weighed in because after several hours nobody had responded.

Maybe quickly and on the cheap is not the ideal approach?