Summarizing Unique-ness

October 13, 200619 yr

HI,

I am trying to put together an inventory solution that handles unique and non-unique items.

The items are computers with unique serial numbers but have non-unique attributes such as family, model, memory size , hd size, processor speed, condition grade, and operational status.

The unique computer records will also have child records of the parts INSIDE that computer which might need to be repaired, or exchanged with a similar part. The child part records are also unique.

I need to show the origin and the end-disposition for each unique part: for whole machines that would be origin=Purchase Order number, and for end-disposition=Invoice number.

For unique parts origin=Purchase Order AND Unique Machine Serial number (if removed from like defective machine) OR just Purchase Order if purchased from an outside vendor...

Unique part end-disposition would be Invoice AND Unique Machine Serial number OR if defective a Recycling ID number

For auditing purposes I have to reasonably show how and where the parts originated and departed.

My inventory will also contain non unique parts that I have to create purchase orders for as well.

I can't figure out how summarize in both the Purchase Order table or the Invoice table the aggregated amounts of the individual items. If I have 100 unique serial numbers 50 of which are laptops (same model) and 50 are towers (same model), I don't want my Purchase Order to have 100 line items, I want it to summarize as simply two models (two line items).

If I want to include, say 10 units of another part ID that are not unique in the same PO for a third line item.

I've tried to test this with two intermediate line item files (one for unique items and one for non-unique) but I can't get the relationships to work.

I am looking for someone to point me in the right direction if possible!

Thanks!