A little help

[mcjanes]

I'm completely new to Access so bear with me...

I'm currently making a database for a University Music School. We need
to keep track of all instruments, including who checks out instruments,
date they're returned, etc.

We also need to keep track of a repair history...Somehow there needs to
be a field that says the last repair date of each instrument, but there
also needs to be a table keeping track of each repair (multiple repairs
for each instrument) including the date, invoice number, cost, notes,
etc.

Any ideas on how to implement this repair history as painlessly as
possible?

 

[John Vinson]

I'm completely new to Access so bear with me...

I'm currently making a database for a University Music School. We need
to keep track of all instruments, including who checks out instruments,
date they're returned, etc.

We also need to keep track of a repair history...Somehow there needs to
be a field that says the last repair date of each instrument, but there
also needs to be a table keeping track of each repair (multiple repairs
for each instrument) including the date, invoice number, cost, notes,
etc.

Any ideas on how to implement this repair history as painlessly as
possible?

You'll need a table for each type of Entity: an Entity is a person,
thing, or event of importance to your application. The entities that I
see here are Instruments, Students, Checkouts (an event entity),
Repairs (another event), and probably some lookup tables. There'd also
be an Ownership table (since one student might have several
instruments and one instrument might be owned, over time, by several
students).

You'ld start with building your tables, making sure there aren't any
repeating fields; then create Forms (with subforms) to do the data
entry. We'll be glad to help with specific problems.

John W. Vinson[MVP]

 

[Joseph Meehan]

I'm completely new to Access so bear with me...

I'm currently making a database for a University Music School. We
need to keep track of all instruments, including who checks out
instruments, date they're returned, etc.

We also need to keep track of a repair history...Somehow there needs
to be a field that says the last repair date of each instrument, but
there also needs to be a table keeping track of each repair (multiple
repairs for each instrument) including the date, invoice number,
cost, notes, etc.

Any ideas on how to implement this repair history as painlessly as
possible?

I suggest starting with a master table. It should list all the
instruments. You might want to include the date acquired, cost, punched
from, warranty information, stuff like that. Just stuff that relates one
time to that specific instrument.

Next you will want a repair table. All repairs go in there. It might
include who did the repair, why, the date done the cost, invoice numbers,
who paid, maybe a comments field to record things like nice job or it came
back scratched. Again only things that relate to that specific repair.

Next you want a checkout table. There you will include who checked it
out, when, when it is due back, deposits, again anything that directly
relates to that specific loan.

If you have repeated loans the the same person, then you would want a
person table. This one would have name address etc.

Now when you load one out you establish a record in the check out table
and then connect that to a person in the person table, and an instrument in
the instrument table.

See if you can get that much done. Once that is done it will make
reports like who has what and when the last repair on this or that much much
easier.

Take a look at the Northwind sample database. It has this kind of table
relations.

 

[Guest]

I would assume that the "instrument" would be a common factor to all desired
tracking. Make a seperate table with each desired track and the link them
together base on the common "instrument" field. that way when run your
queries you will get the results you need.

 

[Al Camp]

Basically, Instruments to Lendings is a One to Many realtionship.
So is Repairs...
A typical Instrument form would show the Instrument on the main with multiple lendings
listed in an associated Lendings table/subform, related by InstrumentID.
Repairs would be the same. A table of Repairs associated to an Instrument via
InstrumentID. What data you capture about the repair transaction is your choice.

Since Lendings and Repairs are such disparate subjects/values, there's no "easy" way
out... you'll need at least one related table for each.

 

[mcjanes]

Thanks for the suggestion, guys...

I pretty much did exactly what Joseph Meehan suggested before
posting...here's a screenshot of the relationship view:

http://img415.imageshack.us/my.php?image=untitledku0.png

The problem I'm having is how exactly to store the data for repair
history for each instrument, WITHOUT having to create a "Repair
History" table for EACH instrument

Making a single "Repair History" table to store all the repair notes of
every instrument seems to be the most logical...the problem is, that
table could get pretty long. Also, they only want to be able keep a
log of a maximum of 20 repairs per instrument....that way, if an
instrument reaches 20 repairs, it's clear the instrument isn't worth
the cost of repairing and needs to go in favour of purchasing a brand
new one. Any thoughts? Would making this one table and using a Query
be the best way?

thanks

 

[Joseph Meehan]

Thanks for the suggestion, guys...

I pretty much did exactly what Joseph Meehan suggested before
posting...here's a screenshot of the relationship view:

http://img415.imageshack.us/my.php?image=untitledku0.png

The problem I'm having is how exactly to store the data for repair
history for each instrument, WITHOUT having to create a "Repair
History" table for EACH instrument

Making a single "Repair History" table to store all the repair notes
of every instrument seems to be the most logical...the problem is,
that table could get pretty long. Also, they only want to be able
keep a log of a maximum of 20 repairs per instrument....that way, if
an instrument reaches 20 repairs, it's clear the instrument isn't
worth the cost of repairing and needs to go in favour of purchasing a
brand new one. Any thoughts? Would making this one table and using
a Query be the best way?

Since Access can handle thousands of repairs for each of hundreds of
instruments, there is no problem. You just add a new record for each
repair.

 

[Al Camp]

McJanes,
First, looking at your realtionships...
I don't think tblStudents and tblCourses needs to be "related" to tblCheckOut. They
are simply value tables to fill in the StudentID on the checkout form. Actually Students
should be related to a new "tblCoursesTaken" table, and stand alone on their own, with
your tblCourses table providing a master list of Courses to select from... (against each
student's record).
Same with tblMajors to Students.

Once a Checkout record has indicated a Student ID from Students, the related
CoursesTaken table will provide an list of CoursesTaken for that student only.

The same with Rooms. There's no need to "relate" it to Instruments. A combo box in
Instruments, based on Rooms will provide the values you need for your Instrument Location
field.
------------------------------------------------
The RepairHistory field in Instruments won't work. Given that an Instrument can have
up to 20 repairs were you planning to add 20 Repair Date fields to Instruments, 20 Costs,
20 ReturnDates...etc...?? Multiple repairs against an instrument is just another One to
Many realtionship... just like tblInstruments to tblCheckouts, related via the
InstrumentID

You wrote...

The problem I'm having is how exactly to store the data for repair
history for each instrument, WITHOUT having to create a "Repair
History" table for EACH instrument

You don't create a table for each repair...
Only one tblRepairs is all you need. All repairs on all instruments go in there, but
because each repair record is associated with just one instrument, when you go to the
Clarinet with ID=1524 record, only repairs against that clarinet will display in the
repairs subform, becasue of the One to Many realtionship. Just like when you go to that
same record, only Checkout records against that unique InstrumentID will be displayed...
even though the tblCheckout (like tblRepairs) has ALL the checkouts against all
Instruments.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[John Vinson]

The problem I'm having is how exactly to store the data for repair
history for each instrument, WITHOUT having to create a "Repair
History" table for EACH instrument

Certainly you wouldn't do that. Just have an InstrumentID field in the
Repair table.

Making a single "Repair History" table to store all the repair notes of
every instrument seems to be the most logical...the problem is, that
table could get pretty long.

What's your impression of "pretty long"? An Access table with
20,000,000 records is getting quite hefty. Two million records is
quite managable. You're not likely to get ANYWHERE near close to
"long".

Also, they only want to be able keep a
log of a maximum of 20 repairs per instrument....that way, if an
instrument reaches 20 repairs, it's clear the instrument isn't worth
the cost of repairing and needs to go in favour of purchasing a brand
new one. Any thoughts?

Periodically run a "Junker" report based on a Totals query, grouping
by Instrument and counting Repairs, with a criterion >20 on the count:

SELECT InstrumentID, <other instrument fields>, Count(*)
FROM Instruments
INNER JOIN Repairs
ON Repairs.InstrumentID = Instruments.InstrumentID
GROUP BY Instruments.InstrumentID
HAVING Count(*) > 20;

Would making this one table and using a Query
be the best way?

Well... one Repairs table certainly.

John W. Vinson[MVP]

 

[Jamie Collins]

Periodically run a "Junker" report

I disagree. My interpretation is the OP want to *prevent* the count
from exceeding 20. Running a report periodically may not prevent an
instrument being repaired when it should have been written off.

I can think of a couple of ways of achieving this:

1) Quick and dirty, using familiar tools. Create an integer column (NOT
NULL/Required = True) named, say, repair_occurance_number. Create a
validation rule e.g. BETWEEN 1 AND 20. Create a compound UNIQUE
constraint with your InstrumentID.

2) Cleaner and more elegant, using a less familiar tool. Create a Jet
4.0 table-level CHECK constraint e.g.

CREATE TABLE RepairHistory (
instrument_ID INTEGER NOT NULL
REFERENCES Instruments (instrument_ID),
CONSTRAINT RepairHistory__max_20_repairs
CHECK (20 >= (
SELECT COUNT(*)
FROM RepairHistory AS T2
WHERE RepairHistory.instrument_ID = T2.instrument_ID
)
),
....<<other columns>>
);

Creating a CHECK constraint requires ANSI-92 mode () e.g. use an ADO
connection.

It is also worth noting that constraints in a history table are
difficult to define; even an effective primary key requires CHECK
constraints. For an example, see:

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

Jamie.

--