Sub-grouping Inventory Data

G

Guest

Hello,

How do I get a table of inventory data to go from looking like this:

[Stk Nmber].....[UI].....[ITEM NAME]................[O/H].......[IC_Group]
005643780.......EA.......LAMP, HOUSING.................1........0152A
005644783.......EA.......REMOTE, CONTROL...............0........0924B
005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
005654789.......EA.......TABLE, OAK 3. x 5.............1........
005655436.......EA.......HINGE, ASSY...................3........
006655538.......EA.......BOOK, DISPLAY, RED............3........
006656402.......EA.......RUG, DISPLAY, BLUE............1........
006656406.......EA.......CHAIR, OAK....................2........
007665759.......EA.......CUST SAMPLE...................2........
009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
015718722.......EA.......FOOTSTOOL, BIRCH, 3. X 5......6........
015733146.......EA.......FRUIT, DISPLAY................0........0451C
016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
018749717.......EA.......FRUIT, SMALL..................1........0451C
018756024.......EA.......CENTER, STONE.................0........

to this and/or in a report to print out:

[Stk Nmber].....[UI].....[ITEM NAME]................[O/H].......[IC_Group]
005643780.......EA.......LAMP, HOUSING.................1........0152A
......016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
005644783.......EA.......REMOTE, CONTROL...............0........0924B
......013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
......009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
005654789.......EA.......TABLE, OAK 3. x 5.............1........
005655436.......EA.......HINGE, ASSY...................3........
006655538.......EA.......BOOK, DISPLAY, RED............3........
006656402.......EA.......RUG, DISPLAY, BLUE............1........
006656406.......EA.......CHAIR, OAK....................2........
007665759.......EA.......CUST SAMPLE...................2........
009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
......005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
......005644783.......EA.......REMOTE, CONTROL...............0........0924B
015718722.......EA.......FOOTSTOOL, BIRCH, 3. X 5......6........
015733146.......EA.......FRUIT, DISPLAY................0........0451C
......018749717.......EA.......FRUIT, SMALL..................1........0451C
016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
......005643780.......EA.......LAMP, HOUSING.................1........0152A
018749717.......EA.......FRUIT, SMALL..................1........0451C
......015733146.......EA.......FRUIT, DISPLAY................0........0451C
018756024.......EA.......CENTER, STONE.................0........


What I want to do is take a table of inventory data, with Interchangeable
Groups ( [IC_Group] ), and make a report that I can use out in the shop to
quickly look up by the [Stk Nmber] and find all other records that have the
same [IC_Group]. Doing this will of course increase the count of records,
but I'm looking for a very fast way to see all the data I need, and find all
the other interchangeable items at the same time without having to look at
another listing.

I also intend on formating the result so it looks a better and a little
easier to read that the above example.

Thank you!
 
M

Michel Walsh

Is the result with the same number of rows than there is for the input? If
so, rank the records accordingly to the field [item name]. Many possible
syntax, I prefer the joins:


SELECT LAST(a.[stk nmber]), LAST(a.ui), a.[item name], LAST(a.[o/h]),
LAST(a.[ic_group])
FROM yourTableName As a INNER JOIN yourTableName As b
ON a.[item name] >= b.[item name]
GROUP BY a.[item name]
ORDER BY COUNT(*)



assuming a.[item name] is a potential primary key (no duplicated values).



Hoping it may help
Vanderghast, Access MVP





Senexis said:
Hello,

How do I get a table of inventory data to go from looking like this:

[Stk Nmber].....[UI].....[ITEM NAME]................[O/H].......[IC_Group]
005643780.......EA.......LAMP, HOUSING.................1........0152A
005644783.......EA.......REMOTE, CONTROL...............0........0924B
005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
005654789.......EA.......TABLE, OAK 3. x 5.............1........
005655436.......EA.......HINGE, ASSY...................3........
006655538.......EA.......BOOK, DISPLAY, RED............3........
006656402.......EA.......RUG, DISPLAY, BLUE............1........
006656406.......EA.......CHAIR, OAK....................2........
007665759.......EA.......CUST SAMPLE...................2........
009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
015718722.......EA.......FOOTSTOOL, BIRCH, 3. X 5......6........
015733146.......EA.......FRUIT, DISPLAY................0........0451C
016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
018749717.......EA.......FRUIT, SMALL..................1........0451C
018756024.......EA.......CENTER, STONE.................0........

to this and/or in a report to print out:

[Stk Nmber].....[UI].....[ITEM NAME]................[O/H].......[IC_Group]
005643780.......EA.......LAMP, HOUSING.................1........0152A
.....016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
005644783.......EA.......REMOTE, CONTROL...............0........0924B
.....013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
.....009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
005654789.......EA.......TABLE, OAK 3. x 5.............1........
005655436.......EA.......HINGE, ASSY...................3........
006655538.......EA.......BOOK, DISPLAY, RED............3........
006656402.......EA.......RUG, DISPLAY, BLUE............1........
006656406.......EA.......CHAIR, OAK....................2........
007665759.......EA.......CUST SAMPLE...................2........
009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
.....005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
.....005644783.......EA.......REMOTE, CONTROL...............0........0924B
015718722.......EA.......FOOTSTOOL, BIRCH, 3. X 5......6........
015733146.......EA.......FRUIT, DISPLAY................0........0451C
.....018749717.......EA.......FRUIT, SMALL..................1........0451C
016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
.....005643780.......EA.......LAMP, HOUSING.................1........0152A
018749717.......EA.......FRUIT, SMALL..................1........0451C
.....015733146.......EA.......FRUIT, DISPLAY................0........0451C
018756024.......EA.......CENTER, STONE.................0........


What I want to do is take a table of inventory data, with Interchangeable
Groups ( [IC_Group] ), and make a report that I can use out in the shop to
quickly look up by the [Stk Nmber] and find all other records that have
the
same [IC_Group]. Doing this will of course increase the count of records,
but I'm looking for a very fast way to see all the data I need, and find
all
the other interchangeable items at the same time without having to look at
another listing.

I also intend on formating the result so it looks a better and a little
easier to read that the above example.

Thank you!
Click to expand...
 
G

Guest

Michel,

Thank you for your help, however I'm getting the desired effect for my query
using ur isql... I have made another example as to how I'm trying to get it
below. What I intend on doing is take a inventory list with interchange
items (grouped by the IC_Group field) and sub group them against themselves
in the inventory. Take the IC_Group "0152A", there are three items that are
interchangeable, but I want to see them grouped with the other
interchangeable items listed below it with a "+" infront of the stk_Nmber. I
have had a sort of successful isql, but will have to post it soon as I get
back to my DB. In the mean time... Any ideas? :)

[Stk Nmber].....[UI].....[ITEM NAME]................[O/H].......[IC_Group]
005643780.......EA.......LAMP, HOUSING.................1........0152A
005644783.......EA.......REMOTE, CONTROL...............0........0924B
005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
005654789.......EA.......TABLE, OAK 3. x 5.............1........
005655436.......EA.......HINGE, ASSY...................3........
006655538.......EA.......BOOK, DISPLAY, RED............3........
006656402.......EA.......RUG, DISPLAY, BLUE............1........
006656406.......EA.......CHAIR, OAK....................2........
007665759.......EA.......CUST SAMPLE...................2........
009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
015718722.......EA.......FOOTSTOOL, BIRCH, 3. X 5......6........
015733146.......EA.......FRUIT, DISPLAY................0........0451C
016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
018749717.......EA.......FRUIT, SMALL..................1........0451C
018756024.......EA.......CENTER, STONE.................0........
018763563.......EA.......LAMP, HOUSING, WOOD...........4........0152A

to this and/or in a report to print out:

[Stk Nmber].....[UI].....[ITEM NAME]................[O/H].......[IC_Group]
005643780.......EA.......LAMP, HOUSING.................1........0152A
+016733159......EA.......LAMP, HOUSING, BRASS..........4........0152A
+018763563......EA.......LAMP, HOUSING, WOOD...........4........0152A
005644783.......EA.......REMOTE, CONTROL...............0........0924B
+013704547......EA.......REMOTE, CONTROL, LARGE........6........0924B
005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
+009683469......EA.......TV CASE, RED, PN 7503.........1........0816D
005654789.......EA.......TABLE, OAK 3. x 5.............1........
005655436.......EA.......HINGE, ASSY...................3........
006655538.......EA.......BOOK, DISPLAY, RED............3........
006656402.......EA.......RUG, DISPLAY, BLUE............1........
006656406.......EA.......CHAIR, OAK....................2........
007665759.......EA.......CUST SAMPLE...................2........
009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
+005644784......EA.......TV CASE, BLACK, P/N 4902......1........0816D
013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
+005644783......EA.......REMOTE, CONTROL...............0........0924B
015718722.......EA.......FOOTSTOOL, BIRCH, 3. X 5......6........
015733146.......EA.......FRUIT, DISPLAY................0........0451C
+018749717......EA.......FRUIT, SMALL..................1........0451C
016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
+005643780......EA.......LAMP, HOUSING.................1........0152A
+018763563......EA.......LAMP, HOUSING, WOOD...........4........0152A
018749717.......EA.......FRUIT, SMALL..................1........0451C
+015733146......EA.......FRUIT, DISPLAY................0........0451C
018756024.......EA.......CENTER, STONE.................0........
018763563.......EA.......LAMP, HOUSING, WOOD...........4........0152A
+016733159......EA.......LAMP, HOUSING, BRASS..........4........0152A
+005643780......EA.......LAMP, HOUSING.................1........0152A

Thank you!

Michel Walsh said:
Is the result with the same number of rows than there is for the input? If
so, rank the records accordingly to the field [item name]. Many possible
syntax, I prefer the joins:


SELECT LAST(a.[stk nmber]), LAST(a.ui), a.[item name], LAST(a.[o/h]),
LAST(a.[ic_group])
FROM yourTableName As a INNER JOIN yourTableName As b
ON a.[item name] >= b.[item name]
GROUP BY a.[item name]
ORDER BY COUNT(*)



assuming a.[item name] is a potential primary key (no duplicated values).



Hoping it may help
Vanderghast, Access MVP





Senexis said:
Hello,

How do I get a table of inventory data to go from looking like this:

[Stk Nmber].....[UI].....[ITEM NAME]................[O/H].......[IC_Group]
005643780.......EA.......LAMP, HOUSING.................1........0152A
005644783.......EA.......REMOTE, CONTROL...............0........0924B
005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
005654789.......EA.......TABLE, OAK 3. x 5.............1........
005655436.......EA.......HINGE, ASSY...................3........
006655538.......EA.......BOOK, DISPLAY, RED............3........
006656402.......EA.......RUG, DISPLAY, BLUE............1........
006656406.......EA.......CHAIR, OAK....................2........
007665759.......EA.......CUST SAMPLE...................2........
009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
015718722.......EA.......FOOTSTOOL, BIRCH, 3. X 5......6........
015733146.......EA.......FRUIT, DISPLAY................0........0451C
016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
018749717.......EA.......FRUIT, SMALL..................1........0451C
018756024.......EA.......CENTER, STONE.................0........

to this and/or in a report to print out:

[Stk Nmber].....[UI].....[ITEM NAME]................[O/H].......[IC_Group]
005643780.......EA.......LAMP, HOUSING.................1........0152A
.....016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
005644783.......EA.......REMOTE, CONTROL...............0........0924B
.....013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
.....009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
005654789.......EA.......TABLE, OAK 3. x 5.............1........
005655436.......EA.......HINGE, ASSY...................3........
006655538.......EA.......BOOK, DISPLAY, RED............3........
006656402.......EA.......RUG, DISPLAY, BLUE............1........
006656406.......EA.......CHAIR, OAK....................2........
007665759.......EA.......CUST SAMPLE...................2........
009683469.......EA.......TV CASE, RED, PN 7503.........1........0816D
.....005644784.......EA.......TV CASE, BLACK, P/N 4902......1........0816D
013704547.......EA.......REMOTE, CONTROL, LARGE........6........0924B
.....005644783.......EA.......REMOTE, CONTROL...............0........0924B
015718722.......EA.......FOOTSTOOL, BIRCH, 3. X 5......6........
015733146.......EA.......FRUIT, DISPLAY................0........0451C
.....018749717.......EA.......FRUIT, SMALL..................1........0451C
016733159.......EA.......LAMP, HOUSING, BRASS..........4........0152A
.....005643780.......EA.......LAMP, HOUSING.................1........0152A
018749717.......EA.......FRUIT, SMALL..................1........0451C
.....015733146.......EA.......FRUIT, DISPLAY................0........0451C
018756024.......EA.......CENTER, STONE.................0........


What I want to do is take a table of inventory data, with Interchangeable
Groups ( [IC_Group] ), and make a report that I can use out in the shop to
quickly look up by the [Stk Nmber] and find all other records that have
the
same [IC_Group]. Doing this will of course increase the count of records,
but I'm looking for a very fast way to see all the data I need, and find
all
the other interchangeable items at the same time without having to look at
another listing.

I also intend on formating the result so it looks a better and a little
easier to read that the above example.

Thank you!
Click to expand...
Click to expand...
 
Top