Hello all,

 

My name is David and I'm the DBA for the Museum of Contemporary Art in
Los Angeles, California.  I have inherited the TMS system from the
registrar's department.  We are currently running 9.20 on SQL Server
2000.  As soon as the guys at Gallery Systems get back to me with
upgrade instructions we will be moving to 9.35 on SQL 2005.

 

My question has to do with generating a shipping report for the
registrars.  I have the table structures from TMS but I am having
problems getting the joins done in such a way that they are all one to
one relationships.  My understanding is that for the report to run from
TMS the joins need to be one to one.  I've had some email conversations
with the guys in support (Dimitry and Brian) but so far we haven't been
able to come up with any sort of solution.  I'm reaching out to all of
you in hopes that someone out there has already slain this particular
dragon.

 

I need the following fields...

 

ObjectNumber

Lender (Constituent first and last name)

Title

Medium

Crate Number

Crate Dimensions

Crate Weight

Value

ShipFrom Geo & Address

ShipTo Geo & Address

 

I know where all of the fields are.  I have joined the tables together
but I get multiple records for each object number.  The "problem" is
that many times the objects have been shipped multiple times, or the
objects require multiple crates.  Those conditions are leading to
duplicates.

 

Any suggestions?  Any report examples that you can share?  Thanks in
advance!

 

 

David Armstrong

Database Administrator

MOCA  THE MUSEUM OF CONTEMPORARY ART