Need help with a database/spreadsheet

Status
Not open for further replies.

02ranger

Golden Member
Mar 22, 2006
1,046
0
76
Mods: First things first. If this is in the wrong forum, please move it. I'm just not sure where this should go.

Now to my problem. I'm trying to create a spreadsheet or database or whatever is the best option to automate or at least simplify the following process for work.

Each day I have to look up several items and get a list of how many were ordered by each store, and then add together how many of each they ordered and enter separate information elsewhere depending on how many they ordered total. For example, let's say store 635 orders 2 bins of fiction books, 1 bin of non-fiction books, and 1 bin of assorted toys. I have to add all of these items up and when the total is 3 or more, I have to enter the information elsewhere, but if it's 2 or less I can ignore the store. Here's where my issue comes in. I have to do this for 4 different warehouses, for anywhere from 100-200 stores in each warehouse daily, in a 30-minute window and I have no easy way to add the different stores together automatically. On top of that, not every store will be listed every day. If they didn't order any of a particular item, they don't show up on the list so it will fluctuate from day to day.

What I want is a way to copy and paste the store list and quantity ordered for each item and then automatically add the items together and create a new list with the total number of items per store. Once its built, it needs to be super-simple for the technologically challeneged because I won't be the only one using it. The list that I get from our system is formatted in 3 columns, something like this:

Store Ord Qty | Store Ord Qty | Store Ord Qty

There is no export function, so it will require me to copy and paste the list.

What do I use to do this? I've tried an Excel spreadsheet but I'm stuck. I don't know Access at all but I'm definitely willing to learn if that's my best option, or is there something else I can use that would be better?

I really appreciate any help anybody can give me.
 

DesiPower

Lifer
Nov 22, 2008
15,299
740
126
I am familiar with these technologies but in no way an expert. I believe Excel and macros will do it for you. Rather than learning Access, learning Excel marco could be more useful and easier
 

TwiceOver

Lifer
Dec 20, 2002
13,544
44
91
If there are 100-200 stores multiple bin locations and warehouses, you should have an IT team to ask to create this report for you...
 

xSauronx

Lifer
Jul 14, 2000
19,582
4
81
If there are 100-200 stores multiple bin locations and warehouses, you should have an IT team to ask to create this report for you...

seriously. whatever application is holding that data to start with should have the ability to sort/export/report on it in a meaningful way. we have people asking for special reports on a fairly regular basis and its not a big deal to get them done

at least, i assume not. i am a sysadmin, we have two DBAs who do the reporting and ive never heard them complain about any reporting requests.
 

pete6032

Diamond Member
Dec 3, 2010
8,027
3,492
136
Can't you just create a pivot table and use the sum function?

Then just paste all the data for each store into the same sheet.
 

02ranger

Golden Member
Mar 22, 2006
1,046
0
76
If there are 100-200 stores multiple bin locations and warehouses, you should have an IT team to ask to create this report for you...
Our IT team will not do this. I wish we could but even if they chose to help I could realistically expect to get it next year...... My department has asked for similar things in the past and gotten nowhere. I usually have more success doing it myself.
seriously. whatever application is holding that data to start with should have the ability to sort/export/report on it in a meaningful way. we have people asking for special reports on a fairly regular basis and its not a big deal to get them done

at least, i assume not. i am a sysadmin, we have two DBAs who do the reporting and ive never heard them complain about any reporting requests.
There is no export function for this. The application was created in the late 80s-early 90s I believe and has had new modules built on top. We can export to Excel in some places, but the area where I access this information is in the oldest and simplest part of the program and there is no export capability. I have spoken with IT about adding export functionality in the past to a similar part of the program and they said it was not a high enough priority or was "too difficult."
Can't you just create a pivot table and use the sum function?

Then just paste all the data for each store into the same sheet.
I'm not sure. I tried looking at a pivot table but I couldn't find a way to combine the store numbers. Since I will have multiple lists whatever I do needs to be able to search for store x in each list and combine the count, keeping in mind that all stores won't appear in all lists and won't be at the same spot in each list. Will a pivot table do this?
 

rifken2

Member
Feb 1, 2010
140
0
71
Excel is not a database...
I repeat... Excel is not a database.

That said this is pretty simple in excel or access.

I don't know if you have the budget or not but alteryx will also do this really simple and you can use whatever data store you have available.

If you don't have an answer in the morning I'll try to chime in... I do retail POS analytics for a living.
 
Status
Not open for further replies.