error -3087 insert query

I

icccapital

I have an insert query that the select part runs fine because I can remove
the append or insert portion and get a table back from the select query by
itself. But when I add the insert into I receive the error Reserved Error
(-3087); there is no message for this error.

I have checked and the Select query returns the same number of fields as the
insert into so that seems right and the insert into has the correct field
names. I am running access 2007 and the query is complex, but shouldn't be
outside of access' abilities.

Does anyone have any suggestions.

I included the query below, although I don't think it will be helpful:

INSERT INTO
Invoice ( [ClientCode], [ClientName], [Market Value], InvDate, [Tier 1 Top
Value], [Tier 1 Rate], [Tier 1 Bill], [Tier 2 Top Value], [Tier 2 Rate],
[Tier 2 Bill], [Tier 3 Top Value], [Tier 3 Rate], [Tier 3 Bill], [Tier 4 Top
Value], [Tier 4 Rate], [Tier 4 Bill], [Above Tiers Value], [Above Tiers
Rate], [Above Tiers Bill], [Total Bill], [Arrears], [Last Modified], [User])
SELECT
Q2.ClientCode, Q2.[Client Name], Sum(Appraisals.MarketValue) AS Expr1,
Appraisals.ReportDate AS InvDate, IIf(Q2.[Tier 1 Top
Value]<Sum (Appraisals.MarketValue), Q2.[Tier 1 Top
Value],Sum(Appraisals.MarketValue)) AS [Tier 1], Q2.[Tier 1 Rate],
(Int(Q2.[Tier 1 Rate]*[Tier 1]/4+0.5)/100) AS [Tier 1 Bill], IIf(Q2.[Tier 1
Top Value]>Sum(Appraisals.MarketValue) Or Q2.[Tier 2 Top
Value]=0,0,IIf(Q2.[Tier 2 Top
Value]>Sum(Appraisals.MarketValue),Sum(Appraisals.MarketValue)-Q2.[Tier 1 Top
Value],Q2.[Tier 2 Top Value]-Q2.[Tier 1 Top Value])) AS [tier 2], Q2.[Tier 2
Rate], IIf([Tier 2]>0,(Int(Q2.[Tier 2 Rate]*[Tier 2]/4)/100),0) AS [Tier 2
Bill], IIf(Q2.[Tier 2 Top Value]> Sum(Appraisals.MarketValue) Or Q2.[Tier 3
Top Value]=0,0,IIf(Q2.[Tier 3 Top
Value]> Sum(Appraisals.MarketValue),Sum(Appraisals.MarketValue)-Q2.[Tier 2
Top Value],Q2. [Tier 3 Top Value]-Q2.[Tier 2 Top Value])) AS [Tier 3],
Q2.[Tier 3 Rate], IIf([Tier 3]>0,(Int (Q2.[Tier 3 Rate]*[Tier 3]/4)/100),0)
AS [Tier 3 Bill], IIf(Q2.[Tier 3 Top Value]>Sum (Appraisals.MarketValue) Or
Q2.[Tier 4 Top Value]=0,0,IIf(Q2.[Tier 4 Top
Value]>Sum (Appraisals.MarketValue),Sum(Appraisals.MarketValue)-Q2.[Tier 3
Top Value],Q2.[Tier 4 Top Value]-Q2.[Tier 3 Top Value])) AS [Tier 4],
Q2.[Tier 4 Rate], IIf([Tier 4]>0,(Int(Q2. [Tier 4 Rate]*[Tier 4]/4)/100),0)
AS [Tier 4 Bill], IIf(Sum(Appraisals.MarketValue)>Q2.[Tier 1 Top
Value],IIf(Sum(Appraisals.MarketValue)>Q2.[Tier 2 Top
Value],IIf(Sum (Appraisals.MarketValue)>Q2.[Tier 3 Top
Value],IIf(Sum(Appraisals.MarketValue)>Q2. [Tier 4 Top Value],IIf([Tier
4]>0,Sum(Appraisals.MarketValue)-Q2.[Tier 4 Top Value],IIf ([Tier
3]>0,Sum(Appraisals.MarketValue)-Q2.[Tier 3 Top Value],IIf([Tier
2]>0,Sum (Appraisals.MarketValue)-Q2.[Tier 2 Top
Value],Sum(Appraisals.MarketValue)-Q2.[Tier 1 Top Value]))),0),0),0),0) AS
[Above Tiers Value], Q2.[Above Tiers Rate], IIf([Above Tiers
Value]>0,(Int(Q2.[Above Tiers Rate]*[Above Tiers Value]/4+0.5)/100),0) AS
[Above Tiers Bill], ([Tier 1 Bill]+[Tier 2 Bill]+[Tier 3 Bill]+[Tier 4
Bill]+[Above Tiers Bill]) AS TotalBill, Q2. [Arrears], Now() AS [Last
Modified], fosUserName() AS [User]
FROM
Appraisals
INNER JOIN
(SELECT
ClientCode.ClientCode, ClientCode.[Client Name], ClientCode.[Tier 1 Top
Value], ClientCode.[Tier 2 Top Value] , ClientCode.[Tier 3 Top Value],
ClientCode.[Tier 4 Top Value], ClientCode.[Tier 1 Rate] , ClientCode.[Tier 2
Rate], ClientCode.[Tier 3 Rate], ClientCode.[Tier 4 Rate], ClientCode.[Above
Tiers Rate], ClientCode.OldClientCode, ClientCode.[Arrears] FROM ClientCode
WHERE ClientCode.OldClientCode = True) AS Q2 ON Appraisals.clientcode =
Q2.clientcode
WHERE
Appraisals.ReportDate = #03/06/09# AND Appraisals.SecurityType <> "unus"
AND Appraisals.SecurityType <> "ucus" AND ClientCode.ClientCode IN
('338genx2')
Group BY
ClientCode.ClientCode, ClientCode.[Client Name], Appraisals.ReportDate,
ClientCode. [Tier 1 Top Value], ClientCode.[Tier 2 Top Value],
ClientCode.[Tier 3 Top Value], ClientCode.[Tier 4 Top Value],
ClientCode.[Tier 1 Rate], ClientCode.[Tier 2 Rate], ClientCode.[Tier 3
Rate], ClientCode.[Tier 4 Rate], ClientCode.[Above Tiers Rate] ,
ClientCode.OldClientCode, [Arrears];
 
J

Jeanette Cunningham

Hi icccapital,
there is a step-by-step process you can use to resolve this.
Here is the way I do it-->
Create a new query that appends only one field's data to the table.
When this works correctly, add one more field and repeat. Keep going until
it all works.
This problem solving method does work.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



icccapital said:
I have an insert query that the select part runs fine because I can remove
the append or insert portion and get a table back from the select query by
itself. But when I add the insert into I receive the error Reserved Error
(-3087); there is no message for this error.

I have checked and the Select query returns the same number of fields as
the
insert into so that seems right and the insert into has the correct field
names. I am running access 2007 and the query is complex, but shouldn't
be
outside of access' abilities.

Does anyone have any suggestions.

I included the query below, although I don't think it will be helpful:

INSERT INTO
Invoice ( [ClientCode], [ClientName], [Market Value], InvDate, [Tier 1 Top
Value], [Tier 1 Rate], [Tier 1 Bill], [Tier 2 Top Value], [Tier 2 Rate],
[Tier 2 Bill], [Tier 3 Top Value], [Tier 3 Rate], [Tier 3 Bill], [Tier 4
Top
Value], [Tier 4 Rate], [Tier 4 Bill], [Above Tiers Value], [Above Tiers
Rate], [Above Tiers Bill], [Total Bill], [Arrears], [Last Modified],
[User])
SELECT
Q2.ClientCode, Q2.[Client Name], Sum(Appraisals.MarketValue) AS Expr1,
Appraisals.ReportDate AS InvDate, IIf(Q2.[Tier 1 Top
Value]<Sum (Appraisals.MarketValue), Q2.[Tier 1 Top
Value],Sum(Appraisals.MarketValue)) AS [Tier 1], Q2.[Tier 1 Rate],
(Int(Q2.[Tier 1 Rate]*[Tier 1]/4+0.5)/100) AS [Tier 1 Bill], IIf(Q2.[Tier
1
Top Value]>Sum(Appraisals.MarketValue) Or Q2.[Tier 2 Top
Value]=0,0,IIf(Q2.[Tier 2 Top
Value]>Sum(Appraisals.MarketValue),Sum(Appraisals.MarketValue)-Q2.[Tier 1
Top
Value],Q2.[Tier 2 Top Value]-Q2.[Tier 1 Top Value])) AS [tier 2], Q2.[Tier
2
Rate], IIf([Tier 2]>0,(Int(Q2.[Tier 2 Rate]*[Tier 2]/4)/100),0) AS [Tier 2
Bill], IIf(Q2.[Tier 2 Top Value]> Sum(Appraisals.MarketValue) Or Q2.[Tier
3
Top Value]=0,0,IIf(Q2.[Tier 3 Top
Value]> Sum(Appraisals.MarketValue),Sum(Appraisals.MarketValue)-Q2.[Tier 2
Top Value],Q2. [Tier 3 Top Value]-Q2.[Tier 2 Top Value])) AS [Tier 3],
Q2.[Tier 3 Rate], IIf([Tier 3]>0,(Int (Q2.[Tier 3 Rate]*[Tier
3]/4)/100),0)
AS [Tier 3 Bill], IIf(Q2.[Tier 3 Top Value]>Sum (Appraisals.MarketValue)
Or
Q2.[Tier 4 Top Value]=0,0,IIf(Q2.[Tier 4 Top
Value]>Sum (Appraisals.MarketValue),Sum(Appraisals.MarketValue)-Q2.[Tier 3
Top Value],Q2.[Tier 4 Top Value]-Q2.[Tier 3 Top Value])) AS [Tier 4],
Q2.[Tier 4 Rate], IIf([Tier 4]>0,(Int(Q2. [Tier 4 Rate]*[Tier
4]/4)/100),0)
AS [Tier 4 Bill], IIf(Sum(Appraisals.MarketValue)>Q2.[Tier 1 Top
Value],IIf(Sum(Appraisals.MarketValue)>Q2.[Tier 2 Top
Value],IIf(Sum (Appraisals.MarketValue)>Q2.[Tier 3 Top
Value],IIf(Sum(Appraisals.MarketValue)>Q2. [Tier 4 Top Value],IIf([Tier
4]>0,Sum(Appraisals.MarketValue)-Q2.[Tier 4 Top Value],IIf ([Tier
3]>0,Sum(Appraisals.MarketValue)-Q2.[Tier 3 Top Value],IIf([Tier
2]>0,Sum (Appraisals.MarketValue)-Q2.[Tier 2 Top
Value],Sum(Appraisals.MarketValue)-Q2.[Tier 1 Top Value]))),0),0),0),0) AS
[Above Tiers Value], Q2.[Above Tiers Rate], IIf([Above Tiers
Value]>0,(Int(Q2.[Above Tiers Rate]*[Above Tiers Value]/4+0.5)/100),0) AS
[Above Tiers Bill], ([Tier 1 Bill]+[Tier 2 Bill]+[Tier 3 Bill]+[Tier 4
Bill]+[Above Tiers Bill]) AS TotalBill, Q2. [Arrears], Now() AS [Last
Modified], fosUserName() AS [User]
FROM
Appraisals
INNER JOIN
(SELECT
ClientCode.ClientCode, ClientCode.[Client Name], ClientCode.[Tier 1 Top
Value], ClientCode.[Tier 2 Top Value] , ClientCode.[Tier 3 Top Value],
ClientCode.[Tier 4 Top Value], ClientCode.[Tier 1 Rate] , ClientCode.[Tier
2
Rate], ClientCode.[Tier 3 Rate], ClientCode.[Tier 4 Rate],
ClientCode.[Above
Tiers Rate], ClientCode.OldClientCode, ClientCode.[Arrears] FROM
ClientCode
WHERE ClientCode.OldClientCode = True) AS Q2 ON Appraisals.clientcode =
Q2.clientcode
WHERE
Appraisals.ReportDate = #03/06/09# AND Appraisals.SecurityType <> "unus"
AND Appraisals.SecurityType <> "ucus" AND ClientCode.ClientCode IN
('338genx2')
Group BY
ClientCode.ClientCode, ClientCode.[Client Name], Appraisals.ReportDate,
ClientCode. [Tier 1 Top Value], ClientCode.[Tier 2 Top Value],
ClientCode.[Tier 3 Top Value], ClientCode.[Tier 4 Top Value],
ClientCode.[Tier 1 Rate], ClientCode.[Tier 2 Rate], ClientCode.[Tier 3
Rate], ClientCode.[Tier 4 Rate], ClientCode.[Above Tiers Rate] ,
ClientCode.OldClientCode, [Arrears];
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

reserved error -3087 0
Same data - different criteria 1
Running sums and other help 0
Can't figure out formula error 3
Awkward Query Idea / Help!? Percentiles. 2
Query Calculation - #Error 0
query error 12
Calculating sales commission that changes based on a sales volume 2

Top