|
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
|
|
|
|
|
plz help me am begginner to ms sql server
my question mulitple rowwise data should be display to one columnwise
suppose from table1 i created column like empname,reference
table2 i created column like reference and salary details
join both table retrieve and display empname, reference,and all salary details for particular employee in one column
table1 value
-----------------------------|
empname | referenceno
-----------------------------|
girijesh | 111
-----------------------------|
ajay | 121
-----------------------------|
table2 value
-----------------------------------------------------|
referenceno | salary
-----------------------------------------------------|
111 | jan-10000
-----------------------------------------------------|
111 | feb-20000
-----------------------------------------------------|
111 | mar-30000
-----------------------------------------------------|
121 | jan-10000
-----------------------------------------------------|
121 | feb-20000
-----------------------------------------------------|
so now result like below
-----------------------------------------------------|
empname | reference | salary
-----------------------------------------------------|
girijesh | 111 | jan-10000,feb-20000,mar-30000
-----------------------------------------------------|
ajay | 121 | jan-10000,feb-20000
-----------------------------------------------------|
i want query to display like above result
|
|
|
|
|
|
in query u put case statement 1,2,3 like that but problem is reference number should chage every time wat to do ... then how to retrieve the data..
|
|
|
|
|
Read the section on 'Concatenating values when the number of items is not known'.
|
|
|
|
|
|
plz gothrough it and plz understand my concept and solve it.. bcoz u not properly understand my output
|
|
|
|
|
select t1.empname,t1.referenceno,t2.salary
from table1 t1 join table2 t2
on t1.referenceno=t2.refereceno
|
|
|
|
|
its show answer like below
-----------------------------------|
empname | reference | salary
-----------------------------------|
girijesh | 111 | jan-10000
-----------------------------------|
girijesh | 111 | feb-20000
-----------------------------------|
girijesh | 111 | mar-30000
-----------------------------------|
ajay | 121 | jan-10000
-----------------------------------|
ajay | 121 feb-20000
-----------------------------------|
this wat if i executed ur query i got but i want reslut like below see ur ouput and wat am accepted output
so now result like below
-----------------------------------------------------|
empname | reference | salary
-----------------------------------------------------|
girijesh | 111 | jan-10000,feb-20000,mar-30000
-----------------------------------------------------|
ajay | 121 | jan-10000,feb-20000
-----------------------------------------------------|
i want query to display like above result
|
|
|
|
|
Use sort by or group by with the query to get the result.
|
|
|
|
|
I have a table Id , b1
Id B1 espectedresult
1 5 5
2 19 24
3 14 38
4 41 79
5 14 93
6 41 134
I want the sum b1 at every Id
can some one help?
|
|
|
|
|
select sum(b1),id from tablename group by id
|
|
|
|
|
It looks like what you want is a running total. I would have to do a search and do not have the time right now.
|
|
|
|
|
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
|
|
|
|
|
i want to know whats benefit use of index on view instead of table
thanks for any help
|
|
|
|
|
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
|
|
|
|
|
hi to all
i have a table that product ID is key and clustered .
i create this view on my table if i not use of cluster index on view whats happen?
CREATE VIEW View3 WITH SCHEMABINDING AS
SELECT ProductID, SUM(UnitPrice*(1.00-UnitPriceDiscount)) AS Price,
COUNT_BIG(*) AS Count, SUM(OrderQty) AS Units
FROM Sales.SalesOrderDetail
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)
note to this point that Product ID also Cluster index in base table
thanks for any help
|
|
|
|
|
|
|
Hi
Using Microsoft.ACE.OLEDB.12.0 to access an MS Access 2007 database from a VB.net (2010) project.
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
WHERE tStk.[difference] <> 0
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTot
I only want rows where the sum of the item qty is Different in the two tables
The line:
WHERE tStk.[difference] <> 0 does not work. If I run the query from MS Access, it is asking for a value for the parameter tStk.[difference]
I also tried
WHERE sum(tStk.Qty) <> tHST.Qty but aggregate functions are not allowed in a WHERE clause.
|
|
|
|
|
Try taking your existing query and make it a sub-query.
Some thing like:
Select * from ("put your query here") XX
where XX.tStk.Qty <> tHST.Qty
Remember to remove your "Where" clause from your inner query.
|
|
|
|
|
Thanks for your reply David. Not sure if I understood correctly, but kept getting 'syntax error'
|
|
|
|
|
|
Awesome! Thanks Jörgen
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], sum(tStk.Qty) as [StkTot], IIF(tHst.HstTot is null,0, tHst.HstTot) AS [HstTot], sum(tStk.Qty) - IIF(tHst.HstTot is null,0, tHst.HstTot) AS [Difference]
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tStk.WH + ' - ' + tStk.StockCode =tHst.WP
GROUP BY tStk.WH+ ' - ' + tStk.StockCode , tHst.HstTot
HAVING sum(tStk.Qty) <>tHst.HstTot
I see that:
Aggregate functions can be used as expressions only in the following:
The select list of a SELECT statement (either a subquery or an outer query).
A COMPUTE or COMPUTE BY clause.
A HAVING clause.
|
|
|
|
|
whate is the different between two phase commit in SQL server and Oracl ???? :
modified 31-Mar-13 13:37pm.
|
|
|
|