Help with SQL Query Update Prior Transaction item Cost

Hi can any one tell me how to update past transactions item cost so my historical P&L reports are correct. Currently any items with 0 cost show up as 100% profit it looks like my profit is much higher then it really is there a way to export all 0 cost items into excel fix cost prices then update data base with SQL query imported from excel please explain how to do this Thanks Al

Reply to
AL
Loading thread data ...

What cost are you going to use?

Are you just going to use the current cost?

Do you need the date of the transaction to use to look up manual records?

Here is a select query that will show you how many records will need updating, run this in Store Operations Administrator and let me know how many records.

SELECT [Transaction].Time, Item.ItemLookupCode, Item.Description, TransactionEntry.ID, TransactionEntry.TransactionNumber, TransactionEntry.Cost FROM (TransactionEntry INNER JOIN Item ON TransactionEntry.ItemID = Item.ID) INNER JOIN [Transaction] ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber WHERE TransactionEntry.Cost = 0

Reply to
Michael

Ugh. This will be time consuming.

Run this query: SELECT ID, TransactionNumber, ItemID FROM TransactionEntry WHERE Cost = 0

In another query window: SELECT ID, ItemLookupCode, Description FROM Item

Export the results ob both queries to separate .csv files.

Get the data into Excel. You probably want to open the .csv with Notepad, use select all/copy then paste into Excel. Use the Text to columns... feature and format any columns that may have leading zeros at TEXT or you will lose the leading zeros. If you lose the leading zeros, things will not go as planned...

Use the VLOOKUP feature to match the ItemID from the TransactionEntry data with the ItemLookupCode/Description information from the Item table data.

Once you have some descriptive data associated with the ItemIDs from the TransactionEntry results, you can start inputing costs into a new column. Once you have all of the cost info entered, you can build a text formula that looks like this: ="UPDATE TransactionEntry SET Cost = ",R1C1," WHERE ID = ",R1C1

The result of this Excel formula will be: UPDATE TransactionEntry SET Cost = XX.XX WHERE ID = Y

Use the Fill Down feature to create a separate query for each ID from TransactionEntry.

WAIT UNTIL THE STORE IS CLOSED. BACKUP YOUR DATABASE.

Copy the array of queries into a Store Ops Admin query window. Run.

Test the results with a report.

If you're happy, great. If not, restore from backup and try again.

I hope that helps...

Tom

Reply to
Terrible Tom

Michael's SELECT query is much better than my two queries + Excel qyrations.

As you may have deduced, I'm much more fluent with Excel than with SQL.

If you are happy with simply inserting your current item costs into the transaction table, a few JOIN statements can probably get you there much faster than my method.

If you intend to research your invoices for the actual cost at the time of the transaction, this will take a lot more time.

How did you end up with zero cost merchandise in the first place?

Tom

Reply to
Terrible Tom

When I started in 2005 using RMS 1.2 I let cashiers ring up some transitions item with a department item that was good for multiple items regardless of cost my first goal was to train my cashiers how to use computers to ring up customers as fast as a cash register.

I currently had all cost up to date but screwed up by using wizard hitting commit on a few items to update cost but didn?t realize the other items got set back to zero cost.

RMS 1.2 did not have a user friendly PO function so I never keep up with adding cost we also have thousands of small items. I currently have 5068 records that need to up dated

Thanks Al

"Terrible Tom" wrote:

Reply to
AL

There is a way to do it. Unfortunately, it is mathematically and logically far above my head. I understand AL's issue becuase it has been a complaint of mine for a very long time - you can't back-date a PO or transfer receipt and have the COGS information updated.

Many times you may sell items before you hit the commit button on a PO, making the cost on the transaction outdated or zero in the case of new items. RMS does not have provisions to account for these costs when you get around to the act of receiving. That is bad. It makes cost data in RMS useless in my opinion.

There must be a way to correct this, and based on the dicussions in the past, I am not the only one with this problem. Whether it is a SQL query, add-in or a MS Hotfix, I think this warrants a serious look, but again, someone with more of a math brain coupled with some database logic experience would need to take a look at it.

Reply to
Jason

The only way the cost data in RMS is useless is if you don't commit the PO in a timely fashion. I can't speak for others, but it seems to me that the best practice would be to commit the PO as soon as you receive the shipment.(I know Jason will disagree with this because we've had this discussion before). If a supplier won't give you an invoice when you receive your shipment then there is something wrong with their procedure. Without an invoice how do you know what the cost is, so how would you set your retail price? Prices do go up unexpectedly at times. I do agree that there should be a process in place in case something happens and you can't commit the PO right away(thing's do happen from time to time), but this shouldn't happen all the time, and certainly shouldn't be more than a couple of days until you receive a late invoice. Craig

Reply to
Craig

Hopefully you have the "Update supplier cost and tax rate from Purchase Order" option set now so that it is updated automaticaly.

What costs would you like to set for your previous transactions?

Reply to
Michael

Craig, since you are aware of the previous discussion, you might also remember that there are many legitimate reasons that you may not know your exact cost before the goods hit your retail floor (like duties, inland freight, port fees, etc. that have NOTHING to do with your supplier).

So your "timely fashion" argument holds little weight. In a perfect world, yes - I would love to know my costs first, but I sure won't lose a sale due to the limitation of my POS or accounting systems. Putting administration ahead of sales is why so many businesses fail.

Those "couple of days" or even couple of hours result in COGS not recorded. That is why the cost data is useless - you might as well forget about importing COGS into your accouting system and them adjust inventory at the end of the month to match RMS total inventory cost (as long as all of your received POs are committed). The COGS and profit on your detailed sales reports will never be accurate unless you are 100% diligent about never selling something unless it has been received and committed, and THAT is putting administration ahead of sales.

Reply to
Jason

Jason I agree with you I have garden center a lot of delivery come on Saturday Moring when the place is pack the flowers get taken off the truck the customers pick them up and bring them to the cashier to buy! This happens with holiday, produce, and craft items a lot. It sounds like Michael would recommend tell the customers to come back latter after I commit the PO for a Christmas trees customers don?t care about my accounting practices they just want to buy the product. What if the owner wants to go on vacation and enter the cost for items when he or she gets home? What about all the little low price items that shouldn?t need a P.O. Threes a lot of time and cost of administrating the PO might be more then the profit of the item. Example a farmer brings $10 dollars of Tomatoes should I spend a halve hr. creating a new items and a P.O I agree with Jason there needs to be easer way to add cost to transactions after the sale so. In a perfect world there should be a receiving department update all items before they get put on the floor. Jason right the sales is more important and should come first then administration. If they want mom and pops to buy RMS they need to make it work for the real word. Imagine a liquor store ran out of beer on new years eve there a line of customers the beer truck pulls in and the owner never had time to create a PO because he was to busy looking on this news group how to update cost in RMS. and it is almost past the legal time he is allowed to sell alcohol. He just sells the beer fast and gives up on the whole PO format like me and probably just about all other small store. I need to no the easiest way to keep track small items that I can buy fast off a truck put on the shelf and update the cost after it sells no before it sells.

Thank you to Michael, Terrible Tom, and Jason the current fix seems to difficult and risky for me. I will just keep not using RMS report until they figure this out I can?t afford to stop selling all the items this may happen with. They should have a wizard to fix this problem how hard would that be ?????????

Thanks Al

"Jason" wrote:

Reply to
AL

Personally I would rather commit with a close estimate and correct any minor changes later, rather than not committing and having a completely inaccurate COGS. There has to be a better way than waiting days, or weeks to commit the inventory while you wait for final costs. Quite possibly another POS software would be better suited for this practice. I understand your situation is different than mine(and probably most everyone that operates solely within the continental US)but in my business, administration is just as important as sales. Without proper back office practices, prices are at less than optimum margin, COGS are inaccurate, inventory will be off, staff won't be at optimum levels, etc., etc. Like I said in the last post, I agree there should be a provision for fixing COGS that are off for any reason, mistakes do happen, but it would only be used as a last resort. Craig

Reply to
Craig

For Craig what kind off business do you use RMS for do have dated or perishable items please explain why administration is more important then selling.

Thanks Al

"Craig" wrote:

Reply to
AL

I didn't say admin is more important than selling, it is just as important. Any business that is not well run will eventually go belly up. I lose no sales by running my store professionally. If I am getting new items in, they are created and ready to sell by the time I get my shipment in. I know the cost of the items I buy, so when I enter it as a new item I put the correct cost in the appropriate field. That way if it sells before I commit the PO, the correct cost is already there. If the cost is off a little bit I make the adjustment, but that happens rarely for me. I'm very surprised that there are business owners that don't believe management is important. If I couldn't do it I would certainly hire someone who could. Sales are very important, without them we would all be out of business, but without competent management something is going to suffer. Prices need to have the optimum margin(which won't happen if you don't know what you are paying for the item)if you price an item too high it won't sell, if it's too low you will lose money, if you buy too much you are wasting money, if you don't buy enough you will lose sales. Staff needs to be scheduled so that we're not over staffed during slow times or under staffed during busy times, shrinkage needs to be controlled, and store use items need to be tracked, just to name a few. These are all things that a POS system will do for you if you use it correctly. If it's not used properly it's just an expensive cash register, why spend all the money on one and then not use it. Selling an item before you know what you paid for it is like putting the cart before the horse. This is all just my opinion, it's your business, run it the way you wish. That's the beauty of owning your own business. BTW, I've been in business over 15 years, so I must be doing something right.

Craig

"AL" wrote in message news: snipped-for-privacy@microsoft.com...

Reply to
Craig

Actually Michael is not involved in the conversation you are having regarding the pros and cons of how to run your system.

Reply to
Michael

To Craig

So I guess if I could by Truckload of $10,000 garden items and produce that is always different prices from an Auction and I sell it over the weekend for $20,000 you would not recommend RMS for me this one more example why someone might need to ad cost after the sale it would be much easier and cost effective to schedule this management position monthly or weekly then make it daily procedure that needs to be done by the cashier before selling some thing RMS makes it totally required to keep accurate records! Personally I don?t know what you don?t understand you should be able to adjust cost after the sale to save time money and reduce the level of management skills needed to operate a store on weekly bases. So you would problem not recommend RMS for liquor stores Farm Markets Consignment Shops Florist Feed Stores and any store that might sell items on a daily basis before creating PO because there is no easy way to up date cost after the transition. I assume you are not using RMS for perishable items. I was just asking if there was an easy way to update cost after the transaction not how successfully your business is. If I was doing over hundred million a year in sales I would have a full time PO person to keep track of it all PO. My Farm market may receive over 20 deliveries in one day hundreds of items daily I tried doing it your way but failed. I would need to hire a full time person just to keep track PO is this you recommend or should I stop buying things from the Auctions.

Thanks Al

"Craig" wrote:

Reply to
AL

I wouldn't even pretend to know whether RMS is right for your business or not, but it seems like what you want to do is very complicated and time consuming using RMS. There must be something more geared toward what you want to do. From my perspective the PO module in RMS is the heart of inventory control for the system, it controls quantity, and cost. If you don't use it everything is off, as you've found. Not all POS systems wofk for every business. There are a few verticals that RMS is not right for, especially out of the box, and yours might be one of them. Hopefully someone develops an addon to do what you want, or maybe MS will add this function(don't hold your breath). Craig

Reply to
Craig

I just want to say...

That. Was. AWESOME!

"AL" wrote:

Reply to
Terrible Tom

Yes. Agreed. It is a hotly contested topic - obviously one that could use some attention by developers (MS or not).

The problem with adjusting prices later is that the weighted average calculation is not that simple, and there is no record of how costs get changed if done manually. I would like to reiterate that there IS a solution. It's just math, but more than I am willing to figure out on my own for an RMS solution. I would be willing to pay (within reason) for a solution.

For now, I am happy to ignore the COGS and Profit numbers coming out of RMS and just handle these in the accounting system. It's one entry a month to adjust Inventory and COGS, so I think I can handle that!

Reply to
Jason

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.