Need appending help?

[asilva]

Hi everyone,

I am trying to append about 7 huge(400k rows) database tables into one 'master' table but it's not working. The tables are all formatted with the same column names but I keep getting the "duplicate output destination" error and I'm not sure what's going wrong. Any tips for a access noob?

[asilva]

Any idea anyone?

[pbaldy]

What is the SQL of your query? It sounds like a field is repeated.

[asilva]

This is the SQL for the Query and it states that there is too many fields defined?

INSERT INTO Master
SELECT H1B_efile_external_FY02_web_1.*, H1B_efile_external_FY03_web_1.*, H1B_efile_external_FY04_web.*, H1B_FY05_Efile_External.*, H1B_efile_FY06.*, H1B_efile_FY07.*, H1B_efile_FY08.*
FROM H1B_efile_external_FY02_web AS H1B_efile_external_FY02_web_1, H1B_efile_external_FY03_web AS H1B_efile_external_FY03_web_1, H1B_efile_external_FY04_web, H1B_efile_FY06, H1B_efile_FY07, H1B_efile_FY08, H1B_FY05_Efile_External;

[pbaldy]

Presuming each table has Field1, Field2 and Field3, that's going to produce

Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field1, Table2.Field2, Table2.Field3,...

not

Table1.Field1, Table1.Field2, Table1.Field3
Table2.Field1, Table2.Field2, Table2.Field3

Without joins, you'd also get a Cartesian product, but I suspect that's the least of your problems. I suspect you want a UNION query to stack the data from those various sources, and base your append query on that.

[asilva]

Presuming each table has Field1, Field2 and Field3, that's going to produce

Table1.Field1, Table1.Field2, Table1.Field3, Table2.Field1, Table2.Field2, Table2.Field3,...

not

Table1.Field1, Table1.Field2, Table1.Field3
Table2.Field1, Table2.Field2, Table2.Field3

I believe so?

Without joins, you'd also get a Cartesian product, but I suspect that's the least of your problems. I suspect you want a UNION query to stack the data from those various sources, and base your append query on that.

I'm not sure what a Union query will do for me... How about this, how can I change the data type in a field of 400k+ data points? My computer states that it doesn't have enough memory to do so?

[pbaldy]

What are the fields in Master and one or two of the others?