Loss of Decimals Upon Link to Access

Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is base
d
on a table which has some data types as float. I created a view on the table
.
The view shows me units of a product divided by units of all products. The
results are expressed in the view as decimals. So, for example, .499857. Thi
s
is what I want. However, when I link the view to Access, all of my decimals
become zero. For example, .499857 becomes 0. I'm completely confounded. Any
suggestions would be fantastic! Thanks!If the SQL view is correct and you can use Query Analyzer to
view the results and they are as expected then this is more
of any MS Access issue. Make sure you have the latest Jet
service pack installed on the client.
But this is more of an Access issue so you would want to try
posting in one of the Access newsgroups. When posting your
question, be sure to include versions (version of SQL
Server, version of Access), what service packs you are
using.
-Sue
On Wed, 19 Apr 2006 08:11:02 -0700, Mike C
<MikeC@.discussions.microsoft.com> wrote:

>Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is bas
ed
>on a table which has some data types as float. I created a view on the tabl
e.
>The view shows me units of a product divided by units of all products. The
>results are expressed in the view as decimals. So, for example, .499857. Th
is
>is what I want. However, when I link the view to Access, all of my decimals
>become zero. For example, .499857 becomes 0. I'm completely confounded. Any
>suggestions would be fantastic! Thanks!

Looping columns in instead of trigger

I have the following view (vProcurementPlan)

SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
dbo.tblRequisition.ROS AS ROS,
dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
ProjectID
FROM dbo.tblProcurementPlan INNER JOIN
dbo.tblRequisition ON
dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID

If I try inserting a record from Access it complains about multiple base
tables, I'm happy to write an "instead of" trigger and handle the 5 columns
from tblRequisition but as it contains all columns from tblProcurementPlan I
don't want to have to list them separately in any insert or update
statement.

The idea is that a record will be inserted into both tables simultaneously
upon insert to the view.Trev@.Work (bouncer@.localhost) writes:
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID
> AS ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5
> columns from tblRequisition but as it contains all columns from
> tblProcurementPlan I don't want to have to list them separately in any
> insert or update statement.

I am afraid you don't have much choice.

Besides, in my opinion SELECT * does not belong in production code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try not using the * and referencing all the column names explicitly.
It should insert without a problem.
eg
SELECT pp.RequisitionID ppreqid, pp.ReqNo ppReqNo, pp.Am AS Am, ppROS
AS ROS, pp.ActivityID ppAcID, pp.ProjectID ppProjID,rq.RequisitionID
rqReqId, rq.ReqNo rqReqNo, rq.Am Am, rqROS ROS, rq.ActivityID rqAcID,
rq.ProjectID rqProjID
FROM dbo.tblProcurementPlan pp INNER JOIN dbo.tblRequisition rq ON
pp.RequisitionID = rq.RequisitionID

Pachydermitis

"Trev@.Work" <bouncer@.localhost> wrote in message news:<3fddeed0$0$13894$afc38c87@.news.easynet.co.uk>...
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
> ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5 columns
> from tblRequisition but as it contains all columns from tblProcurementPlan I
> don't want to have to list them separately in any insert or update
> statement.
> The idea is that a record will be inserted into both tables simultaneously
> upon insert to the view.

Loop inside View

Hello,

is it possible to build a loop for the following statement?

CREATE VIEW vwObjects as (

Select 2001 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate >= '20010101' and o.sdate < '20010401'
union

Select 2001 as year, 2 as quarter, id as id
from dbo.objects o
where o.edate >= '20010301' and o.sdate < '20010701'
...
union

Select 2002 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate > '20020101' and o.sdate < '20020401'
...
)

I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.

Thank you!Huh?

YEAR(edate), MONTH(edate)

What are you trying to do?

And what's with LOOP? I don't see no loop|||Oh,sorry. I have one Table for the objects. Every object as a startdate and an enddate. For my cube, I need kind of dimension, so the user can pick a quarter and he will get the sum of all active objects. I tried several ways to realize this.

My idea is to create of view, that looks like:

year quarter id
2001 1 1
2001 1 2
2001 1 3
2001 2 2
2001 2 4

From objects table:

id startdate enddate
1 2001/05/01 2001/13/02
2 2001/25/02 2001/03/04
3 2001/03/01 2001/5/01
4 2001/09/05 2001/22/05

I hope it's more more understandable now.|||Ok, forget that, I found another way.|||Ok, forget that, I found another way.

Can you elaborate? Your solution may help other users in the future.|||I couldn't solve this. Even if I could, this will be very slow for big tables. I will have to do a little work off on my design and then I will try this loop with a INSERT INTO, not a view. Greets, Silas

Looking for table / view that will tell me if I need to reinitialize subscription

I have kind of unique situation. I am running Merge replication. In one of my publications I am only publishing procedures/functions/views. By design, these do not change that often, but when a programmability object changes, it is scripted in a way so that:

1. The article is dropped from the publication

2. the object is then changed

3. The article is added back to the publication

My question is: Is there a table or view that the subscriber or publisher can see that could tell me if reinitialization needs to occur. I am looking at adding an automated script at the subscriber that makes the determiniation and automatically reinitializes the subscription. My alternative is to force the subscriber to reinitialize every time when synchronizing with this publication, even if nothing has changed because the process has to be automated.

Thanks,

Bill

Are you using SQL 2000 and 2005? In SQL 2005, you don't need to drop/recreate articles in order to change the schema. You can directly do ALTER TABLE/VIEW/FUNCTION. For more info, please take a look at BOL http://msdn2.microsoft.com/en-us/library/ms151870.aspx.

Peng

|||I am using 2005, but that will not work, as the publication only contains programmability objects, the underlying base tables are in another publication.|||Not sure what you mean by that will not work. What Peng means is that when you need to change one your progammability article like stored proc or view etc, you can just run alter view or alter proc ... and this DDL action will be replicated when using SQL 2005. so you dont need to drop the article, alter it and readd it. Hence you will not even need to reinitialize your subscriptions.|||When you execute a DDL statement against a view that: is in a publication without any tables in that publication, the query will run but will not complete execution.|||This is a known issue and the workaround is to add a dummy table in that publication.|||When you say create a dummy table, what exactly do you mean? Is this just the "real" table name with one column or something else?|||

Correct, create a non-necessary table that means absolutely nothing. i.e.

create table dbo.t1 (col1 int primary key, col2 int)

Add this as an article to your publication, then your DDL statements should work.

Looking for how to return the previous payment

I use the following sql in a view to return the last payment date and amount made by clients. What I need is a way to return the payment date and amount for the payment prior to the last one.

Any help is appreciated very much,

SELECT dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDateAS LastPayDate,SUM(dbo.tblPaymentReceipts.AmountPaid)AS LastPayAmt
FROM dbo.tblPaymentReceiptsINNERJOIN
(SELECT Client_ID,MAX(PaymentDate)AS LastPayDate
FROM dbo.tblPaymentReceiptsAS tblPaymentReceipts_1
GROUP BY Client_ID)AS AON dbo.tblPaymentReceipts.Client_ID = A.Client_IDAND dbo.tblPaymentReceipts.PaymentDate = A.LastPayDate
GROUP BY dbo.tblPaymentReceipts.Client_ID, dbo.tblPaymentReceipts.PaymentDate

You could try something like this...

SELECT
X.ClientID,
P.PaymentDate AS ThisPaymentDate,
P.Amount AS ThisPaymentAmount,
L.PaymentDate AS LastPaymentDate,
L.Amount AS LastPaymentAmount
FROM
(
SELECT
ClientID,
MAX(PaymentDate) AS PaymentDate
FROM tblPaymentReceipts
GROUP BY ClientID
) AS X
LEFT JOIN
(
SELECT
R.ClientID,
Max(R.PaymentDate) AS PaymentDate
FROM
(SELECT ClientID, MAX(PaymentDate) AS PaymentDate FROM tblPaymentReceipts GROUP BY ClientID) AS Z
LEFT JOIN tblPaymentReceipts R
ON Z.ClientID = R.ClientID
AND R.PaymentDate < Z.PaymentDate
GROUP BY R.ClientID
) AS Y
ON X.ClientID = Y.ClientID

LEFT JOIN tblPaymentReceipts P
ON X.ClientID = P.ClientID
AND X.PaymentDate = P.PaymentDate

LEFT JOIN tblPaymentReceipts L
ON Y.ClientID = L.ClientID
AND Y.PaymentDate = L.PaymentDate

This most likely isn't the most efficient query but its a starting point for you...

John

|||

--SQL Server 2005

SELECT Client_ID, PaymentDate, AmountPaidFROM(SELECT Client_ID, PaymentDate, AmountPaid, ROW_Number()OVER(PARTITIONBY Client_IDORDERBY PaymentDateDESC)as RowNum

FROM tblPaymentReceipts)AS t

WHERE RowNum=2--RowNum=1 for latest payment

-- If you are not using SQL Server 2005:

SELECT Client_ID, PaymentDate, AmountPaidFROM(SELECT Client_ID, PaymentDate, AmountPaid,

(SELECTCOUNT(*)FROM tblPaymentReceiptsAS a

WHERE(PaymentDate>= b.PaymentDate)AND(Client_ID= b.Client_ID))AS RowNum

FROM tblPaymentReceiptsAS b

)AS t

WHERE RowNum= 2--RowNum=1 for latest payment

|||

These options are much faster to run, however, they are not supoorted as views and I need this to work in a view. the OVER is not supported in SQL Server Express and/or Views.

Dave's option is not as fast but will work in a view.

Thank you both very much,

|||

--SQL Server 2005

CREATEVIEW [dbo].[View_2]

AS

SELECT Client_ID, PaymentDate, AmountPaid

FROM(SELECT Client_ID, PaymentDate, AmountPaid, ROW_Number()OVER(PARTITIONBY Client_ID

ORDERBY PaymentDateDESC)AS RowNum

FROM tblPaymentReceipts)AS t

WHERE RowNum= 2

It shows the error message of OVER not supported at the IDE window, but the view will run correctly.

|||

perhaps I misunderstood the problem, but I thought he was trying to return BOTH the current payment and the previous payment info.?

|||

You are right.

As long as he can work along these logics, it should be straight to get customized results.