By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,743 Members | 1,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,743 IT Pros & Developers. It's quick & easy.

Dealing with Duplicate Information

P: n/a
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!

Aug 24 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
A relational database is not a spreadsheet; Access is not Excel. You need
to redesign your Tables to separate "heading" type information from "detail"
type information.

You can do it, fairly easily, with a couple of "MakeTable" Queries, that you
can build, simply, in the Query Builder.

Larry Linson
Microsoft Access MVP

"afr0ninja" <mo**************@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
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!

Aug 24 '06 #2

P: n/a
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
Aug 24 '06 #3

P: n/a
Thank you both for your suggestions. I think I'm gonna take both of
your advice and try to export our current data, and re-import it in a
better format (using multiple tables.) I think that's going to be the
best way to solve my current issue, along with avoiding future
problems.

Aug 30 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.