Insert into existing table

[Mike Hughes]

When I run this query I want the data inserted into an existing table in the correct place. It is inserting the data in the correct column but not the correct rows. Please see attached screenshot. Can you please show me what I'm doing wrong.

INSERT INTO UNDISTRIBUTED ( Jun2003 )
SELECT Sum(CCur(a.AMT_TO_DISTRIBUTE)) AS AMOUNT
FROM NOLDBA_RECEIPT AS a, NOLDBA_INT_CASE_STATUS AS b
WHERE (((Trim([a].[id_case])) Is Not Null) AND ((a.ID_CASE)=.[case_id]) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False)) OR (((Trim([a].[id_case])) Is Null) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False) AND ((b.CASE_ID)=(select min (y.case_id) from NOLDBA_INT_CASE_STATUS Y, NOLDBA_INT_CASE_MEMBER Z WHERE A.ID_PAYOR=Z.MEMBER_ID AND Z.RELATION_CODE IN ('A','P') AND Z.CASE_ID=Y.CASE_ID)))
GROUP BY b.IV_D_DO_CODE
ORDER BY b.IV_D_DO_CODE;

 

[Uncle Gizmo]

I think you need to use the update Function not the insert.

 

[stopher]

Hi Mike

I'm guessing you want the data at the bottom to appear in the rows AA to ZZ in the June 2003 column? If so then you need to design an UPDATE query not an INSERT query.

An INSERT query is used to append new records at the end of the table.

An UPDATE query is used to update current records.

You will need to create a JOIN on a key (presumably District?) in your query so that the query knows which data is going in which row.

hth
Chris

 

[Mike Hughes]

OK so I took the query above and made a table with it, call it UNDISTRIBUTED2. I then created an empty table and called it UNDISTRIBUTED4 - this table has two fields, one called DISTRICT & a field I called Jun2003. I put in the District id's (AA,BB,CC,DD,ETC) into this empty table. Then I ran the query below to update the UNDISTRIBUTED4 table field Jun2003 with the AMOUNT field from UNDISTRIBUTED2. The tables were joined on District and DO fields.

UPDATE UNDISTRIBUTED4 INNER JOIN UNDISTRIBUTED2 ON UNDISTRIBUTED4.DISTRICT = UNDISTRIBUTED2.DO SET UNDISTRIBUTED2.AMOUNT = "Jun2003";

And I get this error:
When I do the update query I get an error something like this:
Microsoft Office Access didn't update 17 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations. There is a help button on the erro screen but it only opens to a blank page.

Can someone help me please, what am I doing wrong besides building databases without any training?

 

[gemma-the-husky]

mike

your original query may have been working ok

the error in your revised query is a type conversion. ie a field you have specified as numeric say, is being updated by your query into a string , and the column cant accept the data.

back to your original query. you say thr rows are being added, but not in the correct order. Now Access doesn't have an order - a dataset (table) is just that - a set of data. You impose your own presentation order on the data, by the use of a suitable query.
in this respect its similar to a spreadsheet. if you pasted extra rows to a spreadsheet, you wouldnt expect them to go anywhere than at the bottom of the table - having added them, you then have to sort them if you want a different order.

Access has quite a steep learning curve - if you are new to it, it wil ltake a bit of time to get used to it all