Ranking group report with thresholds HELP umm sorry

[amauricio2 via AccessMonster.com]

I have a question that I need help with. I am currently desinging my database.
My First objective, I need to create a report that will group my store data
but that has various thresholds that would preclude an item but then the item
would then need to move to another bucket. Does that make sense?

For Instance I have a 1)XYZ Merlot,2) ABC Chardonnay, 3)GHI Cabernet, 4)TUV
Reisling are all supposed to fit on then end of the store aisle's (Endcap) by
Rank but Store A only has 3 places to put them so I need for the 4)TUV
Riesling to move to the next appropriate are on the floor (Sidestack). Is
there any way to get that done??


So for this Store A that only has the room below this is where they would go

I would have
3 Endcaps - 1)XYZ Merlot,2) ABC Chardonnay, 3)GHI Cabernet
2 Sidestacks - 4)TUV Reisling
1 Woodbox



But Store B that only has the room below this is where they would go

I would have
2 Endcaps - 1)XYZ Merlot,2) ABC Chardonnay,
2 Sidestacks - 3)GHI Cabernet 4)TUV Reisling
1 Woodbox

and so on please help new to this

 

[Steve]

Hello KG,

This type of problem is done with specialized functions in the field called
operations research. I can implement these functions for you in your
database for a modest fee. I provide help with Access, Excel and Word
applications for a modest fee. If you would like my help, contact me.

Steve
(e-mail address removed)

 

[karl dewey]

Steve solicites business on this forum that provide FREE assistance from
VOLUNTEERS.

 

[John W. Vinson]

I have a question that I need help with. I am currently desinging my database.
My First objective, I need to create a report that will group my store data
but that has various thresholds that would preclude an item but then the item
would then need to move to another bucket. Does that make sense?

Not really, no.

For Instance I have a 1)XYZ Merlot,2) ABC Chardonnay, 3)GHI Cabernet, 4)TUV
Reisling are all supposed to fit on then end of the store aisle's (Endcap) by
Rank but Store A only has 3 places to put them so I need for the 4)TUV
Riesling to move to the next appropriate are on the floor (Sidestack). Is
there any way to get that done??

Fit in... what? Centimeters wide on the shelf? What is the meaning of "fit"?

So for this Store A that only has the room below this is where they would go

I would have
3 Endcaps - 1)XYZ Merlot,2) ABC Chardonnay, 3)GHI Cabernet
2 Sidestacks - 4)TUV Reisling
1 Woodbox



But Store B that only has the room below this is where they would go

I would have
2 Endcaps - 1)XYZ Merlot,2) ABC Chardonnay,
2 Sidestacks - 3)GHI Cabernet 4)TUV Reisling
1 Woodbox

and so on please help new to this

So far you've talked about the end result in terms that presumably are clear
to you, or to someone in your line of work; but there's absolutely nothing
here that is meaningful in database terms. Please try to clarify, on the
assumption that we're a bunch of database geeks and that "endcaps" means as
much to us as "Left Outer joins" might mean to you!

 

[CraigH]

I believe I have a little grasp on what you are trying to do. Here are some
tables I think you have or should have to even get started with this:

tblStoreLayout – Show each store’s layout and how many displays can go into
each area (or how many areas of that type you have). – I have put the Rank
for the particular area in here because it may be that you would want to
start a different store layout differently. Or you could just keep the rank
in the tblAreaType.

StoreLayoutID
StoreID (FK) A A B
AreaTypeID (FK) 1 2 1
Quantity 3 1 2
Rank 1 2 1
AreaTypeID 1-Endcaps, 2-Sidecaps …

tblDisplayRanks – Shows the display and what rank it should have – You will
need to expand this table to distinguish (if needed), by week or other
qualify when you make changes.

DisplayRankID
DisplayID 101 102 103 104 105 [Extra to show issues]
Rank 1 2 3 4 5
[Other Qualifier]

I assume a tlbStores, tblAreaTypes, tblDisplays and they are pretty much
self explanatory.

[Disregard next paragraph if you don’t care about it – I just have to think
of options ]
I am not an expert with designing SQL so am not sure if it would even be
possible – you have to get the top 3 then disregard those 3 to get the next
1 in the tblDiplayRanks for the second area t. Then the problem of (with the
105) not being able to be put into A.

What I would do is create a table that would hold the information for the
report.
Starting with the table:
[Again my problem with options- Both tables would work but I like the first
Option – because you would have to add to tblDiplayRanks All the displays
each time you change because of the Qualifier or Rank and unless you need a
history of those changes it is overkill]
Option 1
tblStoreDisplayLayout
StoreID
AreaTypeID
DisplayID
Other Qualifier

Option2
tblStoreDisplayLayout
StoreLayoutID
tblDisplayRanks

So now it is a simple process of looping through the tblStoreLayout sorted
by Store, Rank determining the Quantity to add from the tblDisplayRanks
sorted by rank, and adding the new records to tblStoreDisplayLayout.

StoreID A A A A
AreaTypeID 1 1 1 2
DisplayID 101 102 103 104
Other Qualifier

You said you were new to this so it may not be “Simple†for you to work with
recordsets and coding this. The other option is to do it manually (maybe you
only have a small number of stores) with a subform (which you would want to
do anyways to show for the Store what displays are supposed to be in which
area) The subform will be based upon the tblStoreDisplayLayout – and would
be put on the Store Form (hopefully you have one And now I could go on
with more things about the subform – but I will take this opportunity to stop
and give you time to start –

The first thing is to make sure you have the tables you need.

Let us know how you are going to proceed and we can help you with more
details on the options you choose.

Craig

 

[amauricio2 via AccessMonster.com]

Thanks for request clarification, I apologize. Really, I do
I am not trying to measure the shelf I was just giving you what I felt was a
visual point if you were to walk into a store and what you would see, you
would see boxes of wine stacked in various places throughout their wine
section not including the wines already on the shelf. You see, wine makers
like to have their products stand out. So we identify spots the end of
shelves and other free spaces in the store called Endcaps, Sidestacks etc.
and count the availability of those spots so really the measurement is not
relevant. The part that matters in my world is that I need to be able to
allocate cases of wine by their flavor to spots on the floor by location. So
I can basically fit(place) for instance 5 cases of 15 different wines based
on their favor ability(Ranking). I am attempting to create a mechanism that
will fit (place) all my wines that i have ranked into stores but the issue
becomes that all stores do not have the same amount of placements. I hope
that helps and would appreciate any help and apologize if I sound so dumb and
will understand if you are unable to assist. I await answers from experts
like you

I have a question that I need help with. I am currently desinging my database.
My First objective, I need to create a report that will group my store data
but that has various thresholds that would preclude an item but then the item
would then need to move to another bucket. Does that make sense?

Not really, no.

For Instance I have a 1)XYZ Merlot,2) ABC Chardonnay, 3)GHI Cabernet, 4)TUV
Reisling are all supposed to fit on then end of the store aisle's (Endcap) by
Rank but Store A only has 3 places to put them so I need for the 4)TUV
Riesling to move to the next appropriate are on the floor (Sidestack). Is
there any way to get that done??

Fit in... what? Centimeters wide on the shelf? What is the meaning of "fit"?

So for this Store A that only has the room below this is where they would go

[quoted text clipped - 11 lines]

and so on please help new to this

So far you've talked about the end result in terms that presumably are clear
to you, or to someone in your line of work; but there's absolutely nothing
here that is meaningful in database terms. Please try to clarify, on the
assumption that we're a bunch of database geeks and that "endcaps" means as
much to us as "Left Outer joins" might mean to you!

 

[amauricio2 via AccessMonster.com]

Wow CraigH written like a true teacher, I will try this and will get back to
you guys if you don't mind. I posted more detail on what i was trying to
accomplish but I will probably have alot more questions and will defer to
your expertise soon. I have to make this one work it means my JOB!

I believe I have a little grasp on what you are trying to do. Here are some
tables I think you have or should have to even get started with this:

tblStoreLayout – Show each store’s layout and how many displays can go into
each area (or how many areas of that type you have). – I have put the Rank
for the particular area in here because it may be that you would want to
start a different store layout differently. Or you could just keep the rank
in the tblAreaType.

StoreLayoutID
StoreID (FK) A A B
AreaTypeID (FK) 1 2 1
Quantity 3 1 2
Rank 1 2 1
AreaTypeID 1-Endcaps, 2-Sidecaps …

tblDisplayRanks – Shows the display and what rank it should have – You will
need to expand this table to distinguish (if needed), by week or other
qualify when you make changes.

DisplayRankID
DisplayID 101 102 103 104 105 [Extra to show issues]
Rank 1 2 3 4 5
[Other Qualifier]

I assume a tlbStores, tblAreaTypes, tblDisplays and they are pretty much
self explanatory.

[Disregard next paragraph if you don’t care about it – I just have to think
of options ]
I am not an expert with designing SQL so am not sure if it would even be
possible – you have to get the top 3 then disregard those 3 to get the next
1 in the tblDiplayRanks for the second area t. Then the problem of (with the
105) not being able to be put into A.

What I would do is create a table that would hold the information for the
report.
Starting with the table:
[Again my problem with options- Both tables would work but I like the first
Option – because you would have to add to tblDiplayRanks All the displays
each time you change because of the Qualifier or Rank and unless you need a
history of those changes it is overkill]
Option 1
tblStoreDisplayLayout
StoreID
AreaTypeID
DisplayID
Other Qualifier

Option2
tblStoreDisplayLayout
StoreLayoutID
tblDisplayRanks

So now it is a simple process of looping through the tblStoreLayout sorted
by Store, Rank determining the Quantity to add from the tblDisplayRanks
sorted by rank, and adding the new records to tblStoreDisplayLayout.

StoreID A A A A
AreaTypeID 1 1 1 2
DisplayID 101 102 103 104
Other Qualifier

You said you were new to this so it may not be “Simple†for you to work with
recordsets and coding this. The other option is to do it manually (maybe you
only have a small number of stores) with a subform (which you would want to
do anyways to show for the Store what displays are supposed to be in which
area) The subform will be based upon the tblStoreDisplayLayout – and would
be put on the Store Form (hopefully you have one And now I could go on
with more things about the subform – but I will take this opportunity to stop
and give you time to start –

The first thing is to make sure you have the tables you need.

Let us know how you are going to proceed and we can help you with more
details on the options you choose.

Craig

I have a question that I need help with. I am currently desinging my database.
My First objective, I need to create a report that will group my store data

[quoted text clipped - 22 lines]

and so on please help new to this

 

[CraigH]

I always enjoy working with different projects and problem solving. If it
would help to have more one on one help with this I would like to see what
you come up with and could offer direct assistance. You could zip the file
and send it to me at chornish at live dot com. It is a little slow at work
and I have some free time

 

[John Marshall, MVP]

Hello KG,

This type of problem is done with specialized functions in the field
called operations research. I can implement these functions for you in
your database for a modest fee. I provide help with Access, Excel and Word
applications for a modest fee. If you would like my help, contact me.

Steve
(e-mail address removed)

These newsgroups ar eprovided by Microsoft for FREE peer to peer support. A
concept stevie does not understand. Basically, stevie is a loser.

John... Visio MVP