Same input multiple outputs in append queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this has worked before, but suddenly I can no longer get an append
query to add a field to several fields..e.g.

INSERT INTO [<Pipeline>] (... COST, UNDERWRITE, HOLD, [TYPE OF
TRANSACTION],... Prob_of_Close )
SELECT ... LMS.[Rem Commit 12/31], LMS.[Rem Commit 12/31], LMS.[Rem Commit
12/31], LMS.[Agreement Type Desc],...status.statpercent
FROM LMS INNER JOIN status ON LMS.Status = status.Status
WHERE ((LMS.used) Is Null);

the values within LMS.[Rem Commit 12/31] will append to the COST field but
not to UNDERWRITE or HOLD in the <Pipeline> Table. The LMS table comes from
appending an Excel Spreadsheet and I have confirmed that all the fields are
of consistent type and size.

I am "developing" this query in Office 2000 Pro under Windows 2000 Pro, but
I do know several of my colleagues have already upgraded to XP Pro with
Office 2003 Pro. Is there a possibility their "playing around with" the query
have caused this to no longer work??
 
Thanx for your reply..

I have a query that deletes all the records in the LMS table, then imports
the "new" LMS data from an Excel Spreadsheet. I do it this way to ensure the
Field type and size remain consistent with each run of the proc..

The problem is that UNDERWRITE and HOLD are NOT receiving the data from the
query..I want them to, so that when the query finishes all 3 fields in the
<Pipeline> table (COST, UNDERWRITE and HOLD) will all have the same value...

I have checked the query against earlier versions and found nothing
changed..but I do now there are others with sufficient expertise that might
"modify the query", run it, then "not save" it. Could doing that in 2003
impact my 2000 version somehow??

'Preciate your input


Chris2 said:
BAC said:
I know this has worked before, but suddenly I can no longer get an append
query to add a field to several fields..e.g.

INSERT INTO [<Pipeline>] (... COST, UNDERWRITE, HOLD, [TYPE OF
TRANSACTION],... Prob_of_Close )
SELECT ... LMS.[Rem Commit 12/31], LMS.[Rem Commit 12/31], LMS.[Rem Commit
12/31], LMS.[Agreement Type Desc],...status.statpercent
FROM LMS INNER JOIN status ON LMS.Status = status.Status
WHERE ((LMS.used) Is Null);

the values within LMS.[Rem Commit 12/31] will append to the COST field but
not to UNDERWRITE or HOLD in the <Pipeline> Table.
Click to expand...

BAC,

In the INSERT statement as shown, it appears LMS.[Rem Commit 12/31]
values will wind up in UNDERWRITE and HOLD, as well as COST. If
UNDERWRITE and HOLD aren't receiving values here, then the INSERT will
have to be modified to stop doing that.


The LMS table comes from appending an Excel Spreadsheet and I have
confirmed that all the fields are of consistent type and size.
Click to expand...

I'm not sure I understand. It's certainly possible to link an Excel
Spreadsheet as a Table. But what difference does it make if something
appended records onto the spreadsheet before the linked LMS Table
takes part in the SELECT sub-statement of the INSERT statement?


I am "developing" this query in Office 2000 Pro under Windows 2000
Click to expand...
Pro, but

I'm running Office 2000 Pro and Win2K Pro (SP-4), and Access 2000 SP-3
plus JET 4.0 SP-8, as well.

I do know several of my colleagues have already upgraded to XP Pro with
Office 2003 Pro.
Click to expand...

Are your colleagues using the same code/queries you are and not
experiencing any problems?

Is there a possibility their "playing around with" the query
have caused this to no longer work??
Click to expand...

Yes.

Get copies of the original, unaltered query from your regular backups
(or from your change control software, if any), and compare the
current and original versions.


Sincerely,

Chris O.
Click to expand...
 
BAC said:
I know this has worked before, but suddenly I can no longer get an append
query to add a field to several fields..e.g.

INSERT INTO [<Pipeline>] (... COST, UNDERWRITE, HOLD, [TYPE OF
TRANSACTION],... Prob_of_Close )
SELECT ... LMS.[Rem Commit 12/31], LMS.[Rem Commit 12/31], LMS.[Rem Commit
12/31], LMS.[Agreement Type Desc],...status.statpercent
FROM LMS INNER JOIN status ON LMS.Status = status.Status
WHERE ((LMS.used) Is Null);

the values within LMS.[Rem Commit 12/31] will append to the COST field but
not to UNDERWRITE or HOLD in the <Pipeline> Table.
Click to expand...

BAC,

In the INSERT statement as shown, it appears LMS.[Rem Commit 12/31]
values will wind up in UNDERWRITE and HOLD, as well as COST. If
UNDERWRITE and HOLD aren't receiving values here, then the INSERT will
have to be modified to stop doing that.


The LMS table comes from appending an Excel Spreadsheet and I have
confirmed that all the fields are of consistent type and size.
Click to expand...

I'm not sure I understand. It's certainly possible to link an Excel
Spreadsheet as a Table. But what difference does it make if something
appended records onto the spreadsheet before the linked LMS Table
takes part in the SELECT sub-statement of the INSERT statement?


I am "developing" this query in Office 2000 Pro under Windows 2000
Click to expand...
Pro, but

I'm running Office 2000 Pro and Win2K Pro (SP-4), and Access 2000 SP-3
plus JET 4.0 SP-8, as well.

I do know several of my colleagues have already upgraded to XP Pro with
Office 2003 Pro.
Click to expand...

Are your colleagues using the same code/queries you are and not
experiencing any problems?

Is there a possibility their "playing around with" the query
have caused this to no longer work??
Click to expand...

Yes.

Get copies of the original, unaltered query from your regular backups
(or from your change control software, if any), and compare the
current and original versions.


Sincerely,

Chris O.
 
What I'd expect -> but it's not happening...
Thanx for the effort..


Chris2 said:
BAC said:
Thanx for your reply..

I have a query that deletes all the records in the LMS table, then imports
the "new" LMS data from an Excel Spreadsheet. I do it this way to ensure the
Field type and size remain consistent with each run of the proc..

The problem is that UNDERWRITE and HOLD are NOT receiving the data from the
query..I want them to, so that when the query finishes all 3 fields in the
<Pipeline> table (COST, UNDERWRITE and HOLD) will all have the same value...

I have checked the query against earlier versions and found nothing
changed..but I do now there are others with sufficient expertise that might
"modify the query", run it, then "not save" it. Could doing that in 2003
impact my 2000 version somehow??

'Preciate your input


Chris2 said:
I know this has worked before, but suddenly I can no longer get an
append
query to add a field to several fields..e.g.

INSERT INTO [<Pipeline>] (... COST, UNDERWRITE, HOLD, [TYPE OF
TRANSACTION],... Prob_of_Close )
SELECT ... LMS.[Rem Commit 12/31], LMS.[Rem Commit 12/31], LMS.[Rem
Commit
12/31], LMS.[Agreement Type Desc],...status.statpercent
FROM LMS INNER JOIN status ON LMS.Status = status.Status
WHERE ((LMS.used) Is Null);

the values within LMS.[Rem Commit 12/31] will append to the COST
field but
not to UNDERWRITE or HOLD in the <Pipeline> Table.
Click to expand...
Click to expand...

BAC,

Oh! Ok, now I understand. Hmm, the INSERT appears to be written
correctly.

INSERT INTO [<Pipeline>]
(COST
,UNDERWRITE
,HOLD
,[TYPE OF TRANSACTION]
,Prob_of_Close )
SELECT LMS.[Rem Commit 12/31]
,LMS.[Rem Commit 12/31]
,LMS.[Rem Commit 12/31]
,LMS.[Agreement Type Desc]
,status.statpercent
FROM LMS
INNER JOIN
status
ON LMS.Status = status.Status
WHERE ((LMS.used) Is Null);



CREATE TABLE [<pipeline>]
([pipeline_id] AUTOINCREMENT
,COST CURRENCY
,UNDERWRITE CURRENCY
,HOLD CURRENCY
,[TYPE OF TRANSACTION] TEXT(255)
,Prob_of_Close DOUBLE
,CONSTRAINT pk_pipeline PRIMARY KEY ([pipeline_id])
)


CREATE TABLE status
(status_id AUTOINCREMENT
,status LONG
,statpercent DOUBLE
,CONSTRAINT pk_status PRIMARY KEY (status_id)
)

status statpercent
1 10
1 20
1 30
2 20
2 30
2 40
3 30
3 40
3 50


CREATE TABLE LMS
(LMS_id AUTOINCREMENT
,[Rem Commit 12/31] CURRENCY
,[Agreement Type Desc] TEXT(255)
,status LONG
,used LONG
,CONSTRAINT pk_LMS PRIMARY KEY (LMS_id)
)

[Rem Commit 12/31] [Agreement Type Desc] status used
10.10 Friendly 1 1
20.20 Not-So-Friendly 2 1
30.30 Unfriendly 3 1
40.40 Friendly 1 1
50.50 Not-So-Friendly 2 1
60.60 Unfriendly 3
70.70 Friendly 1
80.80 Not-So-Friendly 2
90.90 Unfriendly 3


I then execute the INSERT query above.

When I create the tables, load them with date, and execute the query,
all three columns, COST, UNDERWRITE, and HOLD are loaded with the data
from [Rem Commit 12/31].


Sincerely,

Chris O.
Click to expand...
 
BAC said:
Thanx for your reply..

I have a query that deletes all the records in the LMS table, then imports
the "new" LMS data from an Excel Spreadsheet. I do it this way to ensure the
Field type and size remain consistent with each run of the proc..

The problem is that UNDERWRITE and HOLD are NOT receiving the data from the
query..I want them to, so that when the query finishes all 3 fields in the
<Pipeline> table (COST, UNDERWRITE and HOLD) will all have the same value...

I have checked the query against earlier versions and found nothing
changed..but I do now there are others with sufficient expertise that might
"modify the query", run it, then "not save" it. Could doing that in 2003
impact my 2000 version somehow??

'Preciate your input


Chris2 said:
BAC said:
I know this has worked before, but suddenly I can no longer get
Click to expand...
an
append
query to add a field to several fields..e.g.

INSERT INTO [<Pipeline>] (... COST, UNDERWRITE, HOLD, [TYPE OF
TRANSACTION],... Prob_of_Close )
SELECT ... LMS.[Rem Commit 12/31], LMS.[Rem Commit 12/31],
Click to expand...
LMS.[Rem
Commit
12/31], LMS.[Agreement Type Desc],...status.statpercent
FROM LMS INNER JOIN status ON LMS.Status = status.Status
WHERE ((LMS.used) Is Null);

the values within LMS.[Rem Commit 12/31] will append to the COST field but
not to UNDERWRITE or HOLD in the <Pipeline> Table.
Click to expand...
Click to expand...
Click to expand...

BAC,

Oh! Ok, now I understand. Hmm, the INSERT appears to be written
correctly.

INSERT INTO [<Pipeline>]
(COST
,UNDERWRITE
,HOLD
,[TYPE OF TRANSACTION]
,Prob_of_Close )
SELECT LMS.[Rem Commit 12/31]
,LMS.[Rem Commit 12/31]
,LMS.[Rem Commit 12/31]
,LMS.[Agreement Type Desc]
,status.statpercent
FROM LMS
INNER JOIN
status
ON LMS.Status = status.Status
WHERE ((LMS.used) Is Null);



CREATE TABLE [<pipeline>]
([pipeline_id] AUTOINCREMENT
,COST CURRENCY
,UNDERWRITE CURRENCY
,HOLD CURRENCY
,[TYPE OF TRANSACTION] TEXT(255)
,Prob_of_Close DOUBLE
,CONSTRAINT pk_pipeline PRIMARY KEY ([pipeline_id])
)


CREATE TABLE status
(status_id AUTOINCREMENT
,status LONG
,statpercent DOUBLE
,CONSTRAINT pk_status PRIMARY KEY (status_id)
)

status statpercent
1 10
1 20
1 30
2 20
2 30
2 40
3 30
3 40
3 50


CREATE TABLE LMS
(LMS_id AUTOINCREMENT
,[Rem Commit 12/31] CURRENCY
,[Agreement Type Desc] TEXT(255)
,status LONG
,used LONG
,CONSTRAINT pk_LMS PRIMARY KEY (LMS_id)
)

[Rem Commit 12/31] [Agreement Type Desc] status used
10.10 Friendly 1 1
20.20 Not-So-Friendly 2 1
30.30 Unfriendly 3 1
40.40 Friendly 1 1
50.50 Not-So-Friendly 2 1
60.60 Unfriendly 3
70.70 Friendly 1
80.80 Not-So-Friendly 2
90.90 Unfriendly 3


I then execute the INSERT query above.

When I create the tables, load them with date, and execute the query,
all three columns, COST, UNDERWRITE, and HOLD are loaded with the data
from [Rem Commit 12/31].


Sincerely,

Chris O.
 
BAC said:
What I'd expect -> but it's not happening...
Thanx for the effort..
Click to expand...

BAC,

Try simplifying.

Save Copies of your tables under temporary names. Chop out all
non-key columns, saving only COST, UNDERWRITE, and HOLD, and the few
fields from the other tables necessary run the Query.

Delete all but about 5-6 rows in the source tables.

Delete all rows from <pipeline>

Then execute a pared-down version of the Query (as posted, but without
columns implicitly noted by the ...).

See what happens . . . perhaps there is a data error in the source
tables (although it seems unlikely, given that COST was being loaded).


Sincerely,

Chris O.
 
Back
Top