I am Stumped!

I am Stumped!

Post by Leah Bake » Thu, 04 Nov 1999 04:00:00



I have a SQL Server 7 problem I hope someone cn help me with. On SQL server
we have a table that is used primarily for reporting, but some of the fields
are updated by the user.The problem I have is that a copy of this table is
"on the road" as an MS Access table. I will try to illustrate this as:

SQL Table:

MyTable -
Key1, Fld2, Fld3, Fld4, Fld5

AccTable (Copy of MyTable on laptop);
Key1, Fld2, Fld3, Fld4, Fld5

The users of the SQL table update Fld4 and Fld5 and never create new
records. The user of the Access table uses Fld2, Fld3 and sometimes creates
new records (assigning Key1 sequentially). When the laptop docks I first use
a DTS to convert AccTable to a SQL Server table. At this point I need to
update Fld2, Fld3 with data from AccTable but MyTable Fld4, Fld5 must be
unaltered as these field are updated by the user on SQL Server. Also because
there may be new records added to AccTable that do not exist in MyTable, I
need to insert Key1, Fld2, Fld3 into MyTable and leave Fld4, Fld5 blank for
the new records.

Hope I have been clear enough.  How might I accomplish this?

 
 
 

I am Stumped!

Post by Abdul Gil » Thu, 04 Nov 1999 04:00:00


Transfer the table from Access TO SQL Server, call it T1.  Say the table in
SQL Server is called T2. Run:

UPDATE T1
SET T1.Fld4 = T2.Fld4, T1.Fld5 = T2.Fld5
FROM T1 INNER JOIN T2 ON (T1.Key1 = T2.Key1)
GO

When the Access user creates new records, he should not be populating Fld4,
Fld5 anyway if can live with it.
If he does populate these two fields and you do need to clear them tehn run
another update.

SELECT MAX(Key1) from T2
GO

UPDATE T1
SET T1.Fld4 = '', T1.Fld5 = ''
WHERE T1.Key1 > XYZ  --[The value returned by your select statement]

Then drop or rename T2.  Rename T1 to T2

Abdul Gill
770-779-3258


>I have a SQL Server 7 problem I hope someone cn help me with. On SQL server
>we have a table that is used primarily for reporting, but some of the
fields
>are updated by the user.The problem I have is that a copy of this table is
>"on the road" as an MS Access table. I will try to illustrate this as:

>SQL Table:

>MyTable -
>Key1, Fld2, Fld3, Fld4, Fld5

>AccTable (Copy of MyTable on laptop);
>Key1, Fld2, Fld3, Fld4, Fld5

>The users of the SQL table update Fld4 and Fld5 and never create new
>records. The user of the Access table uses Fld2, Fld3 and sometimes creates
>new records (assigning Key1 sequentially). When the laptop docks I first
use
>a DTS to convert AccTable to a SQL Server table. At this point I need to
>update Fld2, Fld3 with data from AccTable but MyTable Fld4, Fld5 must be
>unaltered as these field are updated by the user on SQL Server. Also
because
>there may be new records added to AccTable that do not exist in MyTable, I
>need to insert Key1, Fld2, Fld3 into MyTable and leave Fld4, Fld5 blank for
>the new records.

>Hope I have been clear enough.  How might I accomplish this?


 
 
 

I am Stumped!

Post by Mike MacGrego » Thu, 04 Nov 1999 04:00:00


I don't think this answered Leah's question.

For the update of Fld1 & Fld2, use the SQL you suggested but replace Fld4
with Fld1 and Fld5 with Fld2.

Then Leah will need to do an INSERT for those records added to the AccTable
but not MyTable.

INSERT MyTable (Key1, Fld2, Fld3)
    SELECT Key1, Fld2, Fld3
        FROM AccTable
        WHERE Key1 NOT IN (SELECT Key1 FROM MyTable)

The NOT IN clause is not particularly efficient but it should work.

Mike MacGregor
Database Developer
EMJ Data Systems


> Transfer the table from Access TO SQL Server, call it T1.  Say the table
in
> SQL Server is called T2. Run:

> UPDATE T1
> SET T1.Fld4 = T2.Fld4, T1.Fld5 = T2.Fld5
> FROM T1 INNER JOIN T2 ON (T1.Key1 = T2.Key1)
> GO

> When the Access user creates new records, he should not be populating
Fld4,
> Fld5 anyway if can live with it.
> If he does populate these two fields and you do need to clear them tehn
run
> another update.

> SELECT MAX(Key1) from T2
> GO

> UPDATE T1
> SET T1.Fld4 = '', T1.Fld5 = ''
> WHERE T1.Key1 > XYZ  --[The value returned by your select statement]

> Then drop or rename T2.  Rename T1 to T2

> Abdul Gill
> 770-779-3258


> >I have a SQL Server 7 problem I hope someone cn help me with. On SQL
server
> >we have a table that is used primarily for reporting, but some of the
> fields
> >are updated by the user.The problem I have is that a copy of this table
is
> >"on the road" as an MS Access table. I will try to illustrate this as:

> >SQL Table:

> >MyTable -
> >Key1, Fld2, Fld3, Fld4, Fld5

> >AccTable (Copy of MyTable on laptop);
> >Key1, Fld2, Fld3, Fld4, Fld5

> >The users of the SQL table update Fld4 and Fld5 and never create new
> >records. The user of the Access table uses Fld2, Fld3 and sometimes
creates
> >new records (assigning Key1 sequentially). When the laptop docks I first
> use
> >a DTS to convert AccTable to a SQL Server table. At this point I need to
> >update Fld2, Fld3 with data from AccTable but MyTable Fld4, Fld5 must be
> >unaltered as these field are updated by the user on SQL Server. Also
> because
> >there may be new records added to AccTable that do not exist in MyTable,
I
> >need to insert Key1, Fld2, Fld3 into MyTable and leave Fld4, Fld5 blank
for
> >the new records.

> >Hope I have been clear enough.  How might I accomplish this?

 
 
 

1. I AM STUMPED!!!

I have a form on which I have placed a masked edit control with an input mask
of ###-####-#.  The user enters an account number in the box, and then when an
OK button is clicked, I assign the contents of the edit box to an acctno
property of a customer object I created in a class module.  I then use the LEFT
function to retrieve the first three digits of the acctno property, and assign
them to a routeno property for the same object.  The code I am using is as
follows:

dim route$
With customer
.acctno=mskAcctNo.text
route$=Left(mskAcctNo.text,3)
.routeno=route$

As an alternative, I have tried:
With customer
.acctno=mskAcctNo.text
.routeno=Left(mskAcctNo.text,3)

When I examine the value of the routeno property after the assignment, I only
get the first two digits of the string, rather than the first three.

However, when I examine the value of route$ I get all three digits.  

I tried setting all my private variables as both string and variant data types,
but the  problem still persists.  I am out of options as to how to set the
routeno property to the first three digits of this user-entered value.  Does
anyone have ANY suggestions?

Thanks for any help provided!!!

Greg

2. Informix upgrade

3. How easy/difficult is it to go from Access to Oracle?

4. Help I am stumped again.

5. How to Size a Window?

6. I am totally stumped by this!!

7. sp_executesql and cursor types

8. error ORA-01855: AM/A.M. or PM/P.M. required

9. Busy Day = Slowdown from 12 AM - 5 AM

10. Use of @am, Am I dumb?

11. I am getting this message when i am tring to export or import anything using

12. Stumped...Insert Date with Stored Procedure using .CreateParameter(...)