Database

Solved: The data types in the data table were off. Jude

this is solved... thanks

Hence I came here to tap into the vast knowledge of others :D Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

ok thanks for reply i solved this problem with this code: UPDATE mycompany.dbo.Files SET [ReferenceGuid] = ( SELECT mission.[GUID] FROM dbo.Mission mission WHERE mission.ID = [ReferenceID] ) WHERE [ReferenceSoftwareGuid] = '66944D9C-31F4-4594-9407-4E1F64C079D9' thanks anyway

use trunc(your_field name)= trunc(sysdate) or sysdate-1 for yesterday. or trunc(field_name)='12-apr-2012'

OK, thank you very much for your reply. I moved my answer...

ok thanks for repry

At a guess it does not like the semi-colons at the end of the statement(s), and/or the extra END; clause. However, in order to be sure you should check the manual. Use the best guess

Your relationship with MS Access is going to get worse - sorry it really is a crap tool for database work. There are a number of schools of though for the distributed database. Some like to us GUIDs, others like the location/ID concatenation. There are some where you have a master server for such things as products, only HO can add a code etc. I don't even think there are good guidelines on which to use where. Personally I prefer the location/id solution, I know of at least 1 respected member here who would recommend the GUID path (PITA to read the ID) so it will be your choice. You should look into replication before you start, designing so the data can be consolidated from the start is always a GREAT idea. Depending on your business requirements I would opt for a parallel application and write a script that can migrate your data to the new structure. This allows you to completely redesign your structure getting rid of the crap design you have now. Don't finalise your DB design until the bulk of yor app development is done, then make sure your migration script works. It should be possible to migrate your data at any time and repeat until the dev is complete. Consider using a web based solution if the local speed is acceptable - eliminates the distributed problems but introduces others (uptime issues) and criticallity. I recommend not using the cloud if your data is both critical and confidential. Good Luck you have interesting time ahead of you. Never underestimate the power of human stupidity RAH

You're welcome :)

Thanks for your reply David. Not sure if I understood correctly, but kept getting 'syntax error'

Assuming MSSQL < 2012, something like this should work: SELECT ID, B1, (SELECT Sum(B1) FROM TheTable As T2 WHERE T2.ID <= T1.ID) As Actual FROM TheTable As T1 ORDER BY ID http://www.sqlfiddle.com/#!3/59751/2[^] For MSSQL 2012: SELECT ID, B1, Sum(B1) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As Actual FROM TheTable ORDER BY ID http://www.sqlfiddle.com/#!6/59751/1[^] "These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Read this[^]. "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me

thank you for reply

thanks yea its ok and a good solution

Jörgen Andersson wrote: Because some companies are quite divided. Certainly. And some companies need to create their own database. But in the general case neither of those are true. Jörgen Andersson wrote: It's the number of actual users that counts, not the number of users you setup in the database. It's a common misconception. I know how it works. The fact that user licensing exists at all specifically indicates that support in the database per user is something that is in fact significant. Unless you are claiming that only a single user connects at a time, then my comment about user licensing stands. And if you are claiming that then it is far from what any normal business would use.

http://en.wikipedia.org/wiki/2PC[^]

Ennis Ray Lynch, Jr. wrote: your method requires that the other database have the tables and files and file groups already created and requires the DB to be taken offline in order to add the new files and file groups which contain the table data. Not necessarily so; you can attach it under a different name to the same server. Next, you SELECT INTO the tables from Database1.Dbo.TableName to Database2.Dbo.TableName :) Ennis Ray Lynch, Jr. wrote: What I am looking for is some black magic Aight, sacrifice a chicken and a donkey at the next full moon. More seriously; if you have access to a DBA, ask him/her how they'd implement horizontal partitioning. You could do something similar on a more simpeler level; you'd have to change your select-query to dynamically check for new databases, and to union it along. Could be done by querying sys.databases and building the select/union from that. Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Dear gvprabu, Thanks you so much for your help, finally i'm successful with to do it. Regard,