Datatype based on query calculation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Datatype based on query calculation

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-13 : 18:32:26

Does anyone know if it is possible to write data back to a table based on the results of a query?

I have an inventory management database that logs transactions whether they be sales or receipts, etc. The query I have calculates current onhand from all transactions in the table for each item. I've pasted the SQL for my query below.

What I'd like to do is write this data back to the main product table and have it update very regularly. I need to have this "Current Onhand" picture regularly to do inventory evaluations and purchasing based on CURRENT levels.

Anyone have any good ideas?

SELECT Transactions.Item, Sum([Transactions.QTY]*IIf([Type]='Credit',1,-1)) AS CurrentOnhand
FROM Transactions
GROUP BY Transactions.Item;

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-14 : 11:11:42

Anyone out there?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 12:58:24

What db system are you using because "IIF" is not MS Sql Server?
But assuming that is just pseudo code, have you considered not storing that data but using your query to return that info whenever it is requested?

Be One with the Optimizer
TG

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-14 : 13:26:55

Thanks for your reply. I'm using MS Access as my UI, that's where that comes from.

I have considered not storing the data, but I'm having a hard time getting the results to display on the forms I need for our inventory evaluation. I'm currently posting the results of the query from above back into my main product table as a "Current Onhand" field using VB code in Access. I'm updating this once per day and the routine takes about 30 minutes to run through the entire product set because its so large. I'd really like to be able to see the results of the query on the form, but its complicated a little bit more.

This database is actually to manage online sales across multiple channels. I have a separate transaction table for each online customer we service (currently 3), but then I bring all those current inventory allocations back onto my main inventory form for evaluation and reallocation as needed. Its a little complicated, I was just hoping to be able to update the numbers a little more frequently.

Let me know if you have any thoughts.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-14 : 13:39:33

Access does not have Sql jobs or triggers. So your best option would be to use a "update" query or a macro. At the end of the day, you'd run the query to update the statistics. You can possibly schedule it to run automatically within Windows (a little more complex, a separate Access DB would be used as a "stub" to update the "linked" tables from the "data" DB. This way, the windows task scheduler can run the "stub" DB so no modifications need to be made to the "data" DB front end).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 13:48:23

Also, for future posts or follow-ups regarding Access questions please use the Access forum on this site.

Be One with the Optimizer
TG

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-14 : 16:30:33

I think you misunderstood. I'm using SQL Server 2005 as my back end solution and MS Access as the User Interface only. I'm accessing the SQL Server 2005 through linked tables and an ODBC connection. I currently have a solution within Access that works, but only when I tell it to. Currently I tell it to update once per day, but its not enough and it takes far too long.

I want to program something on the SQL Server side because it IS much faster and it DOES have SQL jobs and triggers. That's why I'm in this forum asking this question. I should be in the right place.

So, I go back to the original question. I have a query that sums up my Transaction tables and gives me the available onhand inventory for each item number, I just need to know if there is a way I can write the query results back to the Item table.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 16:46:03

EDIT:
Still not sure if want sql server code like a stored procedure that you call from Access or if you want Access code that you send through your odbc connection. But assuming you want sql server code:
endEDIT

Not sure what you mean by "write back" but you can certainly perform INSERTs and UPDATEs based on the statement you posted above. You would just need to replace your "IIF" with a CASE statement.

What was the answer to my original question:
>>have you considered not storing that (always changing) data in your [item] table but using your query to return that info whenever it is requested?

Here is the ss version of your statement:


SELECT Transactions.Item
       ,Sum([Transactions.QTY] *  
              case
              when type = 'credit' then 1
              else -1
              end) AS CurrentOnhand
FROM Transactions
GROUP BY Transactions.Item

Be One with the Optimizer
TG

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-14 : 19:41:35

Thanks again for the reply. I did give thought to not storing the data results from the query. That seems like it would be the right thing to do, but I'm having trouble getting my access form to display all the data. As I mentioned before, this is a database meant to ease the burden of selling inventory across multiple channels. At any given point, I need to know how much inventory is allocated to each online customer so that I can make transfers and allocations as I see fit. in order to do that, I have to have all data show up on the form I've created so that I can do a side-by-side comparison and work with my 45,000 + SKUs.

The solution in the past has been to update a column on my main product table that is reserved for the current onhand of each online customer. Customer 1 gets an "onhand" column, customer 2 the same, and so forth.

I have it set up this way because I have separate transaction tables for each online customer, so naturally I have three queries that sum to give me the current onhand. Thus, when creating my form for displaying all this data in Access, I'd have to have it based on 1 table and 3 queries and I'm having trouble doing that.

You can see why it seemed easiest to just update each "current onhand" column on my main product table with the current results and have that update query run multiple times throughout the day so that all current transactions are taken into account.

Does that make more sense? I realize its a little screwy, but so is our business. at any rate, I'd like to somehow take the SS you gave me above and Update the results to the main table, but I'm a little fuzzy on how to do that and then put it on a schedule.

Thanks again for your help. I really appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources