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]

 

[Mike]

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

 

[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]