Re: Dealing with Duplicate Information

afr0ninja wrote:
Hello, I'm dealing with some order/inventory information where I need
it in summary format so I can retain it, add to it without duplicating,
and just make the reporting cleaner. I'll list an example of what I'm
trying to accomplish below. I'm pretty new to access, but I'm pretty
well rounded with excel. I'm not sure that I could accomplish what I'm
trying to do in excel even, so any ideas or suggestions would be
greatly appreciated.

Delivery Item Material Description Net weight
2093073741 000010 56749259 Item 1000
2093073741 900001 56749259 Item 1000
2093073741 900002 56749259 Item 1000
2093073741 900003 56749259 Item 1000
2093073741 900004 56749259 Item 1000
2093073741 900005 56749259 Item 1000
2093073741 900006 56749259 Item 1000
2093073741 900007 56749259 Item 1000
2093073741 900008 56749259 Item 1000
2093073741 900009 56749259 Item 1000
2093073741 900010 56749259 Item 1000
2093073741 900011 56749259 Item 1000

In the 'Item' column items that are 000010 are almost like a header,
Items starting with a 9 are detail about item 000010. Delivery is an
order number, Material is a SKU number. Net Weight is the quantity of
an item per line.

What I'm trying to do is get it to show me item 000010 with a total of
all the weights on the order, not listing them line by line. I've
listed just one example from the report I'm working with. The report
actually contains up to several thousand records, and has each order
listed like the example above. Also, each seperate delivery number has
it's own 000010. Meaning that if there are 5 seperate orders there are
5 000010 codes. The 9 numbers vary depending on how many items are
ordered.

Any ideas or suggestions would be greatly appreciated!


Suggestion: separate but related tables to store (1) delivery/header info and (2) detail info are a fine idea. This distinguishes "Excel mode" (a two dimensional world) from a database (a multidimensional world). Please don't take offense to this, I am only suggesting that understanding the foibles of duplication of repeated data and storing disparate information--different "facts" or "dimensions" in a table such as you exemplified--is a common hurdle for newcomers to the multidimensional database world.

Your situation is akin to the classic "order/order details" setup. You might want to look at the Northwind sample database for a working example of the latter. I would also suggest reading up on the concept of normalization, at least through the third level. Wikipedia has a good article on this.

Since you say you already have 1000's of records formatted like the sample above, well, yuck. But assuming each "delivery" value identifies (1) a single header or 000010 code and (2) related detail rows perhaps this query will get you close:

SELECT
D1.DELIVERY,
'9X TOTAL' AS DESCRIPTION,
Sum(D1.WEIGHT) AS SumOfWEIGHT
FROM DELIVERIES AS D1
WHERE (((D1.ITEM)<>'000010'))
GROUP BY D1.DELIVERY
UNION
SELECT
D1.DELIVERY,
'000010 HEADER' AS DESCRIPTION,
NULL AS SumOfWEIGHT
FROM DELIVERIES AS D1
WHERE (((D1.ITEM)<>'000010'))
ORDER BY
DELIVERY ASC,
DESCRIPTION ASC
;

I'm fairly certain this could be improved upon.
--
Smartin
.