Sub-grouping Inventory Data

  • Thread starter Thread starter Guest
  • Start date Start date
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!
 
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...
 
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...
 
Back
Top