July 25, 2012 at 1:26 pm
edward_hall76 (7/25/2012)
The reason for account number being the line item is because each office has there own budget and they are broken into computer supplies, office furnture, copy supplies etc and each one has its on accoun number like xxx-000-yyy so we would have to show which account it is comeing out of for the same po or they would have to fill out multuple pos.
I wondered if you needed something like that. Then it seems that the column in the header is not only unnecessary but also could actually lead to invaluable data. Somebody else may see that in the header and assume the account number is the same for the whole PO which is in fact incorrect. Which account number goes into the header when there are multiple in the detail table? Not trying to bust your chops here but there seems to be some issues with the structures.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2012 at 1:46 pm
what would you suggest then. I have a table called accounts as well would you use just the ID number for that account that coursponds with that dept and account number.
July 25, 2012 at 1:49 pm
edward_hall76 (7/25/2012)
what would you suggest then. I have a table called accounts as well would you use just the ID number for that account that coursponds with that dept and account number.
So the AccountNumber in the header means something different than AccountNumber in the details? If it works for you that is ok, but I will not be the last person confused by the same column name being in both tables and the meaning is context based. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2012 at 1:58 pm
because they want it to update the PONumber table with the total when they fill out the po so they dont have to go into the PONumber and put the Total into the Total for that PONumber.
that worked but when I tired that earlier it didnt. unless I for get to hit execute
July 25, 2012 at 2:04 pm
edward_hall76 (7/25/2012)
because they want it to update the PONumber table with the total when they fill out the po so they dont have to go into the PONumber and put the Total into the Total for that PONumber.that worked but when I tired that earlier it didnt. unless I for get to hit execute
The point I have been trying to get at is that you don't need that table at all. Just drop the table and create a view like I showed you about a dozen posts ago. All queries will continue to work as coded right now and the data will be real time. No crazy triggers and a zillion other challenges to hop through. There is just no need to persist that data from what I can see. If you really do need to persist it, it seems to make more sense to have that as a column in the header instead of a 1:1 relationship with another table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2012 at 2:05 pm
OK I miss read and didn't realized what you were talking about it being in both the Header Table and Detail Table. I don't need to be in both thanks for pointing that out.
July 25, 2012 at 4:25 pm
I have tried the view and it seems to work ok execpt I am using a another software to access my sql database and I have to close it and reopen the software to get it to update it in the Total area other than that it works greate.
July 25, 2012 at 5:09 pm
I figured out how to get my software to update without having to close the program I was able to add a button that did a windows refresh with flush cached sql data.
July 26, 2012 at 7:17 am
edward_hall76 (7/25/2012)
I figured out how to get my software to update without having to close the program I was able to add a button that did a windows refresh with flush cached sql data.
Cool. The view approach seems a whole lot less complicated to me. Glad that worked for you. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply