Converting records to fields 1

[HowardVenable]

Objective:
Transferring data from one table to the other table.

Problem: DB1 table has many fields (over 50), Runs linearly covering many chapters.

BASE / MAJCOM / YEAR / CH1_4_1 / CH1_4_1_Cost / CH1_4_1_Date
Text ! Text ! Date ! Number ! Currency ! Date
-------------------------------------------------------------
/ CH1_Remarks / and continues on linearly up to chp12
! Text !
_____________________________________________________________
DB2 table has many fields that when filtered out equal each chapter group.

Key ID ) (Foreign ID- Chapter#)
ID Vio / Per / Estimated Cost / Date Estimated / ID SubSec /
Auto# !Number! Currency ! Date ! Text !


Solutions tried:
(1) Querying each record in DB2 filter with specific chapter
" 1. 4. 1"
Then tried to append each with query to DB1 Table one at a time. On second query append error message "Can't append all the records in append query" MS Access set 0 field(s) to Null due to type conversion failure, ......"

(2) Queried all queries to one to produce table or query. Can only query up to 10 at a time before query becomes too complex. And remains complex if the queries of 10 are queried together.

(3) About to try linking an Excel table to and from Access tables as an inverter. Should work and it would be nice to write a VBA or Sequel program to do it and keep it with in the access DB but I don't have the experience.

I need help from a gurus......Anybody? Please.

 

[Remou]

Hi
Solution 1 should work. If you include the SQL for your query and the full error message, you will get a reply more quickly.

 

[HowardVenable]

Hello
Thank you for the tip Remou.

Here is the SQL for the first couple of queries and some notes.

Keep in mind that on the DB1 table the three feilds BASE, MAJCOM & YEAR are a key.

I have set them and any text to Allow Zero Length

INSERT INTO Chpt1_4 ( BASE, MAJCOM, [YEAR], CH1_4_1, CH1_4_1_Cost, CH1_4_1_Date )
SELECT [7 Fire Department].Base, [7 Fire Department].Majcom, [7 Fire Department].Year, [4 Deficiency].Per AS CH1_4_1, [4 Deficiency].[Estimated Cost] AS CH1_4_1_Cost, [4 Deficiency].[Date Estimated] AS CH1_4_1_Date
FROM [7 Fire Department] INNER JOIN [4 Deficiency] ON [7 Fire Department].[ID Shop] = [4 Deficiency].[ID Shop]
WHERE ((([4 Deficiency].[ID Sub Sec])=" 1. 4. 1"));

INSERT INTO Chpt1_4 ( BASE, MAJCOM, [YEAR], CH1_4_1, CH1_4_1_Cost, CH1_4_1_Date )
SELECT [7 Fire Department].Base, [7 Fire Department].Majcom, [7 Fire Department].Year, [4 Deficiency].Per AS CH1_4_1, [4 Deficiency].[Estimated Cost] AS CH1_4_1_Cost, [4 Deficiency].[Date Estimated] AS CH1_4_1_Date
FROM [7 Fire Department] INNER JOIN [4 Deficiency] ON [7 Fire Department].[ID Shop] = [4 Deficiency].[ID Shop]
WHERE ((([4 Deficiency].[ID Sub Sec])=" 1. 4. 1"));

 

[Remou]

Hi
The two queries you have included are identical and might cause a problem in key fields, if this is not a typo. You missed including the error text, so I am guessing that you may have such an error.

 

[HowardVenable]

Thanks for catching that,
There identical and the second line was to read as:

INSERT INTO Chpt1_4 ( BASE, MAJCOM, [YEAR], CH1_4_2, CH1_4_2_Cost, CH1_4_2_Date, CH1_Remarks)
SELECT [7 Fire Department].Base, [7 Fire Department].Majcom, [7 Fire Department].Year, [4 Deficiency].Per AS CH1_4_2, [4 Deficiency].[Estimated Cost] AS CH1_4_2_Cost, [4 Deficiency].[Date Estimated] AS CH1_4_1_Date, TableTrans1_4_1C.Comments & " " & TableTrans1_4_2C.Comments AS CH1_Remarks
FROM TableTrans1_4_1C, TableTrans1_4_2C, [7 Fire Department] INNER JOIN [4 Deficiency] ON [7 Fire Department].[ID Shop] = [4 Deficiency].[ID Shop]
WHERE ((([4 Deficiency].[ID Sub Sec])=" 1. 4. 2"));

I have it working as a quick fix using a macro exporting and linking with several excel workbooks. It's like duct tape it works for now but, definitely not professional or secure and very messy.


Keep in mind when I append them from one small table DBase2 to the other table with many chapter columns, they are all going to one record in DBase1. (Many records DB2 to One Record DB1 with many columns).

 

[Remou]

You mentioned that the first query works but that the second fails with an error message. What is the error message in full?
By the way, you seem to be de-normalising (un-normalising?) your table, which is a little strange.

 

[PHV]

What error ? Probably a duplicate key.
You should use an UPDATE query instead of an APPEND query for each (BASE, MAJCOM, [YEAR]) already inserted.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HowardVenable]

Hi,
Tried Updating again but, this time I did it after the Appending and it worked. Problem solved.

The key/trick- Append first then use Update there after.

Thanks for all your help Remou & PHV.